| blob | 07078e6aea58f048108fcd8a51f745d7981976c4 |
1 <!-- $PostgreSQL$ -->
8 </indexterm>
13 <para>
17 a loadable procedural language that
19 <itemizedlist>
20 <listitem>
21 <para>
22 can be used to create functions and trigger procedures,
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, 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>
64 <para>
66 and most other relational databases use as query language. It's
68 statement must be executed individually by the database server.
69 </para>
71 <para>
72 That means that your client application must send each query to
73 the database server, wait for it to be processed, receive and
74 process the results, do some computation, then send further
75 queries to the server. All this incurs interprocess
76 communication and will also incur network overhead if your client
77 is on a different machine than the database server.
78 </para>
80 <para>
83 the database server, thus having the power of a procedural
84 language and the ease of use of SQL, but with considerable
85 savings of client/server communication overhead.
86 </para>
87 <itemizedlist>
89 <listitem><para> Extra round trips between
90 client and server are eliminated </para></listitem>
92 <listitem><para> Intermediate results that the client does not
93 need do not have to be marshaled or transferred between server
94 and client </para></listitem>
96 <listitem><para> Multiple rounds of query
97 parsing can be avoided </para></listitem>
99 </itemizedlist>
100 <para> This can result in a considerable performance increase as
101 compared to an application that does not use stored functions.
102 </para>
104 <para>
106 the data types, operators and functions of SQL.
107 </para>
108 </sect2>
113 <para>
115 as arguments any scalar or array data type supported by the server,
116 and they can return a result of any of these types. They can also
117 accept or return any composite type (row type) specified by name.
120 is a row type whose columns are determined by specification in the
122 </para>
124 <para>
127 works exactly the same way as for SQL functions, as discussed in
129 </para>
131 <para>
133 and return the polymorphic types
136 data types handled by a polymorphic function can vary from call to
139 </para>
141 <para>
144 a single instance. Such a function generates its output by executing
147 evaluating a query.
148 </para>
150 <para>
153 </para>
155 <para>
157 parameters in place of an explicit specification of the return type.
158 This does not add any fundamental capability to the language, but
159 it is often convenient, especially for returning multiple values.
162 </para>
164 <para>
165 Specific examples appear in
168 </para>
169 </sect2>
170 </sect1>
175 <para>
177 The complete text of a function definition must be a
180 <synopsis>
182 <optional> DECLARE
184 BEGIN
187 </synopsis>
188 </para>
190 <para>
191 Each declaration and each statement within a block is terminated
192 by a semicolon. A block that appears within another block must
195 concludes a function body does not require a semicolon.
196 </para>
198 <tip>
199 <para>
200 A common mistake is to write a semicolon immediately after
202 </para>
203 </tip>
205 <para>
207 identify the block for use
209 variables declared in the block. If a label is given after
211 </para>
213 <para>
214 All key words are case-insensitive.
215 Identifiers are implicitly converted to lowercase
216 unless double-quoted, just as they are in ordinary SQL commands.
217 </para>
219 <para>
224 just as in ordinary SQL.
225 </para>
227 <para>
228 Any statement in the statement section of a block
230 logical grouping or to localize variables to a small group
231 of statements. Variables declared in a subblock mask any
232 similarly-named variables of outer blocks for the duration
233 of the subblock; but you can access the outer variables anyway
234 if you qualify their names with their block's label. For example:
235 <programlisting>
236 CREATE FUNCTION somefunc() RETURNS integer AS $$
238 DECLARE
239 quantity integer := 30;
240 BEGIN
241 RAISE NOTICE 'Quantity here is %', quantity; -- Prints 30
242 quantity := 50;
243 --
244 -- Create a subblock
245 --
246 DECLARE
247 quantity integer := 80;
248 BEGIN
249 RAISE NOTICE 'Quantity here is %', quantity; -- Prints 80
250 RAISE NOTICE 'Outer quantity here is %', outerblock.quantity; -- Prints 50
251 END;
253 RAISE NOTICE 'Quantity here is %', quantity; -- Prints 50
255 RETURN quantity;
256 END;
257 $$ LANGUAGE plpgsql;
258 </programlisting>
259 </para>
261 <note>
262 <para>
265 declarations of the function's parameters (if any), as well as some
268 labeled with the function's name, meaning that parameters and special
269 variables can be qualified with the function's name.
270 </para>
271 </note>
273 <para>
274 It is important not to confuse the use of
277 for transaction
279 are only for grouping; they do not start or end a transaction.
280 Functions and trigger procedures are always executed within a transaction
281 established by an outer query — they cannot start or commit that
282 transaction, since there would be no context for them to execute in.
284 forms a subtransaction that can be rolled back without affecting the
285 outer transaction. For more about that see <xref
287 </para>
288 </sect1>
293 <para>
294 All variables used in a block must be declared in the
295 declarations section of the block.
297 iterating over a range of integer values is automatically declared as an
299 iterating over a cursor's result is automatically declared as a
300 record variable.)
301 </para>
303 <para>
307 </para>
309 <para>
310 Here are some examples of variable declarations:
311 <programlisting>
312 user_id integer;
313 quantity numeric(5);
314 url varchar;
315 myrow tablename%ROWTYPE;
316 myfield tablename.columnname%TYPE;
317 arow RECORD;
318 </programlisting>
319 </para>
321 <para>
322 The general syntax of a variable declaration is:
323 <synopsis>
324 <replaceable>name</replaceable> <optional> CONSTANT </optional> <replaceable>type</replaceable> <optional> NOT NULL </optional> <optional> { DEFAULT | := } <replaceable>expression</replaceable> </optional>;
325 </synopsis>
328 is not given then the variable is initialized to the
331 so that its value remains constant for the duration of the block.
333 is specified, an assignment of a null value results in a run-time
335 must have a nonnull default value specified.
336 </para>
338 <para>
339 A variable's default value is evaluated and assigned to the variable
340 each time the block is entered (not just once per function call).
343 time of the current function call, not the time when the function was
344 precompiled.
345 </para>
347 <para>
348 Examples:
349 <programlisting>
350 quantity integer DEFAULT 32;
351 url varchar := 'http://mysite.com';
352 user_id CONSTANT integer := 10;
353 </programlisting>
354 </para>
359 <para>
360 Parameters passed to functions are named with the identifiers
362 etc. Optionally, aliases can be declared for
364 parameter names for increased readability. Either the alias or the
365 numeric identifier can then be used to refer to the parameter value.
366 </para>
368 <para>
369 There are two ways to create an alias. The preferred way is to give a
371 for example:
372 <programlisting>
373 CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
374 BEGIN
375 RETURN subtotal * 0.06;
376 END;
377 $$ LANGUAGE plpgsql;
378 </programlisting>
379 The other way, which was the only way available before
381 declare an alias, using the declaration syntax
383 <synopsis>
385 </synopsis>
387 The same example in this style looks like:
388 <programlisting>
389 CREATE FUNCTION sales_tax(real) RETURNS real AS $$
390 DECLARE
391 subtotal ALIAS FOR $1;
392 BEGIN
393 RETURN subtotal * 0.06;
394 END;
395 $$ LANGUAGE plpgsql;
396 </programlisting>
397 </para>
399 <note>
400 <para>
401 These two examples are not perfectly equivalent. In the first case,
405 be qualified with that label, instead.)
406 </para>
407 </note>
409 <para>
410 Some more examples:
411 <programlisting>
412 CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
413 DECLARE
414 v_string ALIAS FOR $1;
415 index ALIAS FOR $2;
416 BEGIN
417 -- some computations using v_string and index here
418 END;
419 $$ LANGUAGE plpgsql;
422 CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
423 BEGIN
424 RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
425 END;
426 $$ LANGUAGE plpgsql;
427 </programlisting>
428 </para>
430 <para>
432 with output parameters, the output parameters are given
434 aliases in just the same way as the normal input parameters. An
435 output parameter is effectively a variable that starts out NULL;
436 it should be assigned to during the execution of the function.
437 The final value of the parameter is what is returned. For instance,
438 the sales-tax example could also be done this way:
440 <programlisting>
441 CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
442 BEGIN
443 tax := subtotal * 0.06;
444 END;
445 $$ LANGUAGE plpgsql;
446 </programlisting>
449 included it, but it would be redundant.
450 </para>
452 <para>
453 Output parameters are most useful when returning multiple values.
454 A trivial example is:
456 <programlisting>
457 CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
458 BEGIN
459 sum := x + y;
460 prod := x * y;
461 END;
462 $$ LANGUAGE plpgsql;
463 </programlisting>
466 effectively creates an anonymous record type for the function's
469 </para>
471 <para>
475 <programlisting>
476 CREATE FUNCTION extended_sales(p_itemno int) RETURNS TABLE(quantity int, total numeric) AS $$
477 BEGIN
478 RETURN QUERY SELECT quantity, quantity * price FROM sales WHERE itemno = p_itemno;
479 END;
480 $$ LANGUAGE plpgsql;
481 </programlisting>
484 parameters and specifying <literal>RETURNS SETOF
486 </para>
488 <para>
493 is created. Its data type is the actual return type of the function,
494 as deduced from the actual input types (see <xref
496 This allows the function to access its actual return type
499 the function, so it can be used to hold the return value if desired,
501 given an alias. For example, this function works on any data type
504 <programlisting>
505 CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
506 RETURNS anyelement AS $$
507 DECLARE
508 result ALIAS FOR $0;
509 BEGIN
510 result := v1 + v2 + v3;
511 RETURN result;
512 END;
513 $$ LANGUAGE plpgsql;
514 </programlisting>
515 </para>
517 <para>
518 The same effect can be had by declaring one or more output parameters as
519 polymorphic types. In this case the
521 parameters themselves serve the same purpose. For example:
523 <programlisting>
524 CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
525 OUT sum anyelement)
526 AS $$
527 BEGIN
528 sum := v1 + v2 + v3;
529 END;
530 $$ LANGUAGE plpgsql;
531 </programlisting>
532 </para>
533 </sect2>
538 <synopsis>
540 </synopsis>
542 <para>
544 table column. You can use this to declare variables that will hold
545 database values. For example, let's say you have a column named
547 table. To declare a variable with the same data type as
549 <programlisting>
550 user_id users.user_id%TYPE;
551 </programlisting>
552 </para>
554 <para>
556 type of the structure you are referencing, and most importantly,
557 if the data type of the referenced item changes in the future (for
560 to change your function definition.
561 </para>
563 <para>
565 functions, since the data types needed for internal variables can
566 change from one call to the next. Appropriate variables can be
568 arguments or result placeholders.
569 </para>
571 </sect2>
576 <synopsis>
577 <replaceable>name</replaceable> <replaceable>table_name</replaceable><literal>%ROWTYPE</literal>;
579 </synopsis>
581 <para>
585 query result, so long as that query's column set matches the
586 declared type of the variable.
587 The individual fields of the row value
588 are accessed using the usual dot notation, for example
590 </para>
592 <para>
593 A row variable can be declared to have the same type as the rows of
594 an existing table or view, by using the
596 notation; or it can be declared by giving a composite type's name.
597 (Since every table has an associated composite type of the same name,
601 </para>
603 <para>
604 Parameters to a function can be
605 composite types (complete table rows). In that case, the
606 corresponding identifier <literal>$<replaceable>n</replaceable></> will be a row variable, and fields can
608 </para>
610 <para>
611 Only the user-defined columns of a table row are accessible in a
612 row-type variable, not the OID or other system columns (because the
613 row could be from a view). The fields of the row type inherit the
614 table's field size or precision for data types such as
616 </para>
618 <para>
621 mentioned fields:
623 <programlisting>
624 CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
625 DECLARE
626 t2_row table2%ROWTYPE;
627 BEGIN
628 SELECT * INTO t2_row FROM table2 WHERE ... ;
629 RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
630 END;
631 $$ LANGUAGE plpgsql;
633 SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
634 </programlisting>
635 </para>
636 </sect2>
641 <synopsis>
643 </synopsis>
645 <para>
646 Record variables are similar to row-type variables, but they have no
647 predefined structure. They take on the actual row structure of the
649 of a record variable can change each time it is assigned to.
650 A consequence of this is that until a record variable is first assigned
651 to, it has no substructure, and any attempt to access a
652 field in it will draw a run-time error.
653 </para>
655 <para>
659 same concept as a record variable, even though such a function might
660 use a record variable to hold its result. In both cases the actual row
661 structure is unknown when the function is written, but for a function
663 calling query is parsed, whereas a record variable can change its row
664 structure on-the-fly.
665 </para>
666 </sect2>
671 <synopsis>
673 </synopsis>
675 <para>
677 name of a variable, record or row. This is primarily useful if
679 referenced by another name inside a trigger procedure. See also
681 </para>
683 <para>
684 Examples:
685 <programlisting>
686 RENAME id TO user_id;
687 RENAME this_var TO that_var;
688 </programlisting>
689 </para>
691 <note>
692 <para>
697 </para>
698 </note>
699 </sect2>
700 </sect1>
705 <para>
707 statements are processed using the server's main
710 <synopsis>
712 </synopsis>
714 feeding a query like
715 <synopsis>
717 </synopsis>
720 are replaced by parameters, as discussed in detail in
723 be prepared just once and then reused for subsequent
724 evaluations with different values of the variables. Thus, what
725 really happens on first use of an expression is essentially a
728 <programlisting>
729 IF x < y THEN ...
730 </programlisting>
731 what happens behind the scenes is
732 <programlisting>
734 </programlisting>
738 parameter values.
739 The query plan prepared in this way is saved for the life of the database
740 connection, as described in
743 they are useful to know when trying to diagnose a problem.
744 </para>
745 </sect1>
750 <para>
751 In this section and the following ones, we describe all the statement
752 types that are explicitly understood by
754 Anything not recognized as one of these statement types is presumed
755 to be an SQL command and is sent to the main database engine to execute,
758 </para>
763 <para>
765 variable or row/record field is written as:
766 <synopsis>
768 </synopsis>
769 As explained above, the expression in such a statement is evaluated
771 database engine. The expression must yield a single value.
772 </para>
774 <para>
775 If the expression's result data type doesn't match the variable's
776 data type, or the variable has a specific size/precision
779 the result type's output-function and
780 the variable type's input-function. Note that this could potentially
781 result in run-time errors generated by the input function, if the
782 string form of the result value is not acceptable to the input function.
783 </para>
785 <para>
786 Examples:
787 <programlisting>
788 tax := subtotal * 0.06;
789 my_record.user_id := 20;
790 </programlisting>
791 </para>
792 </sect2>
797 <para>
798 For any SQL command that does not return rows, for example
801 just by writing the command.
802 </para>
804 <para>
806 in the command text is replaced by a parameter symbol, and then the
807 current value of the variable is provided as the parameter value
808 at run time. This is exactly like the processing described earlier
810 As an example, if you write:
811 <programlisting>
812 DECLARE
813 key TEXT;
814 delta INTEGER;
815 BEGIN
816 ...
817 UPDATE mytab SET val = val + delta WHERE id = key;
818 </programlisting>
819 the command text seen by the main SQL engine will look like:
820 <programlisting>
822 </programlisting>
823 Although you don't normally have to think about this, it's helpful
824 to know it when you need to make sense of syntax-error messages.
825 </para>
827 <caution>
828 <para>
830 matching one of the function's declared variables; it is not bright
831 enough to know whether that's what you meant! Thus, it is a bad idea
832 to use a variable name that is the same as any table, column, or
833 function name that you need to reference in commands within the
835 </para>
836 </caution>
838 <para>
839 When executing a SQL command in this way,
841 and re-uses the plan on subsequent executions, for the life of
842 the database connection. The implications of this are discussed
844 </para>
846 <para>
848 query but discard the result, for example when calling a function
849 that has side-effects but no useful result value. To do
853 <synopsis>
855 </synopsis>
862 substituted into the query just as for commands that return no result,
863 and the plan is cached in the same way. Also, the special variable
865 least one row, or false if it produced no rows (see
867 </para>
869 <note>
870 <para>
872 would accomplish this result, but at
873 present the only accepted way to do it is
877 next section.
878 </para>
879 </note>
881 <para>
882 An example:
883 <programlisting>
884 PERFORM create_mv('cs_session_page_requests_mv', my_query);
885 </programlisting>
886 </para>
887 </sect2>
895 </indexterm>
900 </indexterm>
902 <para>
903 The result of a SQL command yielding a single row (possibly of multiple
904 columns) can be assigned to a record variable, row-type variable, or list
905 of scalar variables. This is done by writing the base SQL command and
908 <synopsis>
909 SELECT <replaceable>select_expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable> FROM ...;
910 INSERT ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
911 UPDATE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
912 DELETE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
913 </synopsis>
916 variable, or a comma-separated list of simple variables and
917 record/row fields.
919 substituted into the rest of the query, and the plan is cached,
920 just as described above for commands that do not return rows.
927 </para>
929 <tip>
930 <para>
934 target is a newly created table. If you want to create a table from a
938 </para>
939 </tip>
941 <para>
942 If a row or a variable list is used as target, the query's result columns
943 must exactly match the structure of the target as to number and data
944 types, or a run-time error
945 occurs. When a record variable is the target, it automatically
946 configures itself to the row type of the query result columns.
947 </para>
949 <para>
951 command. Customarily it is written either just before or just after
954 command types. It is recommended that you follow this convention
956 stricter in future versions.
957 </para>
959 <para>
962 row returned by the query, or to nulls if the query returned no rows.
965 after the first row are discarded.
968 determine whether a row was returned:
970 <programlisting>
971 SELECT * INTO myrec FROM emp WHERE empname = myname;
972 IF NOT FOUND THEN
973 RAISE EXCEPTION 'employee % not found', myname;
974 END IF;
975 </programlisting>
978 return exactly one row or a run-time error will be reported, either
980 (more than one row). You can use an exception block if you wish
981 to catch the error, for example:
983 <programlisting>
984 BEGIN
985 SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
986 EXCEPTION
987 WHEN NO_DATA_FOUND THEN
988 RAISE EXCEPTION 'employee % not found', myname;
989 WHEN TOO_MANY_ROWS THEN
990 RAISE EXCEPTION 'employee % not unique', myname;
991 END;
992 </programlisting>
995 </para>
997 <para>
1000 an error for more than one returned row, even when
1003 which affected row should be returned.
1004 </para>
1006 <note>
1007 <para>
1010 </para>
1011 </note>
1013 <para>
1014 To handle cases where you need to process multiple result rows
1016 </para>
1018 </sect2>
1023 <para>
1024 Oftentimes you will want to generate dynamic commands inside your
1026 that will involve different tables or different data types each
1028 normal attempts to cache plans for commands (as discussed in
1030 scenarios. To handle this sort of problem, the
1033 <synopsis>
1034 EXECUTE <replaceable class="command">command-string</replaceable> <optional> INTO <optional>STRICT</optional> <replaceable>target</replaceable> </optional> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;
1035 </synopsis>
1040 is a record variable, a row variable, or a comma-separated list of
1041 simple variables and record/row fields, into which the results of
1043 supply values to be inserted into the command.
1044 </para>
1046 <para>
1048 computed command string. Any required variable values must be inserted
1049 in the command string as it is constructed; or you can use parameters
1050 as described below.
1051 </para>
1053 <para>
1054 Also, there is no plan caching for commands executed via
1056 command is prepared each time the statement is run. Thus the command
1057 string can be dynamically created within the function to perform
1058 actions on different tables and columns.
1059 </para>
1061 <para>
1063 a SQL command returning rows should be assigned. If a row
1064 or variable list is provided, it must exactly match the structure
1065 of the query's results (when a
1066 record variable is used, it will configure itself to match the
1067 result structure automatically). If multiple rows are returned,
1069 variable. If no rows are returned, NULL is assigned to the
1071 clause is specified, the query results are discarded.
1072 </para>
1074 <para>
1076 unless the query produces exactly one row.
1077 </para>
1079 <para>
1080 The command string can use parameter values, which are referenced
1083 clause. This method is often preferable to inserting data values
1084 into the command string as text: it avoids run-time overhead of
1085 converting the values to text and back, and it is much less prone
1086 to SQL-injection attacks since there is no need for quoting or escaping.
1087 An example is:
1088 <programlisting>
1090 INTO c
1091 USING checked_user, checked_date;
1092 </programlisting>
1094 Note that parameter symbols can only be used for data values
1095 — if you want to use dynamically determined table or column
1096 names, you must insert them into the command string textually.
1097 For example, if the preceding query needed to be done against a
1098 dynamically selected table, you could do this:
1099 <programlisting>
1100 EXECUTE 'SELECT count(*) FROM '
1101 || tabname::regclass
1103 INTO c
1104 USING checked_user, checked_date;
1105 </programlisting>
1106 </para>
1108 <para>
1111 functionally equivalent to just writing the command directly in
1115 the command on each execution, generating a plan that is specific
1116 to the current parameter values; whereas
1118 and caches it for re-use. In situations where the best plan depends
1120 significantly faster; while when the plan is not sensitive to parameter
1121 values, re-planning will be a waste.
1122 </para>
1124 <para>
1128 itself.
1129 </para>
1131 <note>
1132 <para>
1136 statement supported by the
1140 </para>
1141 </note>
1146 <indexterm>
1149 </indexterm>
1151 <indexterm>
1154 </indexterm>
1156 <indexterm>
1159 </indexterm>
1161 <para>
1162 When working with dynamic commands you will often have to handle escaping
1163 of single quotes. The recommended method for quoting fixed text in your
1164 function body is dollar quoting. (If you have legacy code that does
1165 not use dollar quoting, please refer to the
1167 some effort when translating said code to a more reasonable scheme.)
1168 </para>
1170 <para>
1171 Dynamic values that are to be inserted into the constructed
1172 query require careful handling since they might themselves contain
1173 quote characters.
1174 An example (this assumes that you are using dollar quoting for the
1175 function as a whole, so the quote marks need not be doubled):
1176 <programlisting>
1177 EXECUTE 'UPDATE tbl SET '
1178 || quote_ident(colname)
1179 || ' = '
1180 || quote_literal(newvalue)
1181 || ' WHERE key = '
1182 || quote_literal(keyvalue);
1183 </programlisting>
1184 </para>
1186 <para>
1187 This example demonstrates the use of the
1191 or table identifiers should be passed through
1193 Expressions containing values that should be literal strings in the
1195 These functions take the appropriate steps to return the input text
1196 enclosed in double or single quotes respectively, with any embedded
1197 special characters properly escaped.
1198 </para>
1200 <para>
1209 For example,
1210 <programlisting>
1211 EXECUTE 'UPDATE tbl SET '
1212 || quote_ident(colname)
1213 || ' = '
1214 || quote_nullable(newvalue)
1215 || ' WHERE key = '
1216 || quote_nullable(keyvalue);
1217 </programlisting>
1218 If you are dealing with values that might be null, you should usually
1220 </para>
1222 <para>
1223 As always, care must be taken to ensure that null values in a query do
1225 <programlisting>
1226 'WHERE key = ' || quote_nullable(keyvalue)
1227 </programlisting>
1230 is always null. If you wish null to work like an ordinary key value,
1231 you would need to rewrite the above as
1232 <programlisting>
1233 'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
1234 </programlisting>
1239 </para>
1241 <para>
1242 Note that dollar quoting is only useful for quoting fixed text.
1243 It would be a very bad idea to try to write this example as:
1244 <programlisting>
1245 EXECUTE 'UPDATE tbl SET '
1246 || quote_ident(colname)
1247 || ' = $$'
1248 || newvalue
1249 || '$$ WHERE key = '
1250 || quote_literal(keyvalue);
1251 </programlisting>
1254 apply to any other dollar-quoting delimiter you might pick.
1255 So, to safely quote text that is not known in advance, you
1258 </para>
1259 </example>
1261 <para>
1262 A much larger example of a dynamic command and
1266 </para>
1267 </sect2>
1272 <para>
1273 There are several ways to determine the effect of a command. The
1275 command, which has the form:
1277 <synopsis>
1278 GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
1279 </synopsis>
1281 This command allows retrieval of system status indicators. Each
1283 value to be assigned to the specified variable (which should be
1284 of the right data type to receive it). The currently available
1288 the OID of the last row inserted by the most recent
1291 table containing OIDs.
1292 </para>
1294 <para>
1295 An example:
1296 <programlisting>
1297 GET DIAGNOSTICS integer_var = ROW_COUNT;
1298 </programlisting>
1299 </para>
1301 <para>
1302 The second method to determine the effects of a command is to check the
1306 It is set by each of the following types of statements:
1307 <itemizedlist>
1308 <listitem>
1309 <para>
1312 row is returned.
1313 </para>
1314 </listitem>
1315 <listitem>
1316 <para>
1318 true if it produces (and discards) one or more rows, false if
1319 no row is produced.
1320 </para>
1321 </listitem>
1322 <listitem>
1323 <para>
1326 row is affected, false if no row is affected.
1327 </para>
1328 </listitem>
1329 <listitem>
1330 <para>
1332 true if it returns a row, false if no row is returned.
1333 </para>
1334 </listitem>
1335 <listitem>
1336 <para>
1338 true if it successfully repositions the cursor, false otherwise.
1339 </para>
1340 </listitem>
1342 <listitem>
1343 <para>
1345 if it iterates one or more times, else false. This applies to
1354 execution of other statements within the loop body.
1355 </para>
1356 </listitem>
1357 <listitem>
1358 <para>
1361 true if the query returns at least one row, false if no row
1362 is returned.
1363 </para>
1364 </listitem>
1365 </itemizedlist>
1369 affect only the current function.
1370 </para>
1372 </sect2>
1377 <para>
1378 Sometimes a placeholder statement that does nothing is useful.
1379 For example, it can indicate that one arm of an if/then/else
1380 chain is deliberately empty. For this purpose, use the
1383 <synopsis>
1384 NULL;
1385 </synopsis>
1386 </para>
1388 <para>
1389 For example, the following two fragments of code are equivalent:
1390 <programlisting>
1391 BEGIN
1392 y := x / 0;
1393 EXCEPTION
1394 WHEN division_by_zero THEN
1395 NULL; -- ignore the error
1396 END;
1397 </programlisting>
1399 <programlisting>
1400 BEGIN
1401 y := x / 0;
1402 EXCEPTION
1403 WHEN division_by_zero THEN -- ignore the error
1404 END;
1405 </programlisting>
1406 Which is preferable is a matter of taste.
1407 </para>
1409 <note>
1410 <para>
1411 In Oracle's PL/SQL, empty statement lists are not allowed, and so
1414 just write nothing, instead.
1415 </para>
1416 </note>
1418 </sect2>
1419 </sect1>
1424 <para>
1425 Control structures are probably the most useful (and
1429 flexible and powerful way.
1430 </para>
1435 <para>
1436 There are two commands available that allow you to return data
1438 NEXT</command>.
1439 </para>
1441 <sect3>
1444 <synopsis>
1446 </synopsis>
1448 <para>
1450 function and returns the value of
1453 not return a set.
1454 </para>
1456 <para>
1457 When returning a scalar type, any expression can be used. The
1458 expression's result will be automatically cast into the
1459 function's return type as described for assignments. To return a
1460 composite (row) value, you must write a record or row variable
1462 </para>
1464 <para>
1465 If you declared the function with output parameters, write just
1467 of the output parameter variables will be returned.
1468 </para>
1470 <para>
1473 early; but do not write an expression following
1475 </para>
1477 <para>
1478 The return value of a function cannot be left undefined. If
1479 control reaches the end of the top-level block of the function
1481 error will occur. This restriction does not apply to functions
1484 automatically executed if the top-level block finishes.
1485 </para>
1486 </sect3>
1488 <sect3>
1490 <indexterm>
1493 </indexterm>
1494 <indexterm>
1497 </indexterm>
1499 <synopsis>
1502 RETURN QUERY EXECUTE <replaceable class="command">command-string</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;
1503 </synopsis>
1505 <para>
1508 to follow is slightly different. In that case, the individual
1509 items to return are specified by a sequence of <command>RETURN
1512 is used to indicate that the function has finished executing.
1514 composite data types; with a composite result type, an entire
1517 a query to the function's result set. <command>RETURN
1519 intermixed in a single set-returning function, in which case
1520 their results will be concatenated.
1521 </para>
1523 <para>
1526 they simply append zero or more rows to the function's result
1527 set. Execution then continues with the next statement in the
1530 QUERY</command> commands are executed, the result set is built
1532 argument, causes control to exit the function (or you can just
1533 let control reach the end of the function).
1534 </para>
1536 <para>
1539 query to be executed dynamically. Parameter expressions can
1542 </para>
1544 <para>
1545 If you declared the function with output parameters, write just
1547 execution, the current values of the output parameter
1548 variable(s) will be saved for eventual return as a row of the
1549 result. Note that you must declare the function as returning
1552 when there is just one output parameter of type
1554 function with output parameters.
1555 </para>
1557 <para>
1558 Here is an example of a function using <command>RETURN
1559 NEXT</command>:
1561 <programlisting>
1562 CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
1566 CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
1567 $BODY$
1568 DECLARE
1569 r foo%rowtype;
1570 BEGIN
1571 FOR r IN SELECT * FROM foo
1573 LOOP
1574 -- can do some processing here
1575 RETURN NEXT r; -- return current row of SELECT
1576 END LOOP;
1577 RETURN;
1578 END
1579 $BODY$
1580 LANGUAGE 'plpgsql' ;
1582 SELECT * FROM getallfoo();
1583 </programlisting>
1584 </para>
1586 <note>
1587 <para>
1590 before returning from the function, as discussed above. That
1592 very large result set, performance might be poor: data will be
1593 written to disk to avoid memory exhaustion, but the function
1594 itself will not return until the entire result set has been
1596 allow users to define set-returning functions
1597 that do not have this limitation. Currently, the point at
1598 which data begins being written to disk is controlled by the
1600 configuration variable. Administrators who have sufficient
1601 memory to store larger result sets in memory should consider
1602 increasing this parameter.
1603 </para>
1604 </note>
1605 </sect3>
1606 </sect2>
1611 <para>
1613 alternative commands based on certain conditions.
1615 <itemizedlist>
1616 <listitem>
1618 </listitem>
1619 <listitem>
1621 </listitem>
1622 <listitem>
1624 </listitem>
1625 </itemizedlist>
1628 <itemizedlist>
1629 <listitem>
1631 </listitem>
1632 <listitem>
1634 </listitem>
1635 </itemizedlist>
1636 </para>
1638 <sect3>
1641 <synopsis>
1644 END IF;
1645 </synopsis>
1647 <para>
1651 executed if the condition is true. Otherwise, they are
1652 skipped.
1653 </para>
1655 <para>
1656 Example:
1657 <programlisting>
1659 UPDATE users SET email = v_email WHERE user_id = v_user_id;
1660 END IF;
1661 </programlisting>
1662 </para>
1663 </sect3>
1665 <sect3>
1668 <synopsis>
1671 ELSE
1673 END IF;
1674 </synopsis>
1676 <para>
1679 alternative set of statements that should be executed if the
1680 condition is not true. (Note this includes the case where the
1681 condition evaluates to NULL.)
1682 </para>
1684 <para>
1685 Examples:
1686 <programlisting>
1687 IF parentid IS NULL OR parentid = ''
1688 THEN
1689 RETURN fullname;
1690 ELSE
1691 RETURN hp_true_filename(parentid) || '/' || fullname;
1692 END IF;
1693 </programlisting>
1695 <programlisting>
1697 INSERT INTO users_count (count) VALUES (v_count);
1698 RETURN 't';
1699 ELSE
1700 RETURN 'f';
1701 END IF;
1702 </programlisting>
1703 </para>
1704 </sect3>
1706 <sect3>
1709 <synopsis>
1716 ...
1717 </optional>
1718 </optional>
1719 <optional> ELSE
1721 END IF;
1722 </synopsis>
1724 <para>
1725 Sometimes there are more than just two alternatives.
1727 method of checking several alternatives in turn.
1729 until the first one that is true is found. Then the
1730 associated statement(s) are executed, after which control
1735 </para>
1737 <para>
1738 Here is an example:
1740 <programlisting>
1741 IF number = 0 THEN
1742 result := 'zero';
1744 result := 'positive';
1746 result := 'negative';
1747 ELSE
1748 -- hmm, the only other possibility is that number is null
1749 result := 'NULL';
1750 END IF;
1751 </programlisting>
1752 </para>
1754 <para>
1757 </para>
1759 <para>
1760 An alternative way of accomplishing the same task is to nest
1762 following example:
1764 <programlisting>
1765 IF demo_row.sex = 'm' THEN
1766 pretty_sex := 'man';
1767 ELSE
1768 IF demo_row.sex = 'f' THEN
1769 pretty_sex := 'woman';
1770 END IF;
1771 END IF;
1772 </programlisting>
1773 </para>
1775 <para>
1779 </para>
1780 </sect3>
1782 <sect3>
1785 <synopsis>
1787 WHEN <replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> <optional> ... </optional></optional> THEN
1789 <optional> WHEN <replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> <optional> ... </optional></optional> THEN
1791 ... </optional>
1792 <optional> ELSE
1794 END CASE;
1795 </synopsis>
1797 <para>
1800 is evaluated (once) and successively compared to each
1802 If a match is found, then the corresponding
1809 </para>
1811 <para>
1812 Here is a simple example:
1814 <programlisting>
1815 CASE x
1817 msg := 'one or two';
1818 ELSE
1819 msg := 'other value than one or two';
1820 END CASE;
1821 </programlisting>
1822 </para>
1823 </sect3>
1825 <sect3>
1828 <synopsis>
1829 CASE
1834 ... </optional>
1835 <optional> ELSE
1837 END CASE;
1838 </synopsis>
1840 <para>
1852 </para>
1854 <para>
1855 Here is an example:
1857 <programlisting>
1858 CASE
1860 msg := 'value is between zero and ten';
1862 msg := 'value is between eleven and twenty';
1863 END CASE;
1864 </programlisting>
1865 </para>
1867 <para>
1871 than doing nothing.
1872 </para>
1874 </sect3>
1875 </sect2>
1883 </indexterm>
1885 <para>
1889 function to repeat a series of commands.
1890 </para>
1892 <sect3>
1895 <synopsis>
1897 LOOP
1900 </synopsis>
1902 <para>
1908 specify which loop those statements refer to.
1909 </para>
1910 </sect3>
1912 <sect3>
1915 <indexterm>
1918 </indexterm>
1920 <synopsis>
1921 EXIT <optional> <replaceable>label</replaceable> </optional> <optional> WHEN <replaceable>boolean-expression</replaceable> </optional>;
1922 </synopsis>
1924 <para>
1926 loop is terminated and the statement following <literal>END
1928 is given, it must be the label of the current or some outer
1929 level of nested loop or block. Then the named loop or block is
1930 terminated and control continues with the statement after the
1932 </para>
1934 <para>
1938 </para>
1940 <para>
1942 not limited to use with unconditional loops.
1943 </para>
1945 <para>
1946 When used with a
1948 control to the next statement after the end of the block.
1949 Note that a label must be used for this purpose; an unlabelled
1955 </para>
1957 <para>
1958 Examples:
1959 <programlisting>
1960 LOOP
1961 -- some computations
1963 EXIT; -- exit loop
1964 END IF;
1965 END LOOP;
1967 LOOP
1968 -- some computations
1970 END LOOP;
1973 BEGIN
1974 -- some computations
1976 EXIT ablock; -- causes exit from the BEGIN block
1977 END IF;
1979 END;
1980 </programlisting>
1981 </para>
1982 </sect3>
1984 <sect3>
1987 <indexterm>
1990 </indexterm>
1992 <synopsis>
1993 CONTINUE <optional> <replaceable>label</replaceable> </optional> <optional> WHEN <replaceable>boolean-expression</replaceable> </optional>;
1994 </synopsis>
1996 <para>
1998 the innermost loop is begun. That is, all statements remaining
1999 in the loop body are skipped, and control returns
2000 to the loop control expression (if any) to determine whether
2001 another loop iteration is needed.
2003 specifies the label of the loop whose execution will be
2004 continued.
2005 </para>
2007 <para>
2010 true. Otherwise, control passes to the statement after
2012 </para>
2014 <para>
2016 is not limited to use with unconditional loops.
2017 </para>
2019 <para>
2020 Examples:
2021 <programlisting>
2022 LOOP
2023 -- some computations
2027 END LOOP;
2028 </programlisting>
2029 </para>
2030 </sect3>
2033 <sect3>
2036 <indexterm>
2039 </indexterm>
2041 <synopsis>
2046 </synopsis>
2048 <para>
2050 sequence of statements so long as the
2052 evaluates to true. The expression is checked just before
2053 each entry to the loop body.
2054 </para>
2056 <para>
2057 For example:
2058 <programlisting>
2060 -- some computations here
2061 END LOOP;
2063 WHILE NOT done LOOP
2064 -- some computations here
2065 END LOOP;
2066 </programlisting>
2067 </para>
2068 </sect3>
2073 <synopsis>
2075 FOR <replaceable>name</replaceable> IN <optional> REVERSE </optional> <replaceable>expression</replaceable> .. <replaceable>expression</replaceable> <optional> BY <replaceable>expression</replaceable> </optional> LOOP
2078 </synopsis>
2080 <para>
2082 of integer values. The variable
2085 definition of the variable name is ignored within the loop).
2086 The two expressions giving
2087 the lower and upper bound of the range are evaluated once when entering
2090 clause, which again is evaluated once on loop entry.
2092 subtracted, rather than added, after each iteration.
2093 </para>
2095 <para>
2097 <programlisting>
2100 END LOOP;
2104 END LOOP;
2108 END LOOP;
2109 </programlisting>
2110 </para>
2112 <para>
2113 If the lower bound is greater than the upper bound (or less than,
2115 executed at all. No error is raised.
2116 </para>
2118 <para>
2121 referenced with a qualified name, using that
2123 </para>
2124 </sect3>
2125 </sect2>
2130 <para>
2132 the results of a query and manipulate that data
2133 accordingly. The syntax is:
2134 <synopsis>
2139 </synopsis>
2141 or comma-separated list of scalar variables.
2144 executed for each row. Here is an example:
2145 <programlisting>
2146 CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
2147 DECLARE
2148 mviews RECORD;
2149 BEGIN
2150 PERFORM cs_log('Refreshing materialized views...');
2152 FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
2154 -- Now "mviews" has one record from cs_materialized_views
2156 PERFORM cs_log('Refreshing materialized view ' || quote_ident(mviews.mv_name) || ' ...');
2157 EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
2158 EXECUTE 'INSERT INTO ' || quote_ident(mviews.mv_name) || ' ' || mviews.mv_query;
2159 END LOOP;
2161 PERFORM cs_log('Done refreshing materialized views.');
2162 RETURN 1;
2163 END;
2164 $$ LANGUAGE plpgsql;
2165 </programlisting>
2168 assigned row value is still accessible after the loop.
2169 </para>
2171 <para>
2173 statement can be any SQL command that returns rows to the caller:
2178 </para>
2180 <para>
2182 and the query plan is cached for possible re-use, as discussed in
2185 </para>
2187 <para>
2189 rows:
2190 <synopsis>
2192 FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional> LOOP
2195 </synopsis>
2196 This is like the previous form, except that the source query
2197 is specified as a string expression, which is evaluated and replanned
2199 choose the speed of a preplanned query or the flexibility of a dynamic
2203 </para>
2205 <para>
2206 Another way to specify the query whose results should be iterated
2207 through is to declare it as a cursor. This is described in
2209 </para>
2210 </sect2>
2215 <indexterm>
2218 </indexterm>
2220 <para>
2222 function aborts execution of the function, and indeed of the
2223 surrounding transaction as well. You can trap errors and recover
2228 <synopsis>
2230 <optional> DECLARE
2232 BEGIN
2234 EXCEPTION
2235 WHEN <replaceable>condition</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
2237 <optional> WHEN <replaceable>condition</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
2239 ... </optional>
2240 END;
2241 </synopsis>
2242 </para>
2244 <para>
2245 If no error occurs, this form of block simply executes all the
2252 matching the error that occurred. If a match is found, the
2254 executed, and then control passes to the next statement after
2257 the error can be caught by an enclosing block with
2259 of the function.
2260 </para>
2262 <para>
2265 name matches any error within its category. The special
2269 not case-sensitive. Also, an error condition can be specified
2271 <programlisting>
2272 WHEN division_by_zero THEN ...
2273 WHEN SQLSTATE '22012' THEN ...
2274 </programlisting>
2275 </para>
2277 <para>
2278 If a new error occurs within the selected
2282 </para>
2284 <para>
2287 remain as they were when the error occurred, but all changes
2288 to persistent database state within the block are rolled back.
2289 As an example, consider this fragment:
2291 <programlisting>
2292 INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
2293 BEGIN
2294 UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
2295 x := x + 1;
2296 y := x / 0;
2297 EXCEPTION
2298 WHEN division_by_zero THEN
2299 RAISE NOTICE 'caught division_by_zero';
2300 RETURN x;
2301 END;
2302 </programlisting>
2310 block is not rolled back, however, so the end result is that the database
2312 </para>
2314 <tip>
2315 <para>
2317 more expensive to enter and exit than a block without one. Therefore,
2319 </para>
2320 </tip>
2322 <para>
2324 variable contains the error code that corresponds to the
2325 exception that was raised (refer to <xref
2328 error message associated with the exception. These variables are
2329 undefined outside exception handlers.
2330 </para>
2334 <para>
2336 This example uses exception handling to perform either
2339 <programlisting>
2340 CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
2342 CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
2343 $$
2344 BEGIN
2345 LOOP
2346 -- first try to update the key
2347 UPDATE db SET b = data WHERE a = key;
2348 IF found THEN
2349 RETURN;
2350 END IF;
2351 -- not there, so try to insert the key
2352 -- if someone else inserts the same key concurrently,
2353 -- we could get a unique-key failure
2354 BEGIN
2355 INSERT INTO db(a,b) VALUES (key, data);
2356 RETURN;
2357 EXCEPTION WHEN unique_violation THEN
2358 -- do nothing, and loop to try the UPDATE again
2359 END;
2360 END LOOP;
2361 END;
2362 $$
2363 LANGUAGE plpgsql;
2365 SELECT merge_db(1, 'david');
2366 SELECT merge_db(1, 'dennis');
2367 </programlisting>
2369 </para>
2370 </example>
2371 </sect2>
2372 </sect1>
2380 </indexterm>
2382 <para>
2383 Rather than executing a whole query at once, it is possible to set
2385 the query result a few rows at a time. One reason for doing this is
2386 to avoid memory overrun when the result contains a large number of
2389 internally to avoid memory problems.) A more interesting usage is to
2390 return a reference to a cursor that a function has created, allowing the
2391 caller to read the rows. This provides an efficient way to return
2392 large row sets from functions.
2393 </para>
2398 <para>
2400 cursor variables, which are always of the special data type
2403 Another way is to use the cursor declaration syntax,
2404 which in general is:
2405 <synopsis>
2406 <replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>;
2407 </synopsis>
2412 fetches will be rejected; if neither specification appears, it is
2413 query-dependent whether backward fetches will be allowed.
2417 replaced by parameter values in the given query. The actual
2418 values to substitute for these names will be specified later,
2419 when the cursor is opened.
2420 </para>
2421 <para>
2422 Some examples:
2423 <programlisting>
2424 DECLARE
2425 curs1 refcursor;
2426 curs2 CURSOR FOR SELECT * FROM tenk1;
2427 curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
2428 </programlisting>
2430 but the first can be used with any query, while the second has
2433 replaced by an integer parameter value when the cursor is opened.)
2436 any particular query.
2437 </para>
2438 </sect2>
2443 <para>
2444 Before a cursor can be used to retrieve rows, it must be
2448 cursor variables while the third uses a bound cursor variable.
2449 </para>
2451 <note>
2452 <para>
2453 Bound cursor variables can also be used without explicitly opening the cursor,
2456 </para>
2457 </note>
2459 <sect3>
2462 <synopsis>
2463 OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR <replaceable>query</replaceable>;
2464 </synopsis>
2466 <para>
2467 The cursor variable is opened and given the specified query to
2468 execute. The cursor cannot be open already, and it must have been
2469 declared as an unbound cursor variable (that is, as a simple
2473 is treated in the same way as other SQL commands in
2475 variable names are substituted, and the query plan is cached for
2477 variable is substituted into the cursor query, the value that is
2479 subsequent changes to the variable will not affect the cursor's
2480 behavior.
2482 options have the same meanings as for a bound cursor.
2483 </para>
2485 <para>
2486 An example:
2487 <programlisting>
2488 OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
2489 </programlisting>
2490 </para>
2491 </sect3>
2493 <sect3>
2496 <synopsis>
2497 OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR EXECUTE <replaceable class="command">query_string</replaceable>;
2498 </synopsis>
2500 <para>
2501 The cursor variable is opened and given the specified query to
2502 execute. The cursor cannot be open already, and it must have been
2503 declared as an unbound cursor variable (that is, as a simple
2506 command. As usual, this gives flexibility so the query plan can vary
2508 and it also means that variable substitution is not done on the
2509 command string.
2512 cursor.
2513 </para>
2515 <para>
2516 An example:
2517 <programlisting>
2518 OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
2519 </programlisting>
2520 </para>
2521 </sect3>
2523 <sect3>
2526 <synopsis>
2527 OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <replaceable>argument_values</replaceable> ) </optional>;
2528 </synopsis>
2530 <para>
2532 variable whose query was bound to it when it was declared. The
2533 cursor cannot be open already. A list of actual argument value
2534 expressions must appear if and only if the cursor was declared to
2535 take arguments. These values will be substituted in the query.
2536 The query plan for a bound cursor is always considered cacheable;
2540 behavior was already determined.
2541 </para>
2543 <para>
2544 Note that because variable substitution is done on the bound
2545 cursor's query, there are two ways to pass values into the cursor:
2548 in the query. However, only variables declared before the bound
2549 cursor was declared will be substituted into it. In either case
2550 the value to be passed is determined at the time of the
2552 </para>
2554 <para>
2555 Examples:
2556 <programlisting>
2557 OPEN curs2;
2558 OPEN curs3(42);
2559 </programlisting>
2560 </para>
2561 </sect3>
2562 </sect2>
2567 <para>
2568 Once a cursor has been opened, it can be manipulated with the
2569 statements described here.
2570 </para>
2572 <para>
2573 These manipulations need not occur in the same function that
2575 value out of a function and let the caller operate on the cursor.
2577 of a so-called portal containing the active query for the cursor. This name
2579 and so on, without disturbing the portal.)
2580 </para>
2582 <para>
2583 All portals are implicitly closed at transaction end. Therefore
2585 only until the end of the transaction.
2586 </para>
2588 <sect3>
2591 <synopsis>
2592 FETCH <optional> <replaceable>direction</replaceable> { FROM | IN } </optional> <replaceable>cursor</replaceable> INTO <replaceable>target</replaceable>;
2593 </synopsis>
2595 <para>
2597 cursor into a target, which might be a row variable, a record
2598 variable, or a comma-separated list of simple variables, just like
2600 target is set to NULL(s). As with <command>SELECT
2602 be checked to see whether a row was obtained or not.
2603 </para>
2605 <para>
2609 more than one row; namely, it can be
2621 backward are likely to fail unless the cursor was declared or opened
2623 </para>
2625 <para>
2627 variable that references an open cursor portal.
2628 </para>
2630 <para>
2631 Examples:
2632 <programlisting>
2633 FETCH curs1 INTO rowvar;
2634 FETCH curs2 INTO foo, bar, baz;
2635 FETCH LAST FROM curs3 INTO x, y;
2636 FETCH RELATIVE -2 FROM curs4 INTO x;
2637 </programlisting>
2638 </para>
2639 </sect3>
2641 <sect3>
2644 <synopsis>
2645 MOVE <optional> <replaceable>direction</replaceable> { FROM | IN } </optional> <replaceable>cursor</replaceable>;
2646 </synopsis>
2648 <para>
2652 cursor and does not return the row moved to. As with <command>SELECT
2654 be checked to see whether there was a next row to move to.
2655 </para>
2657 <para>
2671 backward are likely to fail unless the cursor was declared or opened
2673 </para>
2675 <para>
2676 Examples:
2677 <programlisting>
2678 MOVE curs1;
2679 MOVE LAST FROM curs3;
2680 MOVE RELATIVE -2 FROM curs4;
2681 </programlisting>
2682 </para>
2683 </sect3>
2685 <sect3>
2688 <synopsis>
2689 UPDATE <replaceable>table</replaceable> SET ... WHERE CURRENT OF <replaceable>cursor</replaceable>;
2690 DELETE FROM <replaceable>table</replaceable> WHERE CURRENT OF <replaceable>cursor</replaceable>;
2691 </synopsis>
2693 <para>
2694 When a cursor is positioned on a table row, that row can be updated
2695 or deleted using the cursor to identify the row. There are
2696 restrictions on what the cursor's query can be (in particular,
2698 cursor. For additional information see the
2700 reference page.
2701 </para>
2703 <para>
2704 An example:
2705 <programlisting>
2706 UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
2707 </programlisting>
2708 </para>
2709 </sect3>
2711 <sect3>
2714 <synopsis>
2716 </synopsis>
2718 <para>
2720 cursor. This can be used to release resources earlier than end of
2721 transaction, or to free up the cursor variable to be opened again.
2722 </para>
2724 <para>
2725 An example:
2726 <programlisting>
2727 CLOSE curs1;
2728 </programlisting>
2729 </para>
2730 </sect3>
2732 <sect3>
2735 <para>
2737 caller. This is useful to return multiple rows or columns,
2738 especially with very large result sets. To do this, the function
2739 opens the cursor and returns the cursor name to the caller (or simply
2740 opens the cursor using a portal name specified by or otherwise known
2741 to the caller). The caller can then fetch rows from the cursor. The
2742 cursor can be closed by the caller, or it will be closed automatically
2743 when the transaction closes.
2744 </para>
2746 <para>
2747 The portal name used for a cursor can be specified by the
2748 programmer or automatically generated. To specify a portal name,
2754 conflict with any existing portal, and assigns it to the
2756 </para>
2758 <note>
2759 <para>
2760 A bound cursor variable is initialized to the string value
2761 representing its name, so that the portal name is the same as
2762 the cursor variable name, unless the programmer overrides it
2763 by assignment before opening the cursor. But an unbound cursor
2764 variable defaults to the null value initially, so it will receive
2765 an automatically-generated unique name, unless overridden.
2766 </para>
2767 </note>
2769 <para>
2770 The following example shows one way a cursor name can be supplied by
2771 the caller:
2773 <programlisting>
2774 CREATE TABLE test (col text);
2775 INSERT INTO test VALUES ('123');
2777 CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
2778 BEGIN
2779 OPEN $1 FOR SELECT col FROM test;
2780 RETURN $1;
2781 END;
2782 ' LANGUAGE plpgsql;
2784 BEGIN;
2785 SELECT reffunc('funccursor');
2786 FETCH ALL IN funccursor;
2787 COMMIT;
2788 </programlisting>
2789 </para>
2791 <para>
2792 The following example uses automatic cursor name generation:
2794 <programlisting>
2795 CREATE FUNCTION reffunc2() RETURNS refcursor AS '
2796 DECLARE
2797 ref refcursor;
2798 BEGIN
2799 OPEN ref FOR SELECT col FROM test;
2800 RETURN ref;
2801 END;
2802 ' LANGUAGE plpgsql;
2804 BEGIN;
2805 SELECT reffunc2();
2807 reffunc2
2808 --------------------
2810 (1 row)
2812 FETCH ALL IN "<unnamed cursor 1>";
2813 COMMIT;
2814 </programlisting>
2815 </para>
2817 <para>
2818 The following example shows one way to return multiple cursors
2819 from a single function:
2821 <programlisting>
2822 CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
2823 BEGIN
2824 OPEN $1 FOR SELECT * FROM table_1;
2825 RETURN NEXT $1;
2826 OPEN $2 FOR SELECT * FROM table_2;
2827 RETURN NEXT $2;
2828 END;
2829 $$ LANGUAGE plpgsql;
2831 -- need to be in a transaction to use cursors.
2832 BEGIN;
2834 SELECT * FROM myfunc('a', 'b');
2836 FETCH ALL FROM a;
2837 FETCH ALL FROM b;
2838 COMMIT;
2839 </programlisting>
2840 </para>
2841 </sect3>
2842 </sect2>
2847 <para>
2849 iterating through the rows returned by a cursor. The syntax is:
2851 <synopsis>
2853 FOR <replaceable>recordvar</replaceable> IN <replaceable>bound_cursorvar</replaceable> <optional> ( <replaceable>argument_values</replaceable> ) </optional> LOOP
2856 </synopsis>
2858 The cursor variable must have been bound to some query when it was
2861 the cursor again when the loop exits. A list of actual argument value
2862 expressions must appear if and only if the cursor was declared to take
2863 arguments. These values will be substituted in the query, in just
2867 existing definition of the variable name is ignored within the loop).
2868 Each row returned by the cursor is successively assigned to this
2869 record variable and the loop body is executed.
2870 </para>
2871 </sect2>
2873 </sect1>
2878 <indexterm>
2880 </indexterm>
2882 <indexterm>
2885 </indexterm>
2887 <para>
2889 raise errors.
2891 <synopsis>
2892 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>;
2893 RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> <replaceable class="parameter">condition_name</> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;
2894 RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> SQLSTATE '<replaceable class="parameter">sqlstate</>' <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;
2895 RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional>;
2896 RAISE ;
2897 </synopsis>
2904 being the default.
2906 current transaction); the other levels only generate messages of different
2907 priority levels.
2908 Whether messages of a particular priority are reported to the client,
2909 written to the server log, or both is controlled by the
2913 information.
2914 </para>
2916 <para>
2919 (which must be a simple string literal, not an expression). The
2920 format string specifies the error message text to be reported.
2921 The format string can be followed
2922 by optional argument expressions to be inserted into the message.
2924 string representation of the next optional argument's value. Write
2926 </para>
2928 <para>
2931 <programlisting>
2932 RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
2933 </programlisting>
2934 </para>
2936 <para>
2937 You can attach additional information to the error report by writing
2945 expression.
2953 or directly as a five-character SQLSTATE code.
2954 </para>
2956 <para>
2957 This example will abort the transaction with the given error message
2958 and hint:
2959 <programlisting>
2960 RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING HINT = 'Please check your user id';
2961 </programlisting>
2962 </para>
2964 <para>
2965 These two examples show equivalent ways of setting the SQLSTATE:
2966 <programlisting>
2967 RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
2968 RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';
2969 </programlisting>
2970 </para>
2972 <para>
2974 is the condition name or SQLSTATE to be reported, for example:
2975 <programlisting>
2976 RAISE division_by_zero;
2977 RAISE SQLSTATE '22012';
2978 </programlisting>
2980 error message, detail, or hint. Another way to do the earlier
2981 example is
2982 <programlisting>
2983 RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
2984 </programlisting>
2985 </para>
2987 <para>
2991 </para>
2993 <para>
2997 it causes the error currently being handled to be re-thrown to the
2998 next enclosing block.
2999 </para>
3001 <para>
3002 If no condition name nor SQLSTATE is specified in a
3005 text is specified, the default is to use the condition name or
3006 SQLSTATE as message text.
3007 </para>
3009 <note>
3010 <para>
3011 When specifying an error code by SQLSTATE code, you are not
3012 limited to the predefined error codes, but can select any
3013 error code consisting of five digits and/or upper-case ASCII
3015 you avoid throwing error codes that end in three zeroes, because
3016 these are category codes and can only be trapped by trapping
3017 the whole category.
3018 </para>
3019 </note>
3021 </sect1>
3029 </indexterm>
3031 <para>
3033 procedures. A trigger procedure is created with the
3036 the function must be declared with no arguments even if it expects
3039 below.
3040 </para>
3042 <para>
3044 trigger, several special variables are created automatically in the
3045 top-level block. They are:
3047 <variablelist>
3048 <varlistentry>
3050 <listitem>
3051 <para>
3056 </para>
3057 </listitem>
3058 </varlistentry>
3060 <varlistentry>
3062 <listitem>
3063 <para>
3068 </para>
3069 </listitem>
3070 </varlistentry>
3072 <varlistentry>
3074 <listitem>
3075 <para>
3077 fired.
3078 </para>
3079 </listitem>
3080 </varlistentry>
3082 <varlistentry>
3084 <listitem>
3085 <para>
3088 depending on the trigger's definition.
3089 </para>
3090 </listitem>
3091 </varlistentry>
3093 <varlistentry>
3095 <listitem>
3096 <para>
3099 depending on the trigger's definition.
3100 </para>
3101 </listitem>
3102 </varlistentry>
3104 <varlistentry>
3106 <listitem>
3107 <para>
3111 telling for which operation the trigger was fired.
3112 </para>
3113 </listitem>
3114 </varlistentry>
3116 <varlistentry>
3118 <listitem>
3119 <para>
3121 trigger invocation.
3122 </para>
3123 </listitem>
3124 </varlistentry>
3126 <varlistentry>
3128 <listitem>
3129 <para>
3131 invocation. This is now deprecated, and could disappear in a future
3133 </para>
3134 </listitem>
3135 </varlistentry>
3137 <varlistentry>
3139 <listitem>
3140 <para>
3142 caused the trigger invocation.
3143 </para>
3144 </listitem>
3145 </varlistentry>
3147 <varlistentry>
3149 <listitem>
3150 <para>
3152 table that caused the trigger invocation.
3153 </para>
3154 </listitem>
3155 </varlistentry>
3157 <varlistentry>
3159 <listitem>
3160 <para>
3163 </para>
3164 </listitem>
3165 </varlistentry>
3167 <varlistentry>
3169 <listitem>
3170 <para>
3173 The index counts from 0. Invalid
3175 </para>
3176 </listitem>
3177 </varlistentry>
3178 </variablelist>
3179 </para>
3181 <para>
3183 record/row value having exactly the structure of the table the
3184 trigger was fired for.
3185 </para>
3187 <para>
3189 trigger manager to skip the rest of the operation for this row
3190 (i.e., subsequent triggers are not fired, and the
3192 for this row). If a nonnull
3193 value is returned then the operation proceeds with that row value.
3194 Returning a row value different from the original value
3197 To alter the row to be stored, it is possible to replace single values
3199 or to build a complete new record/row to return.
3200 </para>
3202 <para>
3205 always ignored; it might as well be null. However, any of these types of
3206 triggers might still abort the entire operation by raising an error.
3207 </para>
3209 <para>
3212 </para>
3217 <para>
3218 This example trigger ensures that any time a row is inserted or updated
3219 in the table, the current user name and time are stamped into the
3220 row. And it checks that an employee's name is given and that the
3221 salary is a positive value.
3222 </para>
3224 <programlisting>
3225 CREATE TABLE emp (
3226 empname text,
3227 salary integer,
3228 last_date timestamp,
3229 last_user text
3230 );
3232 CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
3233 BEGIN
3234 -- Check that empname and salary are given
3235 IF NEW.empname IS NULL THEN
3236 RAISE EXCEPTION 'empname cannot be null';
3237 END IF;
3238 IF NEW.salary IS NULL THEN
3239 RAISE EXCEPTION '% cannot have null salary', NEW.empname;
3240 END IF;
3242 -- Who works for us when she must pay for it?
3244 RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
3245 END IF;
3247 -- Remember who changed the payroll when
3248 NEW.last_date := current_timestamp;
3249 NEW.last_user := current_user;
3250 RETURN NEW;
3251 END;
3252 $emp_stamp$ LANGUAGE plpgsql;
3254 CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
3255 FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
3256 </programlisting>
3257 </example>
3259 <para>
3260 Another way to log changes to a table involves creating a new table that
3261 holds a row for each insert, update, or delete that occurs. This approach
3262 can be thought of as auditing changes to a table.
3265 </para>
3270 <para>
3271 This example trigger ensures that any insert, update or delete of a row
3272 in the <literal>emp</literal> table is recorded (i.e., audited) in the <literal>emp_audit</literal> table.
3273 The current time and user name are stamped into the row, together with
3274 the type of operation performed on it.
3275 </para>
3277 <programlisting>
3278 CREATE TABLE emp (
3279 empname text NOT NULL,
3280 salary integer
3281 );
3283 CREATE TABLE emp_audit(
3284 operation char(1) NOT NULL,
3285 stamp timestamp NOT NULL,
3286 userid text NOT NULL,
3287 empname text NOT NULL,
3288 salary integer
3289 );
3291 CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
3292 BEGIN
3293 --
3294 -- Create a row in emp_audit to reflect the operation performed on emp,
3295 -- make use of the special variable TG_OP to work out the operation.
3296 --
3297 IF (TG_OP = 'DELETE') THEN
3298 INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
3299 RETURN OLD;
3300 ELSIF (TG_OP = 'UPDATE') THEN
3301 INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
3302 RETURN NEW;
3303 ELSIF (TG_OP = 'INSERT') THEN
3304 INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
3305 RETURN NEW;
3306 END IF;
3307 RETURN NULL; -- result is ignored since this is an AFTER trigger
3308 END;
3309 $emp_audit$ LANGUAGE plpgsql;
3311 CREATE TRIGGER emp_audit
3312 AFTER INSERT OR UPDATE OR DELETE ON emp
3313 FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
3314 </programlisting>
3315 </example>
3317 <para>
3318 One use of triggers is to maintain a summary table
3319 of another table. The resulting summary can be used in place of the
3320 original table for certain queries — often with vastly reduced run
3321 times.
3322 This technique is commonly used in Data Warehousing, where the tables
3323 of measured or observed data (called fact tables) might be extremely large.
3326 a summary table for a fact table in a data warehouse.
3327 </para>
3331 <title>A <application>PL/pgSQL</application> Trigger Procedure For Maintaining A Summary Table</title>
3333 <para>
3334 The schema detailed here is partly based on the <emphasis>Grocery Store
3336 by Ralph Kimball.
3337 </para>
3339 <programlisting>
3340 --
3341 -- Main tables - time dimension and sales fact.
3342 --
3343 CREATE TABLE time_dimension (
3344 time_key integer NOT NULL,
3345 day_of_week integer NOT NULL,
3346 day_of_month integer NOT NULL,
3347 month integer NOT NULL,
3348 quarter integer NOT NULL,
3349 year integer NOT NULL
3350 );
3351 CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
3353 CREATE TABLE sales_fact (
3354 time_key integer NOT NULL,
3355 product_key integer NOT NULL,
3356 store_key integer NOT NULL,
3358 units_sold integer NOT NULL,
3360 );
3361 CREATE INDEX sales_fact_time ON sales_fact(time_key);
3363 --
3364 -- Summary table - sales by time.
3365 --
3366 CREATE TABLE sales_summary_bytime (
3367 time_key integer NOT NULL,
3369 units_sold numeric(12) NOT NULL,
3371 );
3372 CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
3374 --
3375 -- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
3376 --
3377 CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $maint_sales_summary_bytime$
3378 DECLARE
3379 delta_time_key integer;
3381 delta_units_sold numeric(12);
3383 BEGIN
3385 -- Work out the increment/decrement amount(s).
3386 IF (TG_OP = 'DELETE') THEN
3388 delta_time_key = OLD.time_key;
3389 delta_amount_sold = -1 * OLD.amount_sold;
3390 delta_units_sold = -1 * OLD.units_sold;
3391 delta_amount_cost = -1 * OLD.amount_cost;
3393 ELSIF (TG_OP = 'UPDATE') THEN
3395 -- forbid updates that change the time_key -
3396 -- (probably not too onerous, as DELETE + INSERT is how most
3397 -- changes will be made).
3398 IF ( OLD.time_key != NEW.time_key) THEN
3399 RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key, NEW.time_key;
3400 END IF;
3402 delta_time_key = OLD.time_key;
3403 delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
3404 delta_units_sold = NEW.units_sold - OLD.units_sold;
3405 delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
3407 ELSIF (TG_OP = 'INSERT') THEN
3409 delta_time_key = NEW.time_key;
3410 delta_amount_sold = NEW.amount_sold;
3411 delta_units_sold = NEW.units_sold;
3412 delta_amount_cost = NEW.amount_cost;
3414 END IF;
3417 -- Insert or update the summary row with the new values.
3419 LOOP
3420 UPDATE sales_summary_bytime
3421 SET amount_sold = amount_sold + delta_amount_sold,
3422 units_sold = units_sold + delta_units_sold,
3423 amount_cost = amount_cost + delta_amount_cost
3424 WHERE time_key = delta_time_key;
3426 EXIT insert_update WHEN found;
3428 BEGIN
3429 INSERT INTO sales_summary_bytime (
3430 time_key,
3431 amount_sold,
3432 units_sold,
3433 amount_cost)
3434 VALUES (
3435 delta_time_key,
3436 delta_amount_sold,
3437 delta_units_sold,
3438 delta_amount_cost
3439 );
3441 EXIT insert_update;
3443 EXCEPTION
3444 WHEN UNIQUE_VIOLATION THEN
3445 -- do nothing
3446 END;
3447 END LOOP insert_update;
3449 RETURN NULL;
3451 END;
3452 $maint_sales_summary_bytime$ LANGUAGE plpgsql;
3454 CREATE TRIGGER maint_sales_summary_bytime
3455 AFTER INSERT OR UPDATE OR DELETE ON sales_fact
3456 FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();
3462 SELECT * FROM sales_summary_bytime;
3463 DELETE FROM sales_fact WHERE product_key = 1;
3464 SELECT * FROM sales_summary_bytime;
3465 UPDATE sales_fact SET units_sold = units_sold * 2;
3466 SELECT * FROM sales_summary_bytime;
3467 </programlisting>
3468 </example>
3470 </sect1>
3475 <para>
3476 This section discusses some implementation details that are
3478 </para>
3483 <para>
3486 appearing in the statement or expression is replaced by a parameter symbol,
3488 of the variable is then provided as the value for the parameter whenever
3489 the statement or expression is executed. As an example, consider the
3490 function
3491 <programlisting>
3492 CREATE FUNCTION logfunc(logtxt text) RETURNS void AS $$
3493 DECLARE
3494 curtime timestamp := now();
3495 BEGIN
3496 INSERT INTO logtable VALUES (logtxt, curtime);
3497 END;
3498 $$ LANGUAGE plpgsql;
3499 </programlisting>
3501 <programlisting>
3504 </programlisting>
3506 actual values of the two variables. (Note: here we are speaking of
3507 the main SQL engine's
3510 </para>
3512 <para>
3513 <emphasis>The substitution mechanism will replace any token that matches a
3514 known variable's name.</> This poses various traps for the unwary.
3515 For example, it is a bad idea
3516 to use a variable name that is the same as any table or column name
3517 that you need to reference in queries within the function, because
3518 what you think is a table or column name will still get replaced.
3522 <programlisting>
3523 INSERT INTO logtable (logtxt, logtime) VALUES (logtxt, curtime);
3524 </programlisting>
3525 This will be fed to the main SQL parser as
3526 <programlisting>
3528 </programlisting>
3529 resulting in a syntax error like this:
3530 <screen>
3531 ERROR: syntax error at or near "$1"
3533 ^
3536 </screen>
3537 </para>
3539 <para>
3540 This example is fairly easy to diagnose, since it leads to an
3541 obvious syntax error. Much nastier are cases where the substitution
3542 is syntactically permissible, since the only symptom may be misbehavior
3543 of the function. In one case, a user wrote something like this:
3544 <programlisting>
3545 DECLARE
3546 val text;
3547 search_key integer;
3548 BEGIN
3549 ...
3550 FOR val IN SELECT val FROM table WHERE key = search_key LOOP ...
3551 </programlisting>
3552 and wondered why all his table entries seemed to be NULL. Of course
3553 what happened here was that the query became
3554 <programlisting>
3556 </programlisting>
3558 current value back to itself for each row.
3559 </para>
3561 <para>
3562 A commonly used coding rule for avoiding such traps is to use a
3564 variables than you use for table and column names. For example,
3565 if all your variables are named
3568 </para>
3570 <para>
3571 Another workaround is to use qualified (dotted) names for SQL entities.
3572 For instance we could safely have written the above example as
3573 <programlisting>
3574 FOR val IN SELECT table.val FROM table WHERE key = search_key LOOP ...
3575 </programlisting>
3577 variable for a trailing component of a qualified name.
3578 However this solution does not work in every case — you can't
3580 Another point is that record and row variable names will be matched to
3581 the first components of qualified names, so a qualified SQL name is
3582 still vulnerable in some cases.
3583 In such cases choosing a non-conflicting variable name is the only way.
3584 </para>
3586 <para>
3587 Another technique you can use is to attach a label to the block in
3588 which your variables are declared, and then qualify the variable names
3590 For example,
3591 <programlisting>
3593 DECLARE
3594 val text;
3595 BEGIN
3596 ...
3597 UPDATE table SET col = pl.val WHERE ...
3598 </programlisting>
3599 This is not in itself a solution to the problem of conflicts,
3600 since an unqualified name in a SQL command is still at risk of being
3602 the intent of potentially-ambiguous code.
3603 </para>
3605 <para>
3606 Variable substitution does not happen in the command string given
3608 insert a varying value into such a command, do so as part of
3609 constructing the string value, as illustrated in
3611 </para>
3613 <para>
3616 because the main SQL engine allows parameter symbols only in these
3617 commands. To use a non-constant name or value in other statement
3618 types (generically called utility statements), you must construct
3620 </para>
3622 </sect2>
3627 <para>
3629 text and produces an internal binary instruction tree the first time the
3630 function is called (within each session). The instruction tree
3631 fully translates the
3634 used in the function are not translated immediately.
3635 </para>
3637 <para>
3640 creates a prepared execution plan (using the
3645 reuse the prepared plan. Thus, a function with conditional code
3646 that contains many statements for which execution plans might be
3647 required will only prepare and save those plans that are really
3648 used during the lifetime of the database connection. This can
3649 substantially reduce the total amount of time required to parse
3650 and generate execution plans for the statements in a
3652 in a specific expression or command cannot be detected until that
3653 part of the function is reached in execution. (Trivial syntax
3654 errors will be detected during the initial parsing pass, but
3655 anything deeper will not be detected until execution.)
3656 </para>
3658 <para>
3660 command in a function, it will reuse that plan for the life of the
3661 database connection. This is usually a win for performance, but it
3662 can cause some problems if you dynamically
3663 alter your database schema. For example:
3665 <programlisting>
3666 CREATE FUNCTION populate() RETURNS integer AS $$
3667 DECLARE
3668 -- declarations
3669 BEGIN
3670 PERFORM my_function();
3671 END;
3672 $$ LANGUAGE plpgsql;
3673 </programlisting>
3675 If you execute the above function, it will reference the OID for
3682 will be compiled afresh, before it will work again. You can avoid
3684 when updating the definition of
3687 </para>
3689 <note>
3690 <para>
3692 will be replaced whenever any schema changes have occurred to any
3693 tables they reference. This eliminates one of the major disadvantages
3694 of saved plans. However, there is no such mechanism for function
3695 references, and thus the above example involving a reference to a
3696 deleted function is still valid.
3697 </para>
3698 </note>
3700 <para>
3702 in this way, SQL commands that appear directly in a
3704 same tables and columns on every execution; that is, you cannot use
3705 a parameter as the name of a table or column in an SQL command. To get
3706 around this restriction, you can construct dynamic commands using
3708 statement — at the price of constructing a new execution plan on
3709 every execution.
3710 </para>
3712 <para>
3713 Another important point is that the prepared plans are parameterized
3715 to change from one use to the next, as discussed in detail above.
3716 Sometimes this means that a plan is less efficient than it would be
3717 if generated for a specific variable value. As an example, consider
3718 <programlisting>
3719 SELECT * INTO myrec FROM dictionary WHERE word LIKE search_term;
3720 </programlisting>
3722 variable. The cached plan for this query will never use an index on
3725 an index the query must be planned with a specific constant
3728 generated for each execution.
3729 </para>
3731 <para>
3732 The mutable nature of record variables presents another problem in this
3733 connection. When fields of a record variable are used in
3734 expressions or statements, the data types of the fields must not
3735 change from one call of the function to the next, since each
3736 expression will be planned using the data type that is present
3738 used to get around this problem when necessary.
3739 </para>
3741 <para>
3742 If the same function is used as a trigger for more than one table,
3744 independently for each such table — that is, there is a cache
3745 for each trigger function and table combination, not just for each
3746 function. This alleviates some of the problems with varying
3747 data types; for instance, a trigger function will be able to work
3749 to have different types in different tables.
3750 </para>
3752 <para>
3753 Likewise, functions having polymorphic argument types have a separate
3754 plan cache for each combination of actual argument types they have been
3755 invoked for, so that data type differences do not cause unexpected
3756 failures.
3757 </para>
3759 <para>
3760 Plan caching can sometimes have surprising effects on the interpretation
3761 of time-sensitive values. For example there
3762 is a difference between what these two functions do:
3764 <programlisting>
3765 CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
3766 BEGIN
3767 INSERT INTO logtable VALUES (logtxt, 'now');
3768 END;
3769 $$ LANGUAGE plpgsql;
3770 </programlisting>
3772 and:
3774 <programlisting>
3775 CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
3776 DECLARE
3777 curtime timestamp;
3778 BEGIN
3779 curtime := 'now';
3780 INSERT INTO logtable VALUES (logtxt, curtime);
3781 END;
3782 $$ LANGUAGE plpgsql;
3783 </programlisting>
3784 </para>
3786 <para>
3796 of the session. Needless to say, this isn't what the programmer
3797 wanted.
3798 </para>
3800 <para>
3810 functions for the conversion. So, the computed time stamp is updated
3811 on each execution as the programmer expects.
3812 </para>
3814 </sect2>
3816 </sect1>
3821 <para>
3822 One good way to develop in
3824 choice to create your functions, and in another window, use
3826 If you are doing it this way, it
3827 is a good idea to write the function using <command>CREATE OR
3828 REPLACE FUNCTION</>. That way you can just reload the file to update
3829 the function definition. For example:
3830 <programlisting>
3831 CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
3832 ....
3833 $$ LANGUAGE plpgsql;
3834 </programlisting>
3835 </para>
3837 <para>
3839 a function definition file with:
3840 <programlisting>
3841 \i filename.sql
3842 </programlisting>
3843 and then immediately issue SQL commands to test the function.
3844 </para>
3846 <para>
3848 GUI database access tool that facilitates development in a
3849 procedural language. One example of such as a tool is
3851 provide convenient features such as escaping single quotes and
3852 making it easier to recreate and debug functions.
3853 </para>
3858 <para>
3861 write the string literal in the ordinary way with surrounding
3862 single quotes, then any single quotes inside the function body
3863 must be doubled; likewise any backslashes must be doubled (assuming
3864 escape string syntax is used).
3865 Doubling quotes is at best tedious, and in more complicated cases
3866 the code can become downright incomprehensible, because you can
3867 easily find yourself needing half a dozen or more adjacent quote marks.
3868 It's recommended that you instead write the function body as a
3871 approach, you never double any quote marks, but instead take care to
3872 choose a different dollar-quoting delimiter for each level of
3873 nesting you need. For example, you might write the <command>CREATE
3874 FUNCTION</command> command as:
3875 <programlisting>
3876 CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
3877 ....
3878 $PROC$ LANGUAGE plpgsql;
3879 </programlisting>
3880 Within this, you might use quote marks for simple literal strings in
3882 that you are assembling as strings. If you need to quote text that
3884 </para>
3886 <para>
3887 The following chart shows what you have to do when writing quote
3888 marks without dollar quoting. It might be useful when translating
3889 pre-dollar quoting code into something more comprehensible.
3890 </para>
3892 <variablelist>
3893 <varlistentry>
3895 <listitem>
3896 <para>
3897 To begin and end the function body, for example:
3898 <programlisting>
3899 CREATE FUNCTION foo() RETURNS integer AS '
3900 ....
3901 ' LANGUAGE plpgsql;
3902 </programlisting>
3903 Anywhere within a single-quoted function body, quote marks
3905 </para>
3906 </listitem>
3907 </varlistentry>
3909 <varlistentry>
3911 <listitem>
3912 <para>
3913 For string literals inside the function body, for example:
3914 <programlisting>
3915 a_output := ''Blah'';
3916 SELECT * FROM users WHERE f_name=''foobar'';
3917 </programlisting>
3918 In the dollar-quoting approach, you'd just write:
3919 <programlisting>
3920 a_output := 'Blah';
3921 SELECT * FROM users WHERE f_name='foobar';
3922 </programlisting>
3924 in either case.
3925 </para>
3926 </listitem>
3927 </varlistentry>
3929 <varlistentry>
3931 <listitem>
3932 <para>
3933 When you need a single quotation mark in a string constant inside the
3934 function body, for example:
3935 <programlisting>
3936 a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''
3937 </programlisting>
3940 </para>
3941 <para>
3942 In the dollar-quoting approach, you'd write:
3943 <programlisting>
3944 a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$
3945 </programlisting>
3946 being careful that any dollar-quote delimiters around this are not
3948 </para>
3949 </listitem>
3950 </varlistentry>
3952 <varlistentry>
3954 <listitem>
3955 <para>
3956 When a single quotation mark in a string inside the function body is
3957 adjacent to the end of that string constant, for example:
3958 <programlisting>
3959 a_output := a_output || '' AND name LIKE ''''foobar''''''
3960 </programlisting>
3963 </para>
3964 <para>
3965 In the dollar-quoting approach, this becomes:
3966 <programlisting>
3967 a_output := a_output || $$ AND name LIKE 'foobar'$$
3968 </programlisting>
3969 </para>
3970 </listitem>
3971 </varlistentry>
3973 <varlistentry>
3975 <listitem>
3976 <para>
3977 When you want two single quotation marks in a string constant (which
3978 accounts for 8 quotation marks) and this is adjacent to the end of that
3979 string constant (2 more). You will probably only need that if
3980 you are writing a function that generates other functions, as in
3982 For example:
3983 <programlisting>
3984 a_output := a_output || '' if v_'' ||
3985 referrer_keys.kind || '' like ''''''''''
3986 || referrer_keys.key_string || ''''''''''
3987 then return '''''' || referrer_keys.referrer_type
3988 || ''''''; end if;'';
3989 </programlisting>
3991 <programlisting>
3992 if v_... like ''...'' then return ''...''; end if;
3993 </programlisting>
3994 </para>
3995 <para>
3996 In the dollar-quoting approach, this becomes:
3997 <programlisting>
3998 a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
3999 || referrer_keys.key_string || $$'
4000 then return '$$ || referrer_keys.referrer_type
4001 || $$'; end if;$$;
4002 </programlisting>
4003 where we assume we only need to put single quote marks into
4005 </para>
4006 </listitem>
4007 </varlistentry>
4008 </variablelist>
4010 </sect2>
4011 </sect1>
4013 <!-- **** Porting from Oracle PL/SQL **** -->
4021 </indexterm>
4026 </indexterm>
4028 <para>
4029 This section explains differences between
4032 to help developers who port applications from
4034 </para>
4036 <para>
4038 aspects. It is a block-structured, imperative language, and all
4039 variables have to be declared. Assignments, loops, conditionals
4040 are similar. The main differences you should keep in mind when
4044 <itemizedlist>
4045 <listitem>
4046 <para>
4048 </para>
4049 </listitem>
4051 <listitem>
4052 <para>
4054 often used to work around the lack of default parameters.
4055 </para>
4056 </listitem>
4058 <listitem>
4059 <para>
4060 If a name used in a SQL command could be either a column name of a
4061 table or a reference to a variable of the function,
4064 to avoid such ambiguities in the first place, but if necessary you
4065 can fix them by properly qualifying the ambiguous name.
4067 </para>
4068 </listitem>
4070 <listitem>
4071 <para>
4073 a string literal. Therefore you need to use dollar quoting or escape
4074 single quotes in the function body. (See <xref
4076 </para>
4077 </listitem>
4079 <listitem>
4080 <para>
4081 Instead of packages, use schemas to organize your functions
4082 into groups.
4083 </para>
4084 </listitem>
4086 <listitem>
4087 <para>
4088 Since there are no packages, there are no package-level variables
4089 either. This is somewhat annoying. You can keep per-session state
4090 in temporary tables instead.
4091 </para>
4092 </listitem>
4094 <listitem>
4095 <para>
4099 from the first number to the second, requiring the loop bounds
4100 to be swapped when porting. This incompatibility is unfortunate
4101 but is unlikely to be changed. (See <xref
4103 </para>
4104 </listitem>
4106 <listitem>
4107 <para>
4109 differently: the target variable(s) must have been declared,
4111 An advantage of this is that the variable values are still accessible
4112 after the loop exits.
4113 </para>
4114 </listitem>
4116 <listitem>
4117 <para>
4118 There are various notational differences for the use of cursor
4119 variables.
4120 </para>
4121 </listitem>
4123 </itemizedlist>
4124 </para>
4126 <sect2>
4129 <para>
4132 </para>
4135 <title>Porting a Simple Function from <application>PL/SQL</> to <application>PL/pgSQL</></title>
4137 <para>
4139 <programlisting>
4140 CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
4141 v_version varchar)
4142 RETURN varchar IS
4143 BEGIN
4144 IF v_version IS NULL THEN
4145 RETURN v_name;
4146 END IF;
4147 RETURN v_name || '/' || v_version;
4148 END;
4149 /
4150 show errors;
4151 </programlisting>
4152 </para>
4154 <para>
4155 Let's go through this function and see the differences compared to
4158 <itemizedlist>
4159 <listitem>
4160 <para>
4162 prototype (not the function body) becomes
4167 is not the only possible function language.
4168 </para>
4169 </listitem>
4171 <listitem>
4172 <para>
4174 to be a string literal, so you need to use quote marks or dollar
4176 in the Oracle approach.
4177 </para>
4178 </listitem>
4180 <listitem>
4181 <para>
4184 reported automatically.
4185 </para>
4186 </listitem>
4187 </itemizedlist>
4188 </para>
4190 <para>
4191 This is how this function would look when ported to
4194 <programlisting>
4195 CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
4196 v_version varchar)
4197 RETURNS varchar AS $$
4198 BEGIN
4199 IF v_version IS NULL THEN
4200 RETURN v_name;
4201 END IF;
4202 RETURN v_name || '/' || v_version;
4203 END;
4204 $$ LANGUAGE plpgsql;
4205 </programlisting>
4206 </para>
4207 </example>
4209 <para>
4211 function that creates another function and how to handle the
4212 ensuing quoting problems.
4213 </para>
4216 <title>Porting a Function that Creates Another Function from <application>PL/SQL</> to <application>PL/pgSQL</></title>
4218 <para>
4219 The following procedure grabs rows from a
4222 sake of efficiency.
4223 </para>
4225 <para>
4226 This is the Oracle version:
4227 <programlisting>
4228 CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
4229 CURSOR referrer_keys IS
4230 SELECT * FROM cs_referrer_keys
4231 ORDER BY try_order;
4232 func_cmd VARCHAR(4000);
4233 BEGIN
4234 func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR,
4235 v_domain IN VARCHAR, v_url IN VARCHAR) RETURN VARCHAR IS BEGIN';
4237 FOR referrer_key IN referrer_keys LOOP
4238 func_cmd := func_cmd ||
4239 ' IF v_' || referrer_key.kind
4240 || ' LIKE ''' || referrer_key.key_string
4241 || ''' THEN RETURN ''' || referrer_key.referrer_type
4242 || '''; END IF;';
4243 END LOOP;
4245 func_cmd := func_cmd || ' RETURN NULL; END;';
4247 EXECUTE IMMEDIATE func_cmd;
4248 END;
4249 /
4250 show errors;
4251 </programlisting>
4252 </para>
4254 <para>
4256 <programlisting>
4257 CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$
4258 DECLARE
4259 CURSOR referrer_keys IS
4260 SELECT * FROM cs_referrer_keys
4261 ORDER BY try_order;
4262 func_body text;
4263 func_cmd text;
4264 BEGIN
4265 func_body := 'BEGIN';
4267 FOR referrer_key IN referrer_keys LOOP
4268 func_body := func_body ||
4269 ' IF v_' || referrer_key.kind
4270 || ' LIKE ' || quote_literal(referrer_key.key_string)
4271 || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
4272 || '; END IF;' ;
4273 END LOOP;
4275 func_body := func_body || ' RETURN NULL; END;';
4277 func_cmd :=
4278 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
4279 v_domain varchar,
4280 v_url varchar)
4281 RETURNS varchar AS '
4282 || quote_literal(func_body)
4283 || ' LANGUAGE plpgsql;' ;
4285 EXECUTE func_cmd;
4286 END;
4287 $func$ LANGUAGE plpgsql;
4288 </programlisting>
4289 Notice how the body of the function is built separately and passed
4291 technique is needed because we cannot safely use dollar quoting for
4292 defining the new function: we do not know for sure what strings will
4297 anything, in particular it might contain dollar signs.) This function
4298 is actually an improvement on the Oracle original, because it will
4301 </para>
4302 </example>
4304 <para>
4309 using a combination of other
4314 easier.
4315 </para>
4318 <title>Porting a Procedure With String Manipulation and
4322 <para>
4324 to parse a URL and return several elements (host, path, and query).
4325 </para>
4327 <para>
4328 This is the Oracle version:
4329 <programlisting>
4330 CREATE OR REPLACE PROCEDURE cs_parse_url(
4331 v_url IN VARCHAR,
4332 v_host OUT VARCHAR, -- This will be passed back
4333 v_path OUT VARCHAR, -- This one too
4334 v_query OUT VARCHAR) -- And this one
4335 IS
4336 a_pos1 INTEGER;
4337 a_pos2 INTEGER;
4338 BEGIN
4339 v_host := NULL;
4340 v_path := NULL;
4341 v_query := NULL;
4342 a_pos1 := instr(v_url, '//');
4344 IF a_pos1 = 0 THEN
4345 RETURN;
4346 END IF;
4347 a_pos2 := instr(v_url, '/', a_pos1 + 2);
4348 IF a_pos2 = 0 THEN
4349 v_host := substr(v_url, a_pos1 + 2);
4350 v_path := '/';
4351 RETURN;
4352 END IF;
4355 a_pos1 := instr(v_url, '?', a_pos2 + 1);
4357 IF a_pos1 = 0 THEN
4358 v_path := substr(v_url, a_pos2);
4359 RETURN;
4360 END IF;
4362 v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
4363 v_query := substr(v_url, a_pos1 + 1);
4364 END;
4365 /
4366 show errors;
4367 </programlisting>
4368 </para>
4370 <para>
4372 <programlisting>
4373 CREATE OR REPLACE FUNCTION cs_parse_url(
4374 v_url IN VARCHAR,
4375 v_host OUT VARCHAR, -- This will be passed back
4376 v_path OUT VARCHAR, -- This one too
4377 v_query OUT VARCHAR) -- And this one
4378 AS $$
4379 DECLARE
4380 a_pos1 INTEGER;
4381 a_pos2 INTEGER;
4382 BEGIN
4383 v_host := NULL;
4384 v_path := NULL;
4385 v_query := NULL;
4386 a_pos1 := instr(v_url, '//');
4388 IF a_pos1 = 0 THEN
4389 RETURN;
4390 END IF;
4391 a_pos2 := instr(v_url, '/', a_pos1 + 2);
4392 IF a_pos2 = 0 THEN
4393 v_host := substr(v_url, a_pos1 + 2);
4394 v_path := '/';
4395 RETURN;
4396 END IF;
4399 a_pos1 := instr(v_url, '?', a_pos2 + 1);
4401 IF a_pos1 = 0 THEN
4402 v_path := substr(v_url, a_pos2);
4403 RETURN;
4404 END IF;
4406 v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
4407 v_query := substr(v_url, a_pos1 + 1);
4408 END;
4409 $$ LANGUAGE plpgsql;
4410 </programlisting>
4412 This function could be used like this:
4413 <programlisting>
4414 SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
4415 </programlisting>
4416 </para>
4417 </example>
4419 <para>
4421 that uses numerous features that are specific to Oracle.
4422 </para>
4427 <para>
4428 The Oracle version:
4430 <programlisting>
4431 CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
4432 a_running_job_count INTEGER;
4434 BEGIN
4437 SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
4441 raise_application_error(-20000, 'Unable to create a new job: a job is currently running.');
4442 END IF;
4444 DELETE FROM cs_active_job;
4445 INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
4447 BEGIN
4448 INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, sysdate);
4449 EXCEPTION
4450 WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
4451 END;
4452 COMMIT;
4453 END;
4454 /
4455 show errors
4456 </programlisting>
4457 </para>
4459 <para>
4462 particular is interesting because it can teach us some things:
4464 <calloutlist>
4466 <para>
4468 </para>
4469 </callout>
4472 <para>
4474 the lock will not be released until the calling transaction is
4475 finished.
4476 </para>
4477 </callout>
4480 <para>
4484 would imply terminating the function's execution. However, in
4485 this particular case it is not necessary anyway, because the lock
4487 we raise an error.
4488 </para>
4489 </callout>
4490 </calloutlist>
4491 </para>
4493 <para>
4496 <programlisting>
4497 CREATE OR REPLACE FUNCTION cs_create_job(v_job_id integer) RETURNS void AS $$
4498 DECLARE
4499 a_running_job_count integer;
4500 BEGIN
4501 LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
4503 SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
4506 RAISE EXCEPTION 'Unable to create a new job: a job is currently running';<co id="co.plpgsql-porting-raise">
4507 END IF;
4509 DELETE FROM cs_active_job;
4510 INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
4512 BEGIN
4513 INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
4514 EXCEPTION
4516 -- don't worry if it already exists
4517 END;
4518 END;
4519 $$ LANGUAGE plpgsql;
4520 </programlisting>
4522 <calloutlist>
4524 <para>
4528 similarly.
4529 </para>
4530 </callout>
4532 <para>
4534 different from Oracle's. The set of built-in exception names
4536 is not currently a way to declare user-defined exception names.
4537 </para>
4538 </callout>
4539 </calloutlist>
4541 The main functional difference between this procedure and the
4543 table will be held until the calling transaction completes. Also, if
4544 the caller later aborts (for example due to an error), the effects of
4545 this procedure will be rolled back.
4546 </para>
4547 </example>
4548 </sect2>
4553 <para>
4554 This section explains a few other things to watch for when porting
4557 </para>
4562 <para>
4566 is equivalent to what you'd get in Oracle with:
4568 <programlisting>
4569 BEGIN
4570 SAVEPOINT s1;
4571 ... code here ...
4572 EXCEPTION
4573 WHEN ... THEN
4574 ROLLBACK TO s1;
4575 ... code here ...
4576 WHEN ... THEN
4577 ROLLBACK TO s1;
4578 ... code here ...
4579 END;
4580 </programlisting>
4582 If you are translating an Oracle procedure that uses
4587 then some actual thought will be required.
4588 </para>
4589 </sect3>
4591 <sect3>
4594 <para>
4602 reliably unless you use these functions.
4603 </para>
4604 </sect3>
4609 <para>
4612 the function always returns the same result when given the same
4614 returns null if any argument is null). Consult the <xref
4616 reference page for details.
4617 </para>
4619 <para>
4620 When making use of these optimization attributes, your
4622 like this:
4624 <programlisting>
4625 CREATE FUNCTION foo(...) RETURNS integer AS $$
4626 ...
4627 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
4628 </programlisting>
4629 </para>
4630 </sect3>
4631 </sect2>
4636 <para>
4637 This section contains the code for a set of Oracle-compatible
4639 your porting efforts.
4640 </para>
4642 <programlisting>
4643 --
4644 -- instr functions that mimic Oracle's counterpart
4645 -- Syntax: instr(string1, string2, [n], [m]) where [] denotes optional parameters.
4646 --
4647 -- Searches string1 beginning at the nth character for the mth occurrence
4648 -- of string2. If n is negative, search backwards. If m is not passed,
4649 -- assume 1 (search starts at first character).
4650 --
4652 CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
4653 DECLARE
4654 pos integer;
4655 BEGIN
4657 RETURN pos;
4658 END;
4659 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
4662 CREATE FUNCTION instr(string varchar, string_to_search varchar, beg_index integer)
4663 RETURNS integer AS $$
4664 DECLARE
4665 pos integer NOT NULL DEFAULT 0;
4666 temp_str varchar;
4667 beg integer;
4668 length integer;
4669 ss_length integer;
4670 BEGIN
4672 temp_str := substring(string FROM beg_index);
4673 pos := position(string_to_search IN temp_str);
4675 IF pos = 0 THEN
4676 RETURN 0;
4677 ELSE
4678 RETURN pos + beg_index - 1;
4679 END IF;
4680 ELSE
4681 ss_length := char_length(string_to_search);
4682 length := char_length(string);
4683 beg := length + beg_index - ss_length + 2;
4686 temp_str := substring(string FROM beg FOR ss_length);
4687 pos := position(string_to_search IN temp_str);
4690 RETURN beg;
4691 END IF;
4693 beg := beg - 1;
4694 END LOOP;
4696 RETURN 0;
4697 END IF;
4698 END;
4699 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
4702 CREATE FUNCTION instr(string varchar, string_to_search varchar,
4703 beg_index integer, occur_index integer)
4704 RETURNS integer AS $$
4705 DECLARE
4706 pos integer NOT NULL DEFAULT 0;
4707 occur_number integer NOT NULL DEFAULT 0;
4708 temp_str varchar;
4709 beg integer;
4710 i integer;
4711 length integer;
4712 ss_length integer;
4713 BEGIN
4715 beg := beg_index;
4716 temp_str := substring(string FROM beg_index);
4718 FOR i IN 1..occur_index LOOP
4719 pos := position(string_to_search IN temp_str);
4721 IF i = 1 THEN
4722 beg := beg + pos - 1;
4723 ELSE
4724 beg := beg + pos;
4725 END IF;
4727 temp_str := substring(string FROM beg + 1);
4728 END LOOP;
4730 IF pos = 0 THEN
4731 RETURN 0;
4732 ELSE
4733 RETURN beg;
4734 END IF;
4735 ELSE
4736 ss_length := char_length(string_to_search);
4737 length := char_length(string);
4738 beg := length + beg_index - ss_length + 2;
4741 temp_str := substring(string FROM beg FOR ss_length);
4742 pos := position(string_to_search IN temp_str);
4745 occur_number := occur_number + 1;
4747 IF occur_number = occur_index THEN
4748 RETURN beg;
4749 END IF;
4750 END IF;
4752 beg := beg - 1;
4753 END LOOP;
4755 RETURN 0;
4756 END IF;
4757 END;
4758 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
4759 </programlisting>
4760 </sect2>
4762 </sect1>
4764 </chapter>