| blob | 78e4983139b55fa01d19e2e747d2bd458104357d |
1 <!-- doc/src/sgml/plpgsql.sgml -->
4 <title><application>PL/pgSQL</application> — <acronym>SQL</acronym> Procedural Language</title>
8 </indexterm>
13 <para>
17 a loadable procedural language that
19 <itemizedlist>
20 <listitem>
21 <para>
22 can be used to create functions, procedures, and triggers,
23 </para>
24 </listitem>
25 <listitem>
26 <para>
28 </para>
29 </listitem>
30 <listitem>
31 <para>
32 can perform complex computations,
33 </para>
34 </listitem>
35 <listitem>
36 <para>
37 inherits all user-defined types, functions, procedures, and operators,
38 </para>
39 </listitem>
40 <listitem>
41 <para>
42 can be defined to be trusted by the server,
43 </para>
44 </listitem>
45 <listitem>
46 <para>
47 is easy to use.
48 </para>
49 </listitem>
50 </itemizedlist>
51 </para>
53 <para>
55 used anywhere that built-in functions could be used.
56 For example, it is possible to
57 create complex conditional computation functions and later use
58 them to define operators or use them in index expressions.
59 </para>
61 <para>
64 However it is still a loadable module, so especially security-conscious
65 administrators could choose to remove it.
66 </para>
71 <para>
73 and most other relational databases use as query language. It's
75 statement must be executed individually by the database server.
76 </para>
78 <para>
79 That means that your client application must send each query to
80 the database server, wait for it to be processed, receive and
81 process the results, do some computation, then send further
82 queries to the server. All this incurs interprocess
83 communication and will also incur network overhead if your client
84 is on a different machine than the database server.
85 </para>
87 <para>
90 the database server, thus having the power of a procedural
91 language and the ease of use of SQL, but with considerable
92 savings of client/server communication overhead.
93 </para>
94 <itemizedlist>
96 <listitem><para> Extra round trips between
97 client and server are eliminated </para></listitem>
99 <listitem><para> Intermediate results that the client does not
100 need do not have to be marshaled or transferred between server
101 and client </para></listitem>
103 <listitem><para> Multiple rounds of query
104 parsing can be avoided </para></listitem>
106 </itemizedlist>
107 <para> This can result in a considerable performance increase as
108 compared to an application that does not use stored functions.
109 </para>
111 <para>
113 the data types, operators and functions of SQL.
114 </para>
115 </sect2>
120 <para>
122 as arguments any scalar or array data type supported by the server,
123 and they can return a result of any of these types. They can also
124 accept or return any composite type (row type) specified by name.
127 composite type will do as input, or
129 is a row type whose columns are determined by specification in the
131 </para>
133 <para>
136 works exactly the same way as for SQL functions, as discussed in
138 </para>
140 <para>
142 accept and return the polymorphic types described in
144 types handled by the function to vary from call to call.
146 </para>
148 <para>
151 a single instance. Such a function generates its output by executing
154 evaluating a query.
155 </para>
157 <para>
160 could be written as a procedure in that case.)
161 </para>
163 <para>
165 parameters in place of an explicit specification of the return type.
166 This does not add any fundamental capability to the language, but
167 it is often convenient, especially for returning multiple values.
170 </para>
172 <para>
173 Specific examples appear in
176 </para>
177 </sect2>
178 </sect1>
183 <para>
186 Such a command would normally look like, say,
187 <programlisting>
188 CREATE FUNCTION somefunc(integer, text) RETURNS integer
190 LANGUAGE plpgsql;
191 </programlisting>
192 The function body is simply a string literal so far as <command>CREATE
193 FUNCTION</command> is concerned. It is often helpful to use dollar quoting
195 body, rather than the normal single quote syntax. Without dollar quoting,
196 any single quotes or backslashes in the function body must be escaped by
197 doubling them. Almost all the examples in this chapter use dollar-quoted
198 literals for their function bodies.
199 </para>
201 <para>
203 The complete text of a function body must be a
206 <synopsis>
208 <optional> DECLARE
210 BEGIN
213 </synopsis>
214 </para>
216 <para>
217 Each declaration and each statement within a block is terminated
218 by a semicolon. A block that appears within another block must
221 concludes a function body does not require a semicolon.
222 </para>
224 <tip>
225 <para>
226 A common mistake is to write a semicolon immediately after
228 </para>
229 </tip>
231 <para>
233 identify the block for use
235 variables declared in the block. If a label is given after
237 </para>
239 <para>
240 All key words are case-insensitive.
241 Identifiers are implicitly converted to lower case
242 unless double-quoted, just as they are in ordinary SQL commands.
243 </para>
245 <para>
249 block comment that extends to the matching occurrence of
251 </para>
253 <para>
254 Any statement in the statement section of a block
256 logical grouping or to localize variables to a small group
257 of statements. Variables declared in a subblock mask any
258 similarly-named variables of outer blocks for the duration
259 of the subblock; but you can access the outer variables anyway
260 if you qualify their names with their block's label. For example:
261 <programlisting>
262 CREATE FUNCTION somefunc() RETURNS integer AS $$
264 DECLARE
265 quantity integer := 30;
266 BEGIN
267 RAISE NOTICE 'Quantity here is %', quantity; -- Prints 30
268 quantity := 50;
269 --
270 -- Create a subblock
271 --
272 DECLARE
273 quantity integer := 80;
274 BEGIN
275 RAISE NOTICE 'Quantity here is %', quantity; -- Prints 80
276 RAISE NOTICE 'Outer quantity here is %', outerblock.quantity; -- Prints 50
277 END;
279 RAISE NOTICE 'Quantity here is %', quantity; -- Prints 50
281 RETURN quantity;
282 END;
283 $$ LANGUAGE plpgsql;
284 </programlisting>
285 </para>
287 <note>
288 <para>
291 declarations of the function's parameters (if any), as well as some
294 labeled with the function's name, meaning that parameters and special
295 variables can be qualified with the function's name.
296 </para>
297 </note>
299 <para>
300 It is important not to confuse the use of
303 for transaction
305 are only for grouping; they do not start or end a transaction.
309 forms a subtransaction that can be rolled back without affecting the
310 outer transaction. For more about that see <xref
312 </para>
313 </sect1>
318 <para>
319 All variables used in a block must be declared in the
320 declarations section of the block.
322 iterating over a range of integer values is automatically declared as an
324 iterating over a cursor's result is automatically declared as a
325 record variable.)
326 </para>
328 <para>
332 </para>
334 <para>
335 Here are some examples of variable declarations:
336 <programlisting>
337 user_id integer;
338 quantity numeric(5);
339 url varchar;
340 myrow tablename%ROWTYPE;
341 myfield tablename.columnname%TYPE;
342 arow RECORD;
343 </programlisting>
344 </para>
346 <para>
347 The general syntax of a variable declaration is:
348 <synopsis>
349 <replaceable>name</replaceable> <optional> CONSTANT </optional> <replaceable>type</replaceable> <optional> COLLATE <replaceable>collation_name</replaceable> </optional> <optional> NOT NULL </optional> <optional> { DEFAULT | := | = } <replaceable>expression</replaceable> </optional>;
350 </synopsis>
353 is not given then the variable is initialized to the
356 assigned to after initialization, so that its value will remain constant
357 for the duration of the block.
361 is specified, an assignment of a null value results in a run-time
363 must have a nonnull default value specified.
366 </para>
368 <para>
369 A variable's default value is evaluated and assigned to the variable
370 each time the block is entered (not just once per function call).
373 time of the current function call, not the time when the function was
374 precompiled.
375 </para>
377 <para>
378 Examples:
379 <programlisting>
380 quantity integer DEFAULT 32;
381 url varchar := 'http://mysite.com';
382 transaction_time CONSTANT timestamp with time zone := now();
383 </programlisting>
384 </para>
386 <para>
387 Once declared, a variable's value can be used in later initialization
388 expressions in the same block, for example:
389 <programlisting>
390 DECLARE
391 x integer := 1;
392 y integer := x + 1;
393 </programlisting>
394 </para>
399 <para>
400 Parameters passed to functions are named with the identifiers
402 etc. Optionally, aliases can be declared for
404 parameter names for increased readability. Either the alias or the
405 numeric identifier can then be used to refer to the parameter value.
406 </para>
408 <para>
409 There are two ways to create an alias. The preferred way is to give a
411 for example:
412 <programlisting>
413 CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
414 BEGIN
415 RETURN subtotal * 0.06;
416 END;
417 $$ LANGUAGE plpgsql;
418 </programlisting>
419 The other way is to explicitly declare an alias, using the
420 declaration syntax
422 <synopsis>
424 </synopsis>
426 The same example in this style looks like:
427 <programlisting>
428 CREATE FUNCTION sales_tax(real) RETURNS real AS $$
429 DECLARE
430 subtotal ALIAS FOR $1;
431 BEGIN
432 RETURN subtotal * 0.06;
433 END;
434 $$ LANGUAGE plpgsql;
435 </programlisting>
436 </para>
438 <note>
439 <para>
440 These two examples are not perfectly equivalent. In the first case,
444 be qualified with that label, instead.)
445 </para>
446 </note>
448 <para>
449 Some more examples:
450 <programlisting>
451 CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
452 DECLARE
453 v_string ALIAS FOR $1;
454 index ALIAS FOR $2;
455 BEGIN
456 -- some computations using v_string and index here
457 END;
458 $$ LANGUAGE plpgsql;
461 CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
462 BEGIN
463 RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
464 END;
465 $$ LANGUAGE plpgsql;
466 </programlisting>
467 </para>
469 <para>
471 with output parameters, the output parameters are given
473 aliases in just the same way as the normal input parameters. An
474 output parameter is effectively a variable that starts out NULL;
475 it should be assigned to during the execution of the function.
476 The final value of the parameter is what is returned. For instance,
477 the sales-tax example could also be done this way:
479 <programlisting>
480 CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
481 BEGIN
482 tax := subtotal * 0.06;
483 END;
484 $$ LANGUAGE plpgsql;
485 </programlisting>
488 included it, but it would be redundant.
489 </para>
491 <para>
493 output parameter(s) in the function call:
494 <programlisting>
495 SELECT sales_tax(100.00);
496 </programlisting>
497 </para>
499 <para>
500 Output parameters are most useful when returning multiple values.
501 A trivial example is:
503 <programlisting>
504 CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
505 BEGIN
506 sum := x + y;
507 prod := x * y;
508 END;
509 $$ LANGUAGE plpgsql;
512 sum | prod
513 -----+------
515 </programlisting>
518 effectively creates an anonymous record type for the function's
521 </para>
523 <para>
524 This also works with procedures, for example:
526 <programlisting>
527 CREATE PROCEDURE sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
528 BEGIN
529 sum := x + y;
530 prod := x * y;
531 END;
532 $$ LANGUAGE plpgsql;
533 </programlisting>
535 In a call to a procedure, all the parameters must be specified. For
537 calling the procedure from plain SQL:
538 <programlisting>
540 sum | prod
541 -----+------
543 </programlisting>
545 However, when calling a procedure
547 variable for any output parameter; the variable will receive the result
549 for details.
550 </para>
552 <para>
556 <programlisting>
557 CREATE FUNCTION extended_sales(p_itemno int)
558 RETURNS TABLE(quantity int, total numeric) AS $$
559 BEGIN
560 RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
561 WHERE s.itemno = p_itemno;
562 END;
563 $$ LANGUAGE plpgsql;
564 </programlisting>
567 parameters and specifying <literal>RETURNS SETOF
569 </para>
571 <para>
573 is declared as a polymorphic type (see
576 return type of the function, as deduced from the actual input types.
577 This allows the function to access its actual return type
580 the function, so it can be used to hold the return value if desired,
582 given an alias. For example, this function works on any data type
585 <programlisting>
586 CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
587 RETURNS anyelement AS $$
588 DECLARE
589 result ALIAS FOR $0;
590 BEGIN
591 result := v1 + v2 + v3;
592 RETURN result;
593 END;
594 $$ LANGUAGE plpgsql;
595 </programlisting>
596 </para>
598 <para>
599 The same effect can be obtained by declaring one or more output parameters as
600 polymorphic types. In this case the
602 parameters themselves serve the same purpose. For example:
604 <programlisting>
605 CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
606 OUT sum anyelement)
607 AS $$
608 BEGIN
609 sum := v1 + v2 + v3;
610 END;
611 $$ LANGUAGE plpgsql;
612 </programlisting>
613 </para>
615 <para>
616 In practice it might be more useful to declare a polymorphic function
618 promotion of the input arguments to a common type will occur.
619 For example:
621 <programlisting>
622 CREATE FUNCTION add_three_values(v1 anycompatible, v2 anycompatible, v3 anycompatible)
623 RETURNS anycompatible AS $$
624 BEGIN
625 RETURN v1 + v2 + v3;
626 END;
627 $$ LANGUAGE plpgsql;
628 </programlisting>
630 With this example, a call such as
632 <programlisting>
634 </programlisting>
636 will work, automatically promoting the integer inputs to numeric.
638 cast the three inputs to the same type manually.
639 </para>
640 </sect2>
645 <synopsis>
647 </synopsis>
649 <para>
651 previous section: you can declare an alias for any variable, not just
652 function parameters. The main practical use for this is to assign
653 a different name for variables with predetermined names, such as
655 a trigger function.
656 </para>
658 <para>
659 Examples:
660 <programlisting>
661 DECLARE
662 prior ALIAS FOR old;
663 updated ALIAS FOR new;
664 </programlisting>
665 </para>
667 <para>
669 object, unrestricted use can be confusing. It's best to use it only
670 for the purpose of overriding predetermined names.
671 </para>
672 </sect2>
677 <synopsis>
678 <replaceable>name</replaceable> <replaceable>table</replaceable>.<replaceable>column</replaceable>%TYPE
680 </synopsis>
682 <para>
685 variable. You can use this to declare variables that will hold
686 database values. For example, let's say you have a column named
688 table. To declare a variable with the same data type as
690 <programlisting>
691 user_id users.user_id%TYPE;
692 </programlisting>
693 </para>
695 <para>
696 It is also possible to write array decoration
698 an array of the referenced type:
699 <programlisting>
700 user_ids users.user_id%TYPE[];
701 user_ids users.user_id%TYPE ARRAY[4]; -- equivalent to the above
702 </programlisting>
703 Just as when declaring table columns that are arrays, it doesn't
704 matter whether you write multiple bracket pairs or specific array
706 a given element type as the same type, regardless of dimensionality.
708 </para>
710 <para>
712 type of the structure you are referencing, and most importantly,
713 if the data type of the referenced item changes in the future (for
716 to change your function definition.
717 </para>
719 <para>
721 functions, since the data types needed for internal variables can
722 change from one call to the next. Appropriate variables can be
724 arguments or result placeholders.
725 </para>
727 </sect2>
732 <synopsis>
733 <replaceable>name</replaceable> <replaceable>table_name</replaceable><literal>%ROWTYPE</literal>;
735 </synopsis>
737 <para>
741 query result, so long as that query's column set matches the
742 declared type of the variable.
743 The individual fields of the row value
744 are accessed using the usual dot notation, for example
746 </para>
748 <para>
749 A row variable can be declared to have the same type as the rows of
750 an existing table or view, by using the
752 notation; or it can be declared by giving a composite type's name.
753 (Since every table has an associated composite type of the same name,
757 </para>
759 <para>
761 followed by array decoration to declare a variable that holds an array
762 of the referenced composite type.
763 </para>
765 <para>
766 Parameters to a function can be
767 composite types (complete table rows). In that case, the
768 corresponding identifier <literal>$<replaceable>n</replaceable></literal> will be a row variable, and fields can
770 </para>
772 <para>
775 mentioned fields:
777 <programlisting>
778 CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
779 DECLARE
780 t2_row table2%ROWTYPE;
781 BEGIN
782 SELECT * INTO t2_row FROM table2 WHERE ... ;
783 RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
784 END;
785 $$ LANGUAGE plpgsql;
787 SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
788 </programlisting>
789 </para>
790 </sect2>
795 <synopsis>
797 </synopsis>
799 <para>
800 Record variables are similar to row-type variables, but they have no
801 predefined structure. They take on the actual row structure of the
802 row they are assigned during a <command>SELECT</command> or <command>FOR</command> command. The substructure
803 of a record variable can change each time it is assigned to.
804 A consequence of this is that until a record variable is first assigned
805 to, it has no substructure, and any attempt to access a
806 field in it will draw a run-time error.
807 </para>
809 <para>
813 same concept as a record variable, even though such a function might
814 use a record variable to hold its result. In both cases the actual row
815 structure is unknown when the function is written, but for a function
817 calling query is parsed, whereas a record variable can change its row
818 structure on-the-fly.
819 </para>
820 </sect2>
825 <indexterm>
828 </indexterm>
830 <para>
832 parameters of collatable data types, a collation is identified for each
833 function call depending on the collations assigned to the actual
835 successfully identified (i.e., there are no conflicts of implicit
836 collations among the arguments) then all the collatable parameters are
837 treated as having that collation implicitly. This will affect the
838 behavior of collation-sensitive operations within the function.
839 For example, consider
841 <programlisting>
842 CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
843 BEGIN
844 RETURN a < b;
845 END;
846 $$ LANGUAGE plpgsql;
848 SELECT less_than(text_field_1, text_field_2) FROM table1;
849 SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;
850 </programlisting>
855 </para>
857 <para>
858 Furthermore, the identified collation is also assumed as the collation of
859 any local variables that are of collatable types. Thus this function
860 would not work any differently if it were written as
862 <programlisting>
863 CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
864 DECLARE
865 local_a text := a;
866 local_b text := b;
867 BEGIN
868 RETURN local_a < local_b;
869 END;
870 $$ LANGUAGE plpgsql;
871 </programlisting>
872 </para>
874 <para>
875 If there are no parameters of collatable data types, or no common
876 collation can be identified for them, then parameters and local variables
877 use the default collation of their data type (which is usually the
878 database's default collation, but could be different for variables of
879 domain types).
880 </para>
882 <para>
883 A local variable of a collatable data type can have a different collation
885 declaration, for example
887 <programlisting>
888 DECLARE
889 local_a text COLLATE "en_US";
890 </programlisting>
892 This option overrides the collation that would otherwise be
893 given to the variable according to the rules above.
894 </para>
896 <para>
898 a function if it is desired to force a particular collation to be used in
899 a particular operation. For example,
901 <programlisting>
902 CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$
903 BEGIN
905 END;
906 $$ LANGUAGE plpgsql;
907 </programlisting>
909 This overrides the collations associated with the table columns,
910 parameters, or local variables used in the expression, just as would
911 happen in a plain SQL command.
912 </para>
913 </sect2>
914 </sect1>
919 <para>
921 statements are processed using the server's main
924 <synopsis>
926 </synopsis>
928 feeding a query like
929 <synopsis>
931 </synopsis>
934 are replaced by query parameters, as discussed in detail in
937 be prepared just once and then reused for subsequent
938 evaluations with different values of the variables. Thus, what
939 really happens on first use of an expression is essentially a
942 <programlisting>
943 IF x < y THEN ...
944 </programlisting>
945 what happens behind the scenes is equivalent to
946 <programlisting>
948 </programlisting>
952 parameter values. Normally these details are
954 they are useful to know when trying to diagnose a problem.
956 </para>
958 <para>
964 Thus for example one could test whether a table is non-empty with
965 <programlisting>
967 </programlisting>
972 more than one row. (If it produces no rows, the result is taken as
973 NULL.)
974 </para>
975 </sect1>
980 <para>
981 In this section and the following ones, we describe all the statement
982 types that are explicitly understood by
984 Anything not recognized as one of these statement types is presumed
985 to be an SQL command and is sent to the main database engine to execute,
987 </para>
992 <para>
994 variable is written as:
995 <synopsis>
997 </synopsis>
998 As explained previously, the expression in such a statement is evaluated
1000 database engine. The expression must yield a single value (possibly
1001 a row value, if the variable is a row or record variable). The target
1002 variable can be a simple variable (optionally qualified with a block
1003 name), a field of a row or record target, or an element or slice of
1006 </para>
1008 <para>
1009 If the expression's result data type doesn't match the variable's
1010 data type, the value will be coerced as though by an assignment cast
1013 interpreter will attempt to convert the result value textually, that is
1014 by applying the result type's output function followed by the variable
1015 type's input function. Note that this could result in run-time errors
1016 generated by the input function, if the string form of the result value
1017 is not acceptable to the input function.
1018 </para>
1020 <para>
1021 Examples:
1022 <programlisting>
1023 tax := subtotal * 0.06;
1024 my_record.user_id := 20;
1025 my_array[j] := 20;
1027 complex_array[n].realpart = 12.3;
1028 </programlisting>
1029 </para>
1030 </sect2>
1035 <para>
1036 In general, any SQL command that does not return rows can be executed
1038 the command. For example, you could create and fill a table by writing
1039 <programlisting>
1040 CREATE TABLE mytable (id int primary key, data text);
1042 </programlisting>
1043 </para>
1045 <para>
1047 <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>/<command>MERGE</command>
1049 When the command will return at most one row, or you only care about
1050 the first row of output, write the command as usual but add
1053 To process all of the output rows, write the command as the data
1056 </para>
1058 <para>
1059 Usually it is not sufficient just to execute statically-defined SQL
1060 commands. Typically you'll want a command to use varying data values,
1061 or even to vary in more fundamental ways such as by using different
1062 table names at different times. Again, there are two ways to proceed
1063 depending on the situation.
1064 </para>
1066 <para>
1068 automatically inserted into optimizable SQL commands, which
1072 utility commands that incorporate one of these, such
1074 SELECT</command>. In these commands,
1076 in the command text is replaced by a query parameter, and then the
1077 current value of the variable is provided as the parameter value
1078 at run time. This is exactly like the processing described earlier
1080 </para>
1082 <para>
1083 When executing an optimizable SQL command in this way,
1085 plan for the command, as discussed in
1087 </para>
1089 <para>
1090 Non-optimizable SQL commands (also called utility commands) are not
1091 capable of accepting query parameters. So automatic substitution
1093 commands. To include non-constant text in a utility command executed
1097 </para>
1099 <para>
1101 the command in some other way than supplying a data value, for example
1102 by changing a table name.
1103 </para>
1105 <para>
1107 query but discard the result, for example when calling a function
1108 that has side-effects but no useful result value. To do
1112 <synopsis>
1114 </synopsis>
1121 place the query in parentheses. (In this case, the query can only
1122 return one row.)
1124 substituted into the query just as described above,
1125 and the plan is cached in the same way. Also, the special variable
1127 least one row, or false if it produced no rows (see
1129 </para>
1131 <note>
1132 <para>
1134 would accomplish this result, but at
1135 present the only accepted way to do it is
1139 next section.
1140 </para>
1141 </note>
1143 <para>
1144 An example:
1145 <programlisting>
1146 PERFORM create_mv('cs_session_page_requests_mv', my_query);
1147 </programlisting>
1148 </para>
1149 </sect2>
1157 </indexterm>
1162 </indexterm>
1164 <para>
1165 The result of an SQL command yielding a single row (possibly of multiple
1166 columns) can be assigned to a record variable, row-type variable, or list
1167 of scalar variables. This is done by writing the base SQL command and
1170 <synopsis>
1171 SELECT <replaceable>select_expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable> FROM ...;
1172 INSERT ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
1173 UPDATE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
1174 DELETE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
1175 MERGE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
1176 </synopsis>
1179 variable, or a comma-separated list of simple variables and
1180 record/row fields.
1182 substituted into the rest of the command (that is, everything but the
1184 and the plan is cached in the same way.
1186 <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>/<command>MERGE</command>
1191 </para>
1193 <tip>
1194 <para>
1198 target is a newly created table. If you want to create a table from a
1202 </para>
1203 </tip>
1205 <para>
1206 If a row variable or a variable list is used as target,
1207 the command's result columns
1208 must exactly match the structure of the target as to number and data
1209 types, or else a run-time error
1210 occurs. When a record variable is the target, it automatically
1211 configures itself to the row type of the command's result columns.
1212 </para>
1214 <para>
1216 command. Customarily it is written either just before or just after
1219 command types. It is recommended that you follow this convention
1221 stricter in future versions.
1222 </para>
1224 <para>
1227 row returned by the command, or to nulls if the command returned no rows.
1230 after the first row are discarded.
1233 determine whether a row was returned:
1235 <programlisting>
1236 SELECT * INTO myrec FROM emp WHERE empname = myname;
1237 IF NOT FOUND THEN
1238 RAISE EXCEPTION 'employee % not found', myname;
1239 END IF;
1240 </programlisting>
1243 return exactly one row or a run-time error will be reported, either
1245 (more than one row). You can use an exception block if you wish
1246 to catch the error, for example:
1248 <programlisting>
1249 BEGIN
1250 SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
1251 EXCEPTION
1252 WHEN NO_DATA_FOUND THEN
1253 RAISE EXCEPTION 'employee % not found', myname;
1254 WHEN TOO_MANY_ROWS THEN
1255 RAISE EXCEPTION 'employee % not unique', myname;
1256 END;
1257 </programlisting>
1260 </para>
1262 <para>
1263 For <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>/<command>MERGE</command> with
1265 an error for more than one returned row, even when
1268 which affected row should be returned.
1269 </para>
1271 <para>
1273 then when an error is thrown because the requirements
1275 the error message will include information about the parameters
1276 passed to the command.
1278 setting for all functions by setting
1280 function compilations will be affected. You can also enable it
1281 on a per-function basis by using a compiler option, for example:
1282 <programlisting>
1283 CREATE FUNCTION get_userid(username text) RETURNS int
1284 AS $$
1285 #print_strict_params on
1286 DECLARE
1287 userid int;
1288 BEGIN
1289 SELECT users.userid INTO STRICT userid
1290 FROM users WHERE users.username = get_userid.username;
1291 RETURN userid;
1292 END;
1293 $$ LANGUAGE plpgsql;
1294 </programlisting>
1295 On failure, this function might produce an error message such as
1296 <programlisting>
1297 ERROR: query returned no rows
1298 DETAIL: parameters: username = 'nosuchuser'
1299 CONTEXT: PL/pgSQL function get_userid(text) line 6 at SQL statement
1300 </programlisting>
1301 </para>
1303 <note>
1304 <para>
1307 </para>
1308 </note>
1310 </sect2>
1315 <para>
1316 Oftentimes you will want to generate dynamic commands inside your
1318 that will involve different tables or different data types each
1320 normal attempts to cache plans for commands (as discussed in
1322 scenarios. To handle this sort of problem, the
1325 <synopsis>
1326 EXECUTE <replaceable class="command">command-string</replaceable> <optional> INTO <optional>STRICT</optional> <replaceable>target</replaceable> </optional> <optional> USING <replaceable>expression</replaceable> <optional>, ... </optional> </optional>;
1327 </synopsis>
1332 is a record variable, a row variable, or a comma-separated list of
1333 simple variables and record/row fields, into which the results of
1335 supply values to be inserted into the command.
1336 </para>
1338 <para>
1340 computed command string. Any required variable values must be inserted
1341 in the command string as it is constructed; or you can use parameters
1342 as described below.
1343 </para>
1345 <para>
1346 Also, there is no plan caching for commands executed via
1348 each time the statement is run. Thus the command
1349 string can be dynamically created within the function to perform
1350 actions on different tables and columns.
1351 </para>
1353 <para>
1355 an SQL command returning rows should be assigned. If a row variable
1356 or variable list is provided, it must exactly match the structure
1357 of the command's results; if a
1358 record variable is provided, it will configure itself to match the
1359 result structure automatically. If multiple rows are returned,
1361 variable(s). If no rows are returned, NULL is assigned to the
1363 clause is specified, the command results are discarded.
1364 </para>
1366 <para>
1368 unless the command produces exactly one row.
1369 </para>
1371 <para>
1372 The command string can use parameter values, which are referenced
1375 clause. This method is often preferable to inserting data values
1376 into the command string as text: it avoids run-time overhead of
1377 converting the values to text and back, and it is much less prone
1378 to SQL-injection attacks since there is no need for quoting or escaping.
1379 An example is:
1380 <programlisting>
1382 INTO c
1383 USING checked_user, checked_date;
1384 </programlisting>
1385 </para>
1387 <para>
1388 Note that parameter symbols can only be used for data values
1389 — if you want to use dynamically determined table or column
1390 names, you must insert them into the command string textually.
1391 For example, if the preceding query needed to be done against a
1392 dynamically selected table, you could do this:
1393 <programlisting>
1394 EXECUTE 'SELECT count(*) FROM '
1395 || quote_ident(tabname)
1397 INTO c
1398 USING checked_user, checked_date;
1399 </programlisting>
1401 specification to insert table or column names with automatic quoting:
1402 <programlisting>
1403 EXECUTE format('SELECT count(*) FROM %I '
1405 INTO c
1406 USING checked_user, checked_date;
1407 </programlisting>
1408 (This example relies on the SQL rule that string literals separated by a
1409 newline are implicitly concatenated.)
1410 </para>
1412 <para>
1413 Another restriction on parameter symbols is that they only work in
1414 optimizable SQL commands
1416 <command>DELETE</command>, <command>MERGE</command>, and certain commands containing one of these).
1417 In other statement
1418 types (generically called utility statements), you must insert
1419 values textually even if they are just data values.
1420 </para>
1422 <para>
1425 functionally equivalent to just writing the command directly in
1429 the command on each execution, generating a plan that is specific
1430 to the current parameter values; whereas
1432 and cache it for re-use. In situations where the best plan depends
1433 strongly on the parameter values, it can be helpful to use
1435 selected.
1436 </para>
1438 <para>
1442 itself.
1443 </para>
1445 <note>
1446 <para>
1450 statement supported by the
1454 </para>
1455 </note>
1460 <indexterm>
1463 </indexterm>
1465 <indexterm>
1468 </indexterm>
1470 <indexterm>
1473 </indexterm>
1475 <indexterm>
1478 </indexterm>
1480 <para>
1481 When working with dynamic commands you will often have to handle escaping
1482 of single quotes. The recommended method for quoting fixed text in your
1483 function body is dollar quoting. (If you have legacy code that does
1484 not use dollar quoting, please refer to the
1486 some effort when translating said code to a more reasonable scheme.)
1487 </para>
1489 <para>
1490 Dynamic values require careful handling since they might contain
1491 quote characters.
1493 dollar quoting the function body so quote marks need not be doubled):
1494 <programlisting>
1495 EXECUTE format('UPDATE tbl SET %I = $1 '
1496 'WHERE key = $2', colname) USING newvalue, keyvalue;
1497 </programlisting>
1498 It is also possible to call the quoting functions directly:
1499 <programlisting>
1500 EXECUTE 'UPDATE tbl SET '
1501 || quote_ident(colname)
1502 || ' = '
1503 || quote_literal(newvalue)
1504 || ' WHERE key = '
1505 || quote_literal(keyvalue);
1506 </programlisting>
1507 </para>
1509 <para>
1510 This example demonstrates the use of the
1514 or table identifiers should be passed through
1516 Expressions containing values that should be literal strings in the
1518 These functions take the appropriate steps to return the input text
1519 enclosed in double or single quotes respectively, with any embedded
1520 special characters properly escaped.
1521 </para>
1523 <para>
1532 For example,
1533 <programlisting>
1534 EXECUTE 'UPDATE tbl SET '
1535 || quote_ident(colname)
1536 || ' = '
1537 || quote_nullable(newvalue)
1538 || ' WHERE key = '
1539 || quote_nullable(keyvalue);
1540 </programlisting>
1541 If you are dealing with values that might be null, you should usually
1543 </para>
1545 <para>
1546 As always, care must be taken to ensure that null values in a query do
1548 <programlisting>
1549 'WHERE key = ' || quote_nullable(keyvalue)
1550 </programlisting>
1553 is always null. If you wish null to work like an ordinary key value,
1554 you would need to rewrite the above as
1555 <programlisting>
1556 'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
1557 </programlisting>
1562 </para>
1564 <para>
1565 Note that dollar quoting is only useful for quoting fixed text.
1566 It would be a very bad idea to try to write this example as:
1567 <programlisting>
1568 EXECUTE 'UPDATE tbl SET '
1569 || quote_ident(colname)
1570 || ' = $$'
1571 || newvalue
1572 || '$$ WHERE key = '
1573 || quote_literal(keyvalue);
1574 </programlisting>
1577 apply to any other dollar-quoting delimiter you might pick.
1578 So, to safely quote text that is not known in advance, you
1581 </para>
1583 <para>
1584 Dynamic SQL statements can also be safely constructed using the
1587 <programlisting>
1588 EXECUTE format('UPDATE tbl SET %I = %L '
1589 'WHERE key = %L', colname, newvalue, keyvalue);
1590 </programlisting>
1595 <programlisting>
1597 USING newvalue, keyvalue;
1598 </programlisting>
1599 This form is better because the variables are handled in their native
1600 data type format, rather than unconditionally converting them to
1602 </para>
1603 </example>
1605 <para>
1606 A much larger example of a dynamic command and
1610 </para>
1611 </sect2>
1616 <para>
1617 There are several ways to determine the effect of a command. The
1619 command, which has the form:
1621 <synopsis>
1622 GET <optional> CURRENT </optional> DIAGNOSTICS <replaceable>variable</replaceable> { = | := } <replaceable>item</replaceable> <optional> , ... </optional>;
1623 </synopsis>
1625 This command allows retrieval of system status indicators.
1630 (which should be of the right data type to receive it). The currently
1631 available status items are shown
1634 token. An example:
1635 <programlisting>
1636 GET DIAGNOSTICS integer_var = ROW_COUNT;
1637 </programlisting>
1638 </para>
1646 <thead>
1647 <row>
1651 </row>
1652 </thead>
1653 <tbody>
1654 <row>
1657 <entry>the number of rows processed by the most
1659 </row>
1660 <row>
1663 <entry>line(s) of text describing the current call stack
1665 </row>
1666 <row>
1670 </row>
1671 </tbody>
1672 </tgroup>
1673 </table>
1675 <para>
1676 The second method to determine the effects of a command is to check the
1680 It is set by each of the following types of statements:
1682 <itemizedlist>
1683 <listitem>
1684 <para>
1687 row is returned.
1688 </para>
1689 </listitem>
1690 <listitem>
1691 <para>
1693 true if it produces (and discards) one or more rows, false if
1694 no row is produced.
1695 </para>
1696 </listitem>
1697 <listitem>
1698 <para>
1702 row is affected, false if no row is affected.
1703 </para>
1704 </listitem>
1705 <listitem>
1706 <para>
1708 true if it returns a row, false if no row is returned.
1709 </para>
1710 </listitem>
1711 <listitem>
1712 <para>
1714 true if it successfully repositions the cursor, false otherwise.
1715 </para>
1716 </listitem>
1717 <listitem>
1718 <para>
1721 if it iterates one or more times, else false.
1723 loop exits; inside the execution of the loop,
1725 loop statement, although it might be changed by the
1726 execution of other statements within the loop body.
1727 </para>
1728 </listitem>
1729 <listitem>
1730 <para>
1733 true if the query returns at least one row, false if no row
1734 is returned.
1735 </para>
1736 </listitem>
1737 </itemizedlist>
1744 </para>
1746 <para>
1749 affect only the current function.
1750 </para>
1752 </sect2>
1757 <para>
1758 Sometimes a placeholder statement that does nothing is useful.
1759 For example, it can indicate that one arm of an if/then/else
1760 chain is deliberately empty. For this purpose, use the
1763 <synopsis>
1764 NULL;
1765 </synopsis>
1766 </para>
1768 <para>
1769 For example, the following two fragments of code are equivalent:
1770 <programlisting>
1771 BEGIN
1772 y := x / 0;
1773 EXCEPTION
1774 WHEN division_by_zero THEN
1775 NULL; -- ignore the error
1776 END;
1777 </programlisting>
1779 <programlisting>
1780 BEGIN
1781 y := x / 0;
1782 EXCEPTION
1783 WHEN division_by_zero THEN -- ignore the error
1784 END;
1785 </programlisting>
1786 Which is preferable is a matter of taste.
1787 </para>
1789 <note>
1790 <para>
1791 In Oracle's PL/SQL, empty statement lists are not allowed, and so
1794 just write nothing, instead.
1795 </para>
1796 </note>
1798 </sect2>
1799 </sect1>
1804 <para>
1805 Control structures are probably the most useful (and
1809 flexible and powerful way.
1810 </para>
1815 <para>
1816 There are two commands available that allow you to return data
1818 NEXT</command>.
1819 </para>
1824 <synopsis>
1826 </synopsis>
1828 <para>
1830 function and returns the value of
1833 not return a set.
1834 </para>
1836 <para>
1837 In a function that returns a scalar type, the expression's result will
1838 automatically be cast into the function's return type as described for
1839 assignments. But to return a composite (row) value, you must write an
1840 expression delivering exactly the requested column set. This may
1841 require use of explicit casting.
1842 </para>
1844 <para>
1845 If you declared the function with output parameters, write just
1847 of the output parameter variables will be returned.
1848 </para>
1850 <para>
1853 early; but do not write an expression following
1855 </para>
1857 <para>
1858 The return value of a function cannot be left undefined. If
1859 control reaches the end of the top-level block of the function
1861 error will occur. This restriction does not apply to functions
1864 automatically executed if the top-level block finishes.
1865 </para>
1867 <para>
1868 Some examples:
1870 <programlisting>
1871 -- functions returning a scalar type
1873 RETURN scalar_var;
1875 -- functions returning a composite type
1876 RETURN composite_type_var;
1878 </programlisting>
1879 </para>
1880 </sect3>
1884 <indexterm>
1887 </indexterm>
1888 <indexterm>
1891 </indexterm>
1893 <synopsis>
1896 RETURN QUERY EXECUTE <replaceable class="command">command-string</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ... </optional> </optional>;
1897 </synopsis>
1899 <para>
1902 to follow is slightly different. In that case, the individual
1903 items to return are specified by a sequence of <command>RETURN
1906 is used to indicate that the function has finished executing.
1908 composite data types; with a composite result type, an entire
1911 a query to the function's result set. <command>RETURN
1913 intermixed in a single set-returning function, in which case
1914 their results will be concatenated.
1915 </para>
1917 <para>
1920 they simply append zero or more rows to the function's result
1921 set. Execution then continues with the next statement in the
1924 QUERY</command> commands are executed, the result set is built
1926 argument, causes control to exit the function (or you can just
1927 let control reach the end of the function).
1928 </para>
1930 <para>
1933 query to be executed dynamically. Parameter expressions can
1936 </para>
1938 <para>
1939 If you declared the function with output parameters, write just
1941 execution, the current values of the output parameter
1942 variable(s) will be saved for eventual return as a row of the
1943 result. Note that you must declare the function as returning
1946 when there is just one output parameter of type
1948 function with output parameters.
1949 </para>
1951 <para>
1952 Here is an example of a function using <command>RETURN
1953 NEXT</command>:
1955 <programlisting>
1956 CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
1960 CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
1961 $BODY$
1962 DECLARE
1963 r foo%rowtype;
1964 BEGIN
1965 FOR r IN
1967 LOOP
1968 -- can do some processing here
1969 RETURN NEXT r; -- return current row of SELECT
1970 END LOOP;
1971 RETURN;
1972 END;
1973 $BODY$
1974 LANGUAGE plpgsql;
1976 SELECT * FROM get_all_foo();
1977 </programlisting>
1978 </para>
1980 <para>
1981 Here is an example of a function using <command>RETURN
1982 QUERY</command>:
1984 <programlisting>
1985 CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS
1986 $BODY$
1987 BEGIN
1988 RETURN QUERY SELECT flightid
1989 FROM flight
1993 -- Since execution is not finished, we can check whether rows were returned
1994 -- and raise exception if not.
1995 IF NOT FOUND THEN
1996 RAISE EXCEPTION 'No flight at %.', $1;
1997 END IF;
1999 RETURN;
2000 END;
2001 $BODY$
2002 LANGUAGE plpgsql;
2004 -- Returns available flights or raises exception if there are no
2005 -- available flights.
2006 SELECT * FROM get_available_flightid(CURRENT_DATE);
2007 </programlisting>
2008 </para>
2010 <note>
2011 <para>
2014 before returning from the function, as discussed above. That
2016 very large result set, performance might be poor: data will be
2017 written to disk to avoid memory exhaustion, but the function
2018 itself will not return until the entire result set has been
2020 allow users to define set-returning functions
2021 that do not have this limitation. Currently, the point at
2022 which data begins being written to disk is controlled by the
2024 configuration variable. Administrators who have sufficient
2025 memory to store larger result sets in memory should consider
2026 increasing this parameter.
2027 </para>
2028 </note>
2029 </sect3>
2030 </sect2>
2035 <para>
2036 A procedure does not have a return value. A procedure can therefore end
2040 </para>
2042 <para>
2043 If the procedure has output parameters, the final values of the output
2044 parameter variables will be returned to the caller.
2045 </para>
2046 </sect2>
2051 <para>
2057 parameter of the procedure must
2059 whatever the procedure returns is assigned back to that variable after
2060 it returns. For example:
2061 <programlisting>
2062 CREATE PROCEDURE triple(INOUT x int)
2063 LANGUAGE plpgsql
2064 AS $$
2065 BEGIN
2066 x := x * 3;
2067 END;
2068 $$;
2070 DO $$
2071 DECLARE myvar int := 5;
2072 BEGIN
2073 CALL triple(myvar);
2074 RAISE NOTICE 'myvar = %', myvar; -- prints 15
2075 END;
2076 $$;
2077 </programlisting>
2078 The variable corresponding to an output parameter can be a simple
2079 variable or a field of a composite-type variable. Currently,
2080 it cannot be an element of an array.
2081 </para>
2082 </sect2>
2087 <para>
2089 alternative commands based on certain conditions.
2091 <itemizedlist>
2092 <listitem>
2094 </listitem>
2095 <listitem>
2097 </listitem>
2098 <listitem>
2100 </listitem>
2101 </itemizedlist>
2104 <itemizedlist>
2105 <listitem>
2107 </listitem>
2108 <listitem>
2110 </listitem>
2111 </itemizedlist>
2112 </para>
2117 <synopsis>
2120 END IF;
2121 </synopsis>
2123 <para>
2127 executed if the condition is true. Otherwise, they are
2128 skipped.
2129 </para>
2131 <para>
2132 Example:
2133 <programlisting>
2135 UPDATE users SET email = v_email WHERE user_id = v_user_id;
2136 END IF;
2137 </programlisting>
2138 </para>
2139 </sect3>
2144 <synopsis>
2147 ELSE
2149 END IF;
2150 </synopsis>
2152 <para>
2155 alternative set of statements that should be executed if the
2156 condition is not true. (Note this includes the case where the
2157 condition evaluates to NULL.)
2158 </para>
2160 <para>
2161 Examples:
2162 <programlisting>
2163 IF parentid IS NULL OR parentid = ''
2164 THEN
2165 RETURN fullname;
2166 ELSE
2167 RETURN hp_true_filename(parentid) || '/' || fullname;
2168 END IF;
2169 </programlisting>
2171 <programlisting>
2173 INSERT INTO users_count (count) VALUES (v_count);
2174 RETURN 't';
2175 ELSE
2176 RETURN 'f';
2177 END IF;
2178 </programlisting>
2179 </para>
2180 </sect3>
2185 <synopsis>
2192 ...
2193 </optional>
2194 </optional>
2195 <optional> ELSE
2197 END IF;
2198 </synopsis>
2200 <para>
2201 Sometimes there are more than just two alternatives.
2203 method of checking several alternatives in turn.
2205 until the first one that is true is found. Then the
2206 associated statement(s) are executed, after which control
2211 </para>
2213 <para>
2214 Here is an example:
2216 <programlisting>
2217 IF number = 0 THEN
2218 result := 'zero';
2220 result := 'positive';
2222 result := 'negative';
2223 ELSE
2224 -- hmm, the only other possibility is that number is null
2225 result := 'NULL';
2226 END IF;
2227 </programlisting>
2228 </para>
2230 <para>
2233 </para>
2235 <para>
2236 An alternative way of accomplishing the same task is to nest
2238 following example:
2240 <programlisting>
2241 IF demo_row.sex = 'm' THEN
2242 pretty_sex := 'man';
2243 ELSE
2244 IF demo_row.sex = 'f' THEN
2245 pretty_sex := 'woman';
2246 END IF;
2247 END IF;
2248 </programlisting>
2249 </para>
2251 <para>
2255 </para>
2256 </sect3>
2261 <synopsis>
2263 WHEN <replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> <optional> ... </optional></optional> THEN
2265 <optional> WHEN <replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> <optional> ... </optional></optional> THEN
2267 ... </optional>
2268 <optional> ELSE
2270 END CASE;
2271 </synopsis>
2273 <para>
2276 is evaluated (once) and successively compared to each
2278 If a match is found, then the corresponding
2285 </para>
2287 <para>
2288 Here is a simple example:
2290 <programlisting>
2291 CASE x
2293 msg := 'one or two';
2294 ELSE
2295 msg := 'other value than one or two';
2296 END CASE;
2297 </programlisting>
2298 </para>
2299 </sect3>
2304 <synopsis>
2305 CASE
2310 ... </optional>
2311 <optional> ELSE
2313 END CASE;
2314 </synopsis>
2316 <para>
2328 </para>
2330 <para>
2331 Here is an example:
2333 <programlisting>
2334 CASE
2336 msg := 'value is between zero and ten';
2338 msg := 'value is between eleven and twenty';
2339 END CASE;
2340 </programlisting>
2341 </para>
2343 <para>
2347 than doing nothing.
2348 </para>
2350 </sect3>
2351 </sect2>
2359 </indexterm>
2361 <para>
2366 </para>
2371 <synopsis>
2373 LOOP
2376 </synopsis>
2378 <para>
2384 specify which loop those statements refer to.
2385 </para>
2386 </sect3>
2391 <indexterm>
2394 </indexterm>
2396 <synopsis>
2397 EXIT <optional> <replaceable>label</replaceable> </optional> <optional> WHEN <replaceable>boolean-expression</replaceable> </optional>;
2398 </synopsis>
2400 <para>
2402 loop is terminated and the statement following <literal>END
2404 is given, it must be the label of the current or some outer
2405 level of nested loop or block. Then the named loop or block is
2406 terminated and control continues with the statement after the
2408 </para>
2410 <para>
2414 </para>
2416 <para>
2418 not limited to use with unconditional loops.
2419 </para>
2421 <para>
2422 When used with a
2424 control to the next statement after the end of the block.
2425 Note that a label must be used for this purpose; an unlabeled
2431 </para>
2433 <para>
2434 Examples:
2435 <programlisting>
2436 LOOP
2437 -- some computations
2439 EXIT; -- exit loop
2440 END IF;
2441 END LOOP;
2443 LOOP
2444 -- some computations
2446 END LOOP;
2449 BEGIN
2450 -- some computations
2452 EXIT ablock; -- causes exit from the BEGIN block
2453 END IF;
2455 END;
2456 </programlisting>
2457 </para>
2458 </sect3>
2463 <indexterm>
2466 </indexterm>
2468 <synopsis>
2469 CONTINUE <optional> <replaceable>label</replaceable> </optional> <optional> WHEN <replaceable>boolean-expression</replaceable> </optional>;
2470 </synopsis>
2472 <para>
2474 the innermost loop is begun. That is, all statements remaining
2475 in the loop body are skipped, and control returns
2476 to the loop control expression (if any) to determine whether
2477 another loop iteration is needed.
2479 specifies the label of the loop whose execution will be
2480 continued.
2481 </para>
2483 <para>
2486 true. Otherwise, control passes to the statement after
2488 </para>
2490 <para>
2492 is not limited to use with unconditional loops.
2493 </para>
2495 <para>
2496 Examples:
2497 <programlisting>
2498 LOOP
2499 -- some computations
2503 END LOOP;
2504 </programlisting>
2505 </para>
2506 </sect3>
2512 <indexterm>
2515 </indexterm>
2517 <synopsis>
2522 </synopsis>
2524 <para>
2526 sequence of statements so long as the
2528 evaluates to true. The expression is checked just before
2529 each entry to the loop body.
2530 </para>
2532 <para>
2533 For example:
2534 <programlisting>
2536 -- some computations here
2537 END LOOP;
2539 WHILE NOT done LOOP
2540 -- some computations here
2541 END LOOP;
2542 </programlisting>
2543 </para>
2544 </sect3>
2549 <synopsis>
2551 FOR <replaceable>name</replaceable> IN <optional> REVERSE </optional> <replaceable>expression</replaceable> .. <replaceable>expression</replaceable> <optional> BY <replaceable>expression</replaceable> </optional> LOOP
2554 </synopsis>
2556 <para>
2558 of integer values. The variable
2561 definition of the variable name is ignored within the loop).
2562 The two expressions giving
2563 the lower and upper bound of the range are evaluated once when entering
2566 clause, which again is evaluated once on loop entry.
2568 subtracted, rather than added, after each iteration.
2569 </para>
2571 <para>
2573 <programlisting>
2576 END LOOP;
2580 END LOOP;
2584 END LOOP;
2585 </programlisting>
2586 </para>
2588 <para>
2589 If the lower bound is greater than the upper bound (or less than,
2591 executed at all. No error is raised.
2592 </para>
2594 <para>
2597 referenced with a qualified name, using that
2599 </para>
2600 </sect3>
2601 </sect2>
2606 <para>
2608 the results of a query and manipulate that data
2609 accordingly. The syntax is:
2610 <synopsis>
2615 </synopsis>
2617 or comma-separated list of scalar variables.
2620 executed for each row. Here is an example:
2621 <programlisting>
2622 CREATE FUNCTION refresh_mviews() RETURNS integer AS $$
2623 DECLARE
2624 mviews RECORD;
2625 BEGIN
2626 RAISE NOTICE 'Refreshing all materialized views...';
2628 FOR mviews IN
2629 SELECT n.nspname AS mv_schema,
2630 c.relname AS mv_name,
2631 pg_catalog.pg_get_userbyid(c.relowner) AS owner
2632 FROM pg_catalog.pg_class c
2633 LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
2634 WHERE c.relkind = 'm'
2635 ORDER BY 1
2636 LOOP
2638 -- Now "mviews" has one record with information about the materialized view
2640 RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
2641 quote_ident(mviews.mv_schema),
2642 quote_ident(mviews.mv_name),
2643 quote_ident(mviews.owner);
2644 EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
2645 END LOOP;
2647 RAISE NOTICE 'Done refreshing materialized views.';
2648 RETURN 1;
2649 END;
2650 $$ LANGUAGE plpgsql;
2651 </programlisting>
2654 assigned row value is still accessible after the loop.
2655 </para>
2657 <para>
2659 statement can be any SQL command that returns rows to the caller:
2665 </para>
2667 <para>
2669 and the query plan is cached for possible re-use, as discussed in
2672 </para>
2674 <para>
2676 rows:
2677 <synopsis>
2679 FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ... </optional> </optional> LOOP
2682 </synopsis>
2683 This is like the previous form, except that the source query
2684 is specified as a string expression, which is evaluated and replanned
2686 choose the speed of a preplanned query or the flexibility of a dynamic
2690 </para>
2692 <para>
2693 Another way to specify the query whose results should be iterated
2694 through is to declare it as a cursor. This is described in
2696 </para>
2697 </sect2>
2702 <para>
2704 but instead of iterating through the rows returned by an SQL query,
2705 it iterates through the elements of an array value.
2707 components of a composite-valued expression; variants for looping
2708 through composites besides arrays may be added in future.)
2711 <synopsis>
2713 FOREACH <replaceable>target</replaceable> <optional> SLICE <replaceable>number</replaceable> </optional> IN ARRAY <replaceable>expression</replaceable> LOOP
2716 </synopsis>
2717 </para>
2719 <para>
2721 the loop iterates through individual elements of the array produced
2724 element value in sequence, and the loop body is executed for each element.
2725 Here is an example of looping through the elements of an integer
2726 array:
2728 <programlisting>
2729 CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
2730 DECLARE
2731 s int8 := 0;
2732 x int;
2733 BEGIN
2734 FOREACH x IN ARRAY $1
2735 LOOP
2736 s := s + x;
2737 END LOOP;
2738 RETURN s;
2739 END;
2740 $$ LANGUAGE plpgsql;
2741 </programlisting>
2743 The elements are visited in storage order, regardless of the number of
2745 usually just a single variable, it can be a list of variables when
2746 looping through an array of composite values (records). In that case,
2747 for each array element, the variables are assigned from successive
2748 columns of the composite value.
2749 </para>
2751 <para>
2753 iterates through slices of the array rather than single elements.
2755 than the number of dimensions of the array. The
2757 and it receives successive slices of the array value, where each slice
2759 Here is an example of iterating through one-dimensional slices:
2761 <programlisting>
2762 CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
2763 DECLARE
2764 x int[];
2765 BEGIN
2767 LOOP
2768 RAISE NOTICE 'row = %', x;
2769 END LOOP;
2770 END;
2771 $$ LANGUAGE plpgsql;
2779 </programlisting>
2780 </para>
2781 </sect2>
2786 <indexterm>
2789 </indexterm>
2791 <para>
2793 function aborts execution of the function and the
2794 surrounding transaction. You can trap errors and recover
2799 <synopsis>
2801 <optional> DECLARE
2803 BEGIN
2805 EXCEPTION
2806 WHEN <replaceable>condition</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
2808 <optional> WHEN <replaceable>condition</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
2810 ... </optional>
2811 END;
2812 </synopsis>
2813 </para>
2815 <para>
2816 If no error occurs, this form of block simply executes all the
2823 matching the error that occurred. If a match is found, the
2825 executed, and then control passes to the next statement after
2828 the error can be caught by an enclosing block with
2830 of the function.
2831 </para>
2833 <para>
2836 name matches any error within its category. The special
2839 (It is possible, but often unwise, to trap those two error types
2840 by name.) Condition names are
2841 not case-sensitive. Also, an error condition can be specified
2843 <programlisting>
2844 WHEN division_by_zero THEN ...
2845 WHEN SQLSTATE '22012' THEN ...
2846 </programlisting>
2847 </para>
2849 <para>
2850 If a new error occurs within the selected
2854 </para>
2856 <para>
2859 remain as they were when the error occurred, but all changes
2860 to persistent database state within the block are rolled back.
2861 As an example, consider this fragment:
2863 <programlisting>
2864 INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
2865 BEGIN
2866 UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
2867 x := x + 1;
2868 y := x / 0;
2869 EXCEPTION
2870 WHEN division_by_zero THEN
2871 RAISE NOTICE 'caught division_by_zero';
2872 RETURN x;
2873 END;
2874 </programlisting>
2882 block is not rolled back, however, so the end result is that the database
2884 </para>
2886 <tip>
2887 <para>
2889 more expensive to enter and exit than a block without one. Therefore,
2891 </para>
2892 </tip>
2896 <para>
2898 This example uses exception handling to perform either
2902 this pattern. This example serves primarily to illustrate use of
2905 <programlisting>
2906 CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
2908 CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
2909 $$
2910 BEGIN
2911 LOOP
2912 -- first try to update the key
2913 UPDATE db SET b = data WHERE a = key;
2914 IF found THEN
2915 RETURN;
2916 END IF;
2917 -- not there, so try to insert the key
2918 -- if someone else inserts the same key concurrently,
2919 -- we could get a unique-key failure
2920 BEGIN
2921 INSERT INTO db(a,b) VALUES (key, data);
2922 RETURN;
2923 EXCEPTION WHEN unique_violation THEN
2924 -- Do nothing, and loop to try the UPDATE again.
2925 END;
2926 END LOOP;
2927 END;
2928 $$
2929 LANGUAGE plpgsql;
2931 SELECT merge_db(1, 'david');
2932 SELECT merge_db(1, 'dennis');
2933 </programlisting>
2937 trigger function on the table. It might also misbehave if there is
2938 more than one unique index on the table, since it will retry the
2939 operation regardless of which index caused the error.
2940 More safety could be had by using the
2941 features discussed next to check that the trapped error was the one
2942 expected.
2943 </para>
2944 </example>
2949 <para>
2950 Exception handlers frequently need to identify the specific error that
2951 occurred. There are two ways to get information about the current
2954 </para>
2956 <para>
2957 Within an exception handler, the special variable
2960 for a list of possible error codes). The special variable
2962 exception. These variables are undefined outside exception handlers.
2963 </para>
2965 <para>
2966 Within an exception handler, one may also retrieve
2967 information about the current exception by using the
2970 <synopsis>
2971 GET STACKED DIAGNOSTICS <replaceable>variable</replaceable> { = | := } <replaceable>item</replaceable> <optional> , ... </optional>;
2972 </synopsis>
2976 (which should be of the right data type to receive it). The currently
2977 available status items are shown
2979 </para>
2987 <thead>
2988 <row>
2992 </row>
2993 </thead>
2994 <tbody>
2995 <row>
2999 </row>
3000 <row>
3004 </row>
3005 <row>
3009 </row>
3010 <row>
3014 </row>
3015 <row>
3019 </row>
3020 <row>
3024 </row>
3025 <row>
3029 </row>
3030 <row>
3034 </row>
3035 <row>
3039 </row>
3040 <row>
3043 <entry>line(s) of text describing the call stack at the time of the
3045 </row>
3046 </tbody>
3047 </tgroup>
3048 </table>
3050 <para>
3051 If the exception did not set a value for an item, an empty string
3052 will be returned.
3053 </para>
3055 <para>
3056 Here is an example:
3057 <programlisting>
3058 DECLARE
3059 text_var1 text;
3060 text_var2 text;
3061 text_var3 text;
3062 BEGIN
3063 -- some processing which might cause an exception
3064 ...
3065 EXCEPTION WHEN OTHERS THEN
3066 GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
3067 text_var2 = PG_EXCEPTION_DETAIL,
3068 text_var3 = PG_EXCEPTION_HINT;
3069 END;
3070 </programlisting>
3071 </para>
3072 </sect3>
3073 </sect2>
3078 <para>
3081 about current execution state (whereas the <command>GET STACKED
3082 DIAGNOSTICS</command> command discussed above reports information about
3084 status item is useful for identifying the current execution
3086 of text describing the call stack. The first line refers to the current
3088 command. The second and any subsequent lines refer to calling functions
3089 further up the call stack. For example:
3091 <programlisting>
3092 CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
3093 BEGIN
3094 RETURN inner_func();
3095 END;
3096 $$ LANGUAGE plpgsql;
3098 CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
3099 DECLARE
3100 stack text;
3101 BEGIN
3102 GET DIAGNOSTICS stack = PG_CONTEXT;
3103 RAISE NOTICE E'--- Call Stack ---\n%', stack;
3104 RETURN 1;
3105 END;
3106 $$ LANGUAGE plpgsql;
3108 SELECT outer_func();
3110 NOTICE: --- Call Stack ---
3111 PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS
3112 PL/pgSQL function outer_func() line 3 at RETURN
3113 CONTEXT: PL/pgSQL function outer_func() line 3 at RETURN
3114 outer_func
3115 ------------
3116 1
3117 (1 row)
3118 </programlisting>
3120 </para>
3122 <para>
3124 returns the same sort of stack trace, but describing the location
3125 at which an error was detected, rather than the current location.
3126 </para>
3127 </sect2>
3128 </sect1>
3136 </indexterm>
3138 <para>
3139 Rather than executing a whole query at once, it is possible to set
3141 the query result a few rows at a time. One reason for doing this is
3142 to avoid memory overrun when the result contains a large number of
3145 internally to avoid memory problems.) A more interesting usage is to
3146 return a reference to a cursor that a function has created, allowing the
3147 caller to read the rows. This provides an efficient way to return
3148 large row sets from functions.
3149 </para>
3154 <para>
3156 cursor variables, which are always of the special data type
3159 Another way is to use the cursor declaration syntax,
3160 which in general is:
3161 <synopsis>
3162 <replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>;
3163 </synopsis>
3168 fetches will be rejected; if neither specification appears, it is
3169 query-dependent whether backward fetches will be allowed.
3173 replaced by parameter values in the given query. The actual
3174 values to substitute for these names will be specified later,
3175 when the cursor is opened.
3176 </para>
3177 <para>
3178 Some examples:
3179 <programlisting>
3180 DECLARE
3181 curs1 refcursor;
3182 curs2 CURSOR FOR SELECT * FROM tenk1;
3183 curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;
3184 </programlisting>
3186 but the first can be used with any query, while the second has
3189 replaced by an integer parameter value when the cursor is opened.)
3192 any particular query.
3193 </para>
3195 <para>
3200 assumes that re-reading the query's output will give consistent
3201 results, which a volatile function might not do.
3202 </para>
3203 </sect2>
3208 <para>
3209 Before a cursor can be used to retrieve rows, it must be
3212 CURSOR</command></link>.)
3215 cursor variables while the third uses a bound cursor variable.
3216 </para>
3218 <note>
3219 <para>
3220 Bound cursor variables can also be used without explicitly opening the cursor,
3224 close it again when the loop completes.
3225 </para>
3226 </note>
3228 <indexterm>
3231 </indexterm>
3233 <para>
3234 Opening a cursor involves creating a server-internal data structure
3236 state for the cursor's query. A portal has a name, which must be
3237 unique within the session for the duration of the portal's existence.
3239 name to each portal it creates. However, if you assign a non-null
3240 string value to a cursor variable, that string will be used as its
3241 portal name. This feature can be used as described in
3243 </para>
3248 <synopsis>
3249 OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR <replaceable>query</replaceable>;
3250 </synopsis>
3252 <para>
3253 The cursor variable is opened and given the specified query to
3254 execute. The cursor cannot be open already, and it must have been
3255 declared as an unbound cursor variable (that is, as a simple
3259 is treated in the same way as other SQL commands in
3261 variable names are substituted, and the query plan is cached for
3263 variable is substituted into the cursor query, the value that is
3265 subsequent changes to the variable will not affect the cursor's
3266 behavior.
3268 options have the same meanings as for a bound cursor.
3269 </para>
3271 <para>
3272 An example:
3273 <programlisting>
3274 OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
3275 </programlisting>
3276 </para>
3277 </sect3>
3282 <synopsis>
3283 OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR EXECUTE <replaceable class="command">query_string</replaceable>
3284 <optional> USING <replaceable>expression</replaceable> <optional>, ... </optional> </optional>;
3285 </synopsis>
3287 <para>
3288 The cursor variable is opened and given the specified query to
3289 execute. The cursor cannot be open already, and it must have been
3290 declared as an unbound cursor variable (that is, as a simple
3293 command. As usual, this gives flexibility so the query plan can vary
3295 and it also means that variable substitution is not done on the
3297 can be inserted into the dynamic command via
3301 cursor.
3302 </para>
3304 <para>
3305 An example:
3306 <programlisting>
3307 OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;
3308 </programlisting>
3309 In this example, the table name is inserted into the query via
3312 no quoting.
3313 </para>
3314 </sect3>
3319 <synopsis>
3320 OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> := </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional>;
3321 </synopsis>
3323 <para>
3325 variable whose query was bound to it when it was declared. The
3326 cursor cannot be open already. A list of actual argument value
3327 expressions must appear if and only if the cursor was declared to
3328 take arguments. These values will be substituted in the query.
3329 </para>
3331 <para>
3332 The query plan for a bound cursor is always considered cacheable;
3336 behavior was already determined.
3337 </para>
3339 <para>
3342 notation, all arguments are specified in order. In named notation,
3344 separate it from the argument expression. Similar to calling
3346 is also allowed to mix positional and named notation.
3347 </para>
3349 <para>
3350 Examples (these use the cursor declaration examples above):
3351 <programlisting>
3352 OPEN curs2;
3353 OPEN curs3(42);
3354 OPEN curs3(key := 42);
3355 </programlisting>
3356 </para>
3358 <para>
3359 Because variable substitution is done on a bound cursor's query,
3360 there are really two ways to pass values into the cursor: either
3363 However, only variables declared before the bound cursor was
3364 declared will be substituted into it. In either case the value to
3366 For example, another way to get the same effect as the
3368 <programlisting>
3369 DECLARE
3370 key integer;
3371 curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
3372 BEGIN
3373 key := 42;
3374 OPEN curs4;
3375 </programlisting>
3376 </para>
3377 </sect3>
3378 </sect2>
3383 <para>
3384 Once a cursor has been opened, it can be manipulated with the
3385 statements described here.
3386 </para>
3388 <para>
3389 These manipulations need not occur in the same function that
3391 value out of a function and let the caller operate on the cursor.
3393 of the portal containing the active query for the cursor. This name
3395 and so on, without disturbing the portal.)
3396 </para>
3398 <para>
3399 All portals are implicitly closed at transaction end. Therefore
3401 only until the end of the transaction.
3402 </para>
3407 <synopsis>
3408 FETCH <optional> <replaceable>direction</replaceable> { FROM | IN } </optional> <replaceable>cursor</replaceable> INTO <replaceable>target</replaceable>;
3409 </synopsis>
3411 <para>
3413 direction) from the
3414 cursor into a target, which might be a row variable, a record
3415 variable, or a comma-separated list of simple variables, just like
3417 target is set to NULL(s). As with <command>SELECT
3419 be checked to see whether a row was obtained or not. If no row is
3420 obtained, the cursor is positioned after the last row or before the
3421 first row, depending on the movement direction.
3422 </para>
3424 <para>
3427 command except the ones that can fetch
3428 more than one row; namely, it can be
3442 which only allows an integer constant).
3444 backward are likely to fail unless the cursor was declared or opened
3446 </para>
3448 <para>
3450 variable that references an open cursor portal.
3451 </para>
3453 <para>
3454 Examples:
3455 <programlisting>
3456 FETCH curs1 INTO rowvar;
3457 FETCH curs2 INTO foo, bar, baz;
3458 FETCH LAST FROM curs3 INTO x, y;
3459 FETCH RELATIVE -2 FROM curs4 INTO x;
3460 </programlisting>
3461 </para>
3462 </sect3>
3467 <synopsis>
3468 MOVE <optional> <replaceable>direction</replaceable> { FROM | IN } </optional> <replaceable>cursor</replaceable>;
3469 </synopsis>
3471 <para>
3475 cursor and does not return the row moved to.
3478 command, including those that can fetch more than one row;
3479 the cursor is positioned to the last such row.
3483 That syntax is ambiguous with the case where
3485 altogether, and hence it may fail if
3487 As with <command>SELECT
3489 be checked to see whether there was a row to move to. If there is no
3490 such row, the cursor is positioned after the last row or before the
3491 first row, depending on the movement direction.
3492 </para>
3494 <para>
3495 Examples:
3496 <programlisting>
3497 MOVE curs1;
3498 MOVE LAST FROM curs3;
3499 MOVE RELATIVE -2 FROM curs4;
3500 MOVE FORWARD 2 FROM curs4;
3501 </programlisting>
3502 </para>
3503 </sect3>
3508 <synopsis>
3509 UPDATE <replaceable>table</replaceable> SET ... WHERE CURRENT OF <replaceable>cursor</replaceable>;
3510 DELETE FROM <replaceable>table</replaceable> WHERE CURRENT OF <replaceable>cursor</replaceable>;
3511 </synopsis>
3513 <para>
3514 When a cursor is positioned on a table row, that row can be updated
3515 or deleted using the cursor to identify the row. There are
3516 restrictions on what the cursor's query can be (in particular,
3518 cursor. For more information see the
3520 reference page.
3521 </para>
3523 <para>
3524 An example:
3525 <programlisting>
3526 UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
3527 </programlisting>
3528 </para>
3529 </sect3>
3534 <synopsis>
3536 </synopsis>
3538 <para>
3540 cursor. This can be used to release resources earlier than end of
3541 transaction, or to free up the cursor variable to be opened again.
3542 </para>
3544 <para>
3545 An example:
3546 <programlisting>
3547 CLOSE curs1;
3548 </programlisting>
3549 </para>
3550 </sect3>
3555 <para>
3557 caller. This is useful to return multiple rows or columns,
3558 especially with very large result sets. To do this, the function
3559 opens the cursor and returns the cursor name to the caller (or simply
3560 opens the cursor using a portal name specified by or otherwise known
3561 to the caller). The caller can then fetch rows from the cursor. The
3562 cursor can be closed by the caller, or it will be closed automatically
3563 when the transaction closes.
3564 </para>
3566 <para>
3567 The portal name used for a cursor can be specified by the
3568 programmer or automatically generated. To specify a portal name,
3573 (as it will be by default), then
3575 conflict with any existing portal, and assigns it to the
3577 </para>
3579 <note>
3580 <para>
3582 variables were initialized to contain their own names, rather
3583 than being left as null, so that the underlying portal name would
3584 be the same as the cursor variable's name by default. This was
3585 changed because it created too much risk of conflicts between
3586 similarly-named cursors in different functions.
3587 </para>
3588 </note>
3590 <para>
3591 The following example shows one way a cursor name can be supplied by
3592 the caller:
3594 <programlisting>
3595 CREATE TABLE test (col text);
3596 INSERT INTO test VALUES ('123');
3598 CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
3599 BEGIN
3600 OPEN $1 FOR SELECT col FROM test;
3601 RETURN $1;
3602 END;
3603 ' LANGUAGE plpgsql;
3605 BEGIN;
3606 SELECT reffunc('funccursor');
3607 FETCH ALL IN funccursor;
3608 COMMIT;
3609 </programlisting>
3610 </para>
3612 <para>
3613 The following example uses automatic cursor name generation:
3615 <programlisting>
3616 CREATE FUNCTION reffunc2() RETURNS refcursor AS '
3617 DECLARE
3618 ref refcursor;
3619 BEGIN
3620 OPEN ref FOR SELECT col FROM test;
3621 RETURN ref;
3622 END;
3623 ' LANGUAGE plpgsql;
3625 -- need to be in a transaction to use cursors.
3626 BEGIN;
3627 SELECT reffunc2();
3629 reffunc2
3630 --------------------
3632 (1 row)
3634 FETCH ALL IN "<unnamed cursor 1>";
3635 COMMIT;
3636 </programlisting>
3637 </para>
3639 <para>
3640 The following example shows one way to return multiple cursors
3641 from a single function:
3643 <programlisting>
3644 CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
3645 BEGIN
3646 OPEN $1 FOR SELECT * FROM table_1;
3647 RETURN NEXT $1;
3648 OPEN $2 FOR SELECT * FROM table_2;
3649 RETURN NEXT $2;
3650 END;
3651 $$ LANGUAGE plpgsql;
3653 -- need to be in a transaction to use cursors.
3654 BEGIN;
3656 SELECT * FROM myfunc('a', 'b');
3658 FETCH ALL FROM a;
3659 FETCH ALL FROM b;
3660 COMMIT;
3661 </programlisting>
3662 </para>
3663 </sect3>
3664 </sect2>
3669 <para>
3671 iterating through the rows returned by a cursor. The syntax is:
3673 <synopsis>
3675 FOR <replaceable>recordvar</replaceable> IN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> := </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional> LOOP
3678 </synopsis>
3680 The cursor variable must have been bound to some query when it was
3683 the cursor again when the loop exits. A list of actual argument value
3684 expressions must appear if and only if the cursor was declared to take
3685 arguments. These values will be substituted in the query, in just
3688 </para>
3690 <para>
3693 existing definition of the variable name is ignored within the loop).
3694 Each row returned by the cursor is successively assigned to this
3695 record variable and the loop body is executed.
3696 </para>
3697 </sect2>
3699 </sect1>
3704 <para>
3707 it is possible to end transactions using the
3709 transaction is started automatically after a transaction is ended using
3710 these commands, so there is no separate <command>START
3713 </para>
3715 <para>
3716 Here is a simple example:
3717 <programlisting>
3718 CREATE PROCEDURE transaction_test1()
3719 LANGUAGE plpgsql
3720 AS $$
3721 BEGIN
3723 INSERT INTO test1 (a) VALUES (i);
3725 COMMIT;
3726 ELSE
3727 ROLLBACK;
3728 END IF;
3729 END LOOP;
3730 END;
3731 $$;
3733 CALL transaction_test1();
3734 </programlisting>
3735 </para>
3740 </indexterm>
3743 A new transaction starts out with default transaction characteristics such
3744 as transaction isolation level. In cases where transactions are committed
3745 in a loop, it might be desirable to start new transactions automatically
3746 with the same characteristics as the previous one. The commands
3748 CHAIN</command> accomplish this.
3749 </para>
3751 <para>
3755 other intervening command. For example, if the call stack is
3758 procedures can perform transaction control actions. But if the call stack
3761 procedure cannot do transaction control, because of the
3763 </para>
3765 <para>
3769 Typical usage patterns for savepoints can be replaced by blocks with
3771 Under the hood, a block with exception handlers forms a
3772 subtransaction, which means that transactions cannot be ended inside
3773 such a block.
3774 </para>
3776 <para>
3777 Special considerations apply to cursor loops. Consider this example:
3778 <programlisting>
3779 CREATE PROCEDURE transaction_test2()
3780 LANGUAGE plpgsql
3781 AS $$
3782 DECLARE
3783 r RECORD;
3784 BEGIN
3785 FOR r IN SELECT * FROM test2 ORDER BY x LOOP
3786 INSERT INTO test1 (a) VALUES (r.x);
3787 COMMIT;
3788 END LOOP;
3789 END;
3790 $$;
3792 CALL transaction_test2();
3793 </programlisting>
3794 Normally, cursors are automatically closed at transaction commit.
3795 However, a cursor created as part of a loop like this is automatically
3800 removed automatically after the loop, so this is mostly invisible to the
3801 user. But one must keep in mind that any table or row locks taken by
3802 the cursor's query will no longer be held after the
3805 </para>
3807 <para>
3808 Transaction commands are not allowed in cursor loops driven by commands
3809 that are not read-only (for example <command>UPDATE
3810 ... RETURNING</command>).
3811 </para>
3812 </sect1>
3820 <indexterm>
3823 </indexterm>
3825 <indexterm>
3828 </indexterm>
3830 <para>
3832 raise errors.
3834 <synopsis>
3835 RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ... </optional></optional> <optional> USING <replaceable class="parameter">option</replaceable> { = | := } <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;
3836 RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> <replaceable class="parameter">condition_name</replaceable> <optional> USING <replaceable class="parameter">option</replaceable> { = | := } <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;
3837 RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> SQLSTATE '<replaceable class="parameter">sqlstate</replaceable>' <optional> USING <replaceable class="parameter">option</replaceable> { = | := } <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;
3838 RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> USING <replaceable class="parameter">option</replaceable> { = | := } <replaceable class="parameter">expression</replaceable> <optional>, ... </optional>;
3839 RAISE ;
3840 </synopsis>
3847 being the default.
3849 current transaction); the other levels only generate messages of different
3850 priority levels.
3851 Whether messages of a particular priority are reported to the client,
3852 written to the server log, or both is controlled by the
3856 information.
3857 </para>
3859 <para>
3860 In the first syntax variant,
3863 (which must be a simple string literal, not an expression). The
3864 format string specifies the error message text to be reported.
3865 The format string can be followed
3866 by optional argument expressions to be inserted into the message.
3868 string representation of the next optional argument's value. Write
3871 placeholders in the format string, or an error is raised during
3872 the compilation of the function.
3873 </para>
3875 <para>
3878 <programlisting>
3879 RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
3880 </programlisting>
3881 </para>
3883 <para>
3884 In the second and third syntax variants,
3887 error condition name or a five-character SQLSTATE code, respectively.
3889 names and the predefined SQLSTATE codes.
3890 </para>
3892 <para>
3893 Here are examples
3896 <programlisting>
3897 RAISE division_by_zero;
3898 RAISE WARNING SQLSTATE '22012';
3899 </programlisting>
3900 </para>
3902 <para>
3903 In any of these syntax variants,
3904 you can attach additional information to the error report by writing
3909 string-valued expression. The allowed <replaceable
3915 <listitem>
3916 <para>Sets the error message text. This option can't be used in the
3917 first syntax variant, since the message is already supplied.</para>
3918 </listitem>
3919 </varlistentry>
3923 <listitem>
3925 </listitem>
3926 </varlistentry>
3930 <listitem>
3932 </listitem>
3933 </varlistentry>
3937 <listitem>
3938 <para>Specifies the error code (SQLSTATE) to report, either by condition
3940 five-character SQLSTATE code. This option can't be used in the
3941 second or third syntax variant, since the error code is already
3942 supplied.</para>
3943 </listitem>
3944 </varlistentry>
3952 <listitem>
3954 </listitem>
3955 </varlistentry>
3956 </variablelist>
3957 </para>
3959 <para>
3960 This example will abort the transaction with the given error message
3961 and hint:
3962 <programlisting>
3963 RAISE EXCEPTION 'Nonexistent ID --> %', user_id
3964 USING HINT = 'Please check your user ID';
3965 </programlisting>
3966 </para>
3968 <para>
3969 These two examples show equivalent ways of setting the SQLSTATE:
3970 <programlisting>
3971 RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
3972 RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';
3973 </programlisting>
3974 Another way to produce the same result is:
3975 <programlisting>
3976 RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
3977 </programlisting>
3978 </para>
3980 <para>
3981 As shown in the fourth syntax variant, it is also possible to
3985 </para>
3987 <para>
3991 it causes the error currently being handled to be re-thrown.
3992 </para>
3994 <note>
3995 <para>
3997 parameters was interpreted as re-throwing the error from the block
3999 clause nested within that handler could not catch it, even if the
4001 block. This was deemed surprising as well as being incompatible with
4002 Oracle's PL/SQL.
4003 </para>
4004 </note>
4006 <para>
4007 If no condition name nor SQLSTATE is specified in a
4010 If no message text is specified, the default is to use the condition
4011 name or SQLSTATE as message text.
4012 </para>
4014 <note>
4015 <para>
4016 When specifying an error code by SQLSTATE code, you are not
4017 limited to the predefined error codes, but can select any
4018 error code consisting of five digits and/or upper-case ASCII
4020 you avoid throwing error codes that end in three zeroes, because
4021 these are category codes and can only be trapped by trapping
4022 the whole category.
4023 </para>
4024 </note>
4026 </sect2>
4031 <indexterm>
4034 </indexterm>
4036 <indexterm>
4039 </indexterm>
4041 <indexterm>
4043 </indexterm>
4045 <para>
4048 functions.
4050 <synopsis>
4051 ASSERT <replaceable class="parameter">condition</replaceable> <optional> , <replaceable class="parameter">message</replaceable> </optional>;
4052 </synopsis>
4055 expression that is expected to always evaluate to true; if it does,
4058 is raised. (If an error occurs while evaluating
4060 reported as a normal error.)
4061 </para>
4063 <para>
4065 provided, it is an expression whose result (if not null) replaces the
4069 not evaluated in the normal case where the assertion succeeds.
4070 </para>
4072 <para>
4073 Testing of assertions can be enabled or disabled via the configuration
4077 </para>
4079 <para>
4081 bugs, not for reporting ordinary error conditions. Use
4083 </para>
4085 </sect2>
4087 </sect1>
4095 </indexterm>
4097 <para>
4099 functions on data changes or database events.
4101 command, declaring it as a function with no arguments and a return type of
4105 automatically defined to describe the condition that triggered the call.
4106 </para>
4111 <para>
4114 Note that the function must be declared with no arguments even if it
4117 below.
4118 </para>
4120 <para>
4122 trigger, several special variables are created automatically in the
4123 top-level block. They are:
4125 <variablelist>
4128 <listitem>
4129 <para>
4130 new database row for <command>INSERT</command>/<command>UPDATE</command> operations in row-level
4131 triggers. This variable is null in statement-level triggers
4133 </para>
4134 </listitem>
4135 </varlistentry>
4139 <listitem>
4140 <para>
4141 old database row for <command>UPDATE</command>/<command>DELETE</command> operations in row-level
4142 triggers. This variable is null in statement-level triggers
4144 </para>
4145 </listitem>
4146 </varlistentry>
4150 <listitem>
4151 <para>
4152 name of the trigger which fired.
4153 </para>
4154 </listitem>
4155 </varlistentry>
4159 <listitem>
4160 <para>
4163 </para>
4164 </listitem>
4165 </varlistentry>
4169 <listitem>
4170 <para>
4172 depending on the trigger's definition.
4173 </para>
4174 </listitem>
4175 </varlistentry>
4179 <listitem>
4180 <para>
4181 operation for which the trigger was fired:
4184 </para>
4185 </listitem>
4186 </varlistentry>
4189 <term><varname>TG_RELID</varname> <type>oid</type> (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)</term>
4190 <listitem>
4191 <para>
4192 object ID of the table that caused the trigger invocation.
4193 </para>
4194 </listitem>
4195 </varlistentry>
4199 <listitem>
4200 <para>
4201 table that caused the trigger
4202 invocation. This is now deprecated, and could disappear in a future
4204 </para>
4205 </listitem>
4206 </varlistentry>
4210 <listitem>
4211 <para>
4212 table that caused the trigger invocation.
4213 </para>
4214 </listitem>
4215 </varlistentry>
4219 <listitem>
4220 <para>
4221 schema of the table that caused the trigger invocation.
4222 </para>
4223 </listitem>
4224 </varlistentry>
4228 <listitem>
4229 <para>
4230 number of arguments given to the trigger
4232 </para>
4233 </listitem>
4234 </varlistentry>
4238 <listitem>
4239 <para>
4240 arguments from
4242 The index counts from 0. Invalid
4244 result in a null value.
4245 </para>
4246 </listitem>
4247 </varlistentry>
4248 </variablelist>
4249 </para>
4251 <para>
4253 record/row value having exactly the structure of the table the
4254 trigger was fired for.
4255 </para>
4257 <para>
4259 trigger manager to skip the rest of the operation for this row
4260 (i.e., subsequent triggers are not fired, and the
4262 for this row). If a nonnull
4263 value is returned then the operation proceeds with that row value.
4264 Returning a row value different from the original value
4266 updated. Thus, if the trigger function wants the triggering
4267 action to succeed normally without altering the row
4269 returned. To alter the row to be stored, it is possible to
4272 return. In the case of a before-trigger
4274 effect, but it has to be nonnull to allow the trigger action to
4279 </para>
4281 <para>
4283 and may only be used on views) can return null to signal that they did
4284 not perform any updates, and that the rest of the operation for this
4285 row should be skipped (i.e., subsequent triggers are not fired, and the
4286 row is not counted in the rows-affected status for the surrounding
4288 Otherwise a nonnull value should be returned, to signal
4289 that the trigger performed the requested operation. For
4293 (this will also affect the row value passed to any subsequent triggers,
4298 </para>
4300 <para>
4301 The return value of a row-level trigger
4304 always ignored; it might as well be null. However, any of these types of
4305 triggers might still abort the entire operation by raising an error.
4306 </para>
4308 <para>
4311 </para>
4316 <para>
4317 This example trigger ensures that any time a row is inserted or updated
4318 in the table, the current user name and time are stamped into the
4319 row. And it checks that an employee's name is given and that the
4320 salary is a positive value.
4321 </para>
4323 <programlisting>
4324 CREATE TABLE emp (
4325 empname text,
4326 salary integer,
4327 last_date timestamp,
4328 last_user text
4329 );
4331 CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
4332 BEGIN
4333 -- Check that empname and salary are given
4334 IF NEW.empname IS NULL THEN
4335 RAISE EXCEPTION 'empname cannot be null';
4336 END IF;
4337 IF NEW.salary IS NULL THEN
4338 RAISE EXCEPTION '% cannot have null salary', NEW.empname;
4339 END IF;
4341 -- Who works for us when they must pay for it?
4343 RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
4344 END IF;
4346 -- Remember who changed the payroll when
4347 NEW.last_date := current_timestamp;
4348 NEW.last_user := current_user;
4349 RETURN NEW;
4350 END;
4351 $emp_stamp$ LANGUAGE plpgsql;
4353 CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
4354 FOR EACH ROW EXECUTE FUNCTION emp_stamp();
4355 </programlisting>
4356 </example>
4358 <para>
4359 Another way to log changes to a table involves creating a new table that
4360 holds a row for each insert, update, or delete that occurs. This approach
4361 can be thought of as auditing changes to a table.
4364 </para>
4369 <para>
4370 This example trigger ensures that any insert, update or delete of a row
4371 in the <literal>emp</literal> table is recorded (i.e., audited) in the <literal>emp_audit</literal> table.
4372 The current time and user name are stamped into the row, together with
4373 the type of operation performed on it.
4374 </para>
4376 <programlisting>
4377 CREATE TABLE emp (
4378 empname text NOT NULL,
4379 salary integer
4380 );
4382 CREATE TABLE emp_audit(
4383 operation char(1) NOT NULL,
4384 stamp timestamp NOT NULL,
4385 userid text NOT NULL,
4386 empname text NOT NULL,
4387 salary integer
4388 );
4390 CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
4391 BEGIN
4392 --
4393 -- Create a row in emp_audit to reflect the operation performed on emp,
4394 -- making use of the special variable TG_OP to work out the operation.
4395 --
4396 IF (TG_OP = 'DELETE') THEN
4397 INSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*;
4398 ELSIF (TG_OP = 'UPDATE') THEN
4399 INSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*;
4400 ELSIF (TG_OP = 'INSERT') THEN
4401 INSERT INTO emp_audit SELECT 'I', now(), current_user, NEW.*;
4402 END IF;
4403 RETURN NULL; -- result is ignored since this is an AFTER trigger
4404 END;
4405 $emp_audit$ LANGUAGE plpgsql;
4407 CREATE TRIGGER emp_audit
4408 AFTER INSERT OR UPDATE OR DELETE ON emp
4409 FOR EACH ROW EXECUTE FUNCTION process_emp_audit();
4410 </programlisting>
4411 </example>
4413 <para>
4414 A variation of the previous example uses a view joining the main table
4415 to the audit table, to show when each entry was last modified. This
4416 approach still records the full audit trail of changes to the table,
4417 but also presents a simplified view of the audit trail, showing just
4418 the last modified timestamp derived from the audit trail for each entry.
4421 </para>
4426 <para>
4427 This example uses a trigger on the view to make it updatable, and
4428 ensure that any insert, update or delete of a row in the view is
4430 and user name are recorded, together with the type of operation
4431 performed, and the view displays the last modified time of each row.
4432 </para>
4434 <programlisting>
4435 CREATE TABLE emp (
4436 empname text PRIMARY KEY,
4437 salary integer
4438 );
4440 CREATE TABLE emp_audit(
4441 operation char(1) NOT NULL,
4442 userid text NOT NULL,
4443 empname text NOT NULL,
4444 salary integer,
4445 stamp timestamp NOT NULL
4446 );
4448 CREATE VIEW emp_view AS
4449 SELECT e.empname,
4450 e.salary,
4451 max(ea.stamp) AS last_updated
4452 FROM emp e
4453 LEFT JOIN emp_audit ea ON ea.empname = e.empname
4456 CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
4457 BEGIN
4458 --
4459 -- Perform the required operation on emp, and create a row in emp_audit
4460 -- to reflect the change made to emp.
4461 --
4462 IF (TG_OP = 'DELETE') THEN
4463 DELETE FROM emp WHERE empname = OLD.empname;
4464 IF NOT FOUND THEN RETURN NULL; END IF;
4466 OLD.last_updated = now();
4467 INSERT INTO emp_audit VALUES('D', current_user, OLD.*);
4468 RETURN OLD;
4469 ELSIF (TG_OP = 'UPDATE') THEN
4470 UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
4471 IF NOT FOUND THEN RETURN NULL; END IF;
4473 NEW.last_updated = now();
4474 INSERT INTO emp_audit VALUES('U', current_user, NEW.*);
4475 RETURN NEW;
4476 ELSIF (TG_OP = 'INSERT') THEN
4477 INSERT INTO emp VALUES(NEW.empname, NEW.salary);
4479 NEW.last_updated = now();
4480 INSERT INTO emp_audit VALUES('I', current_user, NEW.*);
4481 RETURN NEW;
4482 END IF;
4483 END;
4484 $$ LANGUAGE plpgsql;
4486 CREATE TRIGGER emp_audit
4487 INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
4488 FOR EACH ROW EXECUTE FUNCTION update_emp_view();
4489 </programlisting>
4490 </example>
4492 <para>
4493 One use of triggers is to maintain a summary table
4494 of another table. The resulting summary can be used in place of the
4495 original table for certain queries — often with vastly reduced run
4496 times.
4497 This technique is commonly used in Data Warehousing, where the tables
4498 of measured or observed data (called fact tables) might be extremely large.
4501 a summary table for a fact table in a data warehouse.
4502 </para>
4506 <title>A <application>PL/pgSQL</application> Trigger Function for Maintaining a Summary Table</title>
4508 <para>
4509 The schema detailed here is partly based on the <emphasis>Grocery Store
4511 by Ralph Kimball.
4512 </para>
4514 <programlisting>
4515 --
4516 -- Main tables - time dimension and sales fact.
4517 --
4518 CREATE TABLE time_dimension (
4519 time_key integer NOT NULL,
4520 day_of_week integer NOT NULL,
4521 day_of_month integer NOT NULL,
4522 month integer NOT NULL,
4523 quarter integer NOT NULL,
4524 year integer NOT NULL
4525 );
4526 CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
4528 CREATE TABLE sales_fact (
4529 time_key integer NOT NULL,
4530 product_key integer NOT NULL,
4531 store_key integer NOT NULL,
4533 units_sold integer NOT NULL,
4535 );
4536 CREATE INDEX sales_fact_time ON sales_fact(time_key);
4538 --
4539 -- Summary table - sales by time.
4540 --
4541 CREATE TABLE sales_summary_bytime (
4542 time_key integer NOT NULL,
4544 units_sold numeric(12) NOT NULL,
4546 );
4547 CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
4549 --
4550 -- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
4551 --
4552 CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
4553 AS $maint_sales_summary_bytime$
4554 DECLARE
4555 delta_time_key integer;
4557 delta_units_sold numeric(12);
4559 BEGIN
4561 -- Work out the increment/decrement amount(s).
4562 IF (TG_OP = 'DELETE') THEN
4564 delta_time_key = OLD.time_key;
4565 delta_amount_sold = -1 * OLD.amount_sold;
4566 delta_units_sold = -1 * OLD.units_sold;
4567 delta_amount_cost = -1 * OLD.amount_cost;
4569 ELSIF (TG_OP = 'UPDATE') THEN
4571 -- forbid updates that change the time_key -
4572 -- (probably not too onerous, as DELETE + INSERT is how most
4573 -- changes will be made).
4574 IF ( OLD.time_key != NEW.time_key) THEN
4575 RAISE EXCEPTION 'Update of time_key : % -> % not allowed',
4576 OLD.time_key, NEW.time_key;
4577 END IF;
4579 delta_time_key = OLD.time_key;
4580 delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
4581 delta_units_sold = NEW.units_sold - OLD.units_sold;
4582 delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
4584 ELSIF (TG_OP = 'INSERT') THEN
4586 delta_time_key = NEW.time_key;
4587 delta_amount_sold = NEW.amount_sold;
4588 delta_units_sold = NEW.units_sold;
4589 delta_amount_cost = NEW.amount_cost;
4591 END IF;
4594 -- Insert or update the summary row with the new values.
4596 LOOP
4597 UPDATE sales_summary_bytime
4598 SET amount_sold = amount_sold + delta_amount_sold,
4599 units_sold = units_sold + delta_units_sold,
4600 amount_cost = amount_cost + delta_amount_cost
4601 WHERE time_key = delta_time_key;
4603 EXIT insert_update WHEN found;
4605 BEGIN
4606 INSERT INTO sales_summary_bytime (
4607 time_key,
4608 amount_sold,
4609 units_sold,
4610 amount_cost)
4611 VALUES (
4612 delta_time_key,
4613 delta_amount_sold,
4614 delta_units_sold,
4615 delta_amount_cost
4616 );
4618 EXIT insert_update;
4620 EXCEPTION
4621 WHEN UNIQUE_VIOLATION THEN
4622 -- do nothing
4623 END;
4624 END LOOP insert_update;
4626 RETURN NULL;
4628 END;
4629 $maint_sales_summary_bytime$ LANGUAGE plpgsql;
4631 CREATE TRIGGER maint_sales_summary_bytime
4632 AFTER INSERT OR UPDATE OR DELETE ON sales_fact
4633 FOR EACH ROW EXECUTE FUNCTION maint_sales_summary_bytime();
4639 SELECT * FROM sales_summary_bytime;
4640 DELETE FROM sales_fact WHERE product_key = 1;
4641 SELECT * FROM sales_summary_bytime;
4642 UPDATE sales_fact SET units_sold = units_sold * 2;
4643 SELECT * FROM sales_summary_bytime;
4644 </programlisting>
4645 </example>
4647 <para>
4649 tables</firstterm> to inspect the entire set of rows changed by the triggering
4651 or both transition tables, and then the function can refer to those names
4652 as though they were read-only temporary tables.
4654 </para>
4659 <para>
4660 This example produces the same results as
4662 trigger that fires for every row, it uses a trigger that fires once
4663 per statement, after collecting the relevant information in a transition
4664 table. This can be significantly faster than the row-trigger approach
4665 when the invoking statement has modified many rows. Notice that we must
4666 make a separate trigger declaration for each kind of event, since the
4668 this does not stop us from using a single trigger function if we choose.
4669 (In practice, it might be better to use three separate functions and
4671 </para>
4673 <programlisting>
4674 CREATE TABLE emp (
4675 empname text NOT NULL,
4676 salary integer
4677 );
4679 CREATE TABLE emp_audit(
4680 operation char(1) NOT NULL,
4681 stamp timestamp NOT NULL,
4682 userid text NOT NULL,
4683 empname text NOT NULL,
4684 salary integer
4685 );
4687 CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
4688 BEGIN
4689 --
4690 -- Create rows in emp_audit to reflect the operations performed on emp,
4691 -- making use of the special variable TG_OP to work out the operation.
4692 --
4693 IF (TG_OP = 'DELETE') THEN
4694 INSERT INTO emp_audit
4695 SELECT 'D', now(), current_user, o.* FROM old_table o;
4696 ELSIF (TG_OP = 'UPDATE') THEN
4697 INSERT INTO emp_audit
4698 SELECT 'U', now(), current_user, n.* FROM new_table n;
4699 ELSIF (TG_OP = 'INSERT') THEN
4700 INSERT INTO emp_audit
4701 SELECT 'I', now(), current_user, n.* FROM new_table n;
4702 END IF;
4703 RETURN NULL; -- result is ignored since this is an AFTER trigger
4704 END;
4705 $emp_audit$ LANGUAGE plpgsql;
4707 CREATE TRIGGER emp_audit_ins
4708 AFTER INSERT ON emp
4709 REFERENCING NEW TABLE AS new_table
4710 FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
4711 CREATE TRIGGER emp_audit_upd
4712 AFTER UPDATE ON emp
4713 REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
4714 FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
4715 CREATE TRIGGER emp_audit_del
4716 AFTER DELETE ON emp
4717 REFERENCING OLD TABLE AS old_table
4718 FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
4719 </programlisting>
4720 </example>
4722 </sect2>
4727 <para>
4731 is to be called as an event trigger must be declared as a function with
4733 </para>
4735 <para>
4737 event trigger, several special variables are created automatically
4738 in the top-level block. They are:
4740 <variablelist>
4743 <listitem>
4744 <para>
4745 event the trigger is fired for.
4746 </para>
4747 </listitem>
4748 </varlistentry>
4752 <listitem>
4753 <para>
4754 command tag for which the trigger is fired.
4755 </para>
4756 </listitem>
4757 </varlistentry>
4758 </variablelist>
4759 </para>
4761 <para>
4764 </para>
4769 <para>
4771 each time a supported command is executed.
4772 </para>
4774 <programlisting>
4775 CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
4776 BEGIN
4777 RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
4778 END;
4779 $$ LANGUAGE plpgsql;
4781 CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();
4782 </programlisting>
4783 </example>
4784 </sect2>
4786 </sect1>
4791 <para>
4792 This section discusses some implementation details that are
4794 </para>
4799 <para>
4801 can refer to variables and parameters of the function. Behind the scenes,
4803 Query parameters will only be substituted in places where they are
4804 syntactically permissible. As an extreme case, consider
4805 this example of poor programming style:
4806 <programlisting>
4807 INSERT INTO foo (foo) VALUES (foo(foo));
4808 </programlisting>
4810 name, so it will not be substituted, even if the function has a variable
4812 column of that table, so it will not be substituted either. Likewise
4813 the third occurrence must be a function name, so it also will not be
4814 substituted for. Only the last occurrence is a candidate to be a
4816 function.
4817 </para>
4819 <para>
4820 Another way to understand this is that variable substitution can only
4821 insert data values into an SQL command; it cannot dynamically change which
4822 database objects are referenced by the command. (If you want to do
4823 that, you must build a command string dynamically, as explained in
4825 </para>
4827 <para>
4828 Since the names of variables are syntactically no different from the names
4829 of table columns, there can be ambiguity in statements that also refer to
4830 tables: is a given name meant to refer to a table column, or a variable?
4831 Let's change the previous example to
4832 <programlisting>
4833 INSERT INTO dest (col) SELECT foo + bar FROM src;
4834 </programlisting>
4839 </para>
4841 <para>
4843 in an SQL statement could refer to either a variable or a table column.
4844 You can fix such a problem by renaming the variable or column,
4845 or by qualifying the ambiguous reference, or by telling
4847 </para>
4849 <para>
4850 The simplest solution is to rename the variable or column.
4851 A common coding rule is to use a
4853 variables than you use for column names. For example,
4854 if you consistently name function variables
4857 </para>
4859 <para>
4860 Alternatively you can qualify ambiguous references to make them clear.
4862 to the table column. To create an unambiguous reference to a variable,
4863 declare it in a labeled block and use the block's label
4865 <programlisting>
4867 DECLARE
4868 foo int;
4869 BEGIN
4870 foo := ...;
4871 INSERT INTO dest (col) SELECT block.foo + bar FROM src;
4872 </programlisting>
4876 function's name, because they are implicitly declared in an outer block
4877 labeled with the function's name.
4878 </para>
4880 <para>
4881 Sometimes it is impractical to fix all the ambiguous references in a
4886 table column (which is compatible with some other systems such as
4888 </para>
4890 <indexterm>
4892 </indexterm>
4894 <para>
4895 To change this behavior on a system-wide basis, set the configuration
4899 This parameter affects subsequent compilations
4901 already compiled in the current session.
4902 Because changing this setting
4904 functions, it can only be changed by a superuser.
4905 </para>
4907 <para>
4908 You can also set the behavior on a function-by-function basis, by
4909 inserting one of these special commands at the start of the function
4910 text:
4911 <programlisting>
4912 #variable_conflict error
4913 #variable_conflict use_variable
4914 #variable_conflict use_column
4915 </programlisting>
4916 These commands affect only the function they are written in, and override
4918 <programlisting>
4919 CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
4920 #variable_conflict use_variable
4921 DECLARE
4922 curtime timestamp := now();
4923 BEGIN
4924 UPDATE users SET last_modified = curtime, comment = comment
4925 WHERE users.id = id;
4926 END;
4927 $$ LANGUAGE plpgsql;
4928 </programlisting>
4929 In the <literal>UPDATE</literal> command, <literal>curtime</literal>, <literal>comment</literal>,
4938 in this way:
4939 <programlisting>
4940 CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
4942 DECLARE
4943 curtime timestamp := now();
4944 BEGIN
4945 UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment
4946 WHERE users.id = stamp_user.id;
4947 END;
4948 $$ LANGUAGE plpgsql;
4949 </programlisting>
4950 </para>
4952 <para>
4953 Variable substitution does not happen in a command string given
4955 insert a varying value into such a command, do so as part of
4958 </para>
4960 <para>
4965 ... AS SELECT</command>),
4966 because the main SQL engine allows query parameters only in these
4967 commands. To use a non-constant name or value in other statement
4968 types (generically called utility statements), you must construct
4970 </para>
4972 </sect2>
4977 <para>
4979 text and produces an internal binary instruction tree the first time the
4980 function is called (within each session). The instruction tree
4981 fully translates the
4984 used in the function are not translated immediately.
4985 </para>
4987 <para>
4988 <indexterm>
4991 </indexterm>
4994 parses and analyzes the command to create a prepared statement,
4997 Subsequent visits to that expression or command
4998 reuse the prepared statement. Thus, a function with conditional code
4999 paths that are seldom visited will never incur the overhead of
5000 analyzing those commands that are never executed within the current
5001 session. A disadvantage is that errors
5002 in a specific expression or command cannot be detected until that
5003 part of the function is reached in execution. (Trivial syntax
5004 errors will be detected during the initial parsing pass, but
5005 anything deeper will not be detected until execution.)
5006 </para>
5008 <para>
5010 furthermore attempt to cache the execution plan associated with any
5011 particular prepared statement. If a cached plan is not used, then
5012 a fresh execution plan is generated on each visit to the statement,
5014 variable values) can be used to optimize the selected plan. If the
5015 statement has no parameters, or is executed many times, the SPI manager
5017 on specific parameter values, and caching that for re-use. Typically
5018 this will happen only if the execution plan is not very sensitive to
5020 If it is, generating a plan each time is a net win. See <xref
5022 prepared statements.
5023 </para>
5025 <para>
5027 and sometimes execution plans in this way,
5028 SQL commands that appear directly in a
5030 same tables and columns on every execution; that is, you cannot use
5031 a parameter as the name of a table or column in an SQL command. To get
5032 around this restriction, you can construct dynamic commands using
5034 statement — at the price of performing new parse analysis and
5035 constructing a new execution plan on every execution.
5036 </para>
5038 <para>
5039 The mutable nature of record variables presents another problem in this
5040 connection. When fields of a record variable are used in
5041 expressions or statements, the data types of the fields must not
5042 change from one call of the function to the next, since each
5043 expression will be analyzed using the data type that is present
5045 used to get around this problem when necessary.
5046 </para>
5048 <para>
5049 If the same function is used as a trigger for more than one table,
5051 independently for each such table — that is, there is a cache
5052 for each trigger function and table combination, not just for each
5053 function. This alleviates some of the problems with varying
5054 data types; for instance, a trigger function will be able to work
5056 to have different types in different tables.
5057 </para>
5059 <para>
5060 Likewise, functions having polymorphic argument types have a separate
5061 statement cache for each combination of actual argument types they have
5062 been invoked for, so that data type differences do not cause unexpected
5063 failures.
5064 </para>
5066 <para>
5067 Statement caching can sometimes have surprising effects on the
5068 interpretation of time-sensitive values. For example there
5069 is a difference between what these two functions do:
5071 <programlisting>
5072 CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
5073 BEGIN
5074 INSERT INTO logtable VALUES (logtxt, 'now');
5075 END;
5076 $$ LANGUAGE plpgsql;
5077 </programlisting>
5079 and:
5081 <programlisting>
5082 CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
5083 DECLARE
5084 curtime timestamp;
5085 BEGIN
5086 curtime := 'now';
5087 INSERT INTO logtable VALUES (logtxt, curtime);
5088 END;
5089 $$ LANGUAGE plpgsql;
5090 </programlisting>
5091 </para>
5093 <para>
5101 constant when the
5104 of the session. Needless to say, this isn't what the programmer
5107 </para>
5109 <para>
5119 functions for the conversion. So, the computed time stamp is updated
5120 on each execution as the programmer expects. Even though this
5121 happens to work as expected, it's not terribly efficient, so
5123 </para>
5125 </sect2>
5127 </sect1>
5132 <para>
5133 One good way to develop in
5135 choice to create your functions, and in another window, use
5137 If you are doing it this way, it
5138 is a good idea to write the function using <command>CREATE OR
5139 REPLACE FUNCTION</command>. That way you can just reload the file to update
5140 the function definition. For example:
5141 <programlisting>
5142 CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
5143 ....
5144 $$ LANGUAGE plpgsql;
5145 </programlisting>
5146 </para>
5148 <para>
5150 a function definition file with:
5151 <programlisting>
5152 \i filename.sql
5153 </programlisting>
5154 and then immediately issue SQL commands to test the function.
5155 </para>
5157 <para>
5159 GUI database access tool that facilitates development in a
5160 procedural language. One example of such a tool is
5162 provide convenient features such as escaping single quotes and
5163 making it easier to recreate and debug functions.
5164 </para>
5169 <para>
5172 write the string literal in the ordinary way with surrounding
5173 single quotes, then any single quotes inside the function body
5174 must be doubled; likewise any backslashes must be doubled (assuming
5175 escape string syntax is used).
5176 Doubling quotes is at best tedious, and in more complicated cases
5177 the code can become downright incomprehensible, because you can
5178 easily find yourself needing half a dozen or more adjacent quote marks.
5179 It's recommended that you instead write the function body as a
5182 approach, you never double any quote marks, but instead take care to
5183 choose a different dollar-quoting delimiter for each level of
5184 nesting you need. For example, you might write the <command>CREATE
5185 FUNCTION</command> command as:
5186 <programlisting>
5187 CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
5188 ....
5189 $PROC$ LANGUAGE plpgsql;
5190 </programlisting>
5191 Within this, you might use quote marks for simple literal strings in
5193 that you are assembling as strings. If you need to quote text that
5195 </para>
5197 <para>
5198 The following chart shows what you have to do when writing quote
5199 marks without dollar quoting. It might be useful when translating
5200 pre-dollar quoting code into something more comprehensible.
5201 </para>
5203 <variablelist>
5206 <listitem>
5207 <para>
5208 To begin and end the function body, for example:
5209 <programlisting>
5210 CREATE FUNCTION foo() RETURNS integer AS '
5211 ....
5212 ' LANGUAGE plpgsql;
5213 </programlisting>
5214 Anywhere within a single-quoted function body, quote marks
5216 </para>
5217 </listitem>
5218 </varlistentry>
5222 <listitem>
5223 <para>
5224 For string literals inside the function body, for example:
5225 <programlisting>
5226 a_output := ''Blah'';
5227 SELECT * FROM users WHERE f_name=''foobar'';
5228 </programlisting>
5229 In the dollar-quoting approach, you'd just write:
5230 <programlisting>
5231 a_output := 'Blah';
5232 SELECT * FROM users WHERE f_name='foobar';
5233 </programlisting>
5235 in either case.
5236 </para>
5237 </listitem>
5238 </varlistentry>
5242 <listitem>
5243 <para>
5244 When you need a single quotation mark in a string constant inside the
5245 function body, for example:
5246 <programlisting>
5247 a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''
5248 </programlisting>
5251 </para>
5252 <para>
5253 In the dollar-quoting approach, you'd write:
5254 <programlisting>
5255 a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$
5256 </programlisting>
5257 being careful that any dollar-quote delimiters around this are not
5259 </para>
5260 </listitem>
5261 </varlistentry>
5265 <listitem>
5266 <para>
5267 When a single quotation mark in a string inside the function body is
5268 adjacent to the end of that string constant, for example:
5269 <programlisting>
5270 a_output := a_output || '' AND name LIKE ''''foobar''''''
5271 </programlisting>
5274 </para>
5275 <para>
5276 In the dollar-quoting approach, this becomes:
5277 <programlisting>
5278 a_output := a_output || $$ AND name LIKE 'foobar'$$
5279 </programlisting>
5280 </para>
5281 </listitem>
5282 </varlistentry>
5286 <listitem>
5287 <para>
5288 When you want two single quotation marks in a string constant (which
5289 accounts for 8 quotation marks) and this is adjacent to the end of that
5290 string constant (2 more). You will probably only need that if
5291 you are writing a function that generates other functions, as in
5293 For example:
5294 <programlisting>
5295 a_output := a_output || '' if v_'' ||
5296 referrer_keys.kind || '' like ''''''''''
5297 || referrer_keys.key_string || ''''''''''
5298 then return '''''' || referrer_keys.referrer_type
5299 || ''''''; end if;'';
5300 </programlisting>
5302 <programlisting>
5303 if v_... like ''...'' then return ''...''; end if;
5304 </programlisting>
5305 </para>
5306 <para>
5307 In the dollar-quoting approach, this becomes:
5308 <programlisting>
5309 a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
5310 || referrer_keys.key_string || $$'
5311 then return '$$ || referrer_keys.referrer_type
5312 || $$'; end if;$$;
5313 </programlisting>
5314 where we assume we only need to put single quote marks into
5316 </para>
5317 </listitem>
5318 </varlistentry>
5319 </variablelist>
5321 </sect2>
5325 <para>
5326 To aid the user in finding instances of simple but common problems before
5330 during the compilation of a function. A function which has received
5332 so you are advised to test in a separate development environment.
5333 </para>
5335 <para>
5338 is encouraged in development and/or testing environments.
5339 </para>
5341 <para>
5342 These additional checks are enabled through the configuration variables
5347 the list of available checks includes:
5348 <variablelist>
5351 <listitem>
5352 <para>
5353 Checks if a declaration shadows a previously defined variable.
5354 </para>
5355 </listitem>
5356 </varlistentry>
5360 <listitem>
5361 <para>
5363 values to more than one variable at a time, such as
5365 variables and the number of source variables should match, though
5367 for missing values and extra variables are ignored. Enabling this
5370 number of target variables and the number of source variables are
5371 different.
5372 </para>
5373 </listitem>
5374 </varlistentry>
5378 <listitem>
5379 <para>
5381 check if a given query returns more than one row when an
5383 statement will only ever use one row, having a query return multiple
5384 rows is generally either inefficient and/or nondeterministic and
5385 therefore is likely an error.
5386 </para>
5387 </listitem>
5388 </varlistentry>
5389 </variablelist>
5393 <programlisting>
5394 SET plpgsql.extra_warnings TO 'shadowed_variables';
5396 CREATE FUNCTION foo(f1 int) RETURNS int AS $$
5397 DECLARE
5398 f1 int;
5399 BEGIN
5400 RETURN f1;
5401 END;
5402 $$ LANGUAGE plpgsql;
5403 WARNING: variable "f1" shadows a previously defined variable
5404 LINE 3: f1 int;
5405 ^
5406 CREATE FUNCTION
5407 </programlisting>
5408 The below example shows the effects of setting
5411 <programlisting>
5412 SET plpgsql.extra_warnings TO 'strict_multi_assignment';
5414 CREATE OR REPLACE FUNCTION public.foo()
5415 RETURNS void
5416 LANGUAGE plpgsql
5417 AS $$
5418 DECLARE
5419 x int;
5420 y int;
5421 BEGIN
5422 SELECT 1 INTO x, y;
5425 END;
5426 $$;
5428 SELECT foo();
5429 WARNING: number of source and target fields in assignment does not match
5430 DETAIL: strict_multi_assignment check of extra_warnings is active.
5431 HINT: Make sure the query returns the exact list of columns.
5432 WARNING: number of source and target fields in assignment does not match
5433 DETAIL: strict_multi_assignment check of extra_warnings is active.
5434 HINT: Make sure the query returns the exact list of columns.
5436 foo
5437 -----
5439 (1 row)
5440 </programlisting>
5441 </para>
5442 </sect2>
5443 </sect1>
5445 <!-- **** Porting from Oracle PL/SQL **** -->
5453 </indexterm>
5458 </indexterm>
5460 <para>
5461 This section explains differences between
5464 to help developers who port applications from
5466 </para>
5468 <para>
5470 aspects. It is a block-structured, imperative language, and all
5471 variables have to be declared. Assignments, loops, and conditionals
5472 are similar. The main differences you should keep in mind when
5476 <itemizedlist>
5477 <listitem>
5478 <para>
5479 If a name used in an SQL command could be either a column name of a
5480 table used in the command or a reference to a variable of the function,
5483 complaining that the name is ambiguous. You can specify
5487 It's often best to avoid such ambiguities in the first place,
5488 but if you have to port a large amount of code that depends on
5490 best solution.
5491 </para>
5492 </listitem>
5494 <listitem>
5495 <para>
5497 a string literal. Therefore you need to use dollar quoting or escape
5498 single quotes in the function body. (See <xref
5500 </para>
5501 </listitem>
5503 <listitem>
5504 <para>
5505 Data type names often need translation. For example, in Oracle string
5510 data type if there's a more appropriate one.
5511 </para>
5512 </listitem>
5514 <listitem>
5515 <para>
5516 Instead of packages, use schemas to organize your functions
5517 into groups.
5518 </para>
5519 </listitem>
5521 <listitem>
5522 <para>
5523 Since there are no packages, there are no package-level variables
5524 either. This is somewhat annoying. You can keep per-session state
5525 in temporary tables instead.
5526 </para>
5527 </listitem>
5529 <listitem>
5530 <para>
5534 from the first number to the second, requiring the loop bounds
5535 to be swapped when porting. This incompatibility is unfortunate
5536 but is unlikely to be changed. (See <xref
5538 </para>
5539 </listitem>
5541 <listitem>
5542 <para>
5544 differently: the target variable(s) must have been declared,
5546 An advantage of this is that the variable values are still accessible
5547 after the loop exits.
5548 </para>
5549 </listitem>
5551 <listitem>
5552 <para>
5553 There are various notational differences for the use of cursor
5554 variables.
5555 </para>
5556 </listitem>
5558 </itemizedlist>
5559 </para>
5564 <para>
5567 </para>
5570 <title>Porting a Simple Function from <application>PL/SQL</application> to <application>PL/pgSQL</application></title>
5572 <para>
5574 <programlisting>
5575 CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2,
5576 v_version varchar2)
5577 RETURN varchar2 IS
5578 BEGIN
5579 IF v_version IS NULL THEN
5580 RETURN v_name;
5581 END IF;
5582 RETURN v_name || '/' || v_version;
5583 END;
5584 /
5585 show errors;
5586 </programlisting>
5587 </para>
5589 <para>
5590 Let's go through this function and see the differences compared to
5593 <itemizedlist>
5594 <listitem>
5595 <para>
5599 you do not need specific string length limits.
5600 </para>
5601 </listitem>
5603 <listitem>
5604 <para>
5606 prototype (not the function body) becomes
5611 is not the only possible function language.
5612 </para>
5613 </listitem>
5615 <listitem>
5616 <para>
5618 to be a string literal, so you need to use quote marks or dollar
5620 in the Oracle approach.
5621 </para>
5622 </listitem>
5624 <listitem>
5625 <para>
5628 reported automatically.
5629 </para>
5630 </listitem>
5631 </itemizedlist>
5632 </para>
5634 <para>
5635 This is how this function would look when ported to
5638 <programlisting>
5639 CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
5640 v_version varchar)
5641 RETURNS varchar AS $$
5642 BEGIN
5643 IF v_version IS NULL THEN
5644 RETURN v_name;
5645 END IF;
5646 RETURN v_name || '/' || v_version;
5647 END;
5648 $$ LANGUAGE plpgsql;
5649 </programlisting>
5650 </para>
5651 </example>
5653 <para>
5655 function that creates another function and how to handle the
5656 ensuing quoting problems.
5657 </para>
5660 <title>Porting a Function that Creates Another Function from <application>PL/SQL</application> to <application>PL/pgSQL</application></title>
5662 <para>
5663 The following procedure grabs rows from a
5666 sake of efficiency.
5667 </para>
5669 <para>
5670 This is the Oracle version:
5671 <programlisting>
5672 CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
5673 CURSOR referrer_keys IS
5674 SELECT * FROM cs_referrer_keys
5675 ORDER BY try_order;
5676 func_cmd VARCHAR(4000);
5677 BEGIN
5678 func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR2,
5679 v_domain IN VARCHAR2, v_url IN VARCHAR2) RETURN VARCHAR2 IS BEGIN';
5681 FOR referrer_key IN referrer_keys LOOP
5682 func_cmd := func_cmd ||
5683 ' IF v_' || referrer_key.kind
5684 || ' LIKE ''' || referrer_key.key_string
5685 || ''' THEN RETURN ''' || referrer_key.referrer_type
5686 || '''; END IF;';
5687 END LOOP;
5689 func_cmd := func_cmd || ' RETURN NULL; END;';
5691 EXECUTE IMMEDIATE func_cmd;
5692 END;
5693 /
5694 show errors;
5695 </programlisting>
5696 </para>
5698 <para>
5700 <programlisting>
5701 CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc() AS $func$
5702 DECLARE
5703 referrer_keys CURSOR IS
5704 SELECT * FROM cs_referrer_keys
5705 ORDER BY try_order;
5706 func_body text;
5707 func_cmd text;
5708 BEGIN
5709 func_body := 'BEGIN';
5711 FOR referrer_key IN referrer_keys LOOP
5712 func_body := func_body ||
5713 ' IF v_' || referrer_key.kind
5714 || ' LIKE ' || quote_literal(referrer_key.key_string)
5715 || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
5716 || '; END IF;' ;
5717 END LOOP;
5719 func_body := func_body || ' RETURN NULL; END;';
5721 func_cmd :=
5722 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
5723 v_domain varchar,
5724 v_url varchar)
5725 RETURNS varchar AS '
5726 || quote_literal(func_body)
5727 || ' LANGUAGE plpgsql;' ;
5729 EXECUTE func_cmd;
5730 END;
5731 $func$ LANGUAGE plpgsql;
5732 </programlisting>
5733 Notice how the body of the function is built separately and passed
5735 technique is needed because we cannot safely use dollar quoting for
5736 defining the new function: we do not know for sure what strings will
5741 anything, in particular it might contain dollar signs.) This function
5742 is actually an improvement on the Oracle original, because it will
5745 </para>
5746 </example>
5748 <para>
5753 using a combination of other
5757 easier.
5758 </para>
5761 <title>Porting a Procedure With String Manipulation and
5765 <para>
5767 to parse a URL and return several elements (host, path, and query).
5768 </para>
5770 <para>
5771 This is the Oracle version:
5772 <programlisting>
5773 CREATE OR REPLACE PROCEDURE cs_parse_url(
5774 v_url IN VARCHAR2,
5775 v_host OUT VARCHAR2, -- This will be passed back
5776 v_path OUT VARCHAR2, -- This one too
5777 v_query OUT VARCHAR2) -- And this one
5778 IS
5779 a_pos1 INTEGER;
5780 a_pos2 INTEGER;
5781 BEGIN
5782 v_host := NULL;
5783 v_path := NULL;
5784 v_query := NULL;
5785 a_pos1 := instr(v_url, '//');
5787 IF a_pos1 = 0 THEN
5788 RETURN;
5789 END IF;
5790 a_pos2 := instr(v_url, '/', a_pos1 + 2);
5791 IF a_pos2 = 0 THEN
5792 v_host := substr(v_url, a_pos1 + 2);
5793 v_path := '/';
5794 RETURN;
5795 END IF;
5798 a_pos1 := instr(v_url, '?', a_pos2 + 1);
5800 IF a_pos1 = 0 THEN
5801 v_path := substr(v_url, a_pos2);
5802 RETURN;
5803 END IF;
5805 v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
5806 v_query := substr(v_url, a_pos1 + 1);
5807 END;
5808 /
5809 show errors;
5810 </programlisting>
5811 </para>
5813 <para>
5815 <programlisting>
5816 CREATE OR REPLACE FUNCTION cs_parse_url(
5817 v_url IN VARCHAR,
5818 v_host OUT VARCHAR, -- This will be passed back
5819 v_path OUT VARCHAR, -- This one too
5820 v_query OUT VARCHAR) -- And this one
5821 AS $$
5822 DECLARE
5823 a_pos1 INTEGER;
5824 a_pos2 INTEGER;
5825 BEGIN
5826 v_host := NULL;
5827 v_path := NULL;
5828 v_query := NULL;
5829 a_pos1 := instr(v_url, '//');
5831 IF a_pos1 = 0 THEN
5832 RETURN;
5833 END IF;
5834 a_pos2 := instr(v_url, '/', a_pos1 + 2);
5835 IF a_pos2 = 0 THEN
5836 v_host := substr(v_url, a_pos1 + 2);
5837 v_path := '/';
5838 RETURN;
5839 END IF;
5842 a_pos1 := instr(v_url, '?', a_pos2 + 1);
5844 IF a_pos1 = 0 THEN
5845 v_path := substr(v_url, a_pos2);
5846 RETURN;
5847 END IF;
5849 v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
5850 v_query := substr(v_url, a_pos1 + 1);
5851 END;
5852 $$ LANGUAGE plpgsql;
5853 </programlisting>
5855 This function could be used like this:
5856 <programlisting>
5857 SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
5858 </programlisting>
5859 </para>
5860 </example>
5862 <para>
5864 that uses numerous features that are specific to Oracle.
5865 </para>
5868 <title>Porting a Procedure from <application>PL/SQL</application> to <application>PL/pgSQL</application></title>
5870 <para>
5871 The Oracle version:
5873 <programlisting>
5874 CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
5875 a_running_job_count INTEGER;
5876 BEGIN
5877 LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
5879 SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
5882 COMMIT; -- free lock
5883 raise_application_error(-20000,
5884 'Unable to create a new job: a job is currently running.');
5885 END IF;
5887 DELETE FROM cs_active_job;
5888 INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
5890 BEGIN
5891 INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
5892 EXCEPTION
5893 WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
5894 END;
5895 COMMIT;
5896 END;
5897 /
5898 show errors
5899 </programlisting>
5900 </para>
5902 <para>
5905 <programlisting>
5906 CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id integer) AS $$
5907 DECLARE
5908 a_running_job_count integer;
5909 BEGIN
5910 LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
5912 SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
5915 COMMIT; -- free lock
5916 RAISE EXCEPTION 'Unable to create a new job: a job is currently running'; -- <co id="co.plpgsql-porting-raise"/>
5917 END IF;
5919 DELETE FROM cs_active_job;
5920 INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
5922 BEGIN
5923 INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
5924 EXCEPTION
5926 -- don't worry if it already exists
5927 END;
5928 COMMIT;
5929 END;
5930 $$ LANGUAGE plpgsql;
5931 </programlisting>
5933 <calloutlist>
5935 <para>
5939 similarly.
5940 </para>
5941 </callout>
5943 <para>
5945 different from Oracle's. The set of built-in exception names
5947 is not currently a way to declare user-defined exception names,
5948 although you can throw user-chosen SQLSTATE values instead.
5949 </para>
5950 </callout>
5951 </calloutlist>
5952 </para>
5953 </example>
5954 </sect2>
5959 <para>
5960 This section explains a few other things to watch for when porting
5963 </para>
5968 <para>
5972 is equivalent to what you'd get in Oracle with:
5974 <programlisting>
5975 BEGIN
5976 SAVEPOINT s1;
5977 ... code here ...
5978 EXCEPTION
5979 WHEN ... THEN
5980 ROLLBACK TO s1;
5981 ... code here ...
5982 WHEN ... THEN
5983 ROLLBACK TO s1;
5984 ... code here ...
5985 END;
5986 </programlisting>
5988 If you are translating an Oracle procedure that uses
5993 then some actual thought will be required.
5994 </para>
5995 </sect3>
6000 <para>
6008 reliably unless you use these functions.
6009 </para>
6010 </sect3>
6015 <para>
6018 the function always returns the same result when given the same
6020 returns null if any argument is null). Consult the <xref
6022 reference page for details.
6023 </para>
6025 <para>
6026 When making use of these optimization attributes, your
6028 like this:
6030 <programlisting>
6031 CREATE FUNCTION foo(...) RETURNS integer AS $$
6032 ...
6033 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
6034 </programlisting>
6035 </para>
6036 </sect3>
6037 </sect2>
6042 <para>
6043 This section contains the code for a set of Oracle-compatible
6045 your porting efforts.
6046 </para>
6048 <indexterm>
6050 </indexterm>
6052 <programlisting><![CDATA[
6053 --
6054 -- instr functions that mimic Oracle's counterpart
6055 -- Syntax: instr(string1, string2 [, n [, m]])
6056 -- where [] denotes optional parameters.
6057 --
6058 -- Search string1, beginning at the nth character, for the mth occurrence
6059 -- of string2. If n is negative, search backwards, starting at the abs(n)'th
6060 -- character from the end of string1.
6061 -- If n is not passed, assume 1 (search starts at first character).
6062 -- If m is not passed, assume 1 (find first occurrence).
6063 -- Returns starting index of string2 in string1, or 0 if string2 is not found.
6064 --
6066 CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
6067 BEGIN
6069 END;
6070 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
6073 CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
6074 beg_index integer)
6075 RETURNS integer AS $$
6076 DECLARE
6077 pos integer NOT NULL DEFAULT 0;
6078 temp_str varchar;
6079 beg integer;
6080 length integer;
6081 ss_length integer;
6082 BEGIN
6084 temp_str := substring(string FROM beg_index);
6085 pos := position(string_to_search_for IN temp_str);
6087 IF pos = 0 THEN
6088 RETURN 0;
6089 ELSE
6090 RETURN pos + beg_index - 1;
6091 END IF;
6092 ELSIF beg_index < 0 THEN
6093 ss_length := char_length(string_to_search_for);
6094 length := char_length(string);
6095 beg := length + 1 + beg_index;
6098 temp_str := substring(string FROM beg FOR ss_length);
6099 IF string_to_search_for = temp_str THEN
6100 RETURN beg;
6101 END IF;
6103 beg := beg - 1;
6104 END LOOP;
6106 RETURN 0;
6107 ELSE
6108 RETURN 0;
6109 END IF;
6110 END;
6111 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
6114 CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
6115 beg_index integer, occur_index integer)
6116 RETURNS integer AS $$
6117 DECLARE
6118 pos integer NOT NULL DEFAULT 0;
6119 occur_number integer NOT NULL DEFAULT 0;
6120 temp_str varchar;
6121 beg integer;
6122 i integer;
6123 length integer;
6124 ss_length integer;
6125 BEGIN
6126 IF occur_index <= 0 THEN
6127 RAISE 'argument ''%'' is out of range', occur_index
6128 USING ERRCODE = '22003';
6129 END IF;
6132 beg := beg_index - 1;
6133 FOR i IN 1..occur_index LOOP
6134 temp_str := substring(string FROM beg + 1);
6135 pos := position(string_to_search_for IN temp_str);
6136 IF pos = 0 THEN
6137 RETURN 0;
6138 END IF;
6139 beg := beg + pos;
6140 END LOOP;
6142 RETURN beg;
6143 ELSIF beg_index < 0 THEN
6144 ss_length := char_length(string_to_search_for);
6145 length := char_length(string);
6146 beg := length + 1 + beg_index;
6149 temp_str := substring(string FROM beg FOR ss_length);
6150 IF string_to_search_for = temp_str THEN
6151 occur_number := occur_number + 1;
6152 IF occur_number = occur_index THEN
6153 RETURN beg;
6154 END IF;
6155 END IF;
6157 beg := beg - 1;
6158 END LOOP;
6160 RETURN 0;
6161 ELSE
6162 RETURN 0;
6163 END IF;
6164 END;
6165 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
6166 ]]>
6167 </programlisting>
6168 </sect2>
6170 </sect1>
6172 </chapter>