2 $Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.19 2003/05/28 16:03:55 tgl Exp $
6 <title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
8 <indexterm zone="plpgsql">
9 <primary>PL/pgSQL</primary>
13 <application>PL/pgSQL</application> is a loadable procedural
14 language for the <productname>PostgreSQL</productname> database
15 system. The design goals of <application>PL/pgSQL</> were to create
16 a loadable procedural language that
21 can be used to create functions and trigger procedures,
26 adds control structures to the <acronym>SQL</acronym> language,
31 can perform complex computations,
36 inherits all user-defined types, functions, and operators,
41 can be defined to be trusted by the server,
52 <sect1 id="plpgsql-overview">
53 <title>Overview</title>
56 The <application>PL/pgSQL</> call handler parses the function's source text and
57 produces an internal binary instruction tree the first time the
58 function is called (within each session). The instruction tree
60 <application>PL/pgSQL</> statement structure, but individual
61 <acronym>SQL</acronym> expressions and <acronym>SQL</acronym> commands
62 used in the function are not translated immediately.
66 As each expression and <acronym>SQL</acronym> command is first used
67 in the function, the <application>PL/pgSQL</> interpreter creates
68 a prepared execution plan (using the <acronym>SPI</acronym>
69 manager's <function>SPI_prepare</function> and
70 <function>SPI_saveplan</function> functions). Subsequent visits
71 to that expression or command reuse the prepared plan. Thus, a
72 function with conditional code that contains many statements for
73 which execution plans might be required will only prepare and save
74 those plans that are really used during the lifetime of the
75 database connection. This can substantially reduce the total
76 amount of time required to parse, and generate execution plans for the
77 statements in a <application>PL/pgSQL</> function. A disadvantage is
78 that errors in a specific expression or command may not be detected
79 until that part of the function is reached in execution.
83 Once <application>PL/pgSQL</> has made an execution plan for a particular
84 command in a function, it will reuse that plan for the life of the
85 database connection. This is usually a win for performance, but it
86 can cause some problems if you dynamically
87 alter your database schema. For example:
90 CREATE FUNCTION populate() RETURNS integer AS '
94 PERFORM my_function();
99 If you execute the above function, it will reference the OID for
100 <function>my_function()</function> in the execution plan produced for
101 the <command>PERFORM</command> statement. Later, if you
102 drop and recreate <function>my_function()</function>, then
103 <function>populate()</function> will not be able to find
104 <function>my_function()</function> anymore. You would then have to
105 recreate <function>populate()</function>, or at least start a new
106 database session so that it will be compiled afresh. Another way
107 to avoid this problem is to use <command>CREATE OR REPLACE
108 FUNCTION</command> when updating the definition of
109 <function>my_function</function> (when a function is
110 <quote>replaced</quote>, its OID is not changed).
114 Because <application>PL/pgSQL</application> saves execution plans
115 in this way, SQL commands that appear directly in a
116 <application>PL/pgSQL</application> function must refer to the
117 same tables and columns on every execution; that is, you cannot use
118 a parameter as the name of a table or column in an SQL command. To get
119 around this restriction, you can construct dynamic commands using
120 the <application>PL/pgSQL</application> <command>EXECUTE</command>
121 statement --- at the price of constructing a new execution plan on
127 The <application>PL/pgSQL</application>
128 <command>EXECUTE</command> statement is not related to the
129 <command>EXECUTE</command> statement supported by the
130 <productname>PostgreSQL</productname> server. The server's
131 <command>EXECUTE</command> statement cannot be used within
132 <application>PL/pgSQL</> functions (and is not needed).
137 Except for input/output conversion and calculation functions
138 for user-defined types, anything that can be defined in C language
139 functions can also be done with <application>PL/pgSQL</application>.
140 For example, it is possible to
141 create complex conditional computation functions and later use
142 them to define operators or use them in index expressions.
145 <sect2 id="plpgsql-advantages">
146 <title>Advantages of Using <application>PL/pgSQL</application></title>
149 <acronym>SQL</acronym> is the language <productname>PostgreSQL</>
150 (and most other relational databases) use as query language. It's
151 portable and easy to learn. But every <acronym>SQL</acronym>
152 statement must be executed individually by the database server.
156 That means that your client application must send each query to
157 the database server, wait for it to process it, receive the
158 results, do some computation, then send other queries to the
159 server. All this incurs interprocess communication and may also
160 incur network overhead if your client is on a different machine
161 than the database server.
165 With <application>PL/pgSQL</application> you can group a block of computation and a
166 series of queries <emphasis>inside</emphasis> the
167 database server, thus having the power of a procedural
168 language and the ease of use of SQL, but saving lots of
169 time because you don't have the whole client/server
170 communication overhead. This can make for a
171 considerable performance increase.
175 Also, with <application>PL/pgSQL</application> you can use all
176 the data types, operators and functions of SQL.
180 <sect2 id="plpgsql-overview-developing-in-plpgsql">
181 <title>Developing in <application>PL/pgSQL</application></title>
184 One good way to develop in
185 <application>PL/pgSQL</> is to simply use the text editor of your
186 choice to create your functions, and in another window, use
187 <command>psql</command> to load those functions. If you are doing it this way, it
188 is a good idea to write the function using <command>CREATE OR
189 REPLACE FUNCTION</>. That way you can reload the file to update
190 the function definition. For example:
192 CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS '
200 While running <command>psql</command>, you can load or reload such a
201 function definition file with
205 and then immediately issue SQL commands to test the function.
209 Another good way to develop in <application>PL/pgSQL</> is using a
210 GUI database access tool that facilitates development in a
211 procedural language. One example of such as a tool is
212 <application>PgAccess</>, although others exist. These tools often
213 provide convenient features such as escaping single quotes and
214 making it easier to recreate and debug functions.
219 <sect1 id="plpgsql-quote">
220 <title>Handling of Quotations Marks</title>
223 Since the code of any procedural language function is specified
224 <command>CREATE FUNCTION</command> as a string literal, single
225 quotes inside the function body must be escaped. This can lead to
226 rather complicated code at times, especially if you are writing a
227 function that generates other functions, as in the example in <xref
228 linkend="plpgsql-statements-executing-dyn">. The list below gives
229 you an overview over the needed levels of quotation marks in
230 various situations. Keep this chart handy.
235 <term>1 quotation mark</term>
238 To begin/end function bodies, for example:
240 CREATE FUNCTION foo() RETURNS integer AS '...'
248 <term>2 quotation marks</term>
251 For string literals inside the function body, for example:
253 a_output := ''Blah'';
254 SELECT * FROM users WHERE f_name=''foobar'';
256 The second line is interpreted as
258 SELECT * FROM users WHERE f_name='foobar';
265 <term>4 quotation marks</term>
268 When you need a single quote in a string inside the function
271 a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''
273 The value of <literal>a_output</literal> would then be: <literal>
274 AND name LIKE 'foobar' AND xyz</literal>.
280 <term>6 quotation marks</term>
283 When a single quote in a string inside the function body is
284 adjacent to the end of that string constant, for example:
286 a_output := a_output || '' AND name LIKE ''''foobar''''''
288 The value of <literal>a_output</literal> would then be:
289 <literal> AND name LIKE 'foobar'</literal>.
295 <term>10 quotation marks</term>
298 When you want two single quotes in a string constant (which
299 accounts for 8 quotes) and this is adjacent to the end of that
300 string constant (2 more). You will probably only need that if
301 you are writing a function that generates other functions. For
304 a_output := a_output || '' if v_'' ||
305 referrer_keys.kind || '' like ''''''''''
306 || referrer_keys.key_string || ''''''''''
307 then return '''''' || referrer_keys.referrer_type
308 || ''''''; end if;'';
310 The value of <literal>a_output</literal> would then be:
312 if v_... like ''...'' then return ''...''; end if;
320 <sect1 id="plpgsql-structure">
321 <title>Structure of <application>PL/pgSQL</application></title>
324 <application>PL/pgSQL</application> is a block-structured language.
325 The complete text of a function definition must be a
326 <firstterm>block</>. A block is defined as:
329 <optional> <<<replaceable>label</replaceable>>> </optional>
331 <replaceable>declarations</replaceable> </optional>
333 <replaceable>statements</replaceable>
339 Each declaration and each statement within a block is terminated
344 All key words and identifiers can be written in mixed upper and
345 lower case. Identifiers are implicitly converted to lower-case
346 unless double-quoted.
350 There are two types of comments in <application>PL/pgSQL</>. A double dash (<literal>--</literal>)
351 starts a comment that extends to the end of the line. A <literal>/*</literal>
352 starts a block comment that extends to the next occurrence of <literal>*/</literal>.
353 Block comments cannot be nested, but double dash comments can be
354 enclosed into a block comment and a double dash can hide
355 the block comment delimiters <literal>/*</literal> and <literal>*/</literal>.
359 Any statement in the statement section of a block
360 can be a <firstterm>subblock</>. Subblocks can be used for
361 logical grouping or to localize variables to a small group
366 The variables declared in the declarations section preceding a
367 block are initialized to their default values every time the
368 block is entered, not only once per function call. For example:
370 CREATE FUNCTION somefunc() RETURNS integer AS '
372 quantity integer := 30;
374 RAISE NOTICE ''Quantity here is %'', quantity; -- Quantity here is 30
380 quantity integer := 80;
382 RAISE NOTICE ''Quantity here is %'', quantity; -- Quantity here is 80
385 RAISE NOTICE ''Quantity here is %'', quantity; -- Quantity here is 50
394 It is important not to confuse the use of <command>BEGIN</>/<command>END</> for
395 grouping statements in <application>PL/pgSQL</> with the database commands for
396 transaction control. <application>PL/pgSQL</>'s <command>BEGIN</>/<command>END</> are only for grouping;
397 they do not start or end a transaction. Functions and trigger procedures
398 are always executed within a transaction established by an outer query
399 --- they cannot start or commit transactions, since
400 <productname>PostgreSQL</productname> does not have nested transactions.
404 <sect1 id="plpgsql-declarations">
405 <title>Declarations</title>
408 All variables used in a block must be declared in the
409 declarations section of the block.
410 (The only exception is that the loop variable of a <literal>FOR</> loop iterating
411 over a range of integer values is automatically declared as an integer
416 <application>PL/pgSQL</> variables can have any SQL data type, such as
417 <type>integer</type>, <type>varchar</type>, and
422 Here are some examples of variable declarations:
427 myrow tablename%ROWTYPE;
428 myfield tablename.columnname%TYPE;
434 The general syntax of a variable declaration is:
436 <replaceable>name</replaceable> <optional> CONSTANT </optional> <replaceable>type</replaceable> <optional> NOT NULL </optional> <optional> { DEFAULT | := } <replaceable>expression</replaceable> </optional>;
438 The <literal>DEFAULT</> clause, if given, specifies the initial value assigned
439 to the variable when the block is entered. If the <literal>DEFAULT</> clause
440 is not given then the variable is initialized to the
441 <acronym>SQL</acronym> null value.
442 The <literal>CONSTANT</> option prevents the variable from being assigned to,
443 so that its value remains constant for the duration of the block.
444 If <literal>NOT NULL</>
445 is specified, an assignment of a null value results in a run-time
446 error. All variables declared as <literal>NOT NULL</>
447 must have a nonnull default value specified.
451 The default value is evaluated every time the block is entered. So,
452 for example, assigning <literal>'now'</literal> to a variable of type
453 <type>timestamp</type> causes the variable to have the
454 time of the current function call, not the time when the function was
461 quantity integer DEFAULT 32;
462 url varchar := ''http://mysite.com'';
463 user_id CONSTANT integer := 10;
467 <sect2 id="plpgsql-declaration-aliases">
468 <title>Aliases for Function Parameters</title>
471 <replaceable>name</replaceable> ALIAS FOR $<replaceable>n</replaceable>;
475 Parameters passed to functions are named with the identifiers
476 <literal>$1</literal>, <literal>$2</literal>,
477 etc. Optionally, aliases can be declared for <literal>$<replaceable>n</replaceable></literal>
478 parameter names for increased readability. Either the alias or the
479 numeric identifier can then be used to refer to the parameter value.
482 CREATE FUNCTION sales_tax(real) RETURNS real AS '
484 subtotal ALIAS FOR $1;
486 RETURN subtotal * 0.06;
491 CREATE FUNCTION instr(varchar, integer) RETURNS integer AS '
493 v_string ALIAS FOR $1;
496 -- some computations here
501 CREATE FUNCTION use_many_fields(tablename) RETURNS text AS '
505 RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
512 <sect2 id="plpgsql-declaration-type">
513 <title>Copying Types</title>
516 <replaceable>variable</replaceable>%TYPE
520 <literal>%TYPE</literal> provides the data type of a variable or
521 table column. You can use this to declare variables that will hold
522 database values. For example, let's say you have a column named
523 <literal>user_id</literal> in your <literal>users</literal>
524 table. To declare a variable with the same data type as
525 <literal>users.user_id</> you write:
527 user_id users.user_id%TYPE;
532 By using <literal>%TYPE</literal> you don't need to know the data
533 type of the structure you are referencing, and most importantly,
534 if the data type of the referenced item changes in the future (for
535 instance: you change the type of <literal>user_id</>
536 from <type>integer</type> to <type>real</type>), you may not need
537 to change your function definition.
541 <sect2 id="plpgsql-declaration-rowtypes">
542 <title>Row Types</title>
545 <replaceable>name</replaceable> <replaceable>table_name</replaceable><literal>%ROWTYPE</literal>;
546 <replaceable>name</replaceable> <replaceable>composite_type_name</replaceable>;
550 A variable of a composite type is called a <firstterm>row</>
551 variable (or <firstterm>row-type</> variable). Such a variable
552 can hold a whole row of a <command>SELECT</> or <command>FOR</>
553 query result, so long as that query's column set matches the
554 declared type of the variable.
555 The individual fields of the row value
556 are accessed using the usual dot notation, for example
557 <literal>rowvar.field</literal>.
561 A row variable can be declared to have the same type as the rows of
562 an existing table or view, by using the
563 <replaceable>table_name</replaceable><literal>%ROWTYPE</literal>
564 notation; or it can be declared by giving a composite type's name.
565 (Since every table has an associated datatype of the same name,
566 it actually does not matter in <productname>PostgreSQL</> whether you
567 write <literal>%ROWTYPE</literal> or not. But the form with
568 <literal>%ROWTYPE</literal> is more portable.)
572 Parameters to a function can be
573 composite types (complete table rows). In that case, the
574 corresponding identifier <literal>$<replaceable>n</replaceable></> will be a row variable, and fields can
575 be selected from it, for example <literal>$1.user_id</literal>.
579 Only the user-defined columns of a table row are accessible in a
580 row-type variable, not the OID or other system columns (because the
581 row could be from a view). The fields of the row type inherit the
582 table's field size or precision for data types such as
583 <type>char(<replaceable>n</>)</type>.
587 Here is an example of using composite types:
589 CREATE FUNCTION use_two_tables(tablename) RETURNS text AS '
592 use_t table2name%ROWTYPE;
594 SELECT * INTO use_t FROM table2name WHERE ... ;
595 RETURN in_t.f1 || use_t.f3 || in_t.f5 || use_t.f7;
602 <sect2 id="plpgsql-declaration-records">
603 <title>Record Types</title>
607 <replaceable>name</replaceable> RECORD;
612 Record variables are similar to row-type variables, but they have no
613 predefined structure. They take on the actual row structure of the
614 row they are assigned during a <command>SELECT</> or <command>FOR</> command. The substructure
615 of a record variable can change each time it is assigned to.
616 A consequence of this is that until a record variable is first assigned
617 to, it has no substructure, and any attempt to access a
618 field in it will draw a run-time error.
622 Note that <literal>RECORD</> is not a true data type, only a placeholder.
626 <sect2 id="plpgsql-declaration-renaming-vars">
627 <title><literal>RENAME</></title>
631 RENAME <replaceable>oldname</replaceable> TO <replaceable>newname</replaceable>;
634 Using the RENAME declaration you can change the name of a variable,
635 record or row. This is primarily useful if NEW or OLD should be
636 referenced by another name inside a trigger procedure. See also ALIAS.
642 RENAME id TO user_id;
643 RENAME this_var TO that_var;
649 RENAME appears to be broken as of <productname>PostgreSQL</>
650 7.3. Fixing this is of low priority, since ALIAS covers most of
651 the practical uses of RENAME.
658 <sect1 id="plpgsql-expressions">
659 <title>Expressions</title>
662 All expressions used in <application>PL/pgSQL</application>
663 statements are processed using the server's regular
664 <acronym>SQL</acronym> executor. Expressions that appear to
665 contain constants may in fact require run-time evaluation
666 (e.g., <literal>'now'</literal> for the <type>timestamp</type>
667 type) so it is impossible for the
668 <application>PL/pgSQL</application> parser to identify real
669 constant values other than the key word <literal>NULL</>. All expressions are
670 evaluated internally by executing a query
672 SELECT <replaceable>expression</replaceable>
674 using the <acronym>SPI</acronym> manager. For evaluation,
675 occurrences of <application>PL/pgSQL</application> variable
676 identifiers are replaced by parameters, and the actual values from
677 the variables are passed to the executor in the parameter array.
678 This allows the query plan for the <command>SELECT</command> to
679 be prepared just once and then reused for subsequent
684 The evaluation done by the <productname>PostgreSQL</productname>
685 main parser has some side
686 effects on the interpretation of constant values. In detail there
687 is a difference between what these two functions do:
690 CREATE FUNCTION logfunc1(text) RETURNS timestamp AS '
694 INSERT INTO logtable VALUES (logtxt, ''now'');
703 CREATE FUNCTION logfunc2(text) RETURNS timestamp AS '
709 INSERT INTO logtable VALUES (logtxt, curtime);
717 In the case of <function>logfunc1</function>, the
718 <productname>PostgreSQL</productname> main parser knows when
719 preparing the plan for the <command>INSERT</command>, that the string
720 <literal>'now'</literal> should be interpreted as
721 <type>timestamp</type> because the target column of <classname>logtable</classname>
722 is of that type. Thus, it will make a constant from it at this
723 time and this constant value is then used in all invocations of
724 <function>logfunc1</function> during the lifetime of the
725 session. Needless to say that this isn't what the
730 In the case of <function>logfunc2</function>, the
731 <productname>PostgreSQL</productname> main parser does not know
732 what type <literal>'now'</literal> should become and therefore
733 it returns a data value of type <type>text</type> containing the string
734 <literal>now</literal>. During the ensuing assignment
735 to the local variable <varname>curtime</varname>, the
736 <application>PL/pgSQL</application> interpreter casts this
737 string to the <type>timestamp</type> type by calling the
738 <function>text_out</function> and <function>timestamp_in</function>
739 functions for the conversion. So, the computed time stamp is updated
740 on each execution as the programmer expects.
744 The mutable nature of record variables presents a problem in this
745 connection. When fields of a record variable are used in
746 expressions or statements, the data types of the fields must not
747 change between calls of one and the same expression, since the
748 expression will be planned using the data type that is present
749 when the expression is first reached. Keep this in mind when
750 writing trigger procedures that handle events for more than one
751 table. (<command>EXECUTE</command> can be used to get around
752 this problem when necessary.)
756 <sect1 id="plpgsql-statements">
757 <title>Basic Statements</title>
760 In this section and the following ones, we describe all the statement
761 types that are explicitly understood by
762 <application>PL/pgSQL</application>.
763 Anything not recognized as one of these statement types is presumed
764 to be an SQL command and is sent to the main database engine to execute
765 (after substitution of any <application>PL/pgSQL</application> variables
766 used in the statement). Thus,
767 for example, the SQL commands <command>INSERT</>, <command>UPDATE</>, and
768 <command>DELETE</> may be considered to be statements of
769 <application>PL/pgSQL</application>, but they are not specifically
773 <sect2 id="plpgsql-statements-assignment">
774 <title>Assignment</title>
777 An assignment of a value to a variable or row/record field is
780 <replaceable>identifier</replaceable> := <replaceable>expression</replaceable>;
782 As explained above, the expression in such a statement is evaluated
783 by means of an SQL <command>SELECT</> command sent to the main
784 database engine. The expression must yield a single value.
788 If the expression's result data type doesn't match the variable's
789 data type, or the variable has a specific size/precision
790 (like <type>char(20)</type>), the result value will be implicitly
791 converted by the <application>PL/pgSQL</application> interpreter using
792 the result type's output-function and
793 the variable type's input-function. Note that this could potentially
794 result in run-time errors generated by the input function, if the
795 string form of the result value is not acceptable to the input function.
802 tax := subtotal * 0.06;
807 <sect2 id="plpgsql-select-into">
808 <title><command>SELECT INTO</command></title>
811 The result of a <command>SELECT</command> command yielding multiple columns (but
812 only one row) can be assigned to a record variable, row-type
813 variable, or list of scalar variables. This is done by:
816 SELECT INTO <replaceable>target</replaceable> <replaceable>expressions</replaceable> FROM ...;
819 where <replaceable>target</replaceable> can be a record variable, a row
820 variable, or a comma-separated list of simple variables and
825 Note that this is quite different from
826 <productname>PostgreSQL</>'s normal interpretation of
827 <command>SELECT INTO</command>, where the <literal>INTO</> target
828 is a newly created table. If you want to create a table from a
829 <command>SELECT</> result inside a
830 <application>PL/pgSQL</application> function, use the syntax
831 <command>CREATE TABLE ... AS SELECT</command>.
835 If a row or a variable list is used as target, the selected values
836 must exactly match the structure of the target, or a run-time error
837 occurs. When a record variable is the target, it automatically
838 configures itself to the row type of the query result columns.
842 Except for the <literal>INTO</> clause, the <command>SELECT</>
843 statement is the same as a normal SQL <command>SELECT</> command
844 and can use its full power.
848 If the query returns zero rows, null values are assigned to the
849 target(s). If the query returns multiple rows, the first
850 row is assigned to the target(s) and the rest are discarded.
851 (Note that <quote>the first row</> is not well-defined unless you've
852 used <literal>ORDER BY</>.)
856 At present, the <literal>INTO</> clause can appear almost anywhere in the <command>SELECT</command>
857 statement, but it is recommended to place it immediately after the <literal>SELECT</literal>
858 key word as depicted above. Future versions of
859 <application>PL/pgSQL</application> may be less forgiving about
860 placement of the <literal>INTO</literal> clause.
864 You can use <literal>FOUND</literal> immediately after a <command>SELECT
865 INTO</command> statement to determine whether the assignment was successful
866 (that is, at least one row was was returned by the query). For example:
869 SELECT INTO myrec * FROM emp WHERE empname = myname;
871 RAISE EXCEPTION ''employee % not found'', myname;
877 To test for whether a record/row result is null, you can use the
878 <literal>IS NULL</literal> conditional. There is, however, no
879 way to tell whether any additional rows might have been
880 discarded. Here is an example that handles the case where no
881 rows have been returned:
887 SELECT INTO users_rec * FROM users WHERE user_id=3;
889 IF users_rec.homepage IS NULL THEN
890 -- user entered no homepage, return "http://"
898 <sect2 id="plpgsql-statements-perform">
899 <title>Executing an Expression or Query With No Result</title>
902 Sometimes one wishes to evaluate an expression or query but
903 discard the result (typically because one is calling a function
904 that has useful side-effects but no useful result value). To do
905 this in <application>PL/pgSQL</application>, use the
906 <command>PERFORM</command> statement:
909 PERFORM <replaceable>query</replaceable>;
912 This executes <replaceable>query</replaceable>, which must be a
913 <command>SELECT</command> statement, and discards the
914 result. <application>PL/pgSQL</application> variables are
915 substituted in the query as usual. Also, the special variable
916 <literal>FOUND</literal> is set to true if the query produced at
917 least one row or false if it produced no rows.
922 One might expect that <command>SELECT</command> with no
923 <literal>INTO</> clause would accomplish this result, but at
924 present the only accepted way to do it is
925 <command>PERFORM</command>.
932 PERFORM create_mv(''cs_session_page_requests_mv'', my_query);
937 <sect2 id="plpgsql-statements-executing-dyn">
938 <title>Executing Dynamic Commands</title>
941 Oftentimes you will want to generate dynamic commands inside your
942 <application>PL/pgSQL</application> functions, that is, commands
943 that will involve different tables or different data types each
944 time they are executed. <application>PL/pgSQL</application>'s
945 normal attempts to cache plans for commands will not work in such
946 scenarios. To handle this sort of problem, the
947 <command>EXECUTE</command> statement is provided:
950 EXECUTE <replaceable class="command">command-string</replaceable>;
953 where <replaceable>command-string</replaceable> is an expression
954 yielding a string (of type
955 <type>text</type>) containing the command
956 to be executed. This string is fed literally to the SQL engine.
960 Note in particular that no substitution of <application>PL/pgSQL</>
961 variables is done on the command string. The values of variables must
962 be inserted in the command string as it is constructed.
966 When working with dynamic commands you will have to face
967 escaping of single quotes in <application>PL/pgSQL</>. Please refer to the
968 overview in <xref linkend="plpgsql-quote">,
969 which can save you some effort.
973 Unlike all other commands in <application>PL/pgSQL</>, a command
974 run by an <command>EXECUTE</command> statement is not prepared
975 and saved just once during the life of the session. Instead, the
976 command is prepared each time the statement is run. The command
977 string can be dynamically created within the function to perform
978 actions on variable tables and columns.
982 The results from <command>SELECT</command> commands are discarded
983 by <command>EXECUTE</command>, and <command>SELECT INTO</command>
984 is not currently supported within <command>EXECUTE</command>.
985 So, the only way to extract a result from a dynamically-created
986 <command>SELECT</command> is to use the <command>FOR-IN-EXECUTE</> form
993 EXECUTE ''UPDATE tbl SET ''
994 || quote_ident(colname)
996 || quote_literal(newvalue)
1002 This example shows use of the functions
1003 <function>quote_ident(<type>text</type>)</function> and
1004 <function>quote_literal(<type>text</type>)</function>.
1005 Variables containing column and table identifiers should be
1006 passed to function <function>quote_ident</function>.
1007 Variables containing values that act as value literals in the constructed command
1008 string should be passed to
1009 <function>quote_literal</function>. Both take the
1010 appropriate steps to return the input text enclosed in single
1011 or double quotes and with any embedded special characters
1016 Here is a much larger example of a dynamic command and
1017 <command>EXECUTE</command>:
1019 CREATE FUNCTION cs_update_referrer_type_proc() RETURNS integer AS '
1021 referrer_keys RECORD; -- declare a generic record to be used in a FOR
1022 a_output varchar(4000);
1024 a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar, varchar, varchar)
1025 RETURNS varchar AS ''''
1027 v_host ALIAS FOR $1;
1028 v_domain ALIAS FOR $2;
1032 -- Notice how we scan through the results of a query in a FOR loop
1033 -- using the FOR <record> construct.
1035 FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
1036 a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE ''''''''''
1037 || referrer_keys.key_string || '''''''''' THEN RETURN ''''''
1038 || referrer_keys.referrer_type || ''''''; END IF;'';
1041 a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE plpgsql;'';
1050 <sect2 id="plpgsql-statements-diagnostics">
1051 <title>Obtaining the Result Status</title>
1054 There are several ways to determine the effect of a command. The
1055 first method is to use the <command>GET DIAGNOSTICS</command>,
1059 GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional> ;
1062 This command allows retrieval of system status indicators. Each
1063 <replaceable>item</replaceable> is a key word identifying a state
1064 value to be assigned to the specified variable (which should be
1065 of the right data type to receive it). The currently available
1066 status items are <varname>ROW_COUNT</>, the number of rows
1067 processed by the last <acronym>SQL</acronym> command sent down to
1068 the <acronym>SQL</acronym> engine, and <varname>RESULT_OID</>,
1069 the OID of the last row inserted by the most recent
1070 <acronym>SQL</acronym> command. Note that <varname>RESULT_OID</>
1071 is only useful after an <command>INSERT</command> command.
1077 GET DIAGNOSTICS var_integer = ROW_COUNT;
1082 The second method to determine the effects of a command is the
1083 special variable named <literal>FOUND</literal> of
1084 type <type>boolean</type>. <literal>FOUND</literal> starts out
1085 false within each <application>PL/pgSQL</application> function.
1086 It is set by each of the following types of statements:
1090 A <command>SELECT INTO</command> statement sets
1091 <literal>FOUND</literal> true if it returns a row, false if no
1097 A <command>PERFORM</> statement sets <literal>FOUND</literal>
1098 true if it produces (discards) a row, false if no row is
1104 <command>UPDATE</>, <command>INSERT</>, and <command>DELETE</>
1105 statements set <literal>FOUND</literal> true if at least one
1106 row is affected, false if no row is affected.
1111 A <command>FETCH</> statement sets <literal>FOUND</literal>
1112 true if it returns a row, false if no row is returned.
1117 A <command>FOR</> statement sets <literal>FOUND</literal> true
1118 if it iterates one or more times, else false. This applies to
1119 all three variants of the <command>FOR</> statement (integer
1120 <command>FOR</> loops, record-set <command>FOR</> loops, and
1121 dynamic record-set <command>FOR</>
1122 loops). <literal>FOUND</literal> is only set when the
1123 <command>FOR</> loop exits: inside the execution of the loop,
1124 <literal>FOUND</literal> is not modified by the
1125 <command>FOR</> statement, although it may be changed by the
1126 execution of other statements within the loop body.
1130 <literal>FOUND</literal> is a local variable; any changes
1131 to it affect only the current <application>PL/pgSQL</application>
1138 <sect1 id="plpgsql-control-structures">
1139 <title>Control Structures</title>
1142 Control structures are probably the most useful (and
1143 important) part of <application>PL/pgSQL</>. With
1144 <application>PL/pgSQL</>'s control structures,
1145 you can manipulate <productname>PostgreSQL</> data in a very
1146 flexible and powerful way.
1149 <sect2 id="plpgsql-statements-returning">
1150 <title>Returning From a Function</title>
1153 There are two commands available that allow you to return data
1154 from a function: <command>RETURN</command> and <command>RETURN
1159 <title><command>RETURN</></title>
1162 RETURN <replaceable>expression</replaceable>;
1166 <command>RETURN</command> with an expression terminates the
1167 function and returns the value of
1168 <replaceable>expression</replaceable> to the caller. This form
1169 is to be used for <application>PL/pgSQL</> functions that does
1174 When returning a scalar type, any expression can be used. The
1175 expression's result will be automatically cast into the
1176 function's return type as described for assignments. To return a
1177 composite (row) value, you must write a record or row variable
1178 as the <replaceable>expression</replaceable>.
1182 The return value of a function cannot be left undefined. If
1183 control reaches the end of the top-level block of the function
1184 without hitting a <command>RETURN</command> statement, a run-time
1185 error will occur. Note that if you have declared the function to
1186 return <type>void</type>, a <command>RETURN</command> statement
1187 must still be specified; the expression following
1188 <command>RETURN</command> is, however, optional and will be ignored in
1194 <title><command>RETURN NEXT</></title>
1197 RETURN NEXT <replaceable>expression</replaceable>;
1201 When a <application>PL/pgSQL</> function is declared to return
1202 <literal>SETOF <replaceable>sometype</></literal>, the procedure
1203 to follow is slightly different. In that case, the individual
1204 items to return are specified in <command>RETURN NEXT</command>
1205 commands, and then a final <command>RETURN</command> command
1206 with no arguments is used to indicate that the function has
1207 finished executing. <command>RETURN NEXT</command> can be used
1208 with both scalar and composite data types; in the later case, an
1209 entire <quote>table</quote> of results will be returned.
1213 Functions that use <command>RETURN NEXT</command> should be
1214 called in the following fashion:
1217 SELECT * FROM some_func();
1220 That is, the function is used as a table source in a <literal>FROM</literal>
1225 <command>RETURN NEXT</command> does not actually return from the
1226 function; it simply saves away the value of the expression (or
1227 record or row variable, as appropriate for the data type being
1228 returned). Execution then continues with the next statement in
1229 the <application>PL/pgSQL</> function. As successive
1230 <command>RETURN NEXT</command> commands are executed, the result
1231 set is built up. A final <command>RETURN</command>, which should
1232 have no argument, causes control to exit the function.
1237 The current implementation of <command>RETURN NEXT</command>
1238 for <application>PL/pgSQL</> stores the entire result set
1239 before returning from the function, as discussed above. That
1240 means that if a <application>PL/pgSQL</> function produces a
1241 very large result set, performance may be poor: data will be
1242 written to disk to avoid memory exhaustion, but the function
1243 itself will not return until the entire result set has been
1244 generated. A future version of <application>PL/pgSQL</> may
1245 allow users to allow users to define set-returning functions
1246 that do not have this limitation. Currently, the point at
1247 which data begins being written to disk is controlled by the
1248 <varname>sort_mem</> configuration variable. Administrators
1249 who have sufficient memory to store larger result sets in
1250 memory should consider increasing this parameter.
1256 <sect2 id="plpgsql-conditionals">
1257 <title>Conditionals</title>
1260 <literal>IF</> statements let you execute commands based on
1261 certain conditions. <application>PL/pgSQL</> has four forms of
1265 <para><literal>IF ... THEN</></>
1268 <para><literal>IF ... THEN ... ELSE</></>
1271 <para><literal>IF ... THEN ... ELSE IF</></>
1274 <para><literal>IF ... THEN ... ELSIF ... THEN ... ELSE</></>
1280 <title><literal>IF-THEN</></title>
1283 IF <replaceable>boolean-expression</replaceable> THEN
1284 <replaceable>statements</replaceable>
1289 <literal>IF-THEN</literal> statements are the simplest form of
1290 <literal>IF</literal>. The statements between
1291 <literal>THEN</literal> and <literal>END IF</literal> will be
1292 executed if the condition is true. Otherwise, they are
1299 IF v_user_id <> 0 THEN
1300 UPDATE users SET email = v_email WHERE user_id = v_user_id;
1307 <title><literal>IF-THEN-ELSE</></title>
1310 IF <replaceable>boolean-expression</replaceable> THEN
1311 <replaceable>statements</replaceable>
1313 <replaceable>statements</replaceable>
1318 <literal>IF-THEN-ELSE</literal> statements add to
1319 <literal>IF-THEN</literal> by letting you specify an
1320 alternative set of statements that should be executed if the
1321 condition evaluates to false.
1327 IF parentid IS NULL OR parentid = ''''
1331 RETURN hp_true_filename(parentid) || ''/'' || fullname;
1337 INSERT INTO users_count (count) VALUES (v_count);
1347 <title><literal>IF-THEN-ELSE IF</></title>
1350 <literal>IF</literal> statements can be nested, as in the
1354 IF demo_row.sex = ''m'' THEN
1355 pretty_sex := ''man'';
1357 IF demo_row.sex = ''f'' THEN
1358 pretty_sex := ''woman'';
1365 When you use this form, you are actually nesting an
1366 <literal>IF</literal> statement inside the
1367 <literal>ELSE</literal> part of an outer <literal>IF</literal>
1368 statement. Thus you need one <literal>END IF</literal>
1369 statement for each nested <literal>IF</literal> and one for the parent
1370 <literal>IF-ELSE</literal>. This is workable but grows
1371 tedious when there are many alternatives to be checked.
1372 Hence the next form.
1377 <title><literal>IF-THEN-ELSIF-ELSE</></title>
1380 IF <replaceable>boolean-expression</replaceable> THEN
1381 <replaceable>statements</replaceable>
1382 <optional> ELSIF <replaceable>boolean-expression</replaceable> THEN
1383 <replaceable>statements</replaceable>
1384 <optional> ELSIF <replaceable>boolean-expression</replaceable> THEN
1385 <replaceable>statements</replaceable>
1390 <replaceable>statements</replaceable> </optional>
1395 <literal>IF-THEN-ELSIF-ELSE</> provides a more convenient
1396 method of checking many alternatives in one statement.
1397 Formally it is equivalent to nested
1398 <literal>IF-THEN-ELSE-IF-THEN</> commands, but only one
1399 <literal>END IF</> is needed.
1408 ELSIF number > 0 THEN
1409 result := ''positive'';
1410 ELSIF number < 0 THEN
1411 result := ''negative'';
1413 -- hmm, the only other possibility is that number is null
1421 <sect2 id="plpgsql-control-structures-loops">
1422 <title>Simple Loops</title>
1425 With the <literal>LOOP</>, <literal>EXIT</>, <literal>WHILE</>,
1426 and <literal>FOR</> statements, you can arrange for your
1427 <application>PL/pgSQL</application> function to repeat a series
1432 <title><literal>LOOP</></title>
1435 <optional><<<replaceable>label</replaceable>>></optional>
1437 <replaceable>statements</replaceable>
1442 <literal>LOOP</> defines an unconditional loop that is repeated indefinitely
1443 until terminated by an <literal>EXIT</> or <command>RETURN</command>
1444 statement. The optional label can be used by <literal>EXIT</> statements in
1445 nested loops to specify which level of nesting should be
1451 <title><literal>EXIT</></title>
1454 EXIT <optional> <replaceable>label</replaceable> </optional> <optional> WHEN <replaceable>expression</replaceable> </optional>;
1458 If no <replaceable>label</replaceable> is given,
1459 the innermost loop is terminated and the
1460 statement following <literal>END LOOP</> is executed next.
1461 If <replaceable>label</replaceable> is given, it
1462 must be the label of the current or some outer level of nested loop
1463 or block. Then the named loop or block is terminated and control
1464 continues with the statement after the loop's/block's corresponding
1469 If <literal>WHEN</> is present, loop exit occurs only if the specified condition
1470 is true, otherwise control passes to the statement after <literal>EXIT</>.
1477 -- some computations
1484 -- some computations
1485 EXIT WHEN count > 0;
1489 -- some computations
1490 IF stocks > 100000 THEN
1491 EXIT; -- invalid; cannot use EXIT outside of LOOP
1499 <title><literal>WHILE</></title>
1502 <optional><<<replaceable>label</replaceable>>></optional>
1503 WHILE <replaceable>expression</replaceable> LOOP
1504 <replaceable>statements</replaceable>
1509 The <literal>WHILE</> statement repeats a
1510 sequence of statements so long as the condition expression
1511 evaluates to true. The condition is checked just before
1512 each entry to the loop body.
1518 WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
1519 -- some computations here
1522 WHILE NOT boolean_expression LOOP
1523 -- some computations here
1530 <title><literal>FOR</> (integer variant)</title>
1533 <optional><<<replaceable>label</replaceable>>></optional>
1534 FOR <replaceable>name</replaceable> IN <optional> REVERSE </optional> <replaceable>expression</replaceable> .. <replaceable>expression</replaceable> LOOP
1535 <replaceable>statements</replaceable>
1540 This form of <literal>FOR</> creates a loop that iterates over a range of integer
1541 values. The variable
1542 <replaceable>name</replaceable> is automatically defined as type
1543 <type>integer</> and exists only inside the loop. The two expressions giving
1544 the lower and upper bound of the range are evaluated once when entering
1545 the loop. The iteration step is normally 1, but is -1 when <literal>REVERSE</> is
1550 Some examples of integer <literal>FOR</> loops:
1553 -- some expressions here
1554 RAISE NOTICE ''i is %'', i;
1557 FOR i IN REVERSE 10..1 LOOP
1558 -- some expressions here
1565 <sect2 id="plpgsql-records-iterating">
1566 <title>Looping Through Query Results</title>
1569 Using a different type of <literal>FOR</> loop, you can iterate through
1570 the results of a query and manipulate that data
1571 accordingly. The syntax is:
1573 <optional><<<replaceable>label</replaceable>>></optional>
1574 FOR <replaceable>record_or_row</replaceable> IN <replaceable>query</replaceable> LOOP
1575 <replaceable>statements</replaceable>
1578 The record or row variable is successively assigned each row
1579 resulting from the query (a <command>SELECT</command> command) and the loop
1580 body is executed for each row. Here is an example:
1582 CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS '
1586 PERFORM cs_log(''Refreshing materialized views...'');
1588 FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
1590 -- Now "mviews" has one record from cs_materialized_views
1592 PERFORM cs_log(''Refreshing materialized view '' || quote_ident(mviews.mv_name) || ''...'');
1593 EXECUTE ''TRUNCATE TABLE '' || quote_ident(mviews.mv_name);
1594 EXECUTE ''INSERT INTO '' || quote_ident(mviews.mv_name) || '' '' || mviews.mv_query;
1597 PERFORM cs_log(''Done refreshing materialized views.'');
1603 If the loop is terminated by an <literal>EXIT</> statement, the last
1604 assigned row value is still accessible after the loop.
1608 The <literal>FOR-IN-EXECUTE</> statement is another way to iterate over
1611 <optional><<<replaceable>label</replaceable>>></optional>
1612 FOR <replaceable>record_or_row</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> LOOP
1613 <replaceable>statements</replaceable>
1616 This is like the previous form, except that the source
1617 <command>SELECT</command> statement is specified as a string
1618 expression, which is evaluated and replanned on each entry to
1619 the <literal>FOR</> loop. This allows the programmer to choose the speed of
1620 a preplanned query or the flexibility of a dynamic query, just
1621 as with a plain <command>EXECUTE</command> statement.
1626 The <application>PL/pgSQL</> parser presently distinguishes the
1627 two kinds of <literal>FOR</> loops (integer or query result) by checking
1628 whether the target variable mentioned just after <literal>FOR</> has been
1629 declared as a record or row variable. If not, it's presumed to be
1630 an integer <literal>FOR</> loop. This can cause rather nonintuitive error
1631 messages when the true problem is, say, that one has
1632 misspelled the variable name after the <literal>FOR</>.
1638 <sect1 id="plpgsql-cursors">
1639 <title>Cursors</title>
1642 Rather than executing a whole query at once, it is possible to set
1643 up a <firstterm>cursor</> that encapsulates the query, and then read
1644 the query result a few rows at a time. One reason for doing this is
1645 to avoid memory overrun when the result contains a large number of
1646 rows. (However, <application>PL/pgSQL</> users do not normally need
1647 to worry about that, since <literal>FOR</> loops automatically use a cursor
1648 internally to avoid memory problems.) A more interesting usage is to
1649 return a reference to a cursor that it has created, allowing the
1650 caller to read the rows. This provides an efficient way to return
1651 large row sets from functions.
1654 <sect2 id="plpgsql-cursor-declarations">
1655 <title>Declaring Cursor Variables</title>
1658 All access to cursors in <application>PL/pgSQL</> goes through
1659 cursor variables, which are always of the special data type
1660 <type>refcursor</>. One way to create a cursor variable
1661 is just to declare it as a variable of type <type>refcursor</>.
1662 Another way is to use the cursor declaration syntax,
1663 which in general is:
1665 <replaceable>name</replaceable> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable> ;
1667 (<literal>FOR</> may be replaced by <literal>IS</> for
1668 <productname>Oracle</productname> compatibility.)
1669 <replaceable>arguments</replaceable>, if specified, is a
1670 comma-separated list of pairs <literal><replaceable>name</replaceable>
1671 <replaceable>datatype</replaceable></literal> that define names to be
1672 replaced by parameter values in the given query. The actual
1673 values to substitute for these names will be specified later,
1674 when the cursor is opened.
1681 curs2 CURSOR FOR SELECT * FROM tenk1;
1682 curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
1684 All three of these variables have the data type <type>refcursor</>,
1685 but the first may be used with any query, while the second has
1686 a fully specified query already <firstterm>bound</> to it, and the last
1687 has a parameterized query bound to it. (<literal>key</> will be
1688 replaced by an integer parameter value when the cursor is opened.)
1689 The variable <literal>curs1</>
1690 is said to be <firstterm>unbound</> since it is not bound to
1691 any particular query.
1695 <sect2 id="plpgsql-cursor-opening">
1696 <title>Opening Cursors</title>
1699 Before a cursor can be used to retrieve rows, it must be
1700 <firstterm>opened</>. (This is the equivalent action to the SQL
1701 command <command>DECLARE CURSOR</>.) <application>PL/pgSQL</> has
1702 three forms of the <command>OPEN</> statement, two of which use unbound cursor
1703 variables and the other uses a bound cursor variable.
1707 <title><command>OPEN FOR SELECT</command></title>
1710 OPEN <replaceable>unbound-cursor</replaceable> FOR SELECT ...;
1714 The cursor variable is opened and given the specified query to
1715 execute. The cursor cannot be open already, and it must have been
1716 declared as an unbound cursor (that is, as a simple
1717 <type>refcursor</> variable). The <command>SELECT</command> query
1718 is treated in the same way as other <command>SELECT</command>
1719 statements in <application>PL/pgSQL</>: <application>PL/pgSQL</>
1720 variable names are substituted, and the query plan is cached for
1727 OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
1733 <title><command>OPEN FOR EXECUTE</command></title>
1736 OPEN <replaceable>unbound-cursor</replaceable> FOR EXECUTE <replaceable class="command">query-string</replaceable>;
1740 The cursor variable is opened and given the specified query to
1741 execute. The cursor cannot be open already, and it must have been
1742 declared as an unbound cursor (that is, as a simple
1743 <type>refcursor</> variable). The query is specified as a string
1744 expression in the same way as in the <command>EXECUTE</command>
1745 command. As usual, this gives flexibility so the query can vary
1746 from one run to the next.
1752 OPEN curs1 FOR EXECUTE ''SELECT * FROM '' || quote_ident($1);
1758 <title>Opening a Bound Cursor</title>
1761 OPEN <replaceable>bound-cursor</replaceable> <optional> ( <replaceable>argument_values</replaceable> ) </optional>;
1765 This form of <command>OPEN</command> is used to open a cursor
1766 variable whose query was bound to it when it was declared. The
1767 cursor cannot be open already. A list of actual argument value
1768 expressions must appear if and only if the cursor was declared to
1769 take arguments. These values will be substituted in the query.
1770 The query plan for a bound cursor is always considered cacheable;
1771 there is no equivalent of <command>EXECUTE</command> in this case.
1784 <sect2 id="plpgsql-cursor-using">
1785 <title>Using Cursors</title>
1788 Once a cursor has been opened, it can be manipulated with the
1789 statements described here.
1793 These manipulations need not occur in the same function that
1794 opened the cursor to begin with. You can return a <type>refcursor</>
1795 value out of a function and let the caller operate on the cursor.
1796 (Internally, a <type>refcursor</> value is simply the string name
1797 of a so-called portal containing the active query for the cursor. This name
1798 can be passed around, assigned to other <type>refcursor</> variables,
1799 and so on, without disturbing the portal.)
1803 All portals are implicitly closed at transaction end. Therefore
1804 a <type>refcursor</> value is usable to reference an open cursor
1805 only until the end of the transaction.
1809 <title><literal>FETCH</></title>
1812 FETCH <replaceable>cursor</replaceable> INTO <replaceable>target</replaceable>;
1816 <command>FETCH</command> retrieves the next row from the
1817 cursor into a target, which may be a row variable, a record
1818 variable, or a comma-separated list of simple variables, just like
1819 <command>SELECT INTO</command>. As with <command>SELECT
1820 INTO</command>, the special variable <literal>FOUND</literal> may
1821 be checked to see whether a row was obtained or not.
1827 FETCH curs1 INTO rowvar;
1828 FETCH curs2 INTO foo, bar, baz;
1834 <title><literal>CLOSE</></title>
1837 CLOSE <replaceable>cursor</replaceable>;
1841 <command>CLOSE</command> closes the Portal underlying an open
1842 cursor. This can be used to release resources earlier than end of
1843 transaction, or to free up the cursor variable to be opened again.
1855 <title>Returning Cursors</title>
1858 <application>PL/pgSQL</> functions can return cursors to the
1859 caller. This is used to return multiple rows or columns from
1860 the function. To do this, the function opens the cursor and returns the
1861 cursor name to the caller. The caller can then
1862 fetch rows from the cursor. The cursor can
1863 be closed by the caller, or it will be closed automatically
1864 when the transaction closes.
1868 The cursor name returned by the function can be specified by the
1869 caller or automatically generated. The following example shows
1870 how a cursor name can be supplied by the caller:
1873 CREATE TABLE test (col text);
1874 INSERT INTO test VALUES ('123');
1876 CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
1878 OPEN $1 FOR SELECT col FROM test;
1884 SELECT reffunc('funccursor');
1885 FETCH ALL IN funccursor;
1891 The following example uses automatic cursor name generation:
1894 CREATE FUNCTION reffunc2() RETURNS refcursor AS '
1898 OPEN ref FOR SELECT col FROM test;
1907 --------------------
1908 <unnamed cursor 1>
1911 FETCH ALL IN "<unnamed cursor 1>";
1919 <sect1 id="plpgsql-errors-and-messages">
1920 <title>Errors and Messages</title>
1923 Use the <command>RAISE</command> statement to report messages and
1927 RAISE <replaceable class="parameter">level</replaceable> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">variable</replaceable> <optional>, ...</optional></optional>;
1930 Possible levels are <literal>DEBUG</literal> (write the message to
1931 the server log), <literal>LOG</literal> (write the message to the
1932 server log with a higher priority), <literal>INFO</literal>,
1933 <literal>NOTICE</literal> and <literal>WARNING</literal> (write
1934 the message to the server log and send it to the client, with
1935 respectively higher priorities), and <literal>EXCEPTION</literal>
1936 (raise an error and abort the current transaction). Whether
1937 messages of a particular priority are reported to the client,
1938 written to the server log, or both is controlled by the
1939 <option>log_min_messages</option> and
1940 <option>client_min_messages</option> configuration variables. See
1941 <xref linkend="runtime-config"> for more information.
1945 Inside the format string, <literal>%</literal> is replaced by the
1946 next optional argument's string representation. Write
1947 <literal>%%</literal> to emit a literal <literal>%</literal>. Note
1948 that the optional arguments must presently be simple variables,
1949 not expressions, and the format must be a simple string literal.
1953 This example should work, but does not:
1954 RAISE NOTICE ''Id number '' || key || '' not found!'';
1955 Put it back when we allow non-string-literal formats.
1959 In this example, the value of <literal>v_job_id</> will replace the
1960 <literal>%</literal> in the string.
1962 RAISE NOTICE ''Calling cs_create_job(%)'', v_job_id;
1967 This example will abort the transaction with the given error message.
1969 RAISE EXCEPTION ''Inexistent ID --> %'', user_id;
1974 <productname>PostgreSQL</productname> does not have a very smart
1975 exception handling model. Whenever the parser, planner/optimizer
1976 or executor decide that a statement cannot be processed any longer,
1977 the whole transaction gets aborted and the system jumps back
1978 into the main loop to get the next command from the client application.
1982 It is possible to hook into the error mechanism to notice that this
1983 happens. But currently it is impossible to tell what really
1984 caused the abort (data type format error, floating-point
1985 error, parse error, etc.). And it is possible that the database server
1986 is in an inconsistent state at this point so returning to the upper
1987 executor or issuing more commands might corrupt the whole database.
1991 Thus, the only thing <application>PL/pgSQL</application>
1992 currently does when it encounters an abort during execution of a
1993 function or trigger procedure is to write some additional
1994 <literal>NOTICE</literal> level log messages telling in which
1995 function and where (line number and type of statement) this
1996 happened. The error always stops execution of the function.
2000 <sect1 id="plpgsql-trigger">
2001 <title>Trigger Procedures</title>
2004 <application>PL/pgSQL</application> can be used to define trigger
2005 procedures. A trigger procedure is created with the
2006 <command>CREATE FUNCTION</> command as a function with no
2007 arguments and a return type of <type>trigger</type>. Note that
2008 the function must be declared with no arguments even if it expects
2009 to receive arguments specified in <command>CREATE TRIGGER</> ---
2010 trigger arguments are passed via <varname>TG_ARGV</>, as described
2015 When a <application>PL/pgSQL</application> function is called as a
2016 trigger, several special variables are created automatically in the
2017 top-level block. They are:
2021 <term><varname>NEW</varname></term>
2024 Data type <type>RECORD</type>; variable holding the new
2025 database row for <command>INSERT</>/<command>UPDATE</> operations in row-level
2026 triggers. This variable is null in statement-level triggers.
2032 <term><varname>OLD</varname></term>
2035 Data type <type>RECORD</type>; variable holding the old
2036 database row for <command>UPDATE</>/<command>DELETE</> operations in row-level
2037 triggers. This variable is null in statement-level triggers.
2043 <term><varname>TG_NAME</varname></term>
2046 Data type <type>name</type>; variable that contains the name of the trigger actually
2053 <term><varname>TG_WHEN</varname></term>
2056 Data type <type>text</type>; a string of either
2057 <literal>BEFORE</literal> or <literal>AFTER</literal>
2058 depending on the trigger's definition.
2064 <term><varname>TG_LEVEL</varname></term>
2067 Data type <type>text</type>; a string of either
2068 <literal>ROW</literal> or <literal>STATEMENT</literal>
2069 depending on the trigger's definition.
2075 <term><varname>TG_OP</varname></term>
2078 Data type <type>text</type>; a string of
2079 <literal>INSERT</literal>, <literal>UPDATE</literal>, or
2080 <literal>DELETE</literal> telling for which operation the
2087 <term><varname>TG_RELID</varname></term>
2090 Data type <type>oid</type>; the object ID of the table that caused the
2097 <term><varname>TG_RELNAME</varname></term>
2100 Data type <type>name</type>; the name of the table that caused the trigger
2107 <term><varname>TG_NARGS</varname></term>
2110 Data type <type>integer</type>; the number of arguments given to the trigger
2111 procedure in the <command>CREATE TRIGGER</command> statement.
2117 <term><varname>TG_ARGV[]</varname></term>
2120 Data type array of <type>text</type>; the arguments from
2121 the <command>CREATE TRIGGER</command> statement.
2122 The index counts from 0. Invalid
2123 indices (less than 0 or greater than or equal to <varname>tg_nargs</>) result in a null value.
2131 A trigger function must return either null or a record/row value
2132 having exactly the structure of the table the trigger was fired
2133 for. The return value of a <literal>BEFORE</> or <literal>AFTER</> statement-level
2134 trigger or an <literal>AFTER</> row-level trigger is ignored; it may as well
2135 be null. However, any of these types of triggers can still
2136 abort the entire trigger operation by raising an error.
2140 Row-level triggers fired <literal>BEFORE</> may return null to signal the
2141 trigger manager to skip the rest of the operation for this row
2142 (i.e., subsequent triggers are not fired, and the
2143 <command>INSERT</>/<command>UPDATE</>/<command>DELETE</> does not occur for this row). If a nonnull
2144 value is returned then the operation proceeds with that row value.
2145 Returning a row value different from the original value
2146 of <varname>NEW</> alters the row that will be inserted or updated. It is
2147 possible to replace single values directly in <varname>NEW</> and return <varname>NEW</>,
2148 or to build a complete new record/row to return.
2152 <xref linkend="plpgsql-trigger-example"> shows an example of a
2153 trigger procedure in <application>PL/pgSQL</application>.
2156 <example id="plpgsql-trigger-example">
2157 <title>A <application>PL/pgSQL</application> Trigger Procedure</title>
2160 This example trigger ensures that any time a row is inserted or updated
2161 in the table, the current user name and time are stamped into the
2162 row. And it ensures that an employee's name is given and that the
2163 salary is a positive value.
2170 last_date timestamp,
2174 CREATE FUNCTION emp_stamp() RETURNS trigger AS '
2176 -- Check that empname and salary are given
2177 IF NEW.empname IS NULL THEN
2178 RAISE EXCEPTION ''empname cannot be null'';
2180 IF NEW.salary IS NULL THEN
2181 RAISE EXCEPTION ''% cannot have null salary'', NEW.empname;
2184 -- Who works for us when she must pay for it?
2185 IF NEW.salary < 0 THEN
2186 RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
2189 -- Remember who changed the payroll when
2190 NEW.last_date := ''now'';
2191 NEW.last_user := current_user;
2196 CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
2197 FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
2202 <!-- **** Porting from Oracle PL/SQL **** -->
2204 <sect1 id="plpgsql-porting">
2205 <title>Porting from <productname>Oracle</productname> PL/SQL</title>
2207 <indexterm zone="plpgsql-porting">
2208 <primary>Oracle</primary>
2211 <indexterm zone="plpgsql-porting">
2212 <primary>PL/SQL</primary>
2216 This section explains differences between
2217 <productname>PostgreSQL</>'s <application>PL/pgSQL</application>
2218 language and Oracle's <application>PL/SQL</application> language,
2219 to help developers that port applications from Oracle to
2220 <productname>PostgreSQL</>.
2224 <application>PL/pgSQL</application> is similar to PL/SQL in many
2225 aspects. It is a block-structured, imperative language, and all
2226 variables have to be declared. Assignments, loops, conditionals
2227 are similar. The main differences you should keep in mind when
2228 porting from <application>PL/SQL</> to
2229 <application>PL/pgSQL</application> are:
2234 There are no default values for parameters in <productname>PostgreSQL</>.
2240 You can overload functions in <productname>PostgreSQL</>. This is often used to work
2241 around the lack of default parameters.
2247 No need for cursors in <application>PL/pgSQL</>, just put the
2248 query in the <literal>FOR</literal> statement. (See <xref
2249 linkend="plpgsql-porting-ex2">.)
2255 In <productname>PostgreSQL</> you need to escape single
2256 quotes in the function body. See <xref linkend="plpgsql-quote">.
2262 Instead of packages, use schemas to organize your functions
2270 <title>Porting Examples</title>
2273 <xref linkend="pgsql-porting-ex1"> shows how to port a simple
2274 function from <application>PL/SQL</> to <application>PL/pgSQL</>.
2277 <example id="pgsql-porting-ex1">
2278 <title>Porting a Simple Function from <application>PL/SQL</> to <application>PL/pgSQL</></title>
2281 Here is an <productname>Oracle</productname> <application>PL/SQL</> function:
2283 CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name IN varchar, v_version IN varchar)
2286 IF v_version IS NULL THEN
2289 RETURN v_name || '/' || v_version;
2297 Let's go through this function and see the differences to <application>PL/pgSQL</>:
2302 <productname>PostgreSQL</productname> does not have named
2303 parameters. You have to explicitly alias them inside your
2310 <productname>Oracle</productname> can have
2311 <literal>IN</literal>, <literal>OUT</literal>, and
2312 <literal>INOUT</literal> parameters passed to functions.
2313 <literal>INOUT</literal>, for example, means that the
2314 parameter will receive a value and return
2315 another. <productname>PostgreSQL</> only has <literal>IN</literal>
2322 The <literal>RETURN</literal> key word in the function
2323 prototype (not the function body) becomes
2324 <literal>RETURNS</literal> in PostgreSQL.
2330 In <productname>PostgreSQL</>, functions are created using
2331 single quotes as the delimiters of the function body, so you
2332 have to escape single quotes inside the function body.
2338 The <literal>/show errors</literal> command does not exist in
2339 <productname>PostgreSQL</>.
2346 This is how this function would look when ported to
2347 <productname>PostgreSQL</>:
2350 CREATE OR REPLACE FUNCTION cs_fmt_browser_version(varchar, varchar)
2351 RETURNS varchar AS '
2353 v_name ALIAS FOR $1;
2354 v_version ALIAS FOR $2;
2356 IF v_version IS NULL THEN
2359 RETURN v_name || ''/'' || v_version;
2367 <xref linkend="plpgsql-porting-ex2"> shows how to port a
2368 function that creates another function and how to handle to
2369 ensuing quoting problems.
2372 <example id="plpgsql-porting-ex2">
2373 <title>Porting a Function that Creates Another Function from <application>PL/SQL</> to <application>PL/pgSQL</></title>
2376 The following procedure grabs rows from a
2377 <command>SELECT</command> statement and builds a large function
2378 with the results in <literal>IF</literal> statements, for the
2379 sake of efficiency. Notice particularly the differences in the
2380 cursor and the <literal>FOR</literal> loop,
2384 This is the Oracle version:
2386 CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
2387 CURSOR referrer_keys IS
2388 SELECT * FROM cs_referrer_keys
2391 a_output VARCHAR(4000);
2393 a_output := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR, v_domain IN VARCHAR,
2394 v_url IN VARCHAR) RETURN VARCHAR IS BEGIN';
2396 FOR referrer_key IN referrer_keys LOOP
2397 a_output := a_output || ' IF v_' || referrer_key.kind || ' LIKE ''' ||
2398 referrer_key.key_string || ''' THEN RETURN ''' || referrer_key.referrer_type ||
2402 a_output := a_output || ' RETURN NULL; END;';
2403 EXECUTE IMMEDIATE a_output;
2411 Here is how this function would end up in <productname>PostgreSQL</>:
2414 CREATE FUNCTION cs_update_referrer_type_proc() RETURNS integer AS '
2416 referrer_keys RECORD; -- Declare a generic record to be used in a FOR
2417 a_output varchar(4000);
2419 a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar, varchar, varchar)
2420 RETURNS varchar AS ''''
2422 v_host ALIAS FOR $1;
2423 v_domain ALIAS FOR $2;
2427 -- Notice how we scan through the results of a query in a FOR loop
2428 -- using the FOR <record> construct.
2430 FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
2431 a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE ''''''''''
2432 || referrer_keys.key_string || '''''''''' THEN RETURN ''''''
2433 || referrer_keys.referrer_type || ''''''; END IF;'';
2436 a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE plpgsql;'';
2438 -- EXECUTE will work because we are not substituting any variables.
2439 -- Otherwise it would fail. Look at PERFORM for another way to run functions.
2449 <xref linkend="plpgsql-porting-ex3"> shows how to port a function
2450 with <literal>OUT</> parameters and string manipulation.
2451 <productname>PostgreSQL</> does not have an
2452 <function>instr</function> function, but you can work around it
2453 using a combination of other functions. In <xref
2454 linkend="plpgsql-porting-appendix"> there is a
2455 <application>PL/pgSQL</application> implementation of
2456 <function>instr</function> that you can use to make your porting
2460 <example id="plpgsql-porting-ex3">
2461 <title>Porting a Procedure With String Manipulation and
2462 <literal>OUT</> Parameters from <application>PL/SQL</> to
2463 <application>PL/pgSQL</></title>
2466 The following <productname>Oracle</productname> PL/SQL procedure is used to parse a URL and
2467 return several elements (host, path, and query).
2468 <application>PL/pgSQL</application> functions can return only one value. In
2469 <productname>PostgreSQL</>, one way to work around this is to split the procedure
2470 in three different functions: one to return the host, another for
2471 the path, and another for the query.
2475 This is the Oracle version:
2477 CREATE OR REPLACE PROCEDURE cs_parse_url(
2479 v_host OUT VARCHAR, -- This will be passed back
2480 v_path OUT VARCHAR, -- This one too
2481 v_query OUT VARCHAR) -- And this one
2489 a_pos1 := instr(v_url, '//');
2494 a_pos2 := instr(v_url, '/', a_pos1 + 2);
2496 v_host := substr(v_url, a_pos1 + 2);
2501 v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
2502 a_pos1 := instr(v_url, '?', a_pos2 + 1);
2505 v_path := substr(v_url, a_pos2);
2509 v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
2510 v_query := substr(v_url, a_pos1 + 1);
2518 Here is how the <application>PL/pgSQL</> function that returns
2519 the host part could look like:
2522 CREATE OR REPLACE FUNCTION cs_parse_url_host(varchar) RETURNS varchar AS '
2532 a_pos1 := instr(v_url, ''//'');
2535 RETURN ''''; -- Return a blank
2538 a_pos2 := instr(v_url,''/'',a_pos1 + 2);
2540 v_host := substr(v_url, a_pos1 + 2);
2545 v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2 );
2554 <xref linkend="plpgsql-porting-ex4"> shows how to port a procedure
2555 that uses numerous features that are specific to Oracle.
2558 <example id="plpgsql-porting-ex4">
2559 <title>Porting a Procedure from <application>PL/SQL</> to <application>PL/pgSQL</></title>
2565 CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
2566 a_running_job_count INTEGER;
2567 PRAGMA AUTONOMOUS_TRANSACTION;<co id="co.plpgsql-porting-pragma">
2569 LOCK TABLE cs_jobs IN EXCLUSIVE MODE;<co id="co.plpgsql-porting-locktable">
2571 SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
2573 IF a_running_job_count > 0 THEN
2574 COMMIT; -- free lock<co id="co.plpgsql-porting-commit">
2575 raise_application_error(-20000, 'Unable to create a new job: a job is currently running.');
2578 DELETE FROM cs_active_job;
2579 INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
2582 INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, sysdate);
2583 EXCEPTION WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists<co id="co.plpgsql-porting-exception">
2593 Procedures like this can be easily converted into <productname>PostgreSQL</>
2594 functions returning an <type>integer</type>. This procedure in
2595 particular is interesting because it can teach us some things:
2598 <callout arearefs="co.plpgsql-porting-pragma">
2600 There is no <literal>PRAGMA</literal> statement in <productname>PostgreSQL</>.
2604 <callout arearefs="co.plpgsql-porting-locktable">
2606 If you do a <command>LOCK TABLE</command> in <application>PL/pgSQL</>, the lock
2607 will not be released until the calling transaction is finished.
2611 <callout arearefs="co.plpgsql-porting-commit">
2613 You also cannot have transactions in <application>PL/pgSQL</application> functions. The
2614 entire function (and other functions called from therein) is
2615 executed in one transaction and <productname>PostgreSQL</> rolls back the transaction if
2616 something goes wrong.
2620 <callout arearefs="co.plpgsql-porting-exception">
2622 The exception when would have to be replaced by an
2623 <literal>IF</literal> statement.
2630 This is how we could port this procedure to <application>PL/pgSQL</>:
2633 CREATE OR REPLACE FUNCTION cs_create_job(integer) RETURNS integer AS '
2635 v_job_id ALIAS FOR $1;
2636 a_running_job_count integer;
2639 LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
2640 SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
2642 IF a_running_job_count > 0
2644 RAISE EXCEPTION ''Unable to create a new job: a job is currently running.'';
2647 DELETE FROM cs_active_job;
2648 INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
2650 SELECT count(*) INTO a_num FROM cs_jobs WHERE job_id=v_job_id;
2651 IF NOT FOUND THEN -- If nothing was returned in the last query
2652 -- This job is not in the table so lets insert it.
2653 INSERT INTO cs_jobs(job_id, start_stamp) VALUES (v_job_id, current_timestamp);
2656 RAISE NOTICE ''Job already running.'';<co id="co.plpgsql-porting-raise">
2665 <callout arearefs="co.plpgsql-porting-raise">
2667 Notice how you can raise notices (or errors) in <application>PL/pgSQL</>.
2675 <sect2 id="plpgsql-porting-other">
2676 <title>Other Things to Watch For</title>
2679 This section explains a few other things to watch for when porting
2680 Oracle <application>PL/SQL</> functions to PostgreSQL.
2684 <title><command>EXECUTE</command></title>
2687 The <application>PL/pgSQL</> version of
2688 <command>EXECUTE</command> works similar to the
2689 <application>PL/SQL</> version, but you have to remember to use
2690 <function>quote_literal(text)</function> and
2691 <function>quote_string(text)</function> as described in <xref
2692 linkend="plpgsql-statements-executing-dyn">. Constructs of the
2693 type <literal>EXECUTE ''SELECT * FROM $1'';</literal> will not
2694 work unless you use these functions.
2698 <sect3 id="plpgsql-porting-optimization">
2699 <title>Optimizing <application>PL/pgSQL</application> Functions</title>
2702 <productname>PostgreSQL</> gives you two function creation
2703 modifiers to optimize execution: the volatility (whether the
2704 function always returns the same result when given the same
2705 arguments) and the <quote>strictness</quote> (whether the
2706 function returns null if any argument is null). Consult the description of
2707 <command>CREATE FUNCTION</command> for details.
2711 To make use of these optimization attributes, your
2712 <command>CREATE FUNCTION</command> statement could look something
2716 CREATE FUNCTION foo(...) RETURNS integer AS '
2718 ' LANGUAGE plpgsql STRICT IMMUTABLE;
2724 <sect2 id="plpgsql-porting-appendix">
2725 <title>Appendix</title>
2728 This section contains the code for an Oracle-compatible
2729 <function>instr</function> function that you can use to simplify
2730 your porting efforts.
2735 -- instr functions that mimic Oracle's counterpart
2736 -- Syntax: instr(string1, string2, [n], [m]) where [] denotes optional parameters.
2738 -- Searches string1 beginning at the nth character for the mth occurrence
2739 -- of string2. If n is negative, search backwards. If m is not passed,
2740 -- assume 1 (search starts at first character).
2743 CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS '
2747 pos:= instr($1, $2, 1);
2753 CREATE FUNCTION instr(varchar, varchar, varchar) RETURNS integer AS '
2755 string ALIAS FOR $1;
2756 string_to_search ALIAS FOR $2;
2757 beg_index ALIAS FOR $3;
2758 pos integer NOT NULL DEFAULT 0;
2764 IF beg_index > 0 THEN
2765 temp_str := substring(string FROM beg_index);
2766 pos := position(string_to_search IN temp_str);
2771 RETURN pos + beg_index - 1;
2774 ss_length := char_length(string_to_search);
2775 length := char_length(string);
2776 beg := length + beg_index - ss_length + 2;
2779 temp_str := substring(string FROM beg FOR ss_length);
2780 pos := position(string_to_search IN temp_str);
2795 CREATE FUNCTION instr(varchar, varchar, integer, integer) RETURNS integer AS '
2797 string ALIAS FOR $1;
2798 string_to_search ALIAS FOR $2;
2799 beg_index ALIAS FOR $3;
2800 occur_index ALIAS FOR $4;
2801 pos integer NOT NULL DEFAULT 0;
2802 occur_number integer NOT NULL DEFAULT 0;
2809 IF beg_index > 0 THEN
2811 temp_str := substring(string FROM beg_index);
2813 FOR i IN 1..occur_index LOOP
2814 pos := position(string_to_search IN temp_str);
2817 beg := beg + pos - 1;
2822 temp_str := substring(string FROM beg + 1);
2831 ss_length := char_length(string_to_search);
2832 length := char_length(string);
2833 beg := length + beg_index - ss_length + 2;
2836 temp_str := substring(string FROM beg FOR ss_length);
2837 pos := position(string_to_search IN temp_str);
2840 occur_number := occur_number + 1;
2842 IF occur_number = occur_index THEN
2861 <!-- Keep this comment at the end of the file
2866 sgml-minimize-attributes:nil
2867 sgml-always-quote-attributes:t
2870 sgml-parent-document:nil
2871 sgml-default-dtd-file:"./reference.ced"
2872 sgml-exposed-tags:nil
2873 sgml-local-catalogs:("/usr/lib/sgml/catalog")
2874 sgml-local-ecat-files:nil