From f514bb3e2586d3896f4584b9b345f9565bef1f8a Mon Sep 17 00:00:00 2001
From: Bruce Momjian
Date: Tue, 30 Jul 2002 19:36:13 +0000
Subject: Would it be wise to have the plsql.sgml file renamed to plpgsql.sgml?
All of the internal tags are of the latter.
The other thing I noticed is that most of the quick examples in the file
use a para and synopsis. Is there a reason we're not using ?
Rod Taylor
---
doc/src/sgml/filelist.sgml | 4 +-
doc/src/sgml/plpgsql.sgml | 3010 ++++++++++++++++++++++++++++++++++++++++++++
doc/src/sgml/plsql.sgml | 3001 -------------------------------------------
3 files changed, 3012 insertions(+), 3003 deletions(-)
create mode 100644 doc/src/sgml/plpgsql.sgml
delete mode 100644 doc/src/sgml/plsql.sgml
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index d8c5998fb84..d6fdd71561b 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -1,4 +1,4 @@
-
+
@@ -83,7 +83,7 @@
-
+
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 00000000000..9a58e6b8e3b
--- /dev/null
+++ b/doc/src/sgml/plpgsql.sgml
@@ -0,0 +1,3010 @@
+
+
+
+ PL/pgSQL - SQL Procedural Language
+
+
+ PL/pgSQL
+
+
+
+ PL/pgSQL is a loadable procedural language for the
+ PostgreSQL database system.
+
+
+
+ This package was originally written by Jan Wieck. This
+ documentation was in part written
+ by Roberto Mello (rmello@fslc.usu.edu).
+
+
+
+ Overview
+
+
+ The design goals of PL/pgSQL> were to create a loadable procedural
+ language that
+
+
+
+ can be used to create functions and trigger procedures,
+
+
+
+
+ adds control structures to the SQL language,
+
+
+
+
+ can perform complex computations,
+
+
+
+
+ inherits all user defined types, functions and operators,
+
+
+
+
+ can be defined to be trusted by the server,
+
+
+
+
+ is easy to use.
+
+
+
+
+
+ The PL/pgSQL> call handler parses the function's source text and
+ produces an internal binary instruction tree the first time the
+ function is called (within any one backend process). The instruction tree
+ fully translates the
+ PL/pgSQL> statement structure, but individual
+ SQL expressions and SQL queries
+ used in the function are not translated immediately.
+
+
+ As each expression and SQL query is first used
+ in the function, the PL/pgSQL> interpreter creates a
+ prepared execution plan (using the SPI manager's
+ SPI_prepare and
+ SPI_saveplan functions). Subsequent visits
+ to that expression or query re-use the prepared plan. Thus, a function
+ with conditional code that contains many statements for which execution
+ plans might be required, will only prepare and save those plans
+ that are really used during the lifetime of the database
+ connection. This can provide a considerable savings of parsing
+ activity. A disadvantage is that errors in a specific expression
+ or query may not be detected until that part of the function is
+ reached in execution.
+
+
+ Once PL/pgSQL> has made a query plan for a particular
+ query in a function, it will re-use that plan for the life of the
+ database connection. This is usually a win for performance, but it
+ can cause some problems if you dynamically
+ alter your database schema. For example:
+
+
+CREATE FUNCTION populate() RETURNS INTEGER AS '
+DECLARE
+ -- Declarations
+BEGIN
+ PERFORM my_function();
+END;
+' LANGUAGE 'plpgsql';
+
+ If you execute the above function, it will reference the OID for
+ my_function() in the query plan produced for
+ the PERFORM statement. Later, if you
+ drop and re-create my_function(), then
+ populate() will not be able to find
+ my_function() anymore. You would then have to
+ re-create populate(), or at least start a new
+ database session so that it will be compiled afresh.
+
+
+
+ Because PL/pgSQL saves execution plans in this way, queries that appear
+ directly in a PL/pgSQL function must refer to the same tables and fields
+ on every execution; that is, you cannot use a parameter as the name of
+ a table or field in a query. To get around
+ this restriction, you can construct dynamic queries using the PL/pgSQL
+ EXECUTE statement --- at the price of constructing a new query plan
+ on every execution.
+
+
+ Except for input/output conversion and calculation functions
+ for user defined types, anything that can be defined in C language
+ functions can also be done with PL/pgSQL. It is possible to
+ create complex conditional computation functions and later use
+ them to define operators or use them in functional indexes.
+
+
+ Advantages of Using PL/pgSQL
+
+
+
+
+ Better performance (see )
+
+
+
+
+
+ SQL support (see )
+
+
+
+
+
+ Portability (see )
+
+
+
+
+
+ Better Performance
+
+
+ SQL is the language PostgreSQL> (and
+ most other Relational Databases) use as query
+ language. It's portable and easy to learn. But every
+ SQL statement must be executed
+ individually by the database server.
+
+
+
+ That means that your client application must send each
+ query to the database server, wait for it to process it,
+ receive the results, do some computation, then send
+ other queries to the server. All this incurs inter-process communication
+ and may also incur network
+ overhead if your client is on a different machine than
+ the database server.
+
+
+
+ With PL/pgSQL you can group a block of computation and a
+ series of queries inside the
+ database server, thus having the power of a procedural
+ language and the ease of use of SQL, but saving lots of
+ time because you don't have the whole client/server
+ communication overhead. This can make for a
+ considerable performance increase.
+
+
+
+
+ SQL Support
+
+
+ PL/pgSQL adds the power of a procedural language to the
+ flexibility and ease of SQL. With
+ PL/pgSQL you can use all the data types, columns, operators
+ and functions of SQL.
+
+
+
+
+ Portability
+
+
+ Because PL/pgSQL functions run inside PostgreSQL>, these
+ functions will run on any platform where PostgreSQL>
+ runs. Thus you can reuse code and have less development costs.
+
+
+
+
+
+ Developing in PL/pgSQL
+
+
+ Developing in PL/pgSQL is pretty straight forward, especially
+ if you have developed in other database procedural languages,
+ such as Oracle's PL/SQL. Two good ways of developing in
+ PL/pgSQL are:
+
+
+
+
+ Using a text editor and reloading the file with psql
+
+
+
+
+
+ Using PostgreSQL>'s GUI Tool: PgAccess>
+
+
+
+
+
+
+ One good way to develop in PL/pgSQL> is to simply use the text
+ editor of your choice to create your functions, and in another
+ console, use psql (PostgreSQL's interactive monitor) to load
+ those functions. If you are doing it this way, it is a good
+ idea to write the function using CREATE OR REPLACE
+ FUNCTION. That way you can reload the file to update
+ the function definition. For example:
+
+CREATE OR REPLACE FUNCTION testfunc(INTEGER) RETURNS INTEGER AS '
+ ....
+end;
+' LANGUAGE 'plpgsql';
+
+
+
+
+ While running psql, you can load or reload such a
+ function definition file with
+
+ \i filename.sql
+
+ and then immediately issue SQL commands to test the function.
+
+
+
+ Another good way to develop in PL/pgSQL> is using
+ PostgreSQL>'s GUI tool: PgAccess>. It does some
+ nice things for you, like escaping single-quotes, and making
+ it easy to recreate and debug functions.
+
+
+
+
+
+ Structure of PL/pgSQL
+
+
+ PL/pgSQL is a block
+ structured language. The complete text of a function
+ definition must be a block>. A block is defined as:
+
+
+ <<label>>
+ DECLARE
+ declarations
+BEGIN
+ statements
+END;
+
+
+
+
+ Any statement> in the statement section of a block
+ can be a sub-block>. Sub-blocks can be used for
+ logical grouping or to localize variables to a small group
+ of statements.
+
+
+
+ The variables declared in the declarations section preceding a
+ block are initialized to their default values every time the
+ block is entered, not only once per function call. For example:
+
+CREATE FUNCTION somefunc() RETURNS INTEGER AS '
+DECLARE
+ quantity INTEGER := 30;
+BEGIN
+ RAISE NOTICE ''Quantity here is %'',quantity; -- Quantity here is 30
+ quantity := 50;
+ --
+ -- Create a sub-block
+ --
+ DECLARE
+ quantity INTEGER := 80;
+ BEGIN
+ RAISE NOTICE ''Quantity here is %'',quantity; -- Quantity here is 80
+ END;
+
+ RAISE NOTICE ''Quantity here is %'',quantity; -- Quantity here is 50
+
+ RETURN quantity;
+END;
+' LANGUAGE 'plpgsql';
+
+
+
+
+ It is important not to confuse the use of BEGIN/END for
+ grouping statements in PL/pgSQL> with the database commands for
+ transaction control. PL/pgSQL>'s BEGIN/END are only for grouping;
+ they do not start or end a transaction. Functions and trigger procedures
+ are always executed within a transaction established by an outer query
+ --- they cannot start or commit transactions, since
+ PostgreSQL does not have nested transactions.
+
+
+
+ Lexical Details
+
+
+ Each statement and declaration within a block is terminated
+ by a semicolon.
+
+
+
+ All keywords and identifiers can be written in mixed upper- and
+ lower-case. Identifiers are implicitly converted to lower-case
+ unless double-quoted.
+
+
+
+ There are two types of comments in PL/pgSQL>. A double dash --
+ starts a comment that extends to the end of the line. A /*
+ starts a block comment that extends to the next occurrence of */.
+ Block comments cannot be nested, but double dash comments can be
+ enclosed into a block comment and a double dash can hide
+ the block comment delimiters /* and */.
+
+
+
+
+
+ Declarations
+
+
+ All variables, rows and records used in a block must be declared in the
+ declarations section of the block.
+ (The only exception is that the loop variable of a FOR loop iterating
+ over a range of integer values is automatically declared as an integer
+ variable.)
+
+
+
+ PL/pgSQL> variables can have any SQL data type, such as
+ INTEGER, VARCHAR and
+ CHAR.
+
+
+
+ Here are some examples of variable declarations:
+
+user_id INTEGER;
+quantity NUMERIC(5);
+url VARCHAR;
+myrow tablename%ROWTYPE;
+myfield tablename.fieldname%TYPE;
+arow RECORD;
+
+
+
+
+ The general syntax of a variable declaration is:
+
+name CONSTANT type NOT NULL { DEFAULT | := } expression ;
+
+
+
+
+ The DEFAULT clause, if given, specifies the initial value assigned
+ to the variable when the block is entered. If the DEFAULT clause
+ is not given then the variable is initialized to the
+ SQL NULL value.
+
+
+
+ The CONSTANT option prevents the variable from being assigned to,
+ so that its value remains constant for the duration of the block.
+ If NOT NULL
+ is specified, an assignment of a NULL value results in a run-time
+ error. All variables declared as NOT NULL
+ must have a non-NULL default value specified.
+
+
+
+ The default value is evaluated every time the block is entered. So,
+ for example, assigning 'now' to a variable of type
+ timestamp causes the variable to have the
+ time of the current function call, not when the function was
+ precompiled.
+
+
+
+ Examples:
+
+quantity INTEGER DEFAULT 32;
+url varchar := ''http://mysite.com'';
+user_id CONSTANT INTEGER := 10;
+
+
+
+
+ Aliases for Function Parameters
+
+
+
+name ALIAS FOR $n;
+
+
+
+
+ Parameters passed to functions are named with the identifiers
+ $1, $2,
+ etc. Optionally, aliases can be declared for $n
+ parameter names for increased readability. Either the alias or the
+ numeric identifier can then be used to refer to the parameter value.
+ Some examples:
+
+CREATE FUNCTION sales_tax(REAL) RETURNS REAL AS '
+DECLARE
+ subtotal ALIAS FOR $1;
+BEGIN
+ return subtotal * 0.06;
+END;
+' LANGUAGE 'plpgsql';
+
+
+CREATE FUNCTION instr(VARCHAR,INTEGER) RETURNS INTEGER AS '
+DECLARE
+ v_string ALIAS FOR $1;
+ index ALIAS FOR $2;
+BEGIN
+ -- Some computations here
+END;
+' LANGUAGE 'plpgsql';
+
+
+CREATE FUNCTION use_many_fields(tablename) RETURNS TEXT AS '
+DECLARE
+ in_t ALIAS FOR $1;
+BEGIN
+ RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
+END;
+' LANGUAGE 'plpgsql';
+
+
+
+
+
+ Row Types
+
+
+
+name tablename%ROWTYPE;
+
+
+
+
+ A variable of a composite type is called a row>
+ variable (or row-type> variable). Such a variable can hold a
+ whole row of a SELECT or FOR
+ query result, so long as that query's column set matches the declared
+ type of the variable. The individual fields of the row value are
+ accessed using the usual dot notation, for example
+ rowvar.field.
+
+
+
+ Presently, a row variable can only be declared using the
+ %ROWTYPE notation; although one might expect a
+ bare table name to work as a type declaration, it won't be accepted
+ within PL/pgSQL functions.
+
+
+
+ Parameters to a function can be
+ composite types (complete table rows). In that case, the
+ corresponding identifier $n will be a row variable, and fields can
+ be selected from it, for example $1.user_id.
+
+
+
+ Only the user-defined attributes of a table row are accessible in a
+ row-type variable, not OID or other system attributes (because the
+ row could be from a view). The fields of the row type inherit the
+ table's field size or precision for data types such as
+ char(n).
+
+CREATE FUNCTION use_two_tables(tablename) RETURNS TEXT AS '
+DECLARE
+ in_t ALIAS FOR $1;
+ use_t table2name%ROWTYPE;
+BEGIN
+ SELECT * INTO use_t FROM table2name WHERE ... ;
+ RETURN in_t.f1 || use_t.f3 || in_t.f5 || use_t.f7;
+END;
+' LANGUAGE 'plpgsql';
+
+
+
+
+
+ Records
+
+
+
+name RECORD;
+
+
+
+
+ Record variables are similar to row-type variables, but they have no
+ predefined structure. They take on the actual row structure of the
+ row they are assigned during a SELECT or FOR command. The substructure
+ of a record variable can change each time it is assigned to.
+ A consequence of this is that until a record variable is first assigned
+ to, it has no> substructure, and any attempt to access a
+ field in it will draw a run-time error.
+
+
+
+ Note that RECORD> is not a true data type, only a placeholder.
+ Thus, for example, one cannot declare a function returning
+ RECORD>.
+
+
+
+
+ Attributes
+
+
+ Using the %TYPE and %ROWTYPE
+ attributes, you can declare variables with the same
+ data type or structure as another database item (e.g: a
+ table field).
+
+
+
+
+
+ variable%TYPE
+
+
+
+ %TYPE provides the data type of a
+ variable or database column. You can use this to
+ declare variables that will hold database
+ values. For example, let's say you have a column
+ named user_id in your
+ users table. To declare a variable with
+ the same data type as users>.user_id> you write:
+
+user_id users.user_id%TYPE;
+
+
+
+
+ By using %TYPE you don't need to know
+ the data type of the structure you are referencing,
+ and most important, if the data type of the
+ referenced item changes in the future (e.g: you
+ change your table definition of user_id from INTEGER to
+ REAL), you may not need to change your function
+ definition.
+
+
+
+
+
+
+ table%ROWTYPE
+
+
+
+ %ROWTYPE provides the composite data type corresponding
+ to a whole row of the specified table.
+ table must be an existing
+ table or view name of the database.
+
+
+
+DECLARE
+ users_rec users%ROWTYPE;
+ user_id users.user_id%TYPE;
+BEGIN
+ user_id := users_rec.user_id;
+ ...
+
+CREATE FUNCTION does_view_exist(INTEGER) RETURNS bool AS '
+ DECLARE
+ key ALIAS FOR $1;
+ table_data cs_materialized_views%ROWTYPE;
+ BEGIN
+ SELECT INTO table_data * FROM cs_materialized_views
+ WHERE sort_key=key;
+
+ IF NOT FOUND THEN
+ RETURN false;
+ END IF;
+ RETURN true;
+ END;
+' LANGUAGE 'plpgsql';
+
+
+
+
+
+
+
+ RENAME
+
+
+
+RENAME oldname TO newname;
+
+
+ Using the RENAME declaration you can change the name of a variable,
+ record or row. This is primarily useful if NEW or OLD should be
+ referenced by another name inside a trigger procedure. See also ALIAS.
+
+
+
+ Examples:
+
+RENAME id TO user_id;
+RENAME this_var TO that_var;
+
+
+
+
+
+ RENAME appears to be broken as of PostgreSQL 7.2. Fixing this is
+ of low priority, since ALIAS covers most of the practical uses of
+ RENAME.
+
+
+
+
+
+
+
+ Expressions
+
+
+ All expressions used in PL/pgSQL statements
+ are processed using the server's regular SQL executor. Expressions that
+ appear to contain
+ constants may in fact require run-time evaluation
+ (e.g. 'now' for the
+ timestamp type) so
+ it is impossible for the PL/pgSQL parser
+ to identify real constant values other than the NULL keyword. All
+ expressions are evaluated internally by executing a query
+
+SELECT expression
+
+ using the SPI manager. In the expression, occurrences
+ of PL/pgSQL variable
+ identifiers are replaced by parameters and the actual values from
+ the variables are passed to the executor in the parameter array.
+ This allows the query plan for the SELECT to be prepared just once
+ and then re-used for subsequent evaluations.
+
+
+
+ The evaluation done by the PostgreSQL
+ main parser has some side
+ effects on the interpretation of constant values. In detail there
+ is a difference between what these two functions do:
+
+
+CREATE FUNCTION logfunc1 (TEXT) RETURNS TIMESTAMP AS '
+ DECLARE
+ logtxt ALIAS FOR $1;
+ BEGIN
+ INSERT INTO logtable VALUES (logtxt, ''now'');
+ RETURN ''now'';
+ END;
+' LANGUAGE 'plpgsql';
+
+
+ and
+
+
+CREATE FUNCTION logfunc2 (TEXT) RETURNS TIMESTAMP AS '
+ DECLARE
+ logtxt ALIAS FOR $1;
+ curtime timestamp;
+ BEGIN
+ curtime := ''now'';
+ INSERT INTO logtable VALUES (logtxt, curtime);
+ RETURN curtime;
+ END;
+' LANGUAGE 'plpgsql';
+
+
+ In the case of logfunc1(), the
+ PostgreSQL main parser knows when
+ preparing the plan for the INSERT, that the string
+ 'now' should be interpreted as
+ timestamp because the target field of logtable
+ is of that type. Thus, it will make a constant from it at this
+ time and this constant value is then used in all invocations of
+ logfunc1() during the lifetime of the
+ backend. Needless to say that this isn't what the
+ programmer wanted.
+
+
+
+ In the case of logfunc2(), the
+ PostgreSQL main parser does not know
+ what type 'now' should become and therefore
+ it returns a data value of type text containing the string
+ 'now'. During the ensuing assignment
+ to the local variable curtime, the
+ PL/pgSQL interpreter casts this
+ string to the timestamp type by calling the
+ text_out() and timestamp_in()
+ functions for the conversion. So, the computed time stamp is updated
+ on each execution as the programmer expects.
+
+
+
+ The mutable nature of record variables presents a problem in this
+ connection. When fields of a record variable are used in expressions or
+ statements, the data types of the
+ fields must not change between calls of one and the same expression,
+ since the expression will be planned using the data type that is present
+ when the expression is first reached.
+ Keep this in mind when writing trigger procedures that handle events
+ for more than one table. (EXECUTE can be used to get around this
+ problem when necessary.)
+
+
+
+
+ Basic Statements
+
+
+ In this section and the following ones, we describe all the statement
+ types that are explicitly understood by
+ PL/pgSQL.
+ Anything not recognized as one of these statement types is presumed
+ to be an SQL query, and is sent to the main database engine to execute
+ (after substitution for any PL/pgSQL variables
+ used in the statement). Thus,
+ for example, SQL INSERT>, UPDATE>, and
+ DELETE> commands may be considered to be statements of
+ PL/pgSQL. But they are not specifically
+ listed here.
+
+
+
+ Assignment
+
+
+ An assignment of a value to a variable or row/record field is
+ written as:
+
+identifier := expression;
+
+ As explained above, the expression in such a statement is evaluated
+ by means of an SQL SELECT> command sent to the main
+ database engine. The expression must yield a single value.
+
+
+
+ If the expression's result data type doesn't match the variable's
+ data type, or the variable has a specific size/precision
+ (like char(20)), the result value will be implicitly
+ converted by the PL/pgSQL interpreter using
+ the result type's output-function and
+ the variable type's input-function. Note that this could potentially
+ result in run-time errors generated by the input function, if the
+ string form of the result value is not acceptable to the input function.
+
+
+
+ Examples:
+
+user_id := 20;
+tax := subtotal * 0.06;
+
+
+
+
+
+ SELECT INTO
+
+
+ The result of a SELECT command yielding multiple columns (but
+ only one row) can be assigned to a record variable, row-type
+ variable, or list of scalar variables. This is done by:
+
+
+SELECT INTO target expressions FROM ...;
+
+
+ where target can be a record variable, a row
+ variable, or a comma-separated list of simple variables and
+ record/row fields. Note that this is quite different from
+ PostgreSQL>'s normal interpretation of SELECT INTO, which is that the
+ INTO target is a newly created table. (If you want to create a
+ table from a SELECT result inside a PL/pgSQL function, use the
+ syntax CREATE TABLE ... AS SELECT.)
+
+
+
+ If a row or a variable list is used as target, the selected values
+ must exactly match the structure of the target(s), or a run-time error
+ occurs. When a record variable is the target, it automatically
+ configures itself to the row type of the query result columns.
+
+
+
+ Except for the INTO clause, the SELECT statement is the same as a normal
+ SQL SELECT query and can use the full power of SELECT.
+
+
+
+ If the SELECT query returns zero rows, NULLs are assigned to the
+ target(s). If the SELECT query returns multiple rows, the first
+ row is assigned to the target(s) and the rest are discarded.
+ (Note that the first row> is not well-defined unless you've
+ used ORDER BY.)
+
+
+
+ At present, the INTO clause can appear almost anywhere in the SELECT
+ query, but it is recommended to place it immediately after the SELECT
+ keyword as depicted above. Future versions of
+ PL/pgSQL may be less forgiving about
+ placement of the INTO clause.
+
+
+
+ There is a special variable named FOUND of type
+ boolean that can be used immediately after a SELECT
+ INTO to check if an assignment had success (that is, at least one
+ row was returned by the SELECT). For example,
+
+
+SELECT INTO myrec * FROM EMP WHERE empname = myname;
+IF NOT FOUND THEN
+ RAISE EXCEPTION ''employee % not found'', myname;
+END IF;
+
+
+ Alternatively, you can use the IS NULL (or ISNULL) conditional to
+ test for NULLity of a RECORD/ROW result. Note that there is no
+ way to tell whether any additional rows might have been discarded.
+
+
+
+
+DECLARE
+ users_rec RECORD;
+ full_name varchar;
+BEGIN
+ SELECT INTO users_rec * FROM users WHERE user_id=3;
+
+ IF users_rec.homepage IS NULL THEN
+ -- user entered no homepage, return "http://"
+
+ RETURN ''http://'';
+ END IF;
+END;
+
+
+
+
+
+ Executing an expression or query with no result
+
+
+ Sometimes one wishes to evaluate an expression or query but discard
+ the result (typically because one is calling a function that has
+ useful side-effects but no useful result value). To do this in
+ PL/pgSQL, use the PERFORM statement:
+
+
+PERFORM query;
+
+
+ This executes a SELECT
+ query and discards the
+ result. PL/pgSQL variables are substituted
+ in the query as usual. Also, the special variable FOUND is set to
+ true if the query produced at least one row, or false if it produced
+ no rows.
+
+
+
+
+ One might expect that SELECT with no INTO clause would accomplish
+ this result, but at present the only accepted way to do it is PERFORM.
+
+
+
+
+ An example:
+
+PERFORM create_mv(''cs_session_page_requests_mv'', my_query);
+
+
+
+
+
+ Executing dynamic queries
+
+
+ Oftentimes you will want to generate dynamic queries inside
+ your PL/pgSQL functions, that is,
+ queries that will involve different tables or different data types
+ each time they are executed. PL/pgSQL's
+ normal attempts to cache plans for queries will not work in such
+ scenarios. To handle this sort of problem, the EXECUTE statement
+ is provided:
+
+
+EXECUTE query-string;
+
+
+ where query-string is an expression
+ yielding a string (of type
+ text) containing the query
+ to be executed. This string is fed literally to the SQL engine.
+
+
+
+ Note in particular that no substitution of PL/pgSQL>
+ variables is done on the query string. The values of variables must
+ be inserted in the query string as it is constructed.
+
+
+
+ When working with dynamic queries you will have to face
+ escaping of single quotes in PL/pgSQL>. Please refer to the
+ table in
+ for a detailed explanation that will save you some effort.
+
+
+
+ Unlike all other queries in PL/pgSQL>, a
+ query run by an EXECUTE statement is
+ not prepared and saved just once during the life of the server.
+ Instead, the query is prepared each
+ time the statement is run. The
+ query-string can be dynamically
+ created within the procedure to perform actions on variable
+ tables and fields.
+
+
+
+ The results from SELECT queries are discarded by EXECUTE, and
+ SELECT INTO is not currently supported within EXECUTE. So, the
+ only way to extract a result from a dynamically-created SELECT is
+ to use the FOR-IN-EXECUTE form described later.
+
+
+
+ An example:
+
+
+EXECUTE ''UPDATE tbl SET ''
+ || quote_ident(fieldname)
+ || '' = ''
+ || quote_literal(newvalue)
+ || '' WHERE ...'';
+
+
+
+
+
+ This example shows use of the functions
+ quote_ident(TEXT) and
+ quote_literal(TEXT).
+ Variables containing field and table identifiers should be
+ passed to function quote_ident().
+ Variables containing literal elements of the dynamic query
+ string should be passed to
+ quote_literal(). Both take the
+ appropriate steps to return the input text enclosed in single
+ or double quotes and with any embedded special characters
+ properly escaped.
+
+
+
+ Here is a much larger example of a dynamic query and EXECUTE:
+
+CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS '
+DECLARE
+ referrer_keys RECORD; -- Declare a generic record to be used in a FOR
+ a_output varchar(4000);
+BEGIN
+ a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar)
+ RETURNS VARCHAR AS ''''
+ DECLARE
+ v_host ALIAS FOR $1;
+ v_domain ALIAS FOR $2;
+ v_url ALIAS FOR $3;
+ BEGIN '';
+
+ --
+ -- Notice how we scan through the results of a query in a FOR loop
+ -- using the FOR <record> construct.
+ --
+
+ FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
+ a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE ''''''''''
+ || referrer_keys.key_string || '''''''''' THEN RETURN ''''''
+ || referrer_keys.referrer_type || ''''''; END IF;'';
+ END LOOP;
+
+ a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE ''''plpgsql'''';'';
+
+ -- This works because we are not substituting any variables
+ -- Otherwise it would fail. Look at PERFORM for another way to run functions
+
+ EXECUTE a_output;
+END;
+' LANGUAGE 'plpgsql';
+
+
+
+
+
+ Obtaining result status
+
+
+
+GET DIAGNOSTICS variable = item , ... ;
+
+
+ This command allows retrieval of system status indicators. Each
+ item is a keyword identifying a state
+ value to be assigned to the specified variable (which should be
+ of the right data type to receive it). The currently available
+ status items are ROW_COUNT>, the number of rows
+ processed by the last SQL query sent down to
+ the SQL engine; and RESULT_OID>,
+ the OID of the last row inserted by the most recent
+ SQL query. Note that RESULT_OID>
+ is only useful after an INSERT query.
+
+
+
+
+ GET DIAGNOSTICS var_integer = ROW_COUNT;
+
+
+
+
+
+
+
+ Control Structures
+
+
+ Control structures are probably the most useful (and
+ important) part of PL/pgSQL>. With
+ PL/pgSQL>'s control structures,
+ you can manipulate PostgreSQL> data in a very
+ flexible and powerful way.
+
+
+
+ Returning from a function
+
+
+
+RETURN expression;
+
+
+ The function terminates and the value of
+ expression will be returned to the
+ upper executor.
+ The expression's result will be automatically cast into the
+ function's return type as described for assignments.
+
+
+
+ The return value of a function cannot be left undefined. If control
+ reaches the end of the top-level block of
+ the function without hitting a RETURN statement, a run-time error
+ will occur.
+
+
+
+
+ Conditionals
+
+
+ IF statements let you execute commands based on
+ certain conditions.
+ PL/pgSQL> has four forms of IF: IF-THEN, IF-THEN-ELSE,
+ IF-THEN-ELSE IF, and IF-THEN-ELSIF-THEN-ELSE.
+
+
+
+ IF-THEN
+
+
+
+IF boolean-expression THEN
+ statements
+END IF;
+
+
+ IF-THEN statements are the simplest form of IF. The
+ statements between THEN and END IF will be executed if
+ the condition is true. Otherwise, they are skipped.
+
+
+IF v_user_id <> 0 THEN
+ UPDATE users SET email = v_email WHERE user_id = v_user_id;
+END IF;
+
+
+
+
+
+ IF-THEN-ELSE
+
+
+
+IF boolean-expression THEN
+ statements
+ELSE
+ statements
+END IF;
+
+
+ IF-THEN-ELSE statements add to IF-THEN by letting you
+ specify an alternative set of statements that should be executed if
+ the condition evaluates to FALSE.
+
+
+IF parentid IS NULL or parentid = ''''
+THEN
+ return fullname;
+ELSE
+ return hp_true_filename(parentid) || ''/'' || fullname;
+END IF;
+
+
+IF v_count > 0 THEN
+ INSERT INTO users_count(count) VALUES(v_count);
+ return ''t'';
+ELSE
+ return ''f'';
+END IF;
+
+
+
+
+
+ IF-THEN-ELSE IF
+
+
+ IF statements can be nested, as in the following example:
+
+IF demo_row.sex = ''m'' THEN
+ pretty_sex := ''man'';
+ELSE
+ IF demo_row.sex = ''f'' THEN
+ pretty_sex := ''woman'';
+ END IF;
+END IF;
+
+
+
+
+ When you use this form, you are actually
+ nesting an IF statement inside the ELSE part of an outer IF
+ statement. Thus you need one END IF statement for each
+ nested IF and one for the parent IF-ELSE.
+ This is workable but grows tedious when there are many
+ alternatives to be checked.
+
+
+
+
+ IF-THEN-ELSIF-ELSE
+
+
+
+IF boolean-expression THEN
+ statements
+ ELSIF boolean-expression THEN
+ statements
+ ELSIF boolean-expression THEN
+ statements
+ ...
+
+
+ ELSE
+ statements
+END IF;
+
+
+ IF-THEN-ELSIF-ELSE provides a more convenient method of checking
+ many alternatives in one statement. Formally it is equivalent
+ to nested IF-THEN-ELSE-IF-THEN commands, but only one END IF
+ is needed.
+
+
+
+ Here is an example:
+
+
+IF number = 0 THEN
+ result := ''zero'';
+ELSIF number > 0 THEN
+ result := ''positive'';
+ELSIF number < 0 THEN
+ result := ''negative'';
+ELSE
+ -- hmm, the only other possibility is that number IS NULL
+ result := ''NULL'';
+END IF;
+
+
+
+
+ The final ELSE section is optional.
+
+
+
+
+
+
+ Simple Loops
+
+
+ With the LOOP, EXIT, WHILE and FOR statements, you can arrange
+ for your PL/pgSQL function to repeat
+ a series of commands.
+
+
+
+ LOOP
+
+
+
+<<label>>
+LOOP
+ statements
+END LOOP;
+
+
+ LOOP defines an unconditional loop that is repeated indefinitely
+ until terminated by an EXIT or RETURN statement.
+ The optional label can be used by
+ EXIT statements in nested loops to specify which level of
+ nesting should be terminated.
+
+
+
+
+ EXIT
+
+
+
+EXIT label WHEN expression ;
+
+
+ If no label is given,
+ the innermost loop is terminated and the
+ statement following END LOOP is executed next.
+ If label is given, it
+ must be the label of the current or some outer level of nested loop
+ or block. Then the named loop or block is terminated and control
+ continues with the statement after the loop's/block's corresponding
+ END.
+
+
+
+ If WHEN is present, loop exit occurs only if the specified condition
+ is true, otherwise control passes to the statement after EXIT.
+
+
+
+ Examples:
+
+LOOP
+ -- some computations
+ IF count > 0 THEN
+ EXIT; -- exit loop
+ END IF;
+END LOOP;
+
+LOOP
+ -- some computations
+ EXIT WHEN count > 0;
+END LOOP;
+
+BEGIN
+ -- some computations
+ IF stocks > 100000 THEN
+ EXIT; -- illegal. Can't use EXIT outside of a LOOP
+ END IF;
+END;
+
+
+
+
+
+ WHILE
+
+
+
+<<label>>
+WHILE expression LOOP
+ statements
+END LOOP;
+
+
+ The WHILE statement repeats a
+ sequence of statements so long as the condition expression
+ evaluates to true. The condition is checked just before
+ each entry to the loop body.
+
+
+
+ For example:
+
+WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
+ -- some computations here
+END LOOP;
+
+WHILE NOT boolean_expression LOOP
+ -- some computations here
+END LOOP;
+
+
+
+
+
+ FOR (integer for-loop)
+
+
+
+<<label>>
+FOR name IN REVERSE expression .. expression LOOP
+ statements
+END LOOP;
+
+
+ This form of FOR creates a loop that iterates over a range of integer
+ values. The variable
+ name is automatically defined as type
+ integer and exists only inside the loop. The two expressions giving
+ the lower and upper bound of the range are evaluated once when entering
+ the loop. The iteration step is normally 1, but is -1 when REVERSE is
+ specified.
+
+
+
+ Some examples of integer FOR loops:
+
+FOR i IN 1..10 LOOP
+ -- some expressions here
+
+ RAISE NOTICE ''i is %'',i;
+END LOOP;
+
+FOR i IN REVERSE 10..1 LOOP
+ -- some expressions here
+END LOOP;
+
+
+
+
+
+
+ Looping Through Query Results
+
+
+ Using a different type of FOR loop, you can iterate through
+ the results of a query and manipulate that data
+ accordingly. The syntax is:
+
+<<label>>
+FOR record | row IN select_query LOOP
+ statements
+END LOOP;
+
+ The record or row variable is successively assigned all the rows
+ resulting from the SELECT query and the loop body is executed
+ for each row. Here is an example:
+
+
+
+
+CREATE FUNCTION cs_refresh_mviews () RETURNS INTEGER AS '
+DECLARE
+ mviews RECORD;
+BEGIN
+ PERFORM cs_log(''Refreshing materialized views...'');
+
+ FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
+
+ -- Now "mviews" has one record from cs_materialized_views
+
+ PERFORM cs_log(''Refreshing materialized view '' || quote_ident(mviews.mv_name) || ''...'');
+ EXECUTE ''TRUNCATE TABLE '' || quote_ident(mviews.mv_name);
+ EXECUTE ''INSERT INTO '' || quote_ident(mviews.mv_name) || '' '' || mviews.mv_query;
+ END LOOP;
+
+ PERFORM cs_log(''Done refreshing materialized views.'');
+ RETURN 1;
+end;
+' LANGUAGE 'plpgsql';
+
+
+ If the loop is terminated by an EXIT statement, the last
+ assigned row value is still accessible after the loop.
+
+
+
+ The FOR-IN-EXECUTE statement is another way to iterate over
+ records:
+
+<<label>>
+FOR record | row IN EXECUTE text_expression LOOP
+ statements
+END LOOP;
+
+ This is like the previous form, except that the source SELECT
+ statement is specified as a string expression, which is evaluated
+ and re-planned on each entry to the FOR loop. This allows the
+ programmer to choose the speed of a pre-planned query or the
+ flexibility of a dynamic query, just as with a plain EXECUTE
+ statement.
+
+
+
+
+ The PL/pgSQL> parser presently distinguishes the
+ two kinds of FOR loops (integer or record-returning) by checking
+ whether the target variable mentioned just after FOR has been
+ declared as a record/row variable. If not, it's presumed to be
+ an integer FOR loop. This can cause rather nonintuitive error
+ messages when the true problem is, say, that one has
+ misspelled the FOR variable name.
+
+
+
+
+
+
+ Cursors
+
+
+ Rather than executing a whole query at once, it is possible to set
+ up a cursor> that encapsulates the query, and then read
+ the query result a few rows at a time. One reason for doing this is
+ to avoid memory overrun when the result contains a large number of
+ rows. (However, PL/pgSQL> users don't normally need
+ to worry about that, since FOR loops automatically use a cursor
+ internally to avoid memory problems.) A more interesting usage is to
+ return a reference to a cursor that it has created, allowing the
+ caller to read the rows. This provides a way to return row sets
+ from functions.
+
+
+
+ Declaring Cursor Variables
+
+
+ All access to cursors in PL/pgSQL> goes through
+ cursor variables, which are always of the special data type
+ refcursor>. One way to create a cursor variable
+ is just to declare it as a variable of type refcursor>.
+ Another way is to use the cursor declaration syntax,
+ which in general is:
+
+name CURSOR ( arguments ) FOR select_query ;
+
+ (FOR> may be replaced by IS> for Oracle
+ compatibility.) arguments, if any,
+ are a comma-separated list of name
+ datatype pairs that define names to
+ be replaced by parameter values in the given query. The actual
+ values to substitute for these names will be specified later,
+ when the cursor is opened.
+
+
+ Some examples:
+
+DECLARE
+ curs1 refcursor;
+ curs2 CURSOR FOR SELECT * from tenk1;
+ curs3 CURSOR (key int) IS SELECT * from tenk1 where unique1 = key;
+
+ All three of these variables have the data type refcursor>,
+ but the first may be used with any query, while the second has
+ a fully specified query already bound> to it, and the last
+ has a parameterized query bound to it. (key> will be
+ replaced by an integer parameter value when the cursor is opened.)
+ The variable curs1>
+ is said to be unbound> since it is not bound to
+ any particular query.
+
+
+
+
+ Opening Cursors
+
+
+ Before a cursor can be used to retrieve rows, it must be
+ opened>. (This is the equivalent action to the SQL
+ command DECLARE CURSOR>.) PL/pgSQL> has
+ four forms of the OPEN statement, two of which use unbound cursor
+ variables and the other two use bound cursor variables.
+
+
+
+ OPEN FOR SELECT
+
+
+
+OPEN unbound-cursor FOR SELECT ...;
+
+
+ The cursor variable is opened and given the specified query
+ to execute. The cursor cannot be open already, and it must
+ have been declared as an unbound cursor (that is, as a simple
+ refcursor> variable). The SELECT query is treated
+ in the same way as other SELECTs in PL/pgSQL>:
+ PL/pgSQL> variable names are substituted,
+ and the query plan is cached for possible re-use.
+
+
+OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
+
+
+
+
+
+ OPEN FOR EXECUTE
+
+
+
+OPEN unbound-cursor FOR EXECUTE query-string;
+
+
+ The cursor variable is opened and given the specified query
+ to execute. The cursor cannot be open already, and it must
+ have been declared as an unbound cursor (that is, as a simple
+ refcursor> variable). The query is specified as a
+ string expression in the same way as in the EXECUTE command.
+ As usual, this gives flexibility so the query can vary
+ from one run to the next.
+
+
+OPEN curs1 FOR EXECUTE ''SELECT * FROM '' || quote_ident($1);
+
+
+
+
+
+ OPENing a bound cursor
+
+
+
+OPEN bound-cursor ( argument_values ) ;
+
+
+ This form of OPEN is used to open a cursor variable whose query
+ was bound to it when it was declared.
+ The cursor cannot be open already. A list of actual argument
+ value expressions must appear if and only if the cursor was
+ declared to take arguments. These values will be substituted
+ in the query.
+ The query plan for a bound cursor is always considered
+ cacheable --- there is no equivalent of EXECUTE in this case.
+
+
+OPEN curs2;
+OPEN curs3(42);
+
+
+
+
+
+
+ Using Cursors
+
+
+ Once a cursor has been opened, it can be manipulated with the
+ statements described here.
+
+
+
+ These manipulations need not occur in the same function that
+ opened the cursor to begin with. You can return a refcursor>
+ value out of a function and let the caller operate on the cursor.
+ (Internally, a refcursor> value is simply the string name
+ of a Portal containing the active query for the cursor. This name
+ can be passed around, assigned to other refcursor> variables,
+ and so on, without disturbing the Portal.)
+
+
+
+ All Portals are implicitly closed at transaction end. Therefore
+ a refcursor> value is useful to reference an open cursor
+ only until the end of the transaction.
+
+
+
+ FETCH
+
+
+
+FETCH cursor INTO target;
+
+
+ FETCH retrieves the next row from the cursor into a target,
+ which may be a row variable, a record variable, or a comma-separated
+ list of simple variables, just like SELECT INTO. As with
+ SELECT INTO, the special variable FOUND may be checked to see
+ whether a row was obtained or not.
+
+
+FETCH curs1 INTO rowvar;
+FETCH curs2 INTO foo,bar,baz;
+
+
+
+
+
+ CLOSE
+
+
+
+CLOSE cursor;
+
+
+ CLOSE closes the Portal underlying an open cursor.
+ This can be used to release resources earlier than end of
+ transaction, or to free up the cursor variable to be opened again.
+
+
+CLOSE curs1;
+
+
+
+
+
+ Returning Cursors
+
+
+
+ PL/pgSQL> functions can return cursors to the
+ caller. This is used to return multiple rows or columns from the
+ function. The function opens the cursor and returns the cursor
+ name to the caller. The caller can then FETCH rows from the
+ cursor. The cursor can be CLOSEd by the caller, or it will be
+ closed automatically when the transaction closes.
+
+
+
+
+ The cursor name returned by the function can be specified by the
+ caller or automatically generated. The following example shows
+ how a cursor name can be supplied by the caller:
+
+
+CREATE TABLE test (col text);
+INSERT INTO test VALUES ('123');
+
+CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
+BEGIN
+ OPEN $1 FOR SELECT col FROM test;
+ RETURN $1;
+END;
+' LANGUAGE 'plpgsql';
+
+BEGIN;
+SELECT reffunc('funccursor');
+FETCH ALL IN funccursor;
+COMMIT;
+
+
+
+
+ The following example uses automatic cursor name generation:
+
+
+CREATE FUNCTION reffunc2() RETURNS refcursor AS '
+DECLARE
+ ref refcursor;
+BEGIN
+ OPEN ref FOR SELECT col FROM test;
+ RETURN ref;
+END;
+' LANGUAGE 'plpgsql';
+
+BEGIN;
+SELECT reffunc2();
+
+ reffunc2
+ --------------------
+ <unnamed cursor 1>
+ (1 row)
+
+FETCH ALL IN "<unnamed cursor 1>";
+COMMIT;
+
+
+
+
+
+
+
+ Errors and Messages
+
+
+ Use the RAISE statement to report messages and raise errors.
+
+
+RAISE level 'format' , variable ...;
+
+
+ Possible levels are DEBUG (write the message into the postmaster log),
+ NOTICE (write the message into the postmaster log and forward it to
+ the client application) and EXCEPTION (raise an error,
+ aborting the transaction).
+
+
+
+ Inside the format string, % is replaced by the next
+ optional argument's external representation.
+ Write %% to emit a literal %.
+ Note that the optional arguments must presently
+ be simple variables, not expressions, and the format must be a simple
+ string literal.
+
+
+
+
+
+ Examples:
+
+RAISE NOTICE ''Calling cs_create_job(%)'',v_job_id;
+
+ In this example, the value of v_job_id will replace the % in the
+ string.
+
+
+
+
+RAISE EXCEPTION ''Inexistent ID --> %'',user_id;
+
+ This will abort the transaction with the given error message.
+
+
+
+ Exceptions
+
+
+ PostgreSQL does not have a very smart
+ exception handling model. Whenever the parser, planner/optimizer
+ or executor decide that a statement cannot be processed any longer,
+ the whole transaction gets aborted and the system jumps back
+ into the main loop to get the next query from the client application.
+
+
+
+ It is possible to hook into the error mechanism to notice that this
+ happens. But currently it is impossible to tell what really
+ caused the abort (input/output conversion error, floating-point
+ error, parse error). And it is possible that the database backend
+ is in an inconsistent state at this point so returning to the upper
+ executor or issuing more commands might corrupt the whole database.
+
+
+
+ Thus, the only thing PL/pgSQL currently does when it encounters
+ an abort during execution of a function or trigger
+ procedure is to write some additional NOTICE level log messages
+ telling in which function and where (line number and type of
+ statement) this happened. The error always stops execution of
+ the function.
+
+
+
+
+
+ Trigger Procedures
+
+
+ PL/pgSQL can be used to define trigger
+ procedures. A trigger procedure is created with the CREATE
+ FUNCTION command as a function with no arguments and a return
+ type of OPAQUE. Note that the function must be declared
+ with no arguments even if it expects to receive arguments specified
+ in CREATE TRIGGER> --- trigger arguments are passed via
+ TG_ARGV>, as described below.
+
+
+
+ When a PL/pgSQL function is called as a
+ trigger, several special variables are created automatically in the
+ top-level block. They are:
+
+
+
+ NEW
+
+
+ Data type RECORD; variable holding the new database row for INSERT/UPDATE
+ operations in ROW level triggers.
+
+
+
+
+
+ OLD
+
+
+ Data type RECORD; variable holding the old database row for UPDATE/DELETE
+ operations in ROW level triggers.
+
+
+
+
+
+ TG_NAME
+
+
+ Data type name; variable that contains the name of the trigger actually
+ fired.
+
+
+
+
+
+ TG_WHEN
+
+
+ Data type text; a string of either
+ BEFORE or AFTER
+ depending on the trigger's definition.
+
+
+
+
+
+ TG_LEVEL
+
+
+ Data type text; a string of either
+ ROW or STATEMENT depending on the
+ trigger's definition.
+
+
+
+
+
+ TG_OP
+
+
+ Data type text; a string of
+ INSERT, UPDATE
+ or DELETE telling
+ for which operation the trigger is fired.
+
+
+
+
+
+ TG_RELID
+
+
+ Data type oid; the object ID of the table that caused the
+ trigger invocation.
+
+
+
+
+
+ TG_RELNAME
+
+
+ Data type name; the name of the table that caused the trigger
+ invocation.
+
+
+
+
+
+ TG_NARGS
+
+
+ Data type integer; the number of arguments given to the trigger
+ procedure in the CREATE TRIGGER statement.
+
+
+
+
+
+ TG_ARGV[]
+
+
+ Data type array of text; the arguments from
+ the CREATE TRIGGER statement.
+ The index counts from 0 and can be given as an expression. Invalid
+ indices (< 0 or >= tg_nargs) result in a NULL value.
+
+
+
+
+
+
+
+ A trigger function must return either NULL or a record/row value
+ having exactly the structure of the table the trigger was fired for.
+ Triggers fired BEFORE may return NULL to signal the trigger manager
+ to skip the rest of the operation for this row (ie, subsequent triggers
+ are not fired, and the INSERT/UPDATE/DELETE does not occur for this
+ row). If a non-NULL value is returned then the operation proceeds with
+ that row value. Note that returning a row value different from the
+ original value of NEW alters the row that will be inserted or updated.
+ It is possible to replace single values directly
+ in NEW and return that, or to build a complete new record/row to
+ return.
+
+
+
+ The return value of a trigger fired AFTER is ignored; it may as well
+ always return a NULL value. But an AFTER trigger can still abort the
+ operation by raising an error.
+
+
+
+ A PL/pgSQL Trigger Procedure Example
+
+
+ This example trigger ensures that any time a row is inserted or updated
+ in the table, the current user name and time are stamped into the
+ row. And it ensures that an employee's name is given and that the
+ salary is a positive value.
+
+
+CREATE TABLE emp (
+ empname text,
+ salary integer,
+ last_date timestamp,
+ last_user text
+);
+
+CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS '
+ BEGIN
+ -- Check that empname and salary are given
+ IF NEW.empname ISNULL THEN
+ RAISE EXCEPTION ''empname cannot be NULL value'';
+ END IF;
+ IF NEW.salary ISNULL THEN
+ RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
+ END IF;
+
+ -- Who works for us when she must pay for?
+ IF NEW.salary < 0 THEN
+ RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
+ END IF;
+
+ -- Remember who changed the payroll when
+ NEW.last_date := ''now'';
+ NEW.last_user := current_user;
+ RETURN NEW;
+ END;
+' LANGUAGE 'plpgsql';
+
+CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
+ FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
+
+
+
+
+
+
+
+
+ Examples
+
+
+ Here are only a few functions to demonstrate how easy it is to
+ write PL/pgSQL>
+ functions. For more complex examples the programmer
+ might look at the regression test for PL/pgSQL>.
+
+
+
+ One painful detail in writing functions in PL/pgSQL is the handling
+ of single quotes. The function's source text in CREATE FUNCTION must
+ be a literal string. Single quotes inside of literal strings must be
+ either doubled or quoted with a backslash. We are still looking for
+ an elegant alternative. In the meantime, doubling the single quotes
+ as in the examples below should be used. Any solution for this
+ in future versions of PostgreSQL will be
+ forward compatible.
+
+
+
+ For a detailed explanation and examples of how to escape single
+ quotes in different situations, please see .
+
+
+
+ A Simple PL/pgSQL Function to Increment an Integer
+
+
+ The following two PL/pgSQL functions are identical to their
+ counterparts from the C language function discussion. This
+ function receives an integer and increments it by
+ one, returning the incremented value.
+
+
+
+CREATE FUNCTION add_one (integer) RETURNS INTEGER AS '
+ BEGIN
+ RETURN $1 + 1;
+ END;
+' LANGUAGE 'plpgsql';
+
+
+
+
+ A Simple PL/pgSQL Function to Concatenate Text
+
+
+ This function receives two text parameters and
+ returns the result of concatenating them.
+
+
+
+CREATE FUNCTION concat_text (TEXT, TEXT) RETURNS TEXT AS '
+ BEGIN
+ RETURN $1 || $2;
+ END;
+' LANGUAGE 'plpgsql';
+
+
+
+
+ A PL/pgSQL Function on Composite Type
+
+
+ In this example, we take EMP> (a table) and an
+ integer as arguments to our function, which returns
+ a boolean. If the salary> field of the EMP> table is
+ NULL, we return f>. Otherwise we compare with
+ that field with the integer passed to the function
+ and return the boolean result of the comparison (t
+ or f). This is the PL/pgSQL equivalent to the example from the C
+ functions.
+
+
+
+CREATE FUNCTION c_overpaid (EMP, INTEGER) RETURNS BOOLEAN AS '
+ DECLARE
+ emprec ALIAS FOR $1;
+ sallim ALIAS FOR $2;
+ BEGIN
+ IF emprec.salary ISNULL THEN
+ RETURN ''f'';
+ END IF;
+ RETURN emprec.salary > sallim;
+ END;
+' LANGUAGE 'plpgsql';
+
+
+
+
+
+
+
+
+
+
+ February 2001
+
+
+ Roberto
+ Mello
+
+
+ rmello@fslc.usu.edu
+
+
+
+
+
+
+ Porting from Oracle PL/SQL
+
+
+ Oracle
+
+
+
+ PL/SQL
+
+
+
+ Author
+
+ Roberto Mello (rmello@fslc.usu.edu)
+
+
+
+
+ This section explains differences between Oracle's PL/SQL and
+ PostgreSQL>'s PL/pgSQL languages in the hopes of helping developers
+ port applications from Oracle to PostgreSQL>. Most of the code here
+ is from the ArsDigita
+ Clickstream
+ module that I ported to PostgreSQL> when I took an
+ internship with OpenForce
+ Inc. in the Summer of 2000.
+
+
+
+ PL/pgSQL is similar to PL/SQL in many aspects. It is a block
+ structured, imperative language (all variables have to be
+ declared). PL/SQL has many more features than its PostgreSQL>
+ counterpart, but PL/pgSQL allows for a great deal of functionality
+ and it is being improved constantly.
+
+
+
+ Main Differences
+
+
+ Some things you should keep in mind when porting from Oracle to PostgreSQL>:
+
+
+
+
+ No default parameters in PostgreSQL>.
+
+
+
+
+
+ You can overload functions in PostgreSQL>. This is often used to work
+ around the lack of default parameters.
+
+
+
+
+
+ Assignments, loops and conditionals are similar.
+
+
+
+
+
+ No need for cursors in PostgreSQL>, just put the query in the FOR
+ statement (see example below)
+
+
+
+
+
+ In PostgreSQL> you need to escape single
+ quotes. See .
+
+
+
+
+
+
+ Quote Me on That: Escaping Single Quotes
+
+
+ In PostgreSQL> you need to escape single quotes inside your
+ function definition. This can lead to quite amusing code at
+ times, especially if you are creating a function that generates
+ other function(s), as in
+ .
+ One thing to keep in mind
+ when escaping lots of single quotes is that, except for the
+ beginning/ending quotes, all the others will come in even
+ quantity.
+
+
+
+ gives the scoop. (You'll
+ love this little chart.)
+
+
+
+ Single Quotes Escaping Chart
+
+
+
+
+ No. of Quotes
+ Usage
+ Example
+ Result
+
+
+
+
+
+ 1
+ To begin/terminate function bodies
+
+CREATE FUNCTION foo() RETURNS INTEGER AS '...'
+LANGUAGE 'plpgsql';
+
+ as is
+
+
+
+ 2
+ In assignments, SELECTs, to delimit strings, etc.
+
+a_output := ''Blah'';
+SELECT * FROM users WHERE f_name=''foobar'';
+
+ SELECT * FROM users WHERE f_name='foobar';
+
+
+
+ 4
+
+ When you need two single quotes in your resulting string
+ without terminating that string.
+
+
+a_output := a_output || '' AND name
+ LIKE ''''foobar'''' AND ...''
+
+ AND name LIKE 'foobar' AND ...
+
+
+
+ 6
+
+ When you want double quotes in your resulting string
+ and terminate that string.
+
+
+a_output := a_output || '' AND name
+ LIKE ''''foobar''''''
+
+
+ AND name LIKE 'foobar'
+
+
+
+
+ 10
+
+ When you want two single quotes in the resulting string
+ (which accounts for 8 quotes) and
+ terminate that string (2 more). You will probably only need
+ that if you were using a function to generate other functions
+ (like in ).
+
+
+a_output := a_output || '' if v_'' ||
+ referrer_keys.kind || '' like ''''''''''
+ || referrer_keys.key_string || ''''''''''
+ then return '''''' || referrer_keys.referrer_type
+ || ''''''; end if;'';
+
+
+ if v_<...> like ''<...>'' then return ''<...>''; end if;
+
+
+
+
+
+
+
+
+
+
+ Porting Functions
+
+
+
+
+ A Simple Function
+
+
+
+ Here is an Oracle function:
+
+CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name IN varchar, v_version IN varchar)
+RETURN varchar IS
+BEGIN
+ IF v_version IS NULL THEN
+ RETURN v_name;
+ END IF;
+ RETURN v_name || '/' || v_version;
+END;
+/
+SHOW ERRORS;
+
+
+
+
+ Let's go through this function and see the differences to PL/pgSQL>:
+
+
+
+
+ PostgreSQL does not have named
+ parameters. You have to explicitly alias them inside your
+ function.
+
+
+
+
+
+ Oracle can have IN, OUT,
+ and INOUT parameters passed to functions.
+ The INOUT, for example, means that the
+ parameter will receive a value and return another. PostgreSQL>
+ only has IN
parameters and functions can return
+ only a single value.
+
+
+
+
+
+ The RETURN key word in the function
+ prototype (not the function body) becomes
+ RETURNS in PostgreSQL>.
+
+
+
+
+
+ On PostgreSQL> functions are created using single quotes as
+ delimiters, so you have to escape single quotes inside your
+ functions (which can be quite annoying at times; see ).
+
+
+
+
+
+ The /show errors command does not exist in
+ PostgreSQL>.
+
+
+
+
+
+
+ So let's see how this function would look when ported to
+ PostgreSQL>:
+
+
+CREATE OR REPLACE FUNCTION cs_fmt_browser_version(VARCHAR, VARCHAR)
+RETURNS VARCHAR AS '
+DECLARE
+ v_name ALIAS FOR $1;
+ v_version ALIAS FOR $2;
+BEGIN
+ IF v_version IS NULL THEN
+ return v_name;
+ END IF;
+ RETURN v_name || ''/'' || v_version;
+END;
+' LANGUAGE 'plpgsql';
+
+
+
+
+
+
+ A Function that Creates Another Function
+
+
+
+ The following procedure grabs rows from a
+ SELECT statement and builds a large function
+ with the results in IF statements, for the
+ sake of efficiency. Notice particularly the differences in
+ cursors, FOR loops, and the need to escape
+ single quotes in PostgreSQL>.
+
+
+CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
+ CURSOR referrer_keys IS
+ SELECT * FROM cs_referrer_keys
+ ORDER BY try_order;
+
+ a_output VARCHAR(4000);
+BEGIN
+ a_output := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR, v_domain IN VARCHAR,
+v_url IN VARCHAR) RETURN VARCHAR IS BEGIN';
+
+ FOR referrer_key IN referrer_keys LOOP
+ a_output := a_output || ' IF v_' || referrer_key.kind || ' LIKE ''' ||
+referrer_key.key_string || ''' THEN RETURN ''' || referrer_key.referrer_type ||
+'''; END IF;';
+ END LOOP;
+
+ a_output := a_output || ' RETURN NULL; END;';
+ EXECUTE IMMEDIATE a_output;
+END;
+/
+show errors
+
+
+
+
+ Here is how this function would end up in PostgreSQL>:
+
+
+CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS '
+DECLARE
+ referrer_keys RECORD; -- Declare a generic record to be used in a FOR
+ a_output varchar(4000);
+BEGIN
+ a_output := ''CREATE FUNCTION cs_find_referrer_type(VARCHAR,VARCHAR,VARCHAR)
+ RETURNS VARCHAR AS ''''
+ DECLARE
+ v_host ALIAS FOR $1;
+ v_domain ALIAS FOR $2;
+ v_url ALIAS FOR $3;
+ BEGIN '';
+
+ --
+ -- Notice how we scan through the results of a query in a FOR loop
+ -- using the FOR <record> construct.
+ --
+
+ FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
+ a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE ''''''''''
+ || referrer_keys.key_string || '''''''''' THEN RETURN ''''''
+ || referrer_keys.referrer_type || ''''''; END IF;'';
+ END LOOP;
+
+ a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE ''''plpgsql'''';'';
+
+ -- This works because we are not substituting any variables
+ -- Otherwise it would fail. Look at PERFORM for another way to run functions
+
+ EXECUTE a_output;
+END;
+' LANGUAGE 'plpgsql';
+
+
+
+
+
+
+ A Procedure with a lot of String Manipulation and OUT Parameters
+
+
+
+ The following Oracle PL/SQL procedure is used to parse a URL and
+ return several elements (host, path and query). It is an
+ procedure because in PL/pgSQL functions only one value can be returned
+ (see ). In
+ PostgreSQL>, one way to work around this is to split the procedure
+ in three different functions: one to return the host, another for
+ the path and another for the query.
+
+
+
+CREATE OR REPLACE PROCEDURE cs_parse_url(
+ v_url IN VARCHAR,
+ v_host OUT VARCHAR, -- This will be passed back
+ v_path OUT VARCHAR, -- This one too
+ v_query OUT VARCHAR) -- And this one
+is
+ a_pos1 INTEGER;
+ a_pos2 INTEGER;
+begin
+ v_host := NULL;
+ v_path := NULL;
+ v_query := NULL;
+ a_pos1 := instr(v_url, '//'); -- PostgreSQL> doesn't have an instr function
+
+ IF a_pos1 = 0 THEN
+ RETURN;
+ END IF;
+ a_pos2 := instr(v_url, '/', a_pos1 + 2);
+ IF a_pos2 = 0 THEN
+ v_host := substr(v_url, a_pos1 + 2);
+ v_path := '/';
+ RETURN;
+ END IF;
+
+ v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
+ a_pos1 := instr(v_url, '?', a_pos2 + 1);
+
+ IF a_pos1 = 0 THEN
+ v_path := substr(v_url, a_pos2);
+ RETURN;
+ END IF;
+
+ v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
+ v_query := substr(v_url, a_pos1 + 1);
+END;
+/
+show errors;
+
+
+
+ Here is how this procedure could be translated for PostgreSQL>:
+
+
+CREATE OR REPLACE FUNCTION cs_parse_url_host(VARCHAR) RETURNS VARCHAR AS '
+DECLARE
+ v_url ALIAS FOR $1;
+ v_host VARCHAR;
+ v_path VARCHAR;
+ a_pos1 INTEGER;
+ a_pos2 INTEGER;
+ a_pos3 INTEGER;
+BEGIN
+ v_host := NULL;
+ a_pos1 := instr(v_url,''//'');
+
+ IF a_pos1 = 0 THEN
+ RETURN ''''; -- Return a blank
+ END IF;
+
+ a_pos2 := instr(v_url,''/'',a_pos1 + 2);
+ IF a_pos2 = 0 THEN
+ v_host := substr(v_url, a_pos1 + 2);
+ v_path := ''/'';
+ RETURN v_host;
+ END IF;
+
+ v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2 );
+ RETURN v_host;
+END;
+' LANGUAGE 'plpgsql';
+
+
+
+
+
+
+ PostgreSQL> does not have an instr function,
+ so you can work around it using a combination of other functions.
+ I got tired of doing this and created my own
+ instr functions that behave exactly like
+ Oracle's (it makes life easier). See the for the code.
+
+
+
+
+
+
+ Procedures
+
+
+
+ Oracle procedures give a little more flexibility to the developer
+ because nothing needs to be explicitly returned, but it can be
+ through the use of INOUT> or OUT> parameters.
+
+
+
+ An example:
+
+
+CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
+ a_running_job_count INTEGER;
+ PRAGMA AUTONOMOUS_TRANSACTION;
+BEGIN
+ LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
+
+ SELECT count(*) INTO a_running_job_count
+ FROM cs_jobs
+ WHERE end_stamp IS NULL;
+
+ IF a_running_job_count > 0 THEN
+ COMMIT; -- free lock
+ raise_application_error(-20000, 'Unable to create a new job: a job is currently running.');
+ END IF;
+
+ DELETE FROM cs_active_job;
+ INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
+
+ BEGIN
+ INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, sysdate);
+ EXCEPTION WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
+ END;
+ COMMIT;
+END;
+/
+show errors
+
+
+
+
+ Procedures like this can be easily converted into PostgreSQL>
+ functions returning an INTEGER. This procedure in
+ particular is interesting because it can teach us some things:
+
+
+
+
+ There is no pragma statement in PostgreSQL>.
+
+
+
+
+
+ If you do a LOCK TABLE in PL/pgSQL>, the lock
+ will not be released until the calling transaction is finished.
+
+
+
+
+
+ You also cannot have transactions in PL/pgSQL procedures. The
+ entire function (and other functions called from therein) is
+ executed in a transaction and PostgreSQL> rolls back the results if
+ something goes wrong. Therefore only one
+ BEGIN statement is allowed.
+
+
+
+
+
+ The exception when would have to be replaced by an
+ IF statement.
+
+
+
+
+
+
+ So let's see one of the ways we could port this procedure to PL/pgSQL>:
+
+
+CREATE OR REPLACE FUNCTION cs_create_job(INTEGER) RETURNS INTEGER AS '
+DECLARE
+ v_job_id ALIAS FOR $1;
+ a_running_job_count INTEGER;
+ a_num INTEGER;
+ -- PRAGMA AUTONOMOUS_TRANSACTION;
+BEGIN
+ LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
+ SELECT count(*) INTO a_running_job_count
+ FROM cs_jobs
+ WHERE end_stamp IS NULL;
+
+ IF a_running_job_count > 0
+ THEN
+ -- COMMIT; -- free lock
+ RAISE EXCEPTION ''Unable to create a new job: a job is currently running.'';
+ END IF;
+
+ DELETE FROM cs_active_job;
+ INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
+
+ SELECT count(*) into a_num
+ FROM cs_jobs
+ WHERE job_id=v_job_id;
+ IF NOT FOUND THEN -- If nothing was returned in the last query
+ -- This job is not in the table so lets insert it.
+ INSERT INTO cs_jobs(job_id, start_stamp) VALUES (v_job_id, sysdate());
+ RETURN 1;
+ ELSE
+ RAISE NOTICE ''Job already running.'';
+ END IF;
+
+ RETURN 0;
+END;
+' LANGUAGE 'plpgsql';
+
+
+
+
+
+ Notice how you can raise notices (or errors) in PL/pgSQL>.
+
+
+
+
+
+
+
+
+ Packages
+
+
+
+
+ I haven't done much with packages myself, so if there are
+ mistakes here, please let me know.
+
+
+
+
+ Packages are a way Oracle gives you to encapsulate PL/SQL
+ statements and functions into one entity, like Java classes, where
+ you define methods and objects. You can access these
+ objects/methods with a .
+ (dot). Here is an example of an Oracle package from ACS 4 (the
+ ArsDigita Community
+ System):
+
+
+CREATE OR REPLACE PACKAGE BODY acs
+AS
+ FUNCTION add_user (
+ user_id IN users.user_id%TYPE DEFAULT NULL,
+ object_type IN acs_objects.object_type%TYPE DEFAULT 'user',
+ creation_date IN acs_objects.creation_date%TYPE DEFAULT sysdate,
+ creation_user IN acs_objects.creation_user%TYPE DEFAULT NULL,
+ creation_ip IN acs_objects.creation_ip%TYPE DEFAULT NULL,
+ ...
+ ) RETURN users.user_id%TYPE
+ IS
+ v_user_id users.user_id%TYPE;
+ v_rel_id membership_rels.rel_id%TYPE;
+ BEGIN
+ v_user_id := acs_user.new (user_id, object_type, creation_date,
+ creation_user, creation_ip, email, ...
+ RETURN v_user_id;
+ END;
+END acs;
+/
+show errors
+
+
+
+
+ We port this to PostgreSQL> by creating the different objects of
+ the Oracle package as functions with a standard naming
+ convention. We have to pay attention to some other details, like
+ the lack of default parameters in PostgreSQL> functions. The above
+ package would become something like this:
+
+
+CREATE FUNCTION acs__add_user(INTEGER,INTEGER,VARCHAR,TIMESTAMP,INTEGER,INTEGER,...)
+RETURNS INTEGER AS '
+DECLARE
+ user_id ALIAS FOR $1;
+ object_type ALIAS FOR $2;
+ creation_date ALIAS FOR $3;
+ creation_user ALIAS FOR $4;
+ creation_ip ALIAS FOR $5;
+ ...
+ v_user_id users.user_id%TYPE;
+ v_rel_id membership_rels.rel_id%TYPE;
+BEGIN
+ v_user_id := acs_user__new(user_id,object_type,creation_date,creation_user,creation_ip, ...);
+ ...
+
+ RETURN v_user_id;
+END;
+' LANGUAGE 'plpgsql';
+
+
+
+
+
+
+ Other Things to Watch For
+
+
+
+ EXECUTE
+
+
+ The PostgreSQL> version of EXECUTE works
+ nicely, but you have to remember to use
+ quote_literal(TEXT) and
+ quote_string(TEXT) as described in . Constructs of the type
+ EXECUTE ''SELECT * from $1''; will not work
+ unless you use these functions.
+
+
+
+
+ Optimizing PL/pgSQL Functions
+
+
+ PostgreSQL> gives you two function creation modifiers to optimize
+ execution: iscachable (function always returns
+ the same result when given the same arguments) and
+ isstrict (function returns NULL if any
+ argument is NULL). Consult the CREATE
+ FUNCTION reference for details.
+
+
+
+ To make use of these optimization attributes, you have to use the
+ WITH modifier in your CREATE
+ FUNCTION statement. Something like:
+
+
+CREATE FUNCTION foo(...) RETURNS INTEGER AS '
+...
+' LANGUAGE 'plpgsql'
+WITH (isstrict, iscachable);
+
+
+
+
+
+
+
+ Appendix
+
+
+
+
+ Code for my instr functions
+
+
+
+ This function should probably be integrated into the core.
+
+
+
+--
+-- instr functions that mimic Oracle's counterpart
+-- Syntax: instr(string1,string2,[n],[m]) where [] denotes optional params.
+--
+-- Searches string1 beginning at the nth character for the mth
+-- occurrence of string2. If n is negative, search backwards. If m is
+-- not passed, assume 1 (search starts at first character).
+--
+-- by Roberto Mello (rmello@fslc.usu.edu)
+-- modified by Robert Gaszewski (graszew@poland.com)
+-- Licensed under the GPL v2 or later.
+--
+
+CREATE FUNCTION instr(VARCHAR,VARCHAR) RETURNS INTEGER AS '
+DECLARE
+ pos integer;
+BEGIN
+ pos:= instr($1,$2,1);
+ RETURN pos;
+END;
+' LANGUAGE 'plpgsql';
+
+
+CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER) RETURNS INTEGER AS '
+DECLARE
+ string ALIAS FOR $1;
+ string_to_search ALIAS FOR $2;
+ beg_index ALIAS FOR $3;
+ pos integer NOT NULL DEFAULT 0;
+ temp_str VARCHAR;
+ beg INTEGER;
+ length INTEGER;
+ ss_length INTEGER;
+BEGIN
+ IF beg_index > 0 THEN
+
+ temp_str := substring(string FROM beg_index);
+ pos := position(string_to_search IN temp_str);
+
+ IF pos = 0 THEN
+ RETURN 0;
+ ELSE
+ RETURN pos + beg_index - 1;
+ END IF;
+ ELSE
+ ss_length := char_length(string_to_search);
+ length := char_length(string);
+ beg := length + beg_index - ss_length + 2;
+
+ WHILE beg > 0 LOOP
+ temp_str := substring(string FROM beg FOR ss_length);
+ pos := position(string_to_search IN temp_str);
+
+ IF pos > 0 THEN
+ RETURN beg;
+ END IF;
+
+ beg := beg - 1;
+ END LOOP;
+ RETURN 0;
+ END IF;
+END;
+' LANGUAGE 'plpgsql';
+
+--
+-- Written by Robert Gaszewski (graszew@poland.com)
+-- Licensed under the GPL v2 or later.
+--
+CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER,INTEGER) RETURNS INTEGER AS '
+DECLARE
+ string ALIAS FOR $1;
+ string_to_search ALIAS FOR $2;
+ beg_index ALIAS FOR $3;
+ occur_index ALIAS FOR $4;
+ pos integer NOT NULL DEFAULT 0;
+ occur_number INTEGER NOT NULL DEFAULT 0;
+ temp_str VARCHAR;
+ beg INTEGER;
+ i INTEGER;
+ length INTEGER;
+ ss_length INTEGER;
+BEGIN
+ IF beg_index > 0 THEN
+ beg := beg_index;
+ temp_str := substring(string FROM beg_index);
+
+ FOR i IN 1..occur_index LOOP
+ pos := position(string_to_search IN temp_str);
+
+ IF i = 1 THEN
+ beg := beg + pos - 1;
+ ELSE
+ beg := beg + pos;
+ END IF;
+
+ temp_str := substring(string FROM beg + 1);
+ END LOOP;
+
+ IF pos = 0 THEN
+ RETURN 0;
+ ELSE
+ RETURN beg;
+ END IF;
+ ELSE
+ ss_length := char_length(string_to_search);
+ length := char_length(string);
+ beg := length + beg_index - ss_length + 2;
+
+ WHILE beg > 0 LOOP
+ temp_str := substring(string FROM beg FOR ss_length);
+ pos := position(string_to_search IN temp_str);
+
+ IF pos > 0 THEN
+ occur_number := occur_number + 1;
+
+ IF occur_number = occur_index THEN
+ RETURN beg;
+ END IF;
+ END IF;
+
+ beg := beg - 1;
+ END LOOP;
+
+ RETURN 0;
+ END IF;
+END;
+' LANGUAGE 'plpgsql';
+
+
+
+
+
+
+
+
+
diff --git a/doc/src/sgml/plsql.sgml b/doc/src/sgml/plsql.sgml
deleted file mode 100644
index 082b77fc5b2..00000000000
--- a/doc/src/sgml/plsql.sgml
+++ /dev/null
@@ -1,3001 +0,0 @@
-
-
-
- PL/pgSQL - SQL Procedural Language
-
-
- PL/pgSQL
-
-
-
- PL/pgSQL is a loadable procedural language for the
- PostgreSQL database system.
-
-
-
- This package was originally written by Jan Wieck. This
- documentation was in part written
- by Roberto Mello (rmello@fslc.usu.edu).
-
-
-
- Overview
-
-
- The design goals of PL/pgSQL> were to create a loadable procedural
- language that
-
-
-
- can be used to create functions and trigger procedures,
-
-
-
-
- adds control structures to the SQL language,
-
-
-
-
- can perform complex computations,
-
-
-
-
- inherits all user defined types, functions and operators,
-
-
-
-
- can be defined to be trusted by the server,
-
-
-
-
- is easy to use.
-
-
-
-
-
- The PL/pgSQL> call handler parses the function's source text and
- produces an internal binary instruction tree the first time the
- function is called (within any one backend process). The instruction tree
- fully translates the
- PL/pgSQL> statement structure, but individual
- SQL expressions and SQL queries
- used in the function are not translated immediately.
-
-
- As each expression and SQL query is first used
- in the function, the PL/pgSQL> interpreter creates a
- prepared execution plan (using the SPI manager's
- SPI_prepare and
- SPI_saveplan functions). Subsequent visits
- to that expression or query re-use the prepared plan. Thus, a function
- with conditional code that contains many statements for which execution
- plans might be required, will only prepare and save those plans
- that are really used during the lifetime of the database
- connection. This can provide a considerable savings of parsing
- activity. A disadvantage is that errors in a specific expression
- or query may not be detected until that part of the function is
- reached in execution.
-
-
- Once PL/pgSQL> has made a query plan for a particular
- query in a function, it will re-use that plan for the life of the
- database connection. This is usually a win for performance, but it
- can cause some problems if you dynamically
- alter your database schema. For example:
-
-
-CREATE FUNCTION populate() RETURNS INTEGER AS '
-DECLARE
- -- Declarations
-BEGIN
- PERFORM my_function();
-END;
-' LANGUAGE 'plpgsql';
-
- If you execute the above function, it will reference the OID for
- my_function() in the query plan produced for
- the PERFORM statement. Later, if you
- drop and re-create my_function(), then
- populate() will not be able to find
- my_function() anymore. You would then have to
- re-create populate(), or at least start a new
- database session so that it will be compiled afresh.
-
-
-
- Because PL/pgSQL saves execution plans in this way, queries that appear
- directly in a PL/pgSQL function must refer to the same tables and fields
- on every execution; that is, you cannot use a parameter as the name of
- a table or field in a query. To get around
- this restriction, you can construct dynamic queries using the PL/pgSQL
- EXECUTE statement --- at the price of constructing a new query plan
- on every execution.
-
-
- Except for input/output conversion and calculation functions
- for user defined types, anything that can be defined in C language
- functions can also be done with PL/pgSQL. It is possible to
- create complex conditional computation functions and later use
- them to define operators or use them in functional indexes.
-
-
- Advantages of Using PL/pgSQL
-
-
-
-
- Better performance (see )
-
-
-
-
-
- SQL support (see )
-
-
-
-
-
- Portability (see )
-
-
-
-
-
- Better Performance
-
-
- SQL is the language PostgreSQL> (and
- most other Relational Databases) use as query
- language. It's portable and easy to learn. But every
- SQL statement must be executed
- individually by the database server.
-
-
-
- That means that your client application must send each
- query to the database server, wait for it to process it,
- receive the results, do some computation, then send
- other queries to the server. All this incurs inter-process communication
- and may also incur network
- overhead if your client is on a different machine than
- the database server.
-
-
-
- With PL/pgSQL you can group a block of computation and a
- series of queries inside the
- database server, thus having the power of a procedural
- language and the ease of use of SQL, but saving lots of
- time because you don't have the whole client/server
- communication overhead. This can make for a
- considerable performance increase.
-
-
-
-
- SQL Support
-
-
- PL/pgSQL adds the power of a procedural language to the
- flexibility and ease of SQL. With
- PL/pgSQL you can use all the data types, columns, operators
- and functions of SQL.
-
-
-
-
- Portability
-
-
- Because PL/pgSQL functions run inside PostgreSQL>, these
- functions will run on any platform where PostgreSQL>
- runs. Thus you can reuse code and have less development costs.
-
-
-
-
-
- Developing in PL/pgSQL
-
-
- Developing in PL/pgSQL is pretty straight forward, especially
- if you have developed in other database procedural languages,
- such as Oracle's PL/SQL. Two good ways of developing in
- PL/pgSQL are:
-
-
-
-
- Using a text editor and reloading the file with psql
-
-
-
-
-
- Using PostgreSQL>'s GUI Tool: PgAccess>
-
-
-
-
-
-
- One good way to develop in PL/pgSQL> is to simply use the text
- editor of your choice to create your functions, and in another
- console, use psql (PostgreSQL's interactive monitor) to load
- those functions. If you are doing it this way, it is a good
- idea to write the function using CREATE OR REPLACE
- FUNCTION. That way you can reload the file to update
- the function definition. For example:
-
-CREATE OR REPLACE FUNCTION testfunc(INTEGER) RETURNS INTEGER AS '
- ....
-end;
-' LANGUAGE 'plpgsql';
-
-
-
-
- While running psql, you can load or reload such a
- function definition file with
-
- \i filename.sql
-
- and then immediately issue SQL commands to test the function.
-
-
-
- Another good way to develop in PL/pgSQL> is using
- PostgreSQL>'s GUI tool: PgAccess>. It does some
- nice things for you, like escaping single-quotes, and making
- it easy to recreate and debug functions.
-
-
-
-
-
- Structure of PL/pgSQL
-
-
- PL/pgSQL is a block
- structured language. The complete text of a function
- definition must be a block>. A block is defined as:
-
-
- <<label>>
- DECLARE
- declarations
-BEGIN
- statements
-END;
-
-
-
-
- Any statement> in the statement section of a block
- can be a sub-block>. Sub-blocks can be used for
- logical grouping or to localize variables to a small group
- of statements.
-
-
-
- The variables declared in the declarations section preceding a
- block are initialized to their default values every time the
- block is entered, not only once per function call. For example:
-
-CREATE FUNCTION somefunc() RETURNS INTEGER AS '
-DECLARE
- quantity INTEGER := 30;
-BEGIN
- RAISE NOTICE ''Quantity here is %'',quantity; -- Quantity here is 30
- quantity := 50;
- --
- -- Create a sub-block
- --
- DECLARE
- quantity INTEGER := 80;
- BEGIN
- RAISE NOTICE ''Quantity here is %'',quantity; -- Quantity here is 80
- END;
-
- RAISE NOTICE ''Quantity here is %'',quantity; -- Quantity here is 50
-
- RETURN quantity;
-END;
-' LANGUAGE 'plpgsql';
-
-
-
-
- It is important not to confuse the use of BEGIN/END for
- grouping statements in PL/pgSQL> with the database commands for
- transaction control. PL/pgSQL>'s BEGIN/END are only for grouping;
- they do not start or end a transaction. Functions and trigger procedures
- are always executed within a transaction established by an outer query
- --- they cannot start or commit transactions, since
- PostgreSQL does not have nested transactions.
-
-
-
- Lexical Details
-
-
- Each statement and declaration within a block is terminated
- by a semicolon.
-
-
-
- All keywords and identifiers can be written in mixed upper- and
- lower-case. Identifiers are implicitly converted to lower-case
- unless double-quoted.
-
-
-
- There are two types of comments in PL/pgSQL>. A double dash --
- starts a comment that extends to the end of the line. A /*
- starts a block comment that extends to the next occurrence of */.
- Block comments cannot be nested, but double dash comments can be
- enclosed into a block comment and a double dash can hide
- the block comment delimiters /* and */.
-
-
-
-
-
- Declarations
-
-
- All variables, rows and records used in a block must be declared in the
- declarations section of the block.
- (The only exception is that the loop variable of a FOR loop iterating
- over a range of integer values is automatically declared as an integer
- variable.)
-
-
-
- PL/pgSQL> variables can have any SQL data type, such as
- INTEGER, VARCHAR and
- CHAR.
-
-
-
- Here are some examples of variable declarations:
-
-user_id INTEGER;
-quantity NUMERIC(5);
-url VARCHAR;
-myrow tablename%ROWTYPE;
-myfield tablename.fieldname%TYPE;
-arow RECORD;
-
-
-
-
- The general syntax of a variable declaration is:
-
-name CONSTANT type NOT NULL { DEFAULT | := } expression ;
-
-
-
-
- The DEFAULT clause, if given, specifies the initial value assigned
- to the variable when the block is entered. If the DEFAULT clause
- is not given then the variable is initialized to the
- SQL NULL value.
-
-
-
- The CONSTANT option prevents the variable from being assigned to,
- so that its value remains constant for the duration of the block.
- If NOT NULL
- is specified, an assignment of a NULL value results in a run-time
- error. All variables declared as NOT NULL
- must have a non-NULL default value specified.
-
-
-
- The default value is evaluated every time the block is entered. So,
- for example, assigning 'now' to a variable of type
- timestamp causes the variable to have the
- time of the current function call, not when the function was
- precompiled.
-
-
-
- Examples:
-
-quantity INTEGER DEFAULT 32;
-url varchar := ''http://mysite.com'';
-user_id CONSTANT INTEGER := 10;
-
-
-
-
- Aliases for Function Parameters
-
-
-
-name ALIAS FOR $n;
-
-
-
-
- Parameters passed to functions are named with the identifiers
- $1, $2,
- etc. Optionally, aliases can be declared for $n
- parameter names for increased readability. Either the alias or the
- numeric identifier can then be used to refer to the parameter value.
- Some examples:
-
-CREATE FUNCTION sales_tax(REAL) RETURNS REAL AS '
-DECLARE
- subtotal ALIAS FOR $1;
-BEGIN
- return subtotal * 0.06;
-END;
-' LANGUAGE 'plpgsql';
-
-
-CREATE FUNCTION instr(VARCHAR,INTEGER) RETURNS INTEGER AS '
-DECLARE
- v_string ALIAS FOR $1;
- index ALIAS FOR $2;
-BEGIN
- -- Some computations here
-END;
-' LANGUAGE 'plpgsql';
-
-
-CREATE FUNCTION use_many_fields(tablename) RETURNS TEXT AS '
-DECLARE
- in_t ALIAS FOR $1;
-BEGIN
- RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
-END;
-' LANGUAGE 'plpgsql';
-
-
-
-
-
- Row Types
-
-
-
-name tablename%ROWTYPE;
-
-
-
-
- A variable of a composite type is called a row>
- variable (or row-type> variable). Such a variable can hold a
- whole row of a SELECT or FOR
- query result, so long as that query's column set matches the declared
- type of the variable. The individual fields of the row value are
- accessed using the usual dot notation, for example
- rowvar.field.
-
-
-
- Presently, a row variable can only be declared using the
- %ROWTYPE notation; although one might expect a
- bare table name to work as a type declaration, it won't be accepted
- within PL/pgSQL functions.
-
-
-
- Parameters to a function can be
- composite types (complete table rows). In that case, the
- corresponding identifier $n will be a row variable, and fields can
- be selected from it, for example $1.user_id.
-
-
-
- Only the user-defined attributes of a table row are accessible in a
- row-type variable, not OID or other system attributes (because the
- row could be from a view). The fields of the row type inherit the
- table's field size or precision for data types such as
- char(n).
-
-CREATE FUNCTION use_two_tables(tablename) RETURNS TEXT AS '
-DECLARE
- in_t ALIAS FOR $1;
- use_t table2name%ROWTYPE;
-BEGIN
- SELECT * INTO use_t FROM table2name WHERE ... ;
- RETURN in_t.f1 || use_t.f3 || in_t.f5 || use_t.f7;
-END;
-' LANGUAGE 'plpgsql';
-
-
-
-
-
- Records
-
-
-
-name RECORD;
-
-
-
-
- Record variables are similar to row-type variables, but they have no
- predefined structure. They take on the actual row structure of the
- row they are assigned during a SELECT or FOR command. The substructure
- of a record variable can change each time it is assigned to.
- A consequence of this is that until a record variable is first assigned
- to, it has no> substructure, and any attempt to access a
- field in it will draw a run-time error.
-
-
-
- Note that RECORD> is not a true data type, only a placeholder.
- Thus, for example, one cannot declare a function returning
- RECORD>.
-
-
-
-
- Attributes
-
-
- Using the %TYPE and %ROWTYPE
- attributes, you can declare variables with the same
- data type or structure as another database item (e.g: a
- table field).
-
-
-
-
-
- variable%TYPE
-
-
-
- %TYPE provides the data type of a
- variable or database column. You can use this to
- declare variables that will hold database
- values. For example, let's say you have a column
- named user_id in your
- users table. To declare a variable with
- the same data type as users>.user_id> you write:
-
-user_id users.user_id%TYPE;
-
-
-
-
- By using %TYPE you don't need to know
- the data type of the structure you are referencing,
- and most important, if the data type of the
- referenced item changes in the future (e.g: you
- change your table definition of user_id from INTEGER to
- REAL), you may not need to change your function
- definition.
-
-
-
-
-
-
- table%ROWTYPE
-
-
-
- %ROWTYPE provides the composite data type corresponding
- to a whole row of the specified table.
- table must be an existing
- table or view name of the database.
-
-
-
-DECLARE
- users_rec users%ROWTYPE;
- user_id users.user_id%TYPE;
-BEGIN
- user_id := users_rec.user_id;
- ...
-
-CREATE FUNCTION does_view_exist(INTEGER) RETURNS bool AS '
- DECLARE
- key ALIAS FOR $1;
- table_data cs_materialized_views%ROWTYPE;
- BEGIN
- SELECT INTO table_data * FROM cs_materialized_views
- WHERE sort_key=key;
-
- IF NOT FOUND THEN
- RETURN false;
- END IF;
- RETURN true;
- END;
-' LANGUAGE 'plpgsql';
-
-
-
-
-
-
-
- RENAME
-
-
-
-RENAME oldname TO newname;
-
-
- Using the RENAME declaration you can change the name of a variable,
- record or row. This is primarily useful if NEW or OLD should be
- referenced by another name inside a trigger procedure. See also ALIAS.
-
-
-
- Examples:
-
-RENAME id TO user_id;
-RENAME this_var TO that_var;
-
-
-
-
-
- RENAME appears to be broken as of PostgreSQL 7.2. Fixing this is
- of low priority, since ALIAS covers most of the practical uses of
- RENAME.
-
-
-
-
-
-
-
- Expressions
-
-
- All expressions used in PL/pgSQL statements
- are processed using the server's regular SQL executor. Expressions that
- appear to contain
- constants may in fact require run-time evaluation
- (e.g. 'now' for the
- timestamp type) so
- it is impossible for the PL/pgSQL parser
- to identify real constant values other than the NULL keyword. All
- expressions are evaluated internally by executing a query
-
-SELECT expression
-
- using the SPI manager. In the expression, occurrences
- of PL/pgSQL variable
- identifiers are replaced by parameters and the actual values from
- the variables are passed to the executor in the parameter array.
- This allows the query plan for the SELECT to be prepared just once
- and then re-used for subsequent evaluations.
-
-
-
- The evaluation done by the PostgreSQL
- main parser has some side
- effects on the interpretation of constant values. In detail there
- is a difference between what these two functions do:
-
-
-CREATE FUNCTION logfunc1 (TEXT) RETURNS TIMESTAMP AS '
- DECLARE
- logtxt ALIAS FOR $1;
- BEGIN
- INSERT INTO logtable VALUES (logtxt, ''now'');
- RETURN ''now'';
- END;
-' LANGUAGE 'plpgsql';
-
-
- and
-
-
-CREATE FUNCTION logfunc2 (TEXT) RETURNS TIMESTAMP AS '
- DECLARE
- logtxt ALIAS FOR $1;
- curtime timestamp;
- BEGIN
- curtime := ''now'';
- INSERT INTO logtable VALUES (logtxt, curtime);
- RETURN curtime;
- END;
-' LANGUAGE 'plpgsql';
-
-
- In the case of logfunc1(), the
- PostgreSQL main parser knows when
- preparing the plan for the INSERT, that the string
- 'now' should be interpreted as
- timestamp because the target field of logtable
- is of that type. Thus, it will make a constant from it at this
- time and this constant value is then used in all invocations of
- logfunc1() during the lifetime of the
- backend. Needless to say that this isn't what the
- programmer wanted.
-
-
-
- In the case of logfunc2(), the
- PostgreSQL main parser does not know
- what type 'now' should become and therefore
- it returns a data value of type text containing the string
- 'now'. During the ensuing assignment
- to the local variable curtime, the
- PL/pgSQL interpreter casts this
- string to the timestamp type by calling the
- text_out() and timestamp_in()
- functions for the conversion. So, the computed time stamp is updated
- on each execution as the programmer expects.
-
-
-
- The mutable nature of record variables presents a problem in this
- connection. When fields of a record variable are used in expressions or
- statements, the data types of the
- fields must not change between calls of one and the same expression,
- since the expression will be planned using the data type that is present
- when the expression is first reached.
- Keep this in mind when writing trigger procedures that handle events
- for more than one table. (EXECUTE can be used to get around this
- problem when necessary.)
-
-
-
-
- Basic Statements
-
-
- In this section and the following ones, we describe all the statement
- types that are explicitly understood by
- PL/pgSQL.
- Anything not recognized as one of these statement types is presumed
- to be an SQL query, and is sent to the main database engine to execute
- (after substitution for any PL/pgSQL variables
- used in the statement). Thus,
- for example, SQL INSERT>, UPDATE>, and
- DELETE> commands may be considered to be statements of
- PL/pgSQL. But they are not specifically
- listed here.
-
-
-
- Assignment
-
-
- An assignment of a value to a variable or row/record field is
- written as:
-
-identifier := expression;
-
- As explained above, the expression in such a statement is evaluated
- by means of an SQL SELECT> command sent to the main
- database engine. The expression must yield a single value.
-
-
-
- If the expression's result data type doesn't match the variable's
- data type, or the variable has a specific size/precision
- (like char(20)), the result value will be implicitly
- converted by the PL/pgSQL interpreter using
- the result type's output-function and
- the variable type's input-function. Note that this could potentially
- result in run-time errors generated by the input function, if the
- string form of the result value is not acceptable to the input function.
-
-
-
- Examples:
-
-user_id := 20;
-tax := subtotal * 0.06;
-
-
-
-
-
- SELECT INTO
-
-
- The result of a SELECT command yielding multiple columns (but
- only one row) can be assigned to a record variable, row-type
- variable, or list of scalar variables. This is done by:
-
-
-SELECT INTO target expressions FROM ...;
-
-
- where target can be a record variable, a row
- variable, or a comma-separated list of simple variables and
- record/row fields. Note that this is quite different from
- PostgreSQL>'s normal interpretation of SELECT INTO, which is that the
- INTO target is a newly created table. (If you want to create a
- table from a SELECT result inside a PL/pgSQL function, use the
- syntax CREATE TABLE ... AS SELECT.)
-
-
-
- If a row or a variable list is used as target, the selected values
- must exactly match the structure of the target(s), or a run-time error
- occurs. When a record variable is the target, it automatically
- configures itself to the row type of the query result columns.
-
-
-
- Except for the INTO clause, the SELECT statement is the same as a normal
- SQL SELECT query and can use the full power of SELECT.
-
-
-
- If the SELECT query returns zero rows, NULLs are assigned to the
- target(s). If the SELECT query returns multiple rows, the first
- row is assigned to the target(s) and the rest are discarded.
- (Note that the first row> is not well-defined unless you've
- used ORDER BY.)
-
-
-
- At present, the INTO clause can appear almost anywhere in the SELECT
- query, but it is recommended to place it immediately after the SELECT
- keyword as depicted above. Future versions of
- PL/pgSQL may be less forgiving about
- placement of the INTO clause.
-
-
-
- There is a special variable named FOUND of type
- boolean that can be used immediately after a SELECT
- INTO to check if an assignment had success (that is, at least one
- row was returned by the SELECT). For example,
-
-
-SELECT INTO myrec * FROM EMP WHERE empname = myname;
-IF NOT FOUND THEN
- RAISE EXCEPTION ''employee % not found'', myname;
-END IF;
-
-
- Alternatively, you can use the IS NULL (or ISNULL) conditional to
- test for NULLity of a RECORD/ROW result. Note that there is no
- way to tell whether any additional rows might have been discarded.
-
-
-
-
-DECLARE
- users_rec RECORD;
- full_name varchar;
-BEGIN
- SELECT INTO users_rec * FROM users WHERE user_id=3;
-
- IF users_rec.homepage IS NULL THEN
- -- user entered no homepage, return "http://"
-
- RETURN ''http://'';
- END IF;
-END;
-
-
-
-
-
- Executing an expression or query with no result
-
-
- Sometimes one wishes to evaluate an expression or query but discard
- the result (typically because one is calling a function that has
- useful side-effects but no useful result value). To do this in
- PL/pgSQL, use the PERFORM statement:
-
-
-PERFORM query;
-
-
- This executes a SELECT
- query and discards the
- result. PL/pgSQL variables are substituted
- in the query as usual. Also, the special variable FOUND is set to
- true if the query produced at least one row, or false if it produced
- no rows.
-
-
-
-
- One might expect that SELECT with no INTO clause would accomplish
- this result, but at present the only accepted way to do it is PERFORM.
-
-
-
-
- An example:
-
-PERFORM create_mv(''cs_session_page_requests_mv'', my_query);
-
-
-
-
-
- Executing dynamic queries
-
-
- Oftentimes you will want to generate dynamic queries inside
- your PL/pgSQL functions, that is,
- queries that will involve different tables or different data types
- each time they are executed. PL/pgSQL's
- normal attempts to cache plans for queries will not work in such
- scenarios. To handle this sort of problem, the EXECUTE statement
- is provided:
-
-
-EXECUTE query-string;
-
-
- where query-string is an expression
- yielding a string (of type
- text) containing the query
- to be executed. This string is fed literally to the SQL engine.
-
-
-
- Note in particular that no substitution of PL/pgSQL>
- variables is done on the query string. The values of variables must
- be inserted in the query string as it is constructed.
-
-
-
- When working with dynamic queries you will have to face
- escaping of single quotes in PL/pgSQL>. Please refer to the
- table in
- for a detailed explanation that will save you some effort.
-
-
-
- Unlike all other queries in PL/pgSQL>, a
- query run by an EXECUTE statement is
- not prepared and saved just once during the life of the server.
- Instead, the query is prepared each
- time the statement is run. The
- query-string can be dynamically
- created within the procedure to perform actions on variable
- tables and fields.
-
-
-
- The results from SELECT queries are discarded by EXECUTE, and
- SELECT INTO is not currently supported within EXECUTE. So, the
- only way to extract a result from a dynamically-created SELECT is
- to use the FOR-IN-EXECUTE form described later.
-
-
-
- An example:
-
-EXECUTE ''UPDATE tbl SET ''
- || quote_ident(fieldname)
- || '' = ''
- || quote_literal(newvalue)
- || '' WHERE ...'';
-
-
-
-
- This example shows use of the functions
- quote_ident(TEXT) and
- quote_literal(TEXT).
- Variables containing field and table identifiers should be
- passed to function quote_ident().
- Variables containing literal elements of the dynamic query
- string should be passed to
- quote_literal(). Both take the
- appropriate steps to return the input text enclosed in single
- or double quotes and with any embedded special characters
- properly escaped.
-
-
-
- Here is a much larger example of a dynamic query and EXECUTE:
-
-CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS '
-DECLARE
- referrer_keys RECORD; -- Declare a generic record to be used in a FOR
- a_output varchar(4000);
-BEGIN
- a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar)
- RETURNS VARCHAR AS ''''
- DECLARE
- v_host ALIAS FOR $1;
- v_domain ALIAS FOR $2;
- v_url ALIAS FOR $3;
- BEGIN '';
-
- --
- -- Notice how we scan through the results of a query in a FOR loop
- -- using the FOR <record> construct.
- --
-
- FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
- a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE ''''''''''
- || referrer_keys.key_string || '''''''''' THEN RETURN ''''''
- || referrer_keys.referrer_type || ''''''; END IF;'';
- END LOOP;
-
- a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE ''''plpgsql'''';'';
-
- -- This works because we are not substituting any variables
- -- Otherwise it would fail. Look at PERFORM for another way to run functions
-
- EXECUTE a_output;
-END;
-' LANGUAGE 'plpgsql';
-
-
-
-
-
- Obtaining result status
-
-
-
-GET DIAGNOSTICS variable = item , ... ;
-
-
- This command allows retrieval of system status indicators. Each
- item is a keyword identifying a state
- value to be assigned to the specified variable (which should be
- of the right data type to receive it). The currently available
- status items are ROW_COUNT>, the number of rows
- processed by the last SQL query sent down to
- the SQL engine; and RESULT_OID>,
- the OID of the last row inserted by the most recent
- SQL query. Note that RESULT_OID>
- is only useful after an INSERT query.
-
-
-
-
-
- Control Structures
-
-
- Control structures are probably the most useful (and
- important) part of PL/pgSQL>. With
- PL/pgSQL>'s control structures,
- you can manipulate PostgreSQL> data in a very
- flexible and powerful way.
-
-
-
- Returning from a function
-
-
-
-RETURN expression;
-
-
- The function terminates and the value of
- expression will be returned to the
- upper executor.
- The expression's result will be automatically cast into the
- function's return type as described for assignments.
-
-
-
- The return value of a function cannot be left undefined. If control
- reaches the end of the top-level block of
- the function without hitting a RETURN statement, a run-time error
- will occur.
-
-
-
-
- Conditionals
-
-
- IF statements let you execute commands based on
- certain conditions.
- PL/pgSQL> has four forms of IF: IF-THEN, IF-THEN-ELSE,
- IF-THEN-ELSE IF, and IF-THEN-ELSIF-THEN-ELSE.
-
-
-
- IF-THEN
-
-
-
-IF boolean-expression THEN
- statements
-END IF;
-
-
- IF-THEN statements are the simplest form of IF. The
- statements between THEN and END IF will be executed if
- the condition is true. Otherwise, they are skipped.
-
-
-IF v_user_id <> 0 THEN
- UPDATE users SET email = v_email WHERE user_id = v_user_id;
-END IF;
-
-
-
-
-
- IF-THEN-ELSE
-
-
-
-IF boolean-expression THEN
- statements
-ELSE
- statements
-END IF;
-
-
- IF-THEN-ELSE statements add to IF-THEN by letting you
- specify an alternative set of statements that should be executed if
- the condition evaluates to FALSE.
-
-
-IF parentid IS NULL or parentid = ''''
-THEN
- return fullname;
-ELSE
- return hp_true_filename(parentid) || ''/'' || fullname;
-END IF;
-
-
-IF v_count > 0 THEN
- INSERT INTO users_count(count) VALUES(v_count);
- return ''t'';
-ELSE
- return ''f'';
-END IF;
-
-
-
-
-
- IF-THEN-ELSE IF
-
-
- IF statements can be nested, as in the following example:
-
-IF demo_row.sex = ''m'' THEN
- pretty_sex := ''man'';
-ELSE
- IF demo_row.sex = ''f'' THEN
- pretty_sex := ''woman'';
- END IF;
-END IF;
-
-
-
-
- When you use this form, you are actually
- nesting an IF statement inside the ELSE part of an outer IF
- statement. Thus you need one END IF statement for each
- nested IF and one for the parent IF-ELSE.
- This is workable but grows tedious when there are many
- alternatives to be checked.
-
-
-
-
- IF-THEN-ELSIF-ELSE
-
-
-
-IF boolean-expression THEN
- statements
- ELSIF boolean-expression THEN
- statements
- ELSIF boolean-expression THEN
- statements
- ...
-
-
- ELSE
- statements
-END IF;
-
-
- IF-THEN-ELSIF-ELSE provides a more convenient method of checking
- many alternatives in one statement. Formally it is equivalent
- to nested IF-THEN-ELSE-IF-THEN commands, but only one END IF
- is needed.
-
-
-
- Here is an example:
-
-
-IF number = 0 THEN
- result := ''zero'';
-ELSIF number > 0 THEN
- result := ''positive'';
-ELSIF number < 0 THEN
- result := ''negative'';
-ELSE
- -- hmm, the only other possibility is that number IS NULL
- result := ''NULL'';
-END IF;
-
-
-
-
- The final ELSE section is optional.
-
-
-
-
-
-
- Simple Loops
-
-
- With the LOOP, EXIT, WHILE and FOR statements, you can arrange
- for your PL/pgSQL function to repeat
- a series of commands.
-
-
-
- LOOP
-
-
-
-<<label>>
-LOOP
- statements
-END LOOP;
-
-
- LOOP defines an unconditional loop that is repeated indefinitely
- until terminated by an EXIT or RETURN statement.
- The optional label can be used by
- EXIT statements in nested loops to specify which level of
- nesting should be terminated.
-
-
-
-
- EXIT
-
-
-
-EXIT label WHEN expression ;
-
-
- If no label is given,
- the innermost loop is terminated and the
- statement following END LOOP is executed next.
- If label is given, it
- must be the label of the current or some outer level of nested loop
- or block. Then the named loop or block is terminated and control
- continues with the statement after the loop's/block's corresponding
- END.
-
-
-
- If WHEN is present, loop exit occurs only if the specified condition
- is true, otherwise control passes to the statement after EXIT.
-
-
-
- Examples:
-
-LOOP
- -- some computations
- IF count > 0 THEN
- EXIT; -- exit loop
- END IF;
-END LOOP;
-
-LOOP
- -- some computations
- EXIT WHEN count > 0;
-END LOOP;
-
-BEGIN
- -- some computations
- IF stocks > 100000 THEN
- EXIT; -- illegal. Can't use EXIT outside of a LOOP
- END IF;
-END;
-
-
-
-
-
- WHILE
-
-
-
-<<label>>
-WHILE expression LOOP
- statements
-END LOOP;
-
-
- The WHILE statement repeats a
- sequence of statements so long as the condition expression
- evaluates to true. The condition is checked just before
- each entry to the loop body.
-
-
-
- For example:
-
-WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
- -- some computations here
-END LOOP;
-
-WHILE NOT boolean_expression LOOP
- -- some computations here
-END LOOP;
-
-
-
-
-
- FOR (integer for-loop)
-
-
-
-<<label>>
-FOR name IN REVERSE expression .. expression LOOP
- statements
-END LOOP;
-
-
- This form of FOR creates a loop that iterates over a range of integer
- values. The variable
- name is automatically defined as type
- integer and exists only inside the loop. The two expressions giving
- the lower and upper bound of the range are evaluated once when entering
- the loop. The iteration step is normally 1, but is -1 when REVERSE is
- specified.
-
-
-
- Some examples of integer FOR loops:
-
-FOR i IN 1..10 LOOP
- -- some expressions here
-
- RAISE NOTICE ''i is %'',i;
-END LOOP;
-
-FOR i IN REVERSE 10..1 LOOP
- -- some expressions here
-END LOOP;
-
-
-
-
-
-
- Looping Through Query Results
-
-
- Using a different type of FOR loop, you can iterate through
- the results of a query and manipulate that data
- accordingly. The syntax is:
-
-<<label>>
-FOR record | row IN select_query LOOP
- statements
-END LOOP;
-
- The record or row variable is successively assigned all the rows
- resulting from the SELECT query and the loop body is executed
- for each row. Here is an example:
-
-
-
-
-CREATE FUNCTION cs_refresh_mviews () RETURNS INTEGER AS '
-DECLARE
- mviews RECORD;
-BEGIN
- PERFORM cs_log(''Refreshing materialized views...'');
-
- FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
-
- -- Now "mviews" has one record from cs_materialized_views
-
- PERFORM cs_log(''Refreshing materialized view '' || quote_ident(mviews.mv_name) || ''...'');
- EXECUTE ''TRUNCATE TABLE '' || quote_ident(mviews.mv_name);
- EXECUTE ''INSERT INTO '' || quote_ident(mviews.mv_name) || '' '' || mviews.mv_query;
- END LOOP;
-
- PERFORM cs_log(''Done refreshing materialized views.'');
- RETURN 1;
-end;
-' LANGUAGE 'plpgsql';
-
-
- If the loop is terminated by an EXIT statement, the last
- assigned row value is still accessible after the loop.
-
-
-
- The FOR-IN-EXECUTE statement is another way to iterate over
- records:
-
-<<label>>
-FOR record | row IN EXECUTE text_expression LOOP
- statements
-END LOOP;
-
- This is like the previous form, except that the source SELECT
- statement is specified as a string expression, which is evaluated
- and re-planned on each entry to the FOR loop. This allows the
- programmer to choose the speed of a pre-planned query or the
- flexibility of a dynamic query, just as with a plain EXECUTE
- statement.
-
-
-
-
- The PL/pgSQL> parser presently distinguishes the
- two kinds of FOR loops (integer or record-returning) by checking
- whether the target variable mentioned just after FOR has been
- declared as a record/row variable. If not, it's presumed to be
- an integer FOR loop. This can cause rather nonintuitive error
- messages when the true problem is, say, that one has
- misspelled the FOR variable name.
-
-
-
-
-
-
- Cursors
-
-
- Rather than executing a whole query at once, it is possible to set
- up a cursor> that encapsulates the query, and then read
- the query result a few rows at a time. One reason for doing this is
- to avoid memory overrun when the result contains a large number of
- rows. (However, PL/pgSQL> users don't normally need
- to worry about that, since FOR loops automatically use a cursor
- internally to avoid memory problems.) A more interesting usage is to
- return a reference to a cursor that it has created, allowing the
- caller to read the rows. This provides a way to return row sets
- from functions.
-
-
-
- Declaring Cursor Variables
-
-
- All access to cursors in PL/pgSQL> goes through
- cursor variables, which are always of the special data type
- refcursor>. One way to create a cursor variable
- is just to declare it as a variable of type refcursor>.
- Another way is to use the cursor declaration syntax,
- which in general is:
-
-name CURSOR ( arguments ) FOR select_query ;
-
- (FOR> may be replaced by IS> for Oracle
- compatibility.) arguments, if any,
- are a comma-separated list of name
- datatype pairs that define names to
- be replaced by parameter values in the given query. The actual
- values to substitute for these names will be specified later,
- when the cursor is opened.
-
-
- Some examples:
-
-DECLARE
- curs1 refcursor;
- curs2 CURSOR FOR SELECT * from tenk1;
- curs3 CURSOR (key int) IS SELECT * from tenk1 where unique1 = key;
-
- All three of these variables have the data type refcursor>,
- but the first may be used with any query, while the second has
- a fully specified query already bound> to it, and the last
- has a parameterized query bound to it. (key> will be
- replaced by an integer parameter value when the cursor is opened.)
- The variable curs1>
- is said to be unbound> since it is not bound to
- any particular query.
-
-
-
-
- Opening Cursors
-
-
- Before a cursor can be used to retrieve rows, it must be
- opened>. (This is the equivalent action to the SQL
- command DECLARE CURSOR>.) PL/pgSQL> has
- four forms of the OPEN statement, two of which use unbound cursor
- variables and the other two use bound cursor variables.
-
-
-
- OPEN FOR SELECT
-
-
-
-OPEN unbound-cursor FOR SELECT ...;
-
-
- The cursor variable is opened and given the specified query
- to execute. The cursor cannot be open already, and it must
- have been declared as an unbound cursor (that is, as a simple
- refcursor> variable). The SELECT query is treated
- in the same way as other SELECTs in PL/pgSQL>:
- PL/pgSQL> variable names are substituted,
- and the query plan is cached for possible re-use.
-
-
-OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
-
-
-
-
-
- OPEN FOR EXECUTE
-
-
-
-OPEN unbound-cursor FOR EXECUTE query-string;
-
-
- The cursor variable is opened and given the specified query
- to execute. The cursor cannot be open already, and it must
- have been declared as an unbound cursor (that is, as a simple
- refcursor> variable). The query is specified as a
- string expression in the same way as in the EXECUTE command.
- As usual, this gives flexibility so the query can vary
- from one run to the next.
-
-
-OPEN curs1 FOR EXECUTE ''SELECT * FROM '' || quote_ident($1);
-
-
-
-
-
- OPENing a bound cursor
-
-
-
-OPEN bound-cursor ( argument_values ) ;
-
-
- This form of OPEN is used to open a cursor variable whose query
- was bound to it when it was declared.
- The cursor cannot be open already. A list of actual argument
- value expressions must appear if and only if the cursor was
- declared to take arguments. These values will be substituted
- in the query.
- The query plan for a bound cursor is always considered
- cacheable --- there is no equivalent of EXECUTE in this case.
-
-
-OPEN curs2;
-OPEN curs3(42);
-
-
-
-
-
-
- Using Cursors
-
-
- Once a cursor has been opened, it can be manipulated with the
- statements described here.
-
-
-
- These manipulations need not occur in the same function that
- opened the cursor to begin with. You can return a refcursor>
- value out of a function and let the caller operate on the cursor.
- (Internally, a refcursor> value is simply the string name
- of a Portal containing the active query for the cursor. This name
- can be passed around, assigned to other refcursor> variables,
- and so on, without disturbing the Portal.)
-
-
-
- All Portals are implicitly closed at transaction end. Therefore
- a refcursor> value is useful to reference an open cursor
- only until the end of the transaction.
-
-
-
- FETCH
-
-
-
-FETCH cursor INTO target;
-
-
- FETCH retrieves the next row from the cursor into a target,
- which may be a row variable, a record variable, or a comma-separated
- list of simple variables, just like SELECT INTO. As with
- SELECT INTO, the special variable FOUND may be checked to see
- whether a row was obtained or not.
-
-
-FETCH curs1 INTO rowvar;
-FETCH curs2 INTO foo,bar,baz;
-
-
-
-
-
- CLOSE
-
-
-
-CLOSE cursor;
-
-
- CLOSE closes the Portal underlying an open cursor.
- This can be used to release resources earlier than end of
- transaction, or to free up the cursor variable to be opened again.
-
-
-CLOSE curs1;
-
-
-
-
-
- Returning Cursors
-
-
-
- PL/pgSQL> functions can return cursors to the
- caller. This is used to return multiple rows or columns from the
- function. The function opens the cursor and returns the cursor
- name to the caller. The caller can then FETCH rows from the
- cursor. The cursor can be CLOSEd by the caller, or it will be
- closed automatically when the transaction closes.
-
-
-
-
- The cursor name returned by the function can be specified by the
- caller or automatically generated. The following example shows
- how a cursor name can be supplied by the caller:
-
-
-CREATE TABLE test (col text);
-INSERT INTO test VALUES ('123');
-
-CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
-BEGIN
- OPEN $1 FOR SELECT col FROM test;
- RETURN $1;
-END;
-' LANGUAGE 'plpgsql';
-
-BEGIN;
-SELECT reffunc('funccursor');
-FETCH ALL IN funccursor;
-COMMIT;
-
-
-
-
- The following example uses automatic cursor name generation:
-
-
-CREATE FUNCTION reffunc2() RETURNS refcursor AS '
-DECLARE
- ref refcursor;
-BEGIN
- OPEN ref FOR SELECT col FROM test;
- RETURN ref;
-END;
-' LANGUAGE 'plpgsql';
-
-BEGIN;
-SELECT reffunc2();
-
- reffunc2
- --------------------
- <unnamed cursor 1>
- (1 row)
-
-FETCH ALL IN "<unnamed cursor 1>";
-COMMIT;
-
-
-
-
-
-
-
- Errors and Messages
-
-
- Use the RAISE statement to report messages and raise errors.
-
-
-RAISE level 'format' , variable ...;
-
-
- Possible levels are DEBUG (write the message into the postmaster log),
- NOTICE (write the message into the postmaster log and forward it to
- the client application) and EXCEPTION (raise an error,
- aborting the transaction).
-
-
-
- Inside the format string, % is replaced by the next
- optional argument's external representation.
- Write %% to emit a literal %.
- Note that the optional arguments must presently
- be simple variables, not expressions, and the format must be a simple
- string literal.
-
-
-
-
-
- Examples:
-
-RAISE NOTICE ''Calling cs_create_job(%)'',v_job_id;
-
- In this example, the value of v_job_id will replace the % in the
- string.
-
-
-
-
-RAISE EXCEPTION ''Inexistent ID --> %'',user_id;
-
- This will abort the transaction with the given error message.
-
-
-
- Exceptions
-
-
- PostgreSQL does not have a very smart
- exception handling model. Whenever the parser, planner/optimizer
- or executor decide that a statement cannot be processed any longer,
- the whole transaction gets aborted and the system jumps back
- into the main loop to get the next query from the client application.
-
-
-
- It is possible to hook into the error mechanism to notice that this
- happens. But currently it is impossible to tell what really
- caused the abort (input/output conversion error, floating-point
- error, parse error). And it is possible that the database backend
- is in an inconsistent state at this point so returning to the upper
- executor or issuing more commands might corrupt the whole database.
-
-
-
- Thus, the only thing PL/pgSQL currently does when it encounters
- an abort during execution of a function or trigger
- procedure is to write some additional NOTICE level log messages
- telling in which function and where (line number and type of
- statement) this happened. The error always stops execution of
- the function.
-
-
-
-
-
- Trigger Procedures
-
-
- PL/pgSQL can be used to define trigger
- procedures. A trigger procedure is created with the CREATE
- FUNCTION command as a function with no arguments and a return
- type of OPAQUE. Note that the function must be declared
- with no arguments even if it expects to receive arguments specified
- in CREATE TRIGGER> --- trigger arguments are passed via
- TG_ARGV>, as described below.
-
-
-
- When a PL/pgSQL function is called as a
- trigger, several special variables are created automatically in the
- top-level block. They are:
-
-
-
- NEW
-
-
- Data type RECORD; variable holding the new database row for INSERT/UPDATE
- operations in ROW level triggers.
-
-
-
-
-
- OLD
-
-
- Data type RECORD; variable holding the old database row for UPDATE/DELETE
- operations in ROW level triggers.
-
-
-
-
-
- TG_NAME
-
-
- Data type name; variable that contains the name of the trigger actually
- fired.
-
-
-
-
-
- TG_WHEN
-
-
- Data type text; a string of either
- BEFORE or AFTER
- depending on the trigger's definition.
-
-
-
-
-
- TG_LEVEL
-
-
- Data type text; a string of either
- ROW or STATEMENT depending on the
- trigger's definition.
-
-
-
-
-
- TG_OP
-
-
- Data type text; a string of
- INSERT, UPDATE
- or DELETE telling
- for which operation the trigger is fired.
-
-
-
-
-
- TG_RELID
-
-
- Data type oid; the object ID of the table that caused the
- trigger invocation.
-
-
-
-
-
- TG_RELNAME
-
-
- Data type name; the name of the table that caused the trigger
- invocation.
-
-
-
-
-
- TG_NARGS
-
-
- Data type integer; the number of arguments given to the trigger
- procedure in the CREATE TRIGGER statement.
-
-
-
-
-
- TG_ARGV[]
-
-
- Data type array of text; the arguments from
- the CREATE TRIGGER statement.
- The index counts from 0 and can be given as an expression. Invalid
- indices (< 0 or >= tg_nargs) result in a NULL value.
-
-
-
-
-
-
-
- A trigger function must return either NULL or a record/row value
- having exactly the structure of the table the trigger was fired for.
- Triggers fired BEFORE may return NULL to signal the trigger manager
- to skip the rest of the operation for this row (ie, subsequent triggers
- are not fired, and the INSERT/UPDATE/DELETE does not occur for this
- row). If a non-NULL value is returned then the operation proceeds with
- that row value. Note that returning a row value different from the
- original value of NEW alters the row that will be inserted or updated.
- It is possible to replace single values directly
- in NEW and return that, or to build a complete new record/row to
- return.
-
-
-
- The return value of a trigger fired AFTER is ignored; it may as well
- always return a NULL value. But an AFTER trigger can still abort the
- operation by raising an error.
-
-
-
- A PL/pgSQL Trigger Procedure Example
-
-
- This example trigger ensures that any time a row is inserted or updated
- in the table, the current user name and time are stamped into the
- row. And it ensures that an employee's name is given and that the
- salary is a positive value.
-
-
-CREATE TABLE emp (
- empname text,
- salary integer,
- last_date timestamp,
- last_user text
-);
-
-CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS '
- BEGIN
- -- Check that empname and salary are given
- IF NEW.empname ISNULL THEN
- RAISE EXCEPTION ''empname cannot be NULL value'';
- END IF;
- IF NEW.salary ISNULL THEN
- RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
- END IF;
-
- -- Who works for us when she must pay for?
- IF NEW.salary < 0 THEN
- RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
- END IF;
-
- -- Remember who changed the payroll when
- NEW.last_date := ''now'';
- NEW.last_user := current_user;
- RETURN NEW;
- END;
-' LANGUAGE 'plpgsql';
-
-CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
- FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
-
-
-
-
-
-
-
-
- Examples
-
-
- Here are only a few functions to demonstrate how easy it is to
- write PL/pgSQL>
- functions. For more complex examples the programmer
- might look at the regression test for PL/pgSQL>.
-
-
-
- One painful detail in writing functions in PL/pgSQL is the handling
- of single quotes. The function's source text in CREATE FUNCTION must
- be a literal string. Single quotes inside of literal strings must be
- either doubled or quoted with a backslash. We are still looking for
- an elegant alternative. In the meantime, doubling the single quotes
- as in the examples below should be used. Any solution for this
- in future versions of PostgreSQL will be
- forward compatible.
-
-
-
- For a detailed explanation and examples of how to escape single
- quotes in different situations, please see .
-
-
-
- A Simple PL/pgSQL Function to Increment an Integer
-
-
- The following two PL/pgSQL functions are identical to their
- counterparts from the C language function discussion. This
- function receives an integer and increments it by
- one, returning the incremented value.
-
-
-
-CREATE FUNCTION add_one (integer) RETURNS INTEGER AS '
- BEGIN
- RETURN $1 + 1;
- END;
-' LANGUAGE 'plpgsql';
-
-
-
-
- A Simple PL/pgSQL Function to Concatenate Text
-
-
- This function receives two text parameters and
- returns the result of concatenating them.
-
-
-
-CREATE FUNCTION concat_text (TEXT, TEXT) RETURNS TEXT AS '
- BEGIN
- RETURN $1 || $2;
- END;
-' LANGUAGE 'plpgsql';
-
-
-
-
- A PL/pgSQL Function on Composite Type
-
-
- In this example, we take EMP> (a table) and an
- integer as arguments to our function, which returns
- a boolean. If the salary> field of the EMP> table is
- NULL, we return f>. Otherwise we compare with
- that field with the integer passed to the function
- and return the boolean result of the comparison (t
- or f). This is the PL/pgSQL equivalent to the example from the C
- functions.
-
-
-
-CREATE FUNCTION c_overpaid (EMP, INTEGER) RETURNS BOOLEAN AS '
- DECLARE
- emprec ALIAS FOR $1;
- sallim ALIAS FOR $2;
- BEGIN
- IF emprec.salary ISNULL THEN
- RETURN ''f'';
- END IF;
- RETURN emprec.salary > sallim;
- END;
-' LANGUAGE 'plpgsql';
-
-
-
-
-
-
-
-
-
-
- February 2001
-
-
- Roberto
- Mello
-
-
- rmello@fslc.usu.edu
-
-
-
-
-
-
- Porting from Oracle PL/SQL
-
-
- Oracle
-
-
-
- PL/SQL
-
-
-
- Author
-
- Roberto Mello (rmello@fslc.usu.edu)
-
-
-
-
- This section explains differences between Oracle's PL/SQL and
- PostgreSQL>'s PL/pgSQL languages in the hopes of helping developers
- port applications from Oracle to PostgreSQL>. Most of the code here
- is from the ArsDigita
- Clickstream
- module that I ported to PostgreSQL> when I took an
- internship with OpenForce
- Inc. in the Summer of 2000.
-
-
-
- PL/pgSQL is similar to PL/SQL in many aspects. It is a block
- structured, imperative language (all variables have to be
- declared). PL/SQL has many more features than its PostgreSQL>
- counterpart, but PL/pgSQL allows for a great deal of functionality
- and it is being improved constantly.
-
-
-
- Main Differences
-
-
- Some things you should keep in mind when porting from Oracle to PostgreSQL>:
-
-
-
-
- No default parameters in PostgreSQL>.
-
-
-
-
-
- You can overload functions in PostgreSQL>. This is often used to work
- around the lack of default parameters.
-
-
-
-
-
- Assignments, loops and conditionals are similar.
-
-
-
-
-
- No need for cursors in PostgreSQL>, just put the query in the FOR
- statement (see example below)
-
-
-
-
-
- In PostgreSQL> you need to escape single
- quotes. See .
-
-
-
-
-
-
- Quote Me on That: Escaping Single Quotes
-
-
- In PostgreSQL> you need to escape single quotes inside your
- function definition. This can lead to quite amusing code at
- times, especially if you are creating a function that generates
- other function(s), as in
- .
- One thing to keep in mind
- when escaping lots of single quotes is that, except for the
- beginning/ending quotes, all the others will come in even
- quantity.
-
-
-
- gives the scoop. (You'll
- love this little chart.)
-
-
-
- Single Quotes Escaping Chart
-
-
-
-
- No. of Quotes
- Usage
- Example
- Result
-
-
-
-
-
- 1
- To begin/terminate function bodies
-
-CREATE FUNCTION foo() RETURNS INTEGER AS '...'
-LANGUAGE 'plpgsql';
-
- as is
-
-
-
- 2
- In assignments, SELECTs, to delimit strings, etc.
-
-a_output := ''Blah'';
-SELECT * FROM users WHERE f_name=''foobar'';
-
- SELECT * FROM users WHERE f_name='foobar';
-
-
-
- 4
-
- When you need two single quotes in your resulting string
- without terminating that string.
-
-
-a_output := a_output || '' AND name
- LIKE ''''foobar'''' AND ...''
-
- AND name LIKE 'foobar' AND ...
-
-
-
- 6
-
- When you want double quotes in your resulting string
- and terminate that string.
-
-
-a_output := a_output || '' AND name
- LIKE ''''foobar''''''
-
-
- AND name LIKE 'foobar'
-
-
-
-
- 10
-
- When you want two single quotes in the resulting string
- (which accounts for 8 quotes) and
- terminate that string (2 more). You will probably only need
- that if you were using a function to generate other functions
- (like in ).
-
-
-a_output := a_output || '' if v_'' ||
- referrer_keys.kind || '' like ''''''''''
- || referrer_keys.key_string || ''''''''''
- then return '''''' || referrer_keys.referrer_type
- || ''''''; end if;'';
-
-
- if v_<...> like ''<...>'' then return ''<...>''; end if;
-
-
-
-
-
-
-
-
-
-
- Porting Functions
-
-
-
-
- A Simple Function
-
-
-
- Here is an Oracle function:
-
-CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name IN varchar, v_version IN varchar)
-RETURN varchar IS
-BEGIN
- IF v_version IS NULL THEN
- RETURN v_name;
- END IF;
- RETURN v_name || '/' || v_version;
-END;
-/
-SHOW ERRORS;
-
-
-
-
- Let's go through this function and see the differences to PL/pgSQL>:
-
-
-
-
- PostgreSQL does not have named
- parameters. You have to explicitly alias them inside your
- function.
-
-
-
-
-
- Oracle can have IN, OUT,
- and INOUT parameters passed to functions.
- The INOUT, for example, means that the
- parameter will receive a value and return another. PostgreSQL>
- only has IN
parameters and functions can return
- only a single value.
-
-
-
-
-
- The RETURN key word in the function
- prototype (not the function body) becomes
- RETURNS in PostgreSQL>.
-
-
-
-
-
- On PostgreSQL> functions are created using single quotes as
- delimiters, so you have to escape single quotes inside your
- functions (which can be quite annoying at times; see ).
-
-
-
-
-
- The /show errors command does not exist in
- PostgreSQL>.
-
-
-
-
-
-
- So let's see how this function would look when ported to
- PostgreSQL>:
-
-
-CREATE OR REPLACE FUNCTION cs_fmt_browser_version(VARCHAR, VARCHAR)
-RETURNS VARCHAR AS '
-DECLARE
- v_name ALIAS FOR $1;
- v_version ALIAS FOR $2;
-BEGIN
- IF v_version IS NULL THEN
- return v_name;
- END IF;
- RETURN v_name || ''/'' || v_version;
-END;
-' LANGUAGE 'plpgsql';
-
-
-
-
-
-
- A Function that Creates Another Function
-
-
-
- The following procedure grabs rows from a
- SELECT statement and builds a large function
- with the results in IF statements, for the
- sake of efficiency. Notice particularly the differences in
- cursors, FOR loops, and the need to escape
- single quotes in PostgreSQL>.
-
-
-CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
- CURSOR referrer_keys IS
- SELECT * FROM cs_referrer_keys
- ORDER BY try_order;
-
- a_output VARCHAR(4000);
-BEGIN
- a_output := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR, v_domain IN VARCHAR,
-v_url IN VARCHAR) RETURN VARCHAR IS BEGIN';
-
- FOR referrer_key IN referrer_keys LOOP
- a_output := a_output || ' IF v_' || referrer_key.kind || ' LIKE ''' ||
-referrer_key.key_string || ''' THEN RETURN ''' || referrer_key.referrer_type ||
-'''; END IF;';
- END LOOP;
-
- a_output := a_output || ' RETURN NULL; END;';
- EXECUTE IMMEDIATE a_output;
-END;
-/
-show errors
-
-
-
-
- Here is how this function would end up in PostgreSQL>:
-
-
-CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS '
-DECLARE
- referrer_keys RECORD; -- Declare a generic record to be used in a FOR
- a_output varchar(4000);
-BEGIN
- a_output := ''CREATE FUNCTION cs_find_referrer_type(VARCHAR,VARCHAR,VARCHAR)
- RETURNS VARCHAR AS ''''
- DECLARE
- v_host ALIAS FOR $1;
- v_domain ALIAS FOR $2;
- v_url ALIAS FOR $3;
- BEGIN '';
-
- --
- -- Notice how we scan through the results of a query in a FOR loop
- -- using the FOR <record> construct.
- --
-
- FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
- a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE ''''''''''
- || referrer_keys.key_string || '''''''''' THEN RETURN ''''''
- || referrer_keys.referrer_type || ''''''; END IF;'';
- END LOOP;
-
- a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE ''''plpgsql'''';'';
-
- -- This works because we are not substituting any variables
- -- Otherwise it would fail. Look at PERFORM for another way to run functions
-
- EXECUTE a_output;
-END;
-' LANGUAGE 'plpgsql';
-
-
-
-
-
-
- A Procedure with a lot of String Manipulation and OUT Parameters
-
-
-
- The following Oracle PL/SQL procedure is used to parse a URL and
- return several elements (host, path and query). It is an
- procedure because in PL/pgSQL functions only one value can be returned
- (see ). In
- PostgreSQL>, one way to work around this is to split the procedure
- in three different functions: one to return the host, another for
- the path and another for the query.
-
-
-
-CREATE OR REPLACE PROCEDURE cs_parse_url(
- v_url IN VARCHAR,
- v_host OUT VARCHAR, -- This will be passed back
- v_path OUT VARCHAR, -- This one too
- v_query OUT VARCHAR) -- And this one
-is
- a_pos1 INTEGER;
- a_pos2 INTEGER;
-begin
- v_host := NULL;
- v_path := NULL;
- v_query := NULL;
- a_pos1 := instr(v_url, '//'); -- PostgreSQL> doesn't have an instr function
-
- IF a_pos1 = 0 THEN
- RETURN;
- END IF;
- a_pos2 := instr(v_url, '/', a_pos1 + 2);
- IF a_pos2 = 0 THEN
- v_host := substr(v_url, a_pos1 + 2);
- v_path := '/';
- RETURN;
- END IF;
-
- v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
- a_pos1 := instr(v_url, '?', a_pos2 + 1);
-
- IF a_pos1 = 0 THEN
- v_path := substr(v_url, a_pos2);
- RETURN;
- END IF;
-
- v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
- v_query := substr(v_url, a_pos1 + 1);
-END;
-/
-show errors;
-
-
-
- Here is how this procedure could be translated for PostgreSQL>:
-
-
-CREATE OR REPLACE FUNCTION cs_parse_url_host(VARCHAR) RETURNS VARCHAR AS '
-DECLARE
- v_url ALIAS FOR $1;
- v_host VARCHAR;
- v_path VARCHAR;
- a_pos1 INTEGER;
- a_pos2 INTEGER;
- a_pos3 INTEGER;
-BEGIN
- v_host := NULL;
- a_pos1 := instr(v_url,''//'');
-
- IF a_pos1 = 0 THEN
- RETURN ''''; -- Return a blank
- END IF;
-
- a_pos2 := instr(v_url,''/'',a_pos1 + 2);
- IF a_pos2 = 0 THEN
- v_host := substr(v_url, a_pos1 + 2);
- v_path := ''/'';
- RETURN v_host;
- END IF;
-
- v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2 );
- RETURN v_host;
-END;
-' LANGUAGE 'plpgsql';
-
-
-
-
-
-
- PostgreSQL> does not have an instr function,
- so you can work around it using a combination of other functions.
- I got tired of doing this and created my own
- instr functions that behave exactly like
- Oracle's (it makes life easier). See the for the code.
-
-
-
-
-
-
- Procedures
-
-
-
- Oracle procedures give a little more flexibility to the developer
- because nothing needs to be explicitly returned, but it can be
- through the use of INOUT> or OUT> parameters.
-
-
-
- An example:
-
-
-CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
- a_running_job_count INTEGER;
- PRAGMA AUTONOMOUS_TRANSACTION;
-BEGIN
- LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
-
- SELECT count(*) INTO a_running_job_count
- FROM cs_jobs
- WHERE end_stamp IS NULL;
-
- IF a_running_job_count > 0 THEN
- COMMIT; -- free lock
- raise_application_error(-20000, 'Unable to create a new job: a job is currently running.');
- END IF;
-
- DELETE FROM cs_active_job;
- INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
-
- BEGIN
- INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, sysdate);
- EXCEPTION WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
- END;
- COMMIT;
-END;
-/
-show errors
-
-
-
-
- Procedures like this can be easily converted into PostgreSQL>
- functions returning an INTEGER. This procedure in
- particular is interesting because it can teach us some things:
-
-
-
-
- There is no pragma statement in PostgreSQL>.
-
-
-
-
-
- If you do a LOCK TABLE in PL/pgSQL>, the lock
- will not be released until the calling transaction is finished.
-
-
-
-
-
- You also cannot have transactions in PL/pgSQL procedures. The
- entire function (and other functions called from therein) is
- executed in a transaction and PostgreSQL> rolls back the results if
- something goes wrong. Therefore only one
- BEGIN statement is allowed.
-
-
-
-
-
- The exception when would have to be replaced by an
- IF statement.
-
-
-
-
-
-
- So let's see one of the ways we could port this procedure to PL/pgSQL>:
-
-
-CREATE OR REPLACE FUNCTION cs_create_job(INTEGER) RETURNS INTEGER AS '
-DECLARE
- v_job_id ALIAS FOR $1;
- a_running_job_count INTEGER;
- a_num INTEGER;
- -- PRAGMA AUTONOMOUS_TRANSACTION;
-BEGIN
- LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
- SELECT count(*) INTO a_running_job_count
- FROM cs_jobs
- WHERE end_stamp IS NULL;
-
- IF a_running_job_count > 0
- THEN
- -- COMMIT; -- free lock
- RAISE EXCEPTION ''Unable to create a new job: a job is currently running.'';
- END IF;
-
- DELETE FROM cs_active_job;
- INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
-
- SELECT count(*) into a_num
- FROM cs_jobs
- WHERE job_id=v_job_id;
- IF NOT FOUND THEN -- If nothing was returned in the last query
- -- This job is not in the table so lets insert it.
- INSERT INTO cs_jobs(job_id, start_stamp) VALUES (v_job_id, sysdate());
- RETURN 1;
- ELSE
- RAISE NOTICE ''Job already running.'';
- END IF;
-
- RETURN 0;
-END;
-' LANGUAGE 'plpgsql';
-
-
-
-
-
- Notice how you can raise notices (or errors) in PL/pgSQL>.
-
-
-
-
-
-
-
-
- Packages
-
-
-
-
- I haven't done much with packages myself, so if there are
- mistakes here, please let me know.
-
-
-
-
- Packages are a way Oracle gives you to encapsulate PL/SQL
- statements and functions into one entity, like Java classes, where
- you define methods and objects. You can access these
- objects/methods with a .
- (dot). Here is an example of an Oracle package from ACS 4 (the
- ArsDigita Community
- System):
-
-
-CREATE OR REPLACE PACKAGE BODY acs
-AS
- FUNCTION add_user (
- user_id IN users.user_id%TYPE DEFAULT NULL,
- object_type IN acs_objects.object_type%TYPE DEFAULT 'user',
- creation_date IN acs_objects.creation_date%TYPE DEFAULT sysdate,
- creation_user IN acs_objects.creation_user%TYPE DEFAULT NULL,
- creation_ip IN acs_objects.creation_ip%TYPE DEFAULT NULL,
- ...
- ) RETURN users.user_id%TYPE
- IS
- v_user_id users.user_id%TYPE;
- v_rel_id membership_rels.rel_id%TYPE;
- BEGIN
- v_user_id := acs_user.new (user_id, object_type, creation_date,
- creation_user, creation_ip, email, ...
- RETURN v_user_id;
- END;
-END acs;
-/
-show errors
-
-
-
-
- We port this to PostgreSQL> by creating the different objects of
- the Oracle package as functions with a standard naming
- convention. We have to pay attention to some other details, like
- the lack of default parameters in PostgreSQL> functions. The above
- package would become something like this:
-
-
-CREATE FUNCTION acs__add_user(INTEGER,INTEGER,VARCHAR,TIMESTAMP,INTEGER,INTEGER,...)
-RETURNS INTEGER AS '
-DECLARE
- user_id ALIAS FOR $1;
- object_type ALIAS FOR $2;
- creation_date ALIAS FOR $3;
- creation_user ALIAS FOR $4;
- creation_ip ALIAS FOR $5;
- ...
- v_user_id users.user_id%TYPE;
- v_rel_id membership_rels.rel_id%TYPE;
-BEGIN
- v_user_id := acs_user__new(user_id,object_type,creation_date,creation_user,creation_ip, ...);
- ...
-
- RETURN v_user_id;
-END;
-' LANGUAGE 'plpgsql';
-
-
-
-
-
-
- Other Things to Watch For
-
-
-
- EXECUTE
-
-
- The PostgreSQL> version of EXECUTE works
- nicely, but you have to remember to use
- quote_literal(TEXT) and
- quote_string(TEXT) as described in . Constructs of the type
- EXECUTE ''SELECT * from $1''; will not work
- unless you use these functions.
-
-
-
-
- Optimizing PL/pgSQL Functions
-
-
- PostgreSQL> gives you two function creation modifiers to optimize
- execution: iscachable (function always returns
- the same result when given the same arguments) and
- isstrict (function returns NULL if any
- argument is NULL). Consult the CREATE
- FUNCTION reference for details.
-
-
-
- To make use of these optimization attributes, you have to use the
- WITH modifier in your CREATE
- FUNCTION statement. Something like:
-
-
-CREATE FUNCTION foo(...) RETURNS INTEGER AS '
-...
-' LANGUAGE 'plpgsql'
-WITH (isstrict, iscachable);
-
-
-
-
-
-
-
- Appendix
-
-
-
-
- Code for my instr functions
-
-
-
- This function should probably be integrated into the core.
-
-
-
---
--- instr functions that mimic Oracle's counterpart
--- Syntax: instr(string1,string2,[n],[m]) where [] denotes optional params.
---
--- Searches string1 beginning at the nth character for the mth
--- occurrence of string2. If n is negative, search backwards. If m is
--- not passed, assume 1 (search starts at first character).
---
--- by Roberto Mello (rmello@fslc.usu.edu)
--- modified by Robert Gaszewski (graszew@poland.com)
--- Licensed under the GPL v2 or later.
---
-
-CREATE FUNCTION instr(VARCHAR,VARCHAR) RETURNS INTEGER AS '
-DECLARE
- pos integer;
-BEGIN
- pos:= instr($1,$2,1);
- RETURN pos;
-END;
-' LANGUAGE 'plpgsql';
-
-
-CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER) RETURNS INTEGER AS '
-DECLARE
- string ALIAS FOR $1;
- string_to_search ALIAS FOR $2;
- beg_index ALIAS FOR $3;
- pos integer NOT NULL DEFAULT 0;
- temp_str VARCHAR;
- beg INTEGER;
- length INTEGER;
- ss_length INTEGER;
-BEGIN
- IF beg_index > 0 THEN
-
- temp_str := substring(string FROM beg_index);
- pos := position(string_to_search IN temp_str);
-
- IF pos = 0 THEN
- RETURN 0;
- ELSE
- RETURN pos + beg_index - 1;
- END IF;
- ELSE
- ss_length := char_length(string_to_search);
- length := char_length(string);
- beg := length + beg_index - ss_length + 2;
-
- WHILE beg > 0 LOOP
- temp_str := substring(string FROM beg FOR ss_length);
- pos := position(string_to_search IN temp_str);
-
- IF pos > 0 THEN
- RETURN beg;
- END IF;
-
- beg := beg - 1;
- END LOOP;
- RETURN 0;
- END IF;
-END;
-' LANGUAGE 'plpgsql';
-
---
--- Written by Robert Gaszewski (graszew@poland.com)
--- Licensed under the GPL v2 or later.
---
-CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER,INTEGER) RETURNS INTEGER AS '
-DECLARE
- string ALIAS FOR $1;
- string_to_search ALIAS FOR $2;
- beg_index ALIAS FOR $3;
- occur_index ALIAS FOR $4;
- pos integer NOT NULL DEFAULT 0;
- occur_number INTEGER NOT NULL DEFAULT 0;
- temp_str VARCHAR;
- beg INTEGER;
- i INTEGER;
- length INTEGER;
- ss_length INTEGER;
-BEGIN
- IF beg_index > 0 THEN
- beg := beg_index;
- temp_str := substring(string FROM beg_index);
-
- FOR i IN 1..occur_index LOOP
- pos := position(string_to_search IN temp_str);
-
- IF i = 1 THEN
- beg := beg + pos - 1;
- ELSE
- beg := beg + pos;
- END IF;
-
- temp_str := substring(string FROM beg + 1);
- END LOOP;
-
- IF pos = 0 THEN
- RETURN 0;
- ELSE
- RETURN beg;
- END IF;
- ELSE
- ss_length := char_length(string_to_search);
- length := char_length(string);
- beg := length + beg_index - ss_length + 2;
-
- WHILE beg > 0 LOOP
- temp_str := substring(string FROM beg FOR ss_length);
- pos := position(string_to_search IN temp_str);
-
- IF pos > 0 THEN
- occur_number := occur_number + 1;
-
- IF occur_number = occur_index THEN
- RETURN beg;
- END IF;
- END IF;
-
- beg := beg - 1;
- END LOOP;
-
- RETURN 0;
- END IF;
-END;
-' LANGUAGE 'plpgsql';
-
-
-
-
-
-
-
-
-
--
cgit v1.2.3