| blob | 9b2e2e9402742bd68e38e04c9b666177ae79a5ae |
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 nested, but double dash comments can be enclosed into a block comment and
227 </para>
229 <para>
230 Any statement in the statement section of a block
232 logical grouping or to localize variables to a small group
233 of statements. Variables declared in a subblock mask any
234 similarly-named variables of outer blocks for the duration
235 of the subblock; but you can access the outer variables anyway
236 if you qualify their names with their block's label. For example:
237 <programlisting>
238 CREATE FUNCTION somefunc() RETURNS integer AS $$
240 DECLARE
241 quantity integer := 30;
242 BEGIN
243 RAISE NOTICE 'Quantity here is %', quantity; -- Prints 30
244 quantity := 50;
245 --
246 -- Create a subblock
247 --
248 DECLARE
249 quantity integer := 80;
250 BEGIN
251 RAISE NOTICE 'Quantity here is %', quantity; -- Prints 80
252 RAISE NOTICE 'Outer quantity here is %', outerblock.quantity; -- Prints 50
253 END;
255 RAISE NOTICE 'Quantity here is %', quantity; -- Prints 50
257 RETURN quantity;
258 END;
259 $$ LANGUAGE plpgsql;
260 </programlisting>
261 </para>
263 <note>
264 <para>
267 declarations of the function's parameters (if any), as well as some
270 labeled with the function's name, meaning that parameters and special
271 variables can be qualified with the function's name.
272 </para>
273 </note>
275 <para>
276 It is important not to confuse the use of
279 for transaction
281 are only for grouping; they do not start or end a transaction.
282 Functions and trigger procedures are always executed within a transaction
283 established by an outer query — they cannot start or commit that
284 transaction, since there would be no context for them to execute in.
286 forms a subtransaction that can be rolled back without affecting the
287 outer transaction. For more about that see <xref
289 </para>
290 </sect1>
295 <para>
296 All variables used in a block must be declared in the
297 declarations section of the block.
299 iterating over a range of integer values is automatically declared as an
301 iterating over a cursor's result is automatically declared as a
302 record variable.)
303 </para>
305 <para>
309 </para>
311 <para>
312 Here are some examples of variable declarations:
313 <programlisting>
314 user_id integer;
315 quantity numeric(5);
316 url varchar;
317 myrow tablename%ROWTYPE;
318 myfield tablename.columnname%TYPE;
319 arow RECORD;
320 </programlisting>
321 </para>
323 <para>
324 The general syntax of a variable declaration is:
325 <synopsis>
326 <replaceable>name</replaceable> <optional> CONSTANT </optional> <replaceable>type</replaceable> <optional> NOT NULL </optional> <optional> { DEFAULT | := } <replaceable>expression</replaceable> </optional>;
327 </synopsis>
330 is not given then the variable is initialized to the
333 so that its value remains constant for the duration of the block.
335 is specified, an assignment of a null value results in a run-time
337 must have a nonnull default value specified.
338 </para>
340 <para>
341 A variable's default value is evaluated and assigned to the variable
342 each time the block is entered (not just once per function call).
345 time of the current function call, not the time when the function was
346 precompiled.
347 </para>
349 <para>
350 Examples:
351 <programlisting>
352 quantity integer DEFAULT 32;
353 url varchar := 'http://mysite.com';
354 user_id CONSTANT integer := 10;
355 </programlisting>
356 </para>
361 <para>
362 Parameters passed to functions are named with the identifiers
364 etc. Optionally, aliases can be declared for
366 parameter names for increased readability. Either the alias or the
367 numeric identifier can then be used to refer to the parameter value.
368 </para>
370 <para>
371 There are two ways to create an alias. The preferred way is to give a
373 for example:
374 <programlisting>
375 CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
376 BEGIN
377 RETURN subtotal * 0.06;
378 END;
379 $$ LANGUAGE plpgsql;
380 </programlisting>
381 The other way, which was the only way available before
383 declare an alias, using the declaration syntax
385 <synopsis>
387 </synopsis>
389 The same example in this style looks like:
390 <programlisting>
391 CREATE FUNCTION sales_tax(real) RETURNS real AS $$
392 DECLARE
393 subtotal ALIAS FOR $1;
394 BEGIN
395 RETURN subtotal * 0.06;
396 END;
397 $$ LANGUAGE plpgsql;
398 </programlisting>
399 </para>
401 <note>
402 <para>
403 These two examples are not perfectly equivalent. In the first case,
407 be qualified with that label, instead.)
408 </para>
409 </note>
411 <para>
412 Some more examples:
413 <programlisting>
414 CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
415 DECLARE
416 v_string ALIAS FOR $1;
417 index ALIAS FOR $2;
418 BEGIN
419 -- some computations using v_string and index here
420 END;
421 $$ LANGUAGE plpgsql;
424 CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
425 BEGIN
426 RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
427 END;
428 $$ LANGUAGE plpgsql;
429 </programlisting>
430 </para>
432 <para>
434 with output parameters, the output parameters are given
436 aliases in just the same way as the normal input parameters. An
437 output parameter is effectively a variable that starts out NULL;
438 it should be assigned to during the execution of the function.
439 The final value of the parameter is what is returned. For instance,
440 the sales-tax example could also be done this way:
442 <programlisting>
443 CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
444 BEGIN
445 tax := subtotal * 0.06;
446 END;
447 $$ LANGUAGE plpgsql;
448 </programlisting>
451 included it, but it would be redundant.
452 </para>
454 <para>
455 Output parameters are most useful when returning multiple values.
456 A trivial example is:
458 <programlisting>
459 CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
460 BEGIN
461 sum := x + y;
462 prod := x * y;
463 END;
464 $$ LANGUAGE plpgsql;
465 </programlisting>
468 effectively creates an anonymous record type for the function's
471 </para>
473 <para>
477 <programlisting>
478 CREATE FUNCTION extended_sales(p_itemno int) RETURNS TABLE(quantity int, total numeric) AS $$
479 BEGIN
480 RETURN QUERY SELECT quantity, quantity * price FROM sales WHERE itemno = p_itemno;
481 END;
482 $$ LANGUAGE plpgsql;
483 </programlisting>
486 parameters and specifying <literal>RETURNS SETOF
488 </para>
490 <para>
495 is created. Its data type is the actual return type of the function,
496 as deduced from the actual input types (see <xref
498 This allows the function to access its actual return type
501 the function, so it can be used to hold the return value if desired,
503 given an alias. For example, this function works on any data type
506 <programlisting>
507 CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
508 RETURNS anyelement AS $$
509 DECLARE
510 result ALIAS FOR $0;
511 BEGIN
512 result := v1 + v2 + v3;
513 RETURN result;
514 END;
515 $$ LANGUAGE plpgsql;
516 </programlisting>
517 </para>
519 <para>
520 The same effect can be had by declaring one or more output parameters as
521 polymorphic types. In this case the
523 parameters themselves serve the same purpose. For example:
525 <programlisting>
526 CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
527 OUT sum anyelement)
528 AS $$
529 BEGIN
530 sum := v1 + v2 + v3;
531 END;
532 $$ LANGUAGE plpgsql;
533 </programlisting>
534 </para>
535 </sect2>
540 <synopsis>
542 </synopsis>
544 <para>
546 table column. You can use this to declare variables that will hold
547 database values. For example, let's say you have a column named
549 table. To declare a variable with the same data type as
551 <programlisting>
552 user_id users.user_id%TYPE;
553 </programlisting>
554 </para>
556 <para>
558 type of the structure you are referencing, and most importantly,
559 if the data type of the referenced item changes in the future (for
562 to change your function definition.
563 </para>
565 <para>
567 functions, since the data types needed for internal variables can
568 change from one call to the next. Appropriate variables can be
570 arguments or result placeholders.
571 </para>
573 </sect2>
578 <synopsis>
579 <replaceable>name</replaceable> <replaceable>table_name</replaceable><literal>%ROWTYPE</literal>;
581 </synopsis>
583 <para>
587 query result, so long as that query's column set matches the
588 declared type of the variable.
589 The individual fields of the row value
590 are accessed using the usual dot notation, for example
592 </para>
594 <para>
595 A row variable can be declared to have the same type as the rows of
596 an existing table or view, by using the
598 notation; or it can be declared by giving a composite type's name.
599 (Since every table has an associated composite type of the same name,
603 </para>
605 <para>
606 Parameters to a function can be
607 composite types (complete table rows). In that case, the
608 corresponding identifier <literal>$<replaceable>n</replaceable></> will be a row variable, and fields can
610 </para>
612 <para>
613 Only the user-defined columns of a table row are accessible in a
614 row-type variable, not the OID or other system columns (because the
615 row could be from a view). The fields of the row type inherit the
616 table's field size or precision for data types such as
618 </para>
620 <para>
623 mentioned fields:
625 <programlisting>
626 CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
627 DECLARE
628 t2_row table2%ROWTYPE;
629 BEGIN
630 SELECT * INTO t2_row FROM table2 WHERE ... ;
631 RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
632 END;
633 $$ LANGUAGE plpgsql;
635 SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
636 </programlisting>
637 </para>
638 </sect2>
643 <synopsis>
645 </synopsis>
647 <para>
648 Record variables are similar to row-type variables, but they have no
649 predefined structure. They take on the actual row structure of the
651 of a record variable can change each time it is assigned to.
652 A consequence of this is that until a record variable is first assigned
653 to, it has no substructure, and any attempt to access a
654 field in it will draw a run-time error.
655 </para>
657 <para>
661 same concept as a record variable, even though such a function might
662 use a record variable to hold its result. In both cases the actual row
663 structure is unknown when the function is written, but for a function
665 calling query is parsed, whereas a record variable can change its row
666 structure on-the-fly.
667 </para>
668 </sect2>
673 <synopsis>
675 </synopsis>
677 <para>
679 name of a variable, record or row. This is primarily useful if
681 referenced by another name inside a trigger procedure. See also
683 </para>
685 <para>
686 Examples:
687 <programlisting>
688 RENAME id TO user_id;
689 RENAME this_var TO that_var;
690 </programlisting>
691 </para>
693 <note>
694 <para>
699 </para>
700 </note>
701 </sect2>
702 </sect1>
707 <para>
709 statements are processed using the server's main
712 <synopsis>
714 </synopsis>
716 feeding a query like
717 <synopsis>
719 </synopsis>
722 are replaced by parameters, as discussed in detail in
725 be prepared just once and then reused for subsequent
726 evaluations with different values of the variables. Thus, what
727 really happens on first use of an expression is essentially a
730 <programlisting>
731 IF x < y THEN ...
732 </programlisting>
733 what happens behind the scenes is
734 <programlisting>
736 </programlisting>
740 parameter values.
741 The query plan prepared in this way is saved for the life of the database
742 connection, as described in
745 they are useful to know when trying to diagnose a problem.
746 </para>
747 </sect1>
752 <para>
753 In this section and the following ones, we describe all the statement
754 types that are explicitly understood by
756 Anything not recognized as one of these statement types is presumed
757 to be an SQL command and is sent to the main database engine to execute,
760 </para>
765 <para>
767 variable or row/record field is written as:
768 <synopsis>
770 </synopsis>
771 As explained above, the expression in such a statement is evaluated
773 database engine. The expression must yield a single value.
774 </para>
776 <para>
777 If the expression's result data type doesn't match the variable's
778 data type, or the variable has a specific size/precision
781 the result type's output-function and
782 the variable type's input-function. Note that this could potentially
783 result in run-time errors generated by the input function, if the
784 string form of the result value is not acceptable to the input function.
785 </para>
787 <para>
788 Examples:
789 <programlisting>
790 tax := subtotal * 0.06;
791 my_record.user_id := 20;
792 </programlisting>
793 </para>
794 </sect2>
799 <para>
800 For any SQL command that does not return rows, for example
803 just by writing the command.
804 </para>
806 <para>
808 in the command text is replaced by a parameter symbol, and then the
809 current value of the variable is provided as the parameter value
810 at run time. This is exactly like the processing described earlier
812 As an example, if you write:
813 <programlisting>
814 DECLARE
815 key TEXT;
816 delta INTEGER;
817 BEGIN
818 ...
819 UPDATE mytab SET val = val + delta WHERE id = key;
820 </programlisting>
821 the command text seen by the main SQL engine will look like:
822 <programlisting>
824 </programlisting>
825 Although you don't normally have to think about this, it's helpful
826 to know it when you need to make sense of syntax-error messages.
827 </para>
829 <caution>
830 <para>
832 matching one of the function's declared variables; it is not bright
833 enough to know whether that's what you meant! Thus, it is a bad idea
834 to use a variable name that is the same as any table, column, or
835 function name that you need to reference in commands within the
837 </para>
838 </caution>
840 <para>
841 When executing a SQL command in this way,
843 and re-uses the plan on subsequent executions, for the life of
844 the database connection. The implications of this are discussed
846 </para>
848 <para>
850 query but discard the result, for example when calling a function
851 that has side-effects but no useful result value. To do
855 <synopsis>
857 </synopsis>
864 substituted into the query just as for commands that return no result,
865 and the plan is cached in the same way. Also, the special variable
867 least one row, or false if it produced no rows (see
869 </para>
871 <note>
872 <para>
874 would accomplish this result, but at
875 present the only accepted way to do it is
879 next section.
880 </para>
881 </note>
883 <para>
884 An example:
885 <programlisting>
886 PERFORM create_mv('cs_session_page_requests_mv', my_query);
887 </programlisting>
888 </para>
889 </sect2>
897 </indexterm>
902 </indexterm>
904 <para>
905 The result of a SQL command yielding a single row (possibly of multiple
906 columns) can be assigned to a record variable, row-type variable, or list
907 of scalar variables. This is done by writing the base SQL command and
910 <synopsis>
911 SELECT <replaceable>select_expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable> FROM ...;
912 INSERT ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
913 UPDATE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
914 DELETE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
915 </synopsis>
918 variable, or a comma-separated list of simple variables and
919 record/row fields.
921 substituted into the rest of the query, and the plan is cached,
922 just as described above for commands that do not return rows.
929 </para>
931 <tip>
932 <para>
936 target is a newly created table. If you want to create a table from a
940 </para>
941 </tip>
943 <para>
944 If a row or a variable list is used as target, the query's result columns
945 must exactly match the structure of the target as to number and data
946 types, or a run-time error
947 occurs. When a record variable is the target, it automatically
948 configures itself to the row type of the query result columns.
949 </para>
951 <para>
953 command. Customarily it is written either just before or just after
956 command types. It is recommended that you follow this convention
958 stricter in future versions.
959 </para>
961 <para>
964 row returned by the query, or to nulls if the query returned no rows.
967 after the first row are discarded.
970 determine whether a row was returned:
972 <programlisting>
973 SELECT * INTO myrec FROM emp WHERE empname = myname;
974 IF NOT FOUND THEN
975 RAISE EXCEPTION 'employee % not found', myname;
976 END IF;
977 </programlisting>
980 return exactly one row or a run-time error will be reported, either
982 (more than one row). You can use an exception block if you wish
983 to catch the error, for example:
985 <programlisting>
986 BEGIN
987 SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
988 EXCEPTION
989 WHEN NO_DATA_FOUND THEN
990 RAISE EXCEPTION 'employee % not found', myname;
991 WHEN TOO_MANY_ROWS THEN
992 RAISE EXCEPTION 'employee % not unique', myname;
993 END;
994 </programlisting>
997 </para>
999 <para>
1002 an error for more than one returned row, even when
1005 which affected row should be returned.
1006 </para>
1008 <note>
1009 <para>
1012 </para>
1013 </note>
1015 <para>
1016 To handle cases where you need to process multiple result rows
1018 </para>
1020 </sect2>
1025 <para>
1026 Oftentimes you will want to generate dynamic commands inside your
1028 that will involve different tables or different data types each
1030 normal attempts to cache plans for commands (as discussed in
1032 scenarios. To handle this sort of problem, the
1035 <synopsis>
1036 EXECUTE <replaceable class="command">command-string</replaceable> <optional> INTO <optional>STRICT</optional> <replaceable>target</replaceable> </optional> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;
1037 </synopsis>
1042 is a record variable, a row variable, or a comma-separated list of
1043 simple variables and record/row fields, into which the results of
1045 supply values to be inserted into the command.
1046 </para>
1048 <para>
1050 computed command string. Any required variable values must be inserted
1051 in the command string as it is constructed; or you can use parameters
1052 as described below.
1053 </para>
1055 <para>
1056 Also, there is no plan caching for commands executed via
1058 command is prepared each time the statement is run. Thus the command
1059 string can be dynamically created within the function to perform
1060 actions on different tables and columns.
1061 </para>
1063 <para>
1065 a SQL command returning rows should be assigned. If a row
1066 or variable list is provided, it must exactly match the structure
1067 of the query's results (when a
1068 record variable is used, it will configure itself to match the
1069 result structure automatically). If multiple rows are returned,
1071 variable. If no rows are returned, NULL is assigned to the
1073 clause is specified, the query results are discarded.
1074 </para>
1076 <para>
1078 unless the query produces exactly one row.
1079 </para>
1081 <para>
1082 The command string can use parameter values, which are referenced
1085 clause. This method is often preferable to inserting data values
1086 into the command string as text: it avoids run-time overhead of
1087 converting the values to text and back, and it is much less prone
1088 to SQL-injection attacks since there is no need for quoting or escaping.
1089 An example is:
1090 <programlisting>
1092 INTO c
1093 USING checked_user, checked_date;
1094 </programlisting>
1096 Note that parameter symbols can only be used for data values
1097 — if you want to use dynamically determined table or column
1098 names, you must insert them into the command string textually.
1099 For example, if the preceding query needed to be done against a
1100 dynamically selected table, you could do this:
1101 <programlisting>
1102 EXECUTE 'SELECT count(*) FROM '
1103 || tabname::regclass
1105 INTO c
1106 USING checked_user, checked_date;
1107 </programlisting>
1108 </para>
1110 <para>
1113 functionally equivalent to just writing the command directly in
1117 the command on each execution, generating a plan that is specific
1118 to the current parameter values; whereas
1120 and caches it for re-use. In situations where the best plan depends
1122 significantly faster; while when the plan is not sensitive to parameter
1123 values, re-planning will be a waste.
1124 </para>
1126 <para>
1130 itself.
1131 </para>
1133 <note>
1134 <para>
1138 statement supported by the
1142 </para>
1143 </note>
1148 <indexterm>
1151 </indexterm>
1153 <indexterm>
1156 </indexterm>
1158 <indexterm>
1161 </indexterm>
1163 <para>
1164 When working with dynamic commands you will often have to handle escaping
1165 of single quotes. The recommended method for quoting fixed text in your
1166 function body is dollar quoting. (If you have legacy code that does
1167 not use dollar quoting, please refer to the
1169 some effort when translating said code to a more reasonable scheme.)
1170 </para>
1172 <para>
1173 Dynamic values that are to be inserted into the constructed
1174 query require careful handling since they might themselves contain
1175 quote characters.
1176 An example (this assumes that you are using dollar quoting for the
1177 function as a whole, so the quote marks need not be doubled):
1178 <programlisting>
1179 EXECUTE 'UPDATE tbl SET '
1180 || quote_ident(colname)
1181 || ' = '
1182 || quote_literal(newvalue)
1183 || ' WHERE key = '
1184 || quote_literal(keyvalue);
1185 </programlisting>
1186 </para>
1188 <para>
1189 This example demonstrates the use of the
1193 or table identifiers should be passed through
1195 Expressions containing values that should be literal strings in the
1197 These functions take the appropriate steps to return the input text
1198 enclosed in double or single quotes respectively, with any embedded
1199 special characters properly escaped.
1200 </para>
1202 <para>
1211 For example,
1212 <programlisting>
1213 EXECUTE 'UPDATE tbl SET '
1214 || quote_ident(colname)
1215 || ' = '
1216 || quote_nullable(newvalue)
1217 || ' WHERE key = '
1218 || quote_nullable(keyvalue);
1219 </programlisting>
1220 If you are dealing with values that might be null, you should usually
1222 </para>
1224 <para>
1225 As always, care must be taken to ensure that null values in a query do
1227 <programlisting>
1228 'WHERE key = ' || quote_nullable(keyvalue)
1229 </programlisting>
1232 is always null. If you wish null to work like an ordinary key value,
1233 you would need to rewrite the above as
1234 <programlisting>
1235 'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
1236 </programlisting>
1241 </para>
1243 <para>
1244 Note that dollar quoting is only useful for quoting fixed text.
1245 It would be a very bad idea to try to write this example as:
1246 <programlisting>
1247 EXECUTE 'UPDATE tbl SET '
1248 || quote_ident(colname)
1249 || ' = $$'
1250 || newvalue
1251 || '$$ WHERE key = '
1252 || quote_literal(keyvalue);
1253 </programlisting>
1256 apply to any other dollar-quoting delimiter you might pick.
1257 So, to safely quote text that is not known in advance, you
1260 </para>
1261 </example>
1263 <para>
1264 A much larger example of a dynamic command and
1268 </para>
1269 </sect2>
1274 <para>
1275 There are several ways to determine the effect of a command. The
1277 command, which has the form:
1279 <synopsis>
1280 GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
1281 </synopsis>
1283 This command allows retrieval of system status indicators. Each
1285 value to be assigned to the specified variable (which should be
1286 of the right data type to receive it). The currently available
1290 the OID of the last row inserted by the most recent
1293 table containing OIDs.
1294 </para>
1296 <para>
1297 An example:
1298 <programlisting>
1299 GET DIAGNOSTICS integer_var = ROW_COUNT;
1300 </programlisting>
1301 </para>
1303 <para>
1304 The second method to determine the effects of a command is to check the
1308 It is set by each of the following types of statements:
1309 <itemizedlist>
1310 <listitem>
1311 <para>
1314 row is returned.
1315 </para>
1316 </listitem>
1317 <listitem>
1318 <para>
1320 true if it produces (and discards) one or more rows, false if
1321 no row is produced.
1322 </para>
1323 </listitem>
1324 <listitem>
1325 <para>
1328 row is affected, false if no row is affected.
1329 </para>
1330 </listitem>
1331 <listitem>
1332 <para>
1334 true if it returns a row, false if no row is returned.
1335 </para>
1336 </listitem>
1337 <listitem>
1338 <para>
1340 true if it successfully repositions the cursor, false otherwise.
1341 </para>
1342 </listitem>
1344 <listitem>
1345 <para>
1347 if it iterates one or more times, else false. This applies to
1356 execution of other statements within the loop body.
1357 </para>
1358 </listitem>
1359 </itemizedlist>
1363 affect only the current function.
1364 </para>
1366 </sect2>
1371 <para>
1372 Sometimes a placeholder statement that does nothing is useful.
1373 For example, it can indicate that one arm of an if/then/else
1374 chain is deliberately empty. For this purpose, use the
1377 <synopsis>
1378 NULL;
1379 </synopsis>
1380 </para>
1382 <para>
1383 For example, the following two fragments of code are equivalent:
1384 <programlisting>
1385 BEGIN
1386 y := x / 0;
1387 EXCEPTION
1388 WHEN division_by_zero THEN
1389 NULL; -- ignore the error
1390 END;
1391 </programlisting>
1393 <programlisting>
1394 BEGIN
1395 y := x / 0;
1396 EXCEPTION
1397 WHEN division_by_zero THEN -- ignore the error
1398 END;
1399 </programlisting>
1400 Which is preferable is a matter of taste.
1401 </para>
1403 <note>
1404 <para>
1405 In Oracle's PL/SQL, empty statement lists are not allowed, and so
1408 just write nothing, instead.
1409 </para>
1410 </note>
1412 </sect2>
1413 </sect1>
1418 <para>
1419 Control structures are probably the most useful (and
1423 flexible and powerful way.
1424 </para>
1429 <para>
1430 There are two commands available that allow you to return data
1432 NEXT</command>.
1433 </para>
1435 <sect3>
1438 <synopsis>
1440 </synopsis>
1442 <para>
1444 function and returns the value of
1447 not return a set.
1448 </para>
1450 <para>
1451 When returning a scalar type, any expression can be used. The
1452 expression's result will be automatically cast into the
1453 function's return type as described for assignments. To return a
1454 composite (row) value, you must write a record or row variable
1456 </para>
1458 <para>
1459 If you declared the function with output parameters, write just
1461 of the output parameter variables will be returned.
1462 </para>
1464 <para>
1467 early; but do not write an expression following
1469 </para>
1471 <para>
1472 The return value of a function cannot be left undefined. If
1473 control reaches the end of the top-level block of the function
1475 error will occur. This restriction does not apply to functions
1478 automatically executed if the top-level block finishes.
1479 </para>
1480 </sect3>
1482 <sect3>
1484 <indexterm>
1487 </indexterm>
1488 <indexterm>
1491 </indexterm>
1493 <synopsis>
1496 RETURN QUERY EXECUTE <replaceable class="command">command-string</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;
1497 </synopsis>
1499 <para>
1502 to follow is slightly different. In that case, the individual
1503 items to return are specified by a sequence of <command>RETURN
1506 is used to indicate that the function has finished executing.
1508 composite data types; with a composite result type, an entire
1511 a query to the function's result set. <command>RETURN
1513 intermixed in a single set-returning function, in which case
1514 their results will be concatenated.
1515 </para>
1517 <para>
1520 they simply append zero or more rows to the function's result
1521 set. Execution then continues with the next statement in the
1524 QUERY</command> commands are executed, the result set is built
1526 argument, causes control to exit the function (or you can just
1527 let control reach the end of the function).
1528 </para>
1530 <para>
1533 query to be executed dynamically. Parameter expressions can
1536 </para>
1538 <para>
1539 If you declared the function with output parameters, write just
1541 execution, the current values of the output parameter
1542 variable(s) will be saved for eventual return as a row of the
1543 result. Note that you must declare the function as returning
1546 when there is just one output parameter of type
1548 function with output parameters.
1549 </para>
1551 <para>
1552 Here is an example of a function using <command>RETURN
1553 NEXT</command>:
1555 <programlisting>
1556 CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
1560 CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
1561 $BODY$
1562 DECLARE
1563 r foo%rowtype;
1564 BEGIN
1565 FOR r IN SELECT * FROM foo
1567 LOOP
1568 -- can do some processing here
1569 RETURN NEXT r; -- return current row of SELECT
1570 END LOOP;
1571 RETURN;
1572 END
1573 $BODY$
1574 LANGUAGE 'plpgsql' ;
1576 SELECT * FROM getallfoo();
1577 </programlisting>
1582 </para>
1584 <note>
1585 <para>
1588 before returning from the function, as discussed above. That
1590 very large result set, performance might be poor: data will be
1591 written to disk to avoid memory exhaustion, but the function
1592 itself will not return until the entire result set has been
1594 allow users to define set-returning functions
1595 that do not have this limitation. Currently, the point at
1596 which data begins being written to disk is controlled by the
1598 configuration variable. Administrators who have sufficient
1599 memory to store larger result sets in memory should consider
1600 increasing this parameter.
1601 </para>
1602 </note>
1603 </sect3>
1604 </sect2>
1609 <para>
1611 alternative commands based on certain conditions.
1613 <itemizedlist>
1614 <listitem>
1616 </listitem>
1617 <listitem>
1619 </listitem>
1620 <listitem>
1622 </listitem>
1623 </itemizedlist>
1626 <itemizedlist>
1627 <listitem>
1629 </listitem>
1630 <listitem>
1632 </listitem>
1633 </itemizedlist>
1634 </para>
1636 <sect3>
1639 <synopsis>
1642 END IF;
1643 </synopsis>
1645 <para>
1649 executed if the condition is true. Otherwise, they are
1650 skipped.
1651 </para>
1653 <para>
1654 Example:
1655 <programlisting>
1657 UPDATE users SET email = v_email WHERE user_id = v_user_id;
1658 END IF;
1659 </programlisting>
1660 </para>
1661 </sect3>
1663 <sect3>
1666 <synopsis>
1669 ELSE
1671 END IF;
1672 </synopsis>
1674 <para>
1677 alternative set of statements that should be executed if the
1678 condition is not true. (Note this includes the case where the
1679 condition evaluates to NULL.)
1680 </para>
1682 <para>
1683 Examples:
1684 <programlisting>
1685 IF parentid IS NULL OR parentid = ''
1686 THEN
1687 RETURN fullname;
1688 ELSE
1689 RETURN hp_true_filename(parentid) || '/' || fullname;
1690 END IF;
1691 </programlisting>
1693 <programlisting>
1695 INSERT INTO users_count (count) VALUES (v_count);
1696 RETURN 't';
1697 ELSE
1698 RETURN 'f';
1699 END IF;
1700 </programlisting>
1701 </para>
1702 </sect3>
1704 <sect3>
1707 <synopsis>
1714 ...
1715 </optional>
1716 </optional>
1717 <optional> ELSE
1719 END IF;
1720 </synopsis>
1722 <para>
1723 Sometimes there are more than just two alternatives.
1725 method of checking several alternatives in turn.
1727 until the first one that is true is found. Then the
1728 associated statement(s) are executed, after which control
1733 </para>
1735 <para>
1736 Here is an example:
1738 <programlisting>
1739 IF number = 0 THEN
1740 result := 'zero';
1742 result := 'positive';
1744 result := 'negative';
1745 ELSE
1746 -- hmm, the only other possibility is that number is null
1747 result := 'NULL';
1748 END IF;
1749 </programlisting>
1750 </para>
1752 <para>
1755 </para>
1757 <para>
1758 An alternative way of accomplishing the same task is to nest
1760 following example:
1762 <programlisting>
1763 IF demo_row.sex = 'm' THEN
1764 pretty_sex := 'man';
1765 ELSE
1766 IF demo_row.sex = 'f' THEN
1767 pretty_sex := 'woman';
1768 END IF;
1769 END IF;
1770 </programlisting>
1771 </para>
1773 <para>
1777 </para>
1778 </sect3>
1780 <sect3>
1783 <synopsis>
1785 WHEN <replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> <optional> ... </optional></optional> THEN
1787 <optional> WHEN <replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> <optional> ... </optional></optional> THEN
1789 ... </optional>
1790 <optional> ELSE
1792 END CASE;
1793 </synopsis>
1795 <para>
1798 is evaluated (once) and successively compared to each
1800 If a match is found, then the corresponding
1807 </para>
1809 <para>
1810 Here is a simple example:
1812 <programlisting>
1813 CASE x
1815 msg := 'one or two';
1816 ELSE
1817 msg := 'other value than one or two';
1818 END CASE;
1819 </programlisting>
1820 </para>
1821 </sect3>
1823 <sect3>
1826 <synopsis>
1827 CASE
1832 ... </optional>
1833 <optional> ELSE
1835 END CASE;
1836 </synopsis>
1838 <para>
1850 </para>
1852 <para>
1853 Here is an example:
1855 <programlisting>
1856 CASE
1858 msg := 'value is between zero and ten';
1860 msg := 'value is between eleven and twenty';
1861 END CASE;
1862 </programlisting>
1863 </para>
1865 <para>
1869 than doing nothing.
1870 </para>
1872 </sect3>
1873 </sect2>
1881 </indexterm>
1883 <para>
1887 function to repeat a series of commands.
1888 </para>
1890 <sect3>
1893 <synopsis>
1895 LOOP
1898 </synopsis>
1900 <para>
1906 specify which loop the statement should be applied to.
1907 </para>
1908 </sect3>
1910 <sect3>
1913 <indexterm>
1916 </indexterm>
1918 <synopsis>
1919 EXIT <optional> <replaceable>label</replaceable> </optional> <optional> WHEN <replaceable>boolean-expression</replaceable> </optional>;
1920 </synopsis>
1922 <para>
1924 loop is terminated and the statement following <literal>END
1926 is given, it must be the label of the current or some outer
1927 level of nested loop or block. Then the named loop or block is
1928 terminated and control continues with the statement after the
1930 </para>
1932 <para>
1936 </para>
1938 <para>
1940 not limited to use with unconditional loops. When used with a
1942 control to the next statement after the end of the block.
1943 </para>
1945 <para>
1946 Examples:
1947 <programlisting>
1948 LOOP
1949 -- some computations
1951 EXIT; -- exit loop
1952 END IF;
1953 END LOOP;
1955 LOOP
1956 -- some computations
1958 END LOOP;
1960 BEGIN
1961 -- some computations
1963 EXIT; -- causes exit from the BEGIN block
1964 END IF;
1965 END;
1966 </programlisting>
1967 </para>
1968 </sect3>
1970 <sect3>
1973 <indexterm>
1976 </indexterm>
1978 <synopsis>
1979 CONTINUE <optional> <replaceable>label</replaceable> </optional> <optional> WHEN <replaceable>boolean-expression</replaceable> </optional>;
1980 </synopsis>
1982 <para>
1984 the innermost loop is begun. That is, all statements remaining
1985 in the loop body are skipped, and control returns
1986 to the loop control expression (if any) to determine whether
1987 another loop iteration is needed.
1989 specifies the label of the loop whose execution will be
1990 continued.
1991 </para>
1993 <para>
1996 true. Otherwise, control passes to the statement after
1998 </para>
2000 <para>
2002 is not limited to use with unconditional loops.
2003 </para>
2005 <para>
2006 Examples:
2007 <programlisting>
2008 LOOP
2009 -- some computations
2013 END LOOP;
2014 </programlisting>
2015 </para>
2016 </sect3>
2019 <sect3>
2022 <indexterm>
2025 </indexterm>
2027 <synopsis>
2032 </synopsis>
2034 <para>
2036 sequence of statements so long as the
2038 evaluates to true. The expression is checked just before
2039 each entry to the loop body.
2040 </para>
2042 <para>
2043 For example:
2044 <programlisting>
2046 -- some computations here
2047 END LOOP;
2049 WHILE NOT done LOOP
2050 -- some computations here
2051 END LOOP;
2052 </programlisting>
2053 </para>
2054 </sect3>
2059 <synopsis>
2061 FOR <replaceable>name</replaceable> IN <optional> REVERSE </optional> <replaceable>expression</replaceable> .. <replaceable>expression</replaceable> <optional> BY <replaceable>expression</replaceable> </optional> LOOP
2064 </synopsis>
2066 <para>
2068 of integer values. The variable
2071 definition of the variable name is ignored within the loop).
2072 The two expressions giving
2073 the lower and upper bound of the range are evaluated once when entering
2076 clause, which again is evaluated once on loop entry.
2078 subtracted, rather than added, after each iteration.
2079 </para>
2081 <para>
2083 <programlisting>
2086 END LOOP;
2090 END LOOP;
2094 END LOOP;
2095 </programlisting>
2096 </para>
2098 <para>
2099 If the lower bound is greater than the upper bound (or less than,
2101 executed at all. No error is raised.
2102 </para>
2104 <para>
2107 referenced with a qualified name, using that
2109 </para>
2110 </sect3>
2111 </sect2>
2116 <para>
2118 the results of a query and manipulate that data
2119 accordingly. The syntax is:
2120 <synopsis>
2125 </synopsis>
2127 or comma-separated list of scalar variables.
2130 executed for each row. Here is an example:
2131 <programlisting>
2132 CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
2133 DECLARE
2134 mviews RECORD;
2135 BEGIN
2136 PERFORM cs_log('Refreshing materialized views...');
2138 FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
2140 -- Now "mviews" has one record from cs_materialized_views
2142 PERFORM cs_log('Refreshing materialized view ' || quote_ident(mviews.mv_name) || ' ...');
2143 EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
2144 EXECUTE 'INSERT INTO ' || quote_ident(mviews.mv_name) || ' ' || mviews.mv_query;
2145 END LOOP;
2147 PERFORM cs_log('Done refreshing materialized views.');
2148 RETURN 1;
2149 END;
2150 $$ LANGUAGE plpgsql;
2151 </programlisting>
2154 assigned row value is still accessible after the loop.
2155 </para>
2157 <para>
2159 statement can be any SQL command that returns rows to the caller:
2164 </para>
2166 <para>
2168 and the query plan is cached for possible re-use, as discussed in
2171 </para>
2173 <para>
2175 rows:
2176 <synopsis>
2178 FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional> LOOP
2181 </synopsis>
2182 This is like the previous form, except that the source query
2183 is specified as a string expression, which is evaluated and replanned
2185 choose the speed of a preplanned query or the flexibility of a dynamic
2189 </para>
2191 <para>
2192 Another way to specify the query whose results should be iterated
2193 through is to declare it as a cursor. This is described in
2195 </para>
2196 </sect2>
2201 <indexterm>
2204 </indexterm>
2206 <para>
2208 function aborts execution of the function, and indeed of the
2209 surrounding transaction as well. You can trap errors and recover
2214 <synopsis>
2216 <optional> DECLARE
2218 BEGIN
2220 EXCEPTION
2221 WHEN <replaceable>condition</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
2223 <optional> WHEN <replaceable>condition</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
2225 ... </optional>
2226 END;
2227 </synopsis>
2228 </para>
2230 <para>
2231 If no error occurs, this form of block simply executes all the
2238 matching the error that occurred. If a match is found, the
2240 executed, and then control passes to the next statement after
2243 the error can be caught by an enclosing block with
2245 of the function.
2246 </para>
2248 <para>
2251 name matches any error within its category. The special
2255 not case-sensitive. Also, an error condition can be specified
2257 <programlisting>
2258 WHEN division_by_zero THEN ...
2259 WHEN SQLSTATE '22012' THEN ...
2260 </programlisting>
2261 </para>
2263 <para>
2264 If a new error occurs within the selected
2268 </para>
2270 <para>
2273 remain as they were when the error occurred, but all changes
2274 to persistent database state within the block are rolled back.
2275 As an example, consider this fragment:
2277 <programlisting>
2278 INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
2279 BEGIN
2280 UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
2281 x := x + 1;
2282 y := x / 0;
2283 EXCEPTION
2284 WHEN division_by_zero THEN
2285 RAISE NOTICE 'caught division_by_zero';
2286 RETURN x;
2287 END;
2288 </programlisting>
2296 block is not rolled back, however, so the end result is that the database
2298 </para>
2300 <tip>
2301 <para>
2303 more expensive to enter and exit than a block without one. Therefore,
2305 </para>
2306 </tip>
2308 <para>
2310 variable contains the error code that corresponds to the
2311 exception that was raised (refer to <xref
2314 error message associated with the exception. These variables are
2315 undefined outside exception handlers.
2316 </para>
2320 <para>
2322 This example uses exception handling to perform either
2325 <programlisting>
2326 CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
2328 CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
2329 $$
2330 BEGIN
2331 LOOP
2332 -- first try to update the key
2333 UPDATE db SET b = data WHERE a = key;
2334 IF found THEN
2335 RETURN;
2336 END IF;
2337 -- not there, so try to insert the key
2338 -- if someone else inserts the same key concurrently,
2339 -- we could get a unique-key failure
2340 BEGIN
2341 INSERT INTO db(a,b) VALUES (key, data);
2342 RETURN;
2343 EXCEPTION WHEN unique_violation THEN
2344 -- do nothing, and loop to try the UPDATE again
2345 END;
2346 END LOOP;
2347 END;
2348 $$
2349 LANGUAGE plpgsql;
2351 SELECT merge_db(1, 'david');
2352 SELECT merge_db(1, 'dennis');
2353 </programlisting>
2355 </para>
2356 </example>
2357 </sect2>
2358 </sect1>
2366 </indexterm>
2368 <para>
2369 Rather than executing a whole query at once, it is possible to set
2371 the query result a few rows at a time. One reason for doing this is
2372 to avoid memory overrun when the result contains a large number of
2375 internally to avoid memory problems.) A more interesting usage is to
2376 return a reference to a cursor that a function has created, allowing the
2377 caller to read the rows. This provides an efficient way to return
2378 large row sets from functions.
2379 </para>
2384 <para>
2386 cursor variables, which are always of the special data type
2389 Another way is to use the cursor declaration syntax,
2390 which in general is:
2391 <synopsis>
2392 <replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>;
2393 </synopsis>
2398 fetches will be rejected; if neither specification appears, it is
2399 query-dependent whether backward fetches will be allowed.
2403 replaced by parameter values in the given query. The actual
2404 values to substitute for these names will be specified later,
2405 when the cursor is opened.
2406 </para>
2407 <para>
2408 Some examples:
2409 <programlisting>
2410 DECLARE
2411 curs1 refcursor;
2412 curs2 CURSOR FOR SELECT * FROM tenk1;
2413 curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
2414 </programlisting>
2416 but the first can be used with any query, while the second has
2419 replaced by an integer parameter value when the cursor is opened.)
2422 any particular query.
2423 </para>
2424 </sect2>
2429 <para>
2430 Before a cursor can be used to retrieve rows, it must be
2434 cursor variables while the third uses a bound cursor variable.
2435 </para>
2437 <note>
2438 <para>
2439 Bound cursors can also be used without explicitly opening them,
2442 </para>
2443 </note>
2445 <sect3>
2448 <synopsis>
2449 OPEN <replaceable>unbound_cursor</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR <replaceable>query</replaceable>;
2450 </synopsis>
2452 <para>
2453 The cursor variable is opened and given the specified query to
2454 execute. The cursor cannot be open already, and it must have been
2455 declared as an unbound cursor (that is, as a simple
2459 is treated in the same way as other SQL commands in
2461 variable names are substituted, and the query plan is cached for
2463 variable is substituted into the cursor query, the value that is
2465 subsequent changes to the variable will not affect the cursor's
2466 behavior.
2468 options have the same meanings as for a bound cursor.
2469 </para>
2471 <para>
2472 An example:
2473 <programlisting>
2474 OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
2475 </programlisting>
2476 </para>
2477 </sect3>
2479 <sect3>
2482 <synopsis>
2483 OPEN <replaceable>unbound_cursor</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR EXECUTE <replaceable class="command">query_string</replaceable>;
2484 </synopsis>
2486 <para>
2487 The cursor variable is opened and given the specified query to
2488 execute. The cursor cannot be open already, and it must have been
2489 declared as an unbound cursor (that is, as a simple
2492 command. As usual, this gives flexibility so the query plan can vary
2494 and it also means that variable substitution is not done on the
2495 command string.
2498 cursor.
2499 </para>
2501 <para>
2502 An example:
2503 <programlisting>
2504 OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
2505 </programlisting>
2506 </para>
2507 </sect3>
2509 <sect3>
2512 <synopsis>
2513 OPEN <replaceable>bound_cursor</replaceable> <optional> ( <replaceable>argument_values</replaceable> ) </optional>;
2514 </synopsis>
2516 <para>
2518 variable whose query was bound to it when it was declared. The
2519 cursor cannot be open already. A list of actual argument value
2520 expressions must appear if and only if the cursor was declared to
2521 take arguments. These values will be substituted in the query.
2522 The query plan for a bound cursor is always considered cacheable;
2526 behavior was already determined.
2527 </para>
2529 <para>
2530 Note that because variable substitution is done on the bound
2531 cursor's query, there are two ways to pass values into the cursor:
2534 in the query. However, only variables declared before the bound
2535 cursor was declared will be substituted into it. In either case
2536 the value to be passed is determined at the time of the
2538 </para>
2540 <para>
2541 Examples:
2542 <programlisting>
2543 OPEN curs2;
2544 OPEN curs3(42);
2545 </programlisting>
2546 </para>
2547 </sect3>
2548 </sect2>
2553 <para>
2554 Once a cursor has been opened, it can be manipulated with the
2555 statements described here.
2556 </para>
2558 <para>
2559 These manipulations need not occur in the same function that
2561 value out of a function and let the caller operate on the cursor.
2563 of a so-called portal containing the active query for the cursor. This name
2565 and so on, without disturbing the portal.)
2566 </para>
2568 <para>
2569 All portals are implicitly closed at transaction end. Therefore
2571 only until the end of the transaction.
2572 </para>
2574 <sect3>
2577 <synopsis>
2578 FETCH <optional> <replaceable>direction</replaceable> { FROM | IN } </optional> <replaceable>cursor</replaceable> INTO <replaceable>target</replaceable>;
2579 </synopsis>
2581 <para>
2583 cursor into a target, which might be a row variable, a record
2584 variable, or a comma-separated list of simple variables, just like
2586 target is set to NULL(s). As with <command>SELECT
2588 be checked to see whether a row was obtained or not.
2589 </para>
2591 <para>
2595 more than one row; namely, it can be
2607 backward are likely to fail unless the cursor was declared or opened
2609 </para>
2611 <para>
2613 variable that references an open cursor portal.
2614 </para>
2616 <para>
2617 Examples:
2618 <programlisting>
2619 FETCH curs1 INTO rowvar;
2620 FETCH curs2 INTO foo, bar, baz;
2621 FETCH LAST FROM curs3 INTO x, y;
2622 FETCH RELATIVE -2 FROM curs4 INTO x;
2623 </programlisting>
2624 </para>
2625 </sect3>
2627 <sect3>
2630 <synopsis>
2631 MOVE <optional> <replaceable>direction</replaceable> { FROM | IN } </optional> <replaceable>cursor</replaceable>;
2632 </synopsis>
2634 <para>
2638 cursor and does not return the row moved to. As with <command>SELECT
2640 be checked to see whether there was a next row to move to.
2641 </para>
2643 <para>
2657 backward are likely to fail unless the cursor was declared or opened
2659 </para>
2661 <para>
2662 Examples:
2663 <programlisting>
2664 MOVE curs1;
2665 MOVE LAST FROM curs3;
2666 MOVE RELATIVE -2 FROM curs4;
2667 </programlisting>
2668 </para>
2669 </sect3>
2671 <sect3>
2674 <synopsis>
2675 UPDATE <replaceable>table</replaceable> SET ... WHERE CURRENT OF <replaceable>cursor</replaceable>;
2676 DELETE FROM <replaceable>table</replaceable> WHERE CURRENT OF <replaceable>cursor</replaceable>;
2677 </synopsis>
2679 <para>
2680 When a cursor is positioned on a table row, that row can be updated
2681 or deleted using the cursor to identify the row. Note that this
2682 only works for simple (non-join, non-grouping) cursor queries.
2683 For additional information see the
2685 reference page.
2686 </para>
2688 <para>
2689 An example:
2690 <programlisting>
2691 UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
2692 </programlisting>
2693 </para>
2694 </sect3>
2696 <sect3>
2699 <synopsis>
2701 </synopsis>
2703 <para>
2705 cursor. This can be used to release resources earlier than end of
2706 transaction, or to free up the cursor variable to be opened again.
2707 </para>
2709 <para>
2710 An example:
2711 <programlisting>
2712 CLOSE curs1;
2713 </programlisting>
2714 </para>
2715 </sect3>
2717 <sect3>
2720 <para>
2722 caller. This is useful to return multiple rows or columns,
2723 especially with very large result sets. To do this, the function
2724 opens the cursor and returns the cursor name to the caller (or simply
2725 opens the cursor using a portal name specified by or otherwise known
2726 to the caller). The caller can then fetch rows from the cursor. The
2727 cursor can be closed by the caller, or it will be closed automatically
2728 when the transaction closes.
2729 </para>
2731 <para>
2732 The portal name used for a cursor can be specified by the
2733 programmer or automatically generated. To specify a portal name,
2739 conflict with any existing portal, and assigns it to the
2741 </para>
2743 <note>
2744 <para>
2745 A bound cursor variable is initialized to the string value
2746 representing its name, so that the portal name is the same as
2747 the cursor variable name, unless the programmer overrides it
2748 by assignment before opening the cursor. But an unbound cursor
2749 variable defaults to the null value initially, so it will receive
2750 an automatically-generated unique name, unless overridden.
2751 </para>
2752 </note>
2754 <para>
2755 The following example shows one way a cursor name can be supplied by
2756 the caller:
2758 <programlisting>
2759 CREATE TABLE test (col text);
2760 INSERT INTO test VALUES ('123');
2762 CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
2763 BEGIN
2764 OPEN $1 FOR SELECT col FROM test;
2765 RETURN $1;
2766 END;
2767 ' LANGUAGE plpgsql;
2769 BEGIN;
2770 SELECT reffunc('funccursor');
2771 FETCH ALL IN funccursor;
2772 COMMIT;
2773 </programlisting>
2774 </para>
2776 <para>
2777 The following example uses automatic cursor name generation:
2779 <programlisting>
2780 CREATE FUNCTION reffunc2() RETURNS refcursor AS '
2781 DECLARE
2782 ref refcursor;
2783 BEGIN
2784 OPEN ref FOR SELECT col FROM test;
2785 RETURN ref;
2786 END;
2787 ' LANGUAGE plpgsql;
2789 BEGIN;
2790 SELECT reffunc2();
2792 reffunc2
2793 --------------------
2795 (1 row)
2797 FETCH ALL IN "<unnamed cursor 1>";
2798 COMMIT;
2799 </programlisting>
2800 </para>
2802 <para>
2803 The following example shows one way to return multiple cursors
2804 from a single function:
2806 <programlisting>
2807 CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
2808 BEGIN
2809 OPEN $1 FOR SELECT * FROM table_1;
2810 RETURN NEXT $1;
2811 OPEN $2 FOR SELECT * FROM table_2;
2812 RETURN NEXT $2;
2813 END;
2814 $$ LANGUAGE plpgsql;
2816 -- need to be in a transaction to use cursors.
2817 BEGIN;
2819 SELECT * FROM myfunc('a', 'b');
2821 FETCH ALL FROM a;
2822 FETCH ALL FROM b;
2823 COMMIT;
2824 </programlisting>
2825 </para>
2826 </sect3>
2827 </sect2>
2832 <para>
2834 iterating through the rows returned by a cursor. The syntax is:
2836 <synopsis>
2838 FOR <replaceable>recordvar</replaceable> IN <replaceable>bound_cursor</replaceable> <optional> ( <replaceable>argument_values</replaceable> ) </optional> LOOP
2841 </synopsis>
2843 The cursor variable must have been bound to some query when it was
2846 the cursor again when the loop exits. A list of actual argument value
2847 expressions must appear if and only if the cursor was declared to take
2848 arguments. These values will be substituted in the query, in just
2852 existing definition of the variable name is ignored within the loop).
2853 Each row returned by the cursor is successively assigned to this
2854 record variable and the loop body is executed.
2855 </para>
2856 </sect2>
2858 </sect1>
2863 <indexterm>
2865 </indexterm>
2867 <indexterm>
2870 </indexterm>
2872 <para>
2874 raise errors.
2876 <synopsis>
2877 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>;
2878 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>;
2879 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>;
2880 RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional>;
2881 RAISE ;
2882 </synopsis>
2889 being the default.
2891 current transaction); the other levels only generate messages of different
2892 priority levels.
2893 Whether messages of a particular priority are reported to the client,
2894 written to the server log, or both is controlled by the
2898 information.
2899 </para>
2901 <para>
2904 (which must be a simple string literal, not an expression). The
2905 format string specifies the error message text to be reported.
2906 The format string can be followed
2907 by optional argument expressions to be inserted into the message.
2909 string representation of the next optional argument's value. Write
2911 </para>
2913 <para>
2916 <programlisting>
2917 RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
2918 </programlisting>
2919 </para>
2921 <para>
2922 You can attach additional information to the error report by writing
2930 expression.
2938 or directly as a five-character SQLSTATE code.
2939 </para>
2941 <para>
2942 This example will abort the transaction with the given error message
2943 and hint:
2944 <programlisting>
2945 RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING HINT = 'Please check your user id';
2946 </programlisting>
2947 </para>
2949 <para>
2950 These two examples show equivalent ways of setting the SQLSTATE:
2951 <programlisting>
2952 RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
2953 RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';
2954 </programlisting>
2955 </para>
2957 <para>
2959 is the condition name or SQLSTATE to be reported, for example:
2960 <programlisting>
2961 RAISE division_by_zero;
2962 RAISE SQLSTATE '22012';
2963 </programlisting>
2965 error message, detail, or hint. Another way to do the earlier
2966 example is
2967 <programlisting>
2968 RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
2969 </programlisting>
2970 </para>
2972 <para>
2976 </para>
2978 <para>
2982 it causes the error currently being handled to be re-thrown to the
2983 next enclosing block.
2984 </para>
2986 <para>
2987 If no condition name nor SQLSTATE is specified in a
2990 text is specified, the default is to use the condition name or
2991 SQLSTATE as message text.
2992 </para>
2994 <note>
2995 <para>
2996 When specifying an error code by SQLSTATE code, you are not
2997 limited to the predefined error codes, but can select any
2998 error code consisting of five digits and/or upper-case ASCII
3000 you avoid throwing error codes that end in three zeroes, because
3001 these are category codes and can only be trapped by trapping
3002 the whole category.
3003 </para>
3004 </note>
3006 </sect1>
3014 </indexterm>
3016 <para>
3018 procedures. A trigger procedure is created with the
3021 the function must be declared with no arguments even if it expects
3024 below.
3025 </para>
3027 <para>
3029 trigger, several special variables are created automatically in the
3030 top-level block. They are:
3032 <variablelist>
3033 <varlistentry>
3035 <listitem>
3036 <para>
3040 </para>
3041 </listitem>
3042 </varlistentry>
3044 <varlistentry>
3046 <listitem>
3047 <para>
3051 </para>
3052 </listitem>
3053 </varlistentry>
3055 <varlistentry>
3057 <listitem>
3058 <para>
3060 fired.
3061 </para>
3062 </listitem>
3063 </varlistentry>
3065 <varlistentry>
3067 <listitem>
3068 <para>
3071 depending on the trigger's definition.
3072 </para>
3073 </listitem>
3074 </varlistentry>
3076 <varlistentry>
3078 <listitem>
3079 <para>
3082 depending on the trigger's definition.
3083 </para>
3084 </listitem>
3085 </varlistentry>
3087 <varlistentry>
3089 <listitem>
3090 <para>
3094 telling for which operation the trigger was fired.
3095 </para>
3096 </listitem>
3097 </varlistentry>
3099 <varlistentry>
3101 <listitem>
3102 <para>
3104 trigger invocation.
3105 </para>
3106 </listitem>
3107 </varlistentry>
3109 <varlistentry>
3111 <listitem>
3112 <para>
3114 invocation. This is now deprecated, and could disappear in a future
3116 </para>
3117 </listitem>
3118 </varlistentry>
3120 <varlistentry>
3122 <listitem>
3123 <para>
3125 caused the trigger invocation.
3126 </para>
3127 </listitem>
3128 </varlistentry>
3130 <varlistentry>
3132 <listitem>
3133 <para>
3135 table that caused the trigger invocation.
3136 </para>
3137 </listitem>
3138 </varlistentry>
3140 <varlistentry>
3142 <listitem>
3143 <para>
3146 </para>
3147 </listitem>
3148 </varlistentry>
3150 <varlistentry>
3152 <listitem>
3153 <para>
3156 The index counts from 0. Invalid
3158 </para>
3159 </listitem>
3160 </varlistentry>
3161 </variablelist>
3162 </para>
3164 <para>
3166 record/row value having exactly the structure of the table the
3167 trigger was fired for.
3168 </para>
3170 <para>
3172 trigger manager to skip the rest of the operation for this row
3173 (i.e., subsequent triggers are not fired, and the
3175 for this row). If a nonnull
3176 value is returned then the operation proceeds with that row value.
3177 Returning a row value different from the original value
3180 To alter the row to be stored, it is possible to replace single values
3182 or to build a complete new record/row to return.
3183 </para>
3185 <para>
3188 always ignored; it might as well be null. However, any of these types of
3189 triggers might still abort the entire operation by raising an error.
3190 </para>
3192 <para>
3195 </para>
3200 <para>
3201 This example trigger ensures that any time a row is inserted or updated
3202 in the table, the current user name and time are stamped into the
3203 row. And it checks that an employee's name is given and that the
3204 salary is a positive value.
3205 </para>
3207 <programlisting>
3208 CREATE TABLE emp (
3209 empname text,
3210 salary integer,
3211 last_date timestamp,
3212 last_user text
3213 );
3215 CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
3216 BEGIN
3217 -- Check that empname and salary are given
3218 IF NEW.empname IS NULL THEN
3219 RAISE EXCEPTION 'empname cannot be null';
3220 END IF;
3221 IF NEW.salary IS NULL THEN
3222 RAISE EXCEPTION '% cannot have null salary', NEW.empname;
3223 END IF;
3225 -- Who works for us when she must pay for it?
3227 RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
3228 END IF;
3230 -- Remember who changed the payroll when
3231 NEW.last_date := current_timestamp;
3232 NEW.last_user := current_user;
3233 RETURN NEW;
3234 END;
3235 $emp_stamp$ LANGUAGE plpgsql;
3237 CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
3238 FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
3239 </programlisting>
3240 </example>
3242 <para>
3243 Another way to log changes to a table involves creating a new table that
3244 holds a row for each insert, update, or delete that occurs. This approach
3245 can be thought of as auditing changes to a table.
3248 </para>
3253 <para>
3254 This example trigger ensures that any insert, update or delete of a row
3255 in the <literal>emp</literal> table is recorded (i.e., audited) in the <literal>emp_audit</literal> table.
3256 The current time and user name are stamped into the row, together with
3257 the type of operation performed on it.
3258 </para>
3260 <programlisting>
3261 CREATE TABLE emp (
3262 empname text NOT NULL,
3263 salary integer
3264 );
3266 CREATE TABLE emp_audit(
3267 operation char(1) NOT NULL,
3268 stamp timestamp NOT NULL,
3269 userid text NOT NULL,
3270 empname text NOT NULL,
3271 salary integer
3272 );
3274 CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
3275 BEGIN
3276 --
3277 -- Create a row in emp_audit to reflect the operation performed on emp,
3278 -- make use of the special variable TG_OP to work out the operation.
3279 --
3280 IF (TG_OP = 'DELETE') THEN
3281 INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
3282 RETURN OLD;
3283 ELSIF (TG_OP = 'UPDATE') THEN
3284 INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
3285 RETURN NEW;
3286 ELSIF (TG_OP = 'INSERT') THEN
3287 INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
3288 RETURN NEW;
3289 END IF;
3290 RETURN NULL; -- result is ignored since this is an AFTER trigger
3291 END;
3292 $emp_audit$ LANGUAGE plpgsql;
3294 CREATE TRIGGER emp_audit
3295 AFTER INSERT OR UPDATE OR DELETE ON emp
3296 FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
3297 </programlisting>
3298 </example>
3300 <para>
3301 One use of triggers is to maintain a summary table
3302 of another table. The resulting summary can be used in place of the
3303 original table for certain queries — often with vastly reduced run
3304 times.
3305 This technique is commonly used in Data Warehousing, where the tables
3306 of measured or observed data (called fact tables) might be extremely large.
3309 a summary table for a fact table in a data warehouse.
3310 </para>
3314 <title>A <application>PL/pgSQL</application> Trigger Procedure For Maintaining A Summary Table</title>
3316 <para>
3317 The schema detailed here is partly based on the <emphasis>Grocery Store
3319 by Ralph Kimball.
3320 </para>
3322 <programlisting>
3323 --
3324 -- Main tables - time dimension and sales fact.
3325 --
3326 CREATE TABLE time_dimension (
3327 time_key integer NOT NULL,
3328 day_of_week integer NOT NULL,
3329 day_of_month integer NOT NULL,
3330 month integer NOT NULL,
3331 quarter integer NOT NULL,
3332 year integer NOT NULL
3333 );
3334 CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
3336 CREATE TABLE sales_fact (
3337 time_key integer NOT NULL,
3338 product_key integer NOT NULL,
3339 store_key integer NOT NULL,
3341 units_sold integer NOT NULL,
3343 );
3344 CREATE INDEX sales_fact_time ON sales_fact(time_key);
3346 --
3347 -- Summary table - sales by time.
3348 --
3349 CREATE TABLE sales_summary_bytime (
3350 time_key integer NOT NULL,
3352 units_sold numeric(12) NOT NULL,
3354 );
3355 CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
3357 --
3358 -- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
3359 --
3360 CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $maint_sales_summary_bytime$
3361 DECLARE
3362 delta_time_key integer;
3364 delta_units_sold numeric(12);
3366 BEGIN
3368 -- Work out the increment/decrement amount(s).
3369 IF (TG_OP = 'DELETE') THEN
3371 delta_time_key = OLD.time_key;
3372 delta_amount_sold = -1 * OLD.amount_sold;
3373 delta_units_sold = -1 * OLD.units_sold;
3374 delta_amount_cost = -1 * OLD.amount_cost;
3376 ELSIF (TG_OP = 'UPDATE') THEN
3378 -- forbid updates that change the time_key -
3379 -- (probably not too onerous, as DELETE + INSERT is how most
3380 -- changes will be made).
3381 IF ( OLD.time_key != NEW.time_key) THEN
3382 RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key, NEW.time_key;
3383 END IF;
3385 delta_time_key = OLD.time_key;
3386 delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
3387 delta_units_sold = NEW.units_sold - OLD.units_sold;
3388 delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
3390 ELSIF (TG_OP = 'INSERT') THEN
3392 delta_time_key = NEW.time_key;
3393 delta_amount_sold = NEW.amount_sold;
3394 delta_units_sold = NEW.units_sold;
3395 delta_amount_cost = NEW.amount_cost;
3397 END IF;
3400 -- Insert or update the summary row with the new values.
3402 LOOP
3403 UPDATE sales_summary_bytime
3404 SET amount_sold = amount_sold + delta_amount_sold,
3405 units_sold = units_sold + delta_units_sold,
3406 amount_cost = amount_cost + delta_amount_cost
3407 WHERE time_key = delta_time_key;
3409 EXIT insert_update WHEN found;
3411 BEGIN
3412 INSERT INTO sales_summary_bytime (
3413 time_key,
3414 amount_sold,
3415 units_sold,
3416 amount_cost)
3417 VALUES (
3418 delta_time_key,
3419 delta_amount_sold,
3420 delta_units_sold,
3421 delta_amount_cost
3422 );
3424 EXIT insert_update;
3426 EXCEPTION
3427 WHEN UNIQUE_VIOLATION THEN
3428 -- do nothing
3429 END;
3430 END LOOP insert_update;
3432 RETURN NULL;
3434 END;
3435 $maint_sales_summary_bytime$ LANGUAGE plpgsql;
3437 CREATE TRIGGER maint_sales_summary_bytime
3438 AFTER INSERT OR UPDATE OR DELETE ON sales_fact
3439 FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();
3445 SELECT * FROM sales_summary_bytime;
3446 DELETE FROM sales_fact WHERE product_key = 1;
3447 SELECT * FROM sales_summary_bytime;
3448 UPDATE sales_fact SET units_sold = units_sold * 2;
3449 SELECT * FROM sales_summary_bytime;
3450 </programlisting>
3451 </example>
3453 </sect1>
3458 <para>
3459 This section discusses some implementation details that are
3461 </para>
3466 <para>
3469 appearing in the statement or expression is replaced by a parameter symbol,
3471 of the variable is then provided as the value for the parameter whenever
3472 the statement or expression is executed. As an example, consider the
3473 function
3474 <programlisting>
3475 CREATE FUNCTION logfunc(logtxt text) RETURNS void AS $$
3476 DECLARE
3477 curtime timestamp := now();
3478 BEGIN
3479 INSERT INTO logtable VALUES (logtxt, curtime);
3480 END;
3481 $$ LANGUAGE plpgsql;
3482 </programlisting>
3484 <programlisting>
3487 </programlisting>
3489 actual values of the two variables. (Note: here we are speaking of
3490 the main SQL engine's
3493 </para>
3495 <para>
3496 <emphasis>The substitution mechanism will replace any token that matches a
3497 known variable's name.</> This poses various traps for the unwary.
3498 For example, it is a bad idea
3499 to use a variable name that is the same as any table or column name
3500 that you need to reference in queries within the function, because
3501 what you think is a table or column name will still get replaced.
3505 <programlisting>
3506 INSERT INTO logtable (logtxt, logtime) VALUES (logtxt, curtime);
3507 </programlisting>
3508 This will be fed to the main SQL parser as
3509 <programlisting>
3511 </programlisting>
3512 resulting in a syntax error like this:
3513 <screen>
3514 ERROR: syntax error at or near "$1"
3516 ^
3519 </screen>
3520 </para>
3522 <para>
3523 This example is fairly easy to diagnose, since it leads to an
3524 obvious syntax error. Much nastier are cases where the substitution
3525 is syntactically permissible, since the only symptom may be misbehavior
3526 of the function. In one case, a user wrote something like this:
3527 <programlisting>
3528 DECLARE
3529 val text;
3530 search_key integer;
3531 BEGIN
3532 ...
3533 FOR val IN SELECT val FROM table WHERE key = search_key LOOP ...
3534 </programlisting>
3535 and wondered why all his table entries seemed to be NULL. Of course
3536 what happened here was that the query became
3537 <programlisting>
3539 </programlisting>
3541 current value back to itself for each row.
3542 </para>
3544 <para>
3545 A commonly used coding rule for avoiding such traps is to use a
3547 variables than you use for table and column names. For example,
3548 if all your variables are named
3551 </para>
3553 <para>
3554 Another workaround is to use qualified (dotted) names for SQL entities.
3555 For instance we could safely have written the above example as
3556 <programlisting>
3557 FOR val IN SELECT table.val FROM table WHERE key = search_key LOOP ...
3558 </programlisting>
3560 variable for a trailing component of a qualified name.
3561 However this solution does not work in every case — you can't
3563 Another point is that record and row variable names will be matched to
3564 the first components of qualified names, so a qualified SQL name is
3565 still vulnerable in some cases.
3566 In such cases choosing a non-conflicting variable name is the only way.
3567 </para>
3569 <para>
3570 Another technique you can use is to attach a label to the block in
3571 which your variables are declared, and then qualify the variable names
3573 For example,
3574 <programlisting>
3576 DECLARE
3577 val text;
3578 BEGIN
3579 ...
3580 UPDATE table SET col = pl.val WHERE ...
3581 </programlisting>
3582 This is not in itself a solution to the problem of conflicts,
3583 since an unqualified name in a SQL command is still at risk of being
3585 the intent of potentially-ambiguous code.
3586 </para>
3588 <para>
3589 Variable substitution does not happen in the command string given
3591 insert a varying value into such a command, do so as part of
3592 constructing the string value, as illustrated in
3594 </para>
3596 <para>
3599 because the main SQL engine allows parameter symbols only in these
3600 commands. To use a non-constant name or value in other statement
3601 types (generically called utility statements), you must construct
3603 </para>
3605 </sect2>
3610 <para>
3612 text and produces an internal binary instruction tree the first time the
3613 function is called (within each session). The instruction tree
3614 fully translates the
3617 used in the function are not translated immediately.
3618 </para>
3620 <para>
3623 creates a prepared execution plan (using the
3628 reuse the prepared plan. Thus, a function with conditional code
3629 that contains many statements for which execution plans might be
3630 required will only prepare and save those plans that are really
3631 used during the lifetime of the database connection. This can
3632 substantially reduce the total amount of time required to parse
3633 and generate execution plans for the statements in a
3635 in a specific expression or command cannot be detected until that
3636 part of the function is reached in execution. (Trivial syntax
3637 errors will be detected during the initial parsing pass, but
3638 anything deeper will not be detected until execution.)
3639 </para>
3641 <para>
3643 command in a function, it will reuse that plan for the life of the
3644 database connection. This is usually a win for performance, but it
3645 can cause some problems if you dynamically
3646 alter your database schema. For example:
3648 <programlisting>
3649 CREATE FUNCTION populate() RETURNS integer AS $$
3650 DECLARE
3651 -- declarations
3652 BEGIN
3653 PERFORM my_function();
3654 END;
3655 $$ LANGUAGE plpgsql;
3656 </programlisting>
3658 If you execute the above function, it will reference the OID for
3665 will be compiled afresh, before it will work again. You can avoid
3667 when updating the definition of
3670 </para>
3672 <note>
3673 <para>
3675 will be replaced whenever any schema changes have occurred to any
3676 tables they reference. This eliminates one of the major disadvantages
3677 of saved plans. However, there is no such mechanism for function
3678 references, and thus the above example involving a reference to a
3679 deleted function is still valid.
3680 </para>
3681 </note>
3683 <para>
3685 in this way, SQL commands that appear directly in a
3687 same tables and columns on every execution; that is, you cannot use
3688 a parameter as the name of a table or column in an SQL command. To get
3689 around this restriction, you can construct dynamic commands using
3691 statement — at the price of constructing a new execution plan on
3692 every execution.
3693 </para>
3695 <para>
3696 Another important point is that the prepared plans are parameterized
3698 to change from one use to the next, as discussed in detail above.
3699 Sometimes this means that a plan is less efficient than it would be
3700 if generated for a specific variable value. As an example, consider
3701 <programlisting>
3702 SELECT * INTO myrec FROM dictionary WHERE word LIKE search_term;
3703 </programlisting>
3705 variable. The cached plan for this query will never use an index on
3708 an index the query must be planned with a specific constant
3711 generated for each execution.
3712 </para>
3714 <para>
3715 The mutable nature of record variables presents another problem in this
3716 connection. When fields of a record variable are used in
3717 expressions or statements, the data types of the fields must not
3718 change from one call of the function to the next, since each
3719 expression will be planned using the data type that is present
3721 used to get around this problem when necessary.
3722 </para>
3724 <para>
3725 If the same function is used as a trigger for more than one table,
3727 independently for each such table — that is, there is a cache
3728 for each trigger function and table combination, not just for each
3729 function. This alleviates some of the problems with varying
3730 data types; for instance, a trigger function will be able to work
3732 to have different types in different tables.
3733 </para>
3735 <para>
3736 Likewise, functions having polymorphic argument types have a separate
3737 plan cache for each combination of actual argument types they have been
3738 invoked for, so that data type differences do not cause unexpected
3739 failures.
3740 </para>
3742 <para>
3743 Plan caching can sometimes have surprising effects on the interpretation
3744 of time-sensitive values. For example there
3745 is a difference between what these two functions do:
3747 <programlisting>
3748 CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
3749 BEGIN
3750 INSERT INTO logtable VALUES (logtxt, 'now');
3751 END;
3752 $$ LANGUAGE plpgsql;
3753 </programlisting>
3755 and:
3757 <programlisting>
3758 CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
3759 DECLARE
3760 curtime timestamp;
3761 BEGIN
3762 curtime := 'now';
3763 INSERT INTO logtable VALUES (logtxt, curtime);
3764 END;
3765 $$ LANGUAGE plpgsql;
3766 </programlisting>
3767 </para>
3769 <para>
3779 of the session. Needless to say, this isn't what the programmer
3780 wanted.
3781 </para>
3783 <para>
3793 functions for the conversion. So, the computed time stamp is updated
3794 on each execution as the programmer expects.
3795 </para>
3797 </sect2>
3799 </sect1>
3804 <para>
3805 One good way to develop in
3807 choice to create your functions, and in another window, use
3809 If you are doing it this way, it
3810 is a good idea to write the function using <command>CREATE OR
3811 REPLACE FUNCTION</>. That way you can just reload the file to update
3812 the function definition. For example:
3813 <programlisting>
3814 CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
3815 ....
3816 $$ LANGUAGE plpgsql;
3817 </programlisting>
3818 </para>
3820 <para>
3822 a function definition file with:
3823 <programlisting>
3824 \i filename.sql
3825 </programlisting>
3826 and then immediately issue SQL commands to test the function.
3827 </para>
3829 <para>
3831 GUI database access tool that facilitates development in a
3832 procedural language. One example of such as a tool is
3834 provide convenient features such as escaping single quotes and
3835 making it easier to recreate and debug functions.
3836 </para>
3841 <para>
3844 write the string literal in the ordinary way with surrounding
3845 single quotes, then any single quotes inside the function body
3846 must be doubled; likewise any backslashes must be doubled (assuming
3847 escape string syntax is used).
3848 Doubling quotes is at best tedious, and in more complicated cases
3849 the code can become downright incomprehensible, because you can
3850 easily find yourself needing half a dozen or more adjacent quote marks.
3851 It's recommended that you instead write the function body as a
3854 approach, you never double any quote marks, but instead take care to
3855 choose a different dollar-quoting delimiter for each level of
3856 nesting you need. For example, you might write the <command>CREATE
3857 FUNCTION</command> command as:
3858 <programlisting>
3859 CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
3860 ....
3861 $PROC$ LANGUAGE plpgsql;
3862 </programlisting>
3863 Within this, you might use quote marks for simple literal strings in
3865 that you are assembling as strings. If you need to quote text that
3867 </para>
3869 <para>
3870 The following chart shows what you have to do when writing quote
3871 marks without dollar quoting. It might be useful when translating
3872 pre-dollar quoting code into something more comprehensible.
3873 </para>
3875 <variablelist>
3876 <varlistentry>
3878 <listitem>
3879 <para>
3880 To begin and end the function body, for example:
3881 <programlisting>
3882 CREATE FUNCTION foo() RETURNS integer AS '
3883 ....
3884 ' LANGUAGE plpgsql;
3885 </programlisting>
3886 Anywhere within a single-quoted function body, quote marks
3888 </para>
3889 </listitem>
3890 </varlistentry>
3892 <varlistentry>
3894 <listitem>
3895 <para>
3896 For string literals inside the function body, for example:
3897 <programlisting>
3898 a_output := ''Blah'';
3899 SELECT * FROM users WHERE f_name=''foobar'';
3900 </programlisting>
3901 In the dollar-quoting approach, you'd just write:
3902 <programlisting>
3903 a_output := 'Blah';
3904 SELECT * FROM users WHERE f_name='foobar';
3905 </programlisting>
3907 in either case.
3908 </para>
3909 </listitem>
3910 </varlistentry>
3912 <varlistentry>
3914 <listitem>
3915 <para>
3916 When you need a single quotation mark in a string constant inside the
3917 function body, for example:
3918 <programlisting>
3919 a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''
3920 </programlisting>
3923 </para>
3924 <para>
3925 In the dollar-quoting approach, you'd write:
3926 <programlisting>
3927 a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$
3928 </programlisting>
3929 being careful that any dollar-quote delimiters around this are not
3931 </para>
3932 </listitem>
3933 </varlistentry>
3935 <varlistentry>
3937 <listitem>
3938 <para>
3939 When a single quotation mark in a string inside the function body is
3940 adjacent to the end of that string constant, for example:
3941 <programlisting>
3942 a_output := a_output || '' AND name LIKE ''''foobar''''''
3943 </programlisting>
3946 </para>
3947 <para>
3948 In the dollar-quoting approach, this becomes:
3949 <programlisting>
3950 a_output := a_output || $$ AND name LIKE 'foobar'$$
3951 </programlisting>
3952 </para>
3953 </listitem>
3954 </varlistentry>
3956 <varlistentry>
3958 <listitem>
3959 <para>
3960 When you want two single quotation marks in a string constant (which
3961 accounts for 8 quotation marks) and this is adjacent to the end of that
3962 string constant (2 more). You will probably only need that if
3963 you are writing a function that generates other functions, as in
3965 For example:
3966 <programlisting>
3967 a_output := a_output || '' if v_'' ||
3968 referrer_keys.kind || '' like ''''''''''
3969 || referrer_keys.key_string || ''''''''''
3970 then return '''''' || referrer_keys.referrer_type
3971 || ''''''; end if;'';
3972 </programlisting>
3974 <programlisting>
3975 if v_... like ''...'' then return ''...''; end if;
3976 </programlisting>
3977 </para>
3978 <para>
3979 In the dollar-quoting approach, this becomes:
3980 <programlisting>
3981 a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
3982 || referrer_keys.key_string || $$'
3983 then return '$$ || referrer_keys.referrer_type
3984 || $$'; end if;$$;
3985 </programlisting>
3986 where we assume we only need to put single quote marks into
3988 </para>
3989 </listitem>
3990 </varlistentry>
3991 </variablelist>
3993 </sect2>
3994 </sect1>
3996 <!-- **** Porting from Oracle PL/SQL **** -->
4004 </indexterm>
4009 </indexterm>
4011 <para>
4012 This section explains differences between
4015 to help developers who port applications from
4017 </para>
4019 <para>
4021 aspects. It is a block-structured, imperative language, and all
4022 variables have to be declared. Assignments, loops, conditionals
4023 are similar. The main differences you should keep in mind when
4027 <itemizedlist>
4028 <listitem>
4029 <para>
4031 </para>
4032 </listitem>
4034 <listitem>
4035 <para>
4037 often used to work around the lack of default parameters.
4038 </para>
4039 </listitem>
4041 <listitem>
4042 <para>
4043 If a name used in a SQL command could be either a column name of a
4044 table or a reference to a variable of the function,
4047 to avoid such ambiguities in the first place, but if necessary you
4048 can fix them by properly qualifying the ambiguous name.
4050 </para>
4051 </listitem>
4053 <listitem>
4054 <para>
4056 a string literal. Therefore you need to use dollar quoting or escape
4057 single quotes in the function body. (See <xref
4059 </para>
4060 </listitem>
4062 <listitem>
4063 <para>
4064 Instead of packages, use schemas to organize your functions
4065 into groups.
4066 </para>
4067 </listitem>
4069 <listitem>
4070 <para>
4071 Since there are no packages, there are no package-level variables
4072 either. This is somewhat annoying. You can keep per-session state
4073 in temporary tables instead.
4074 </para>
4075 </listitem>
4077 <listitem>
4078 <para>
4082 from the first number to the second, requiring the loop bounds
4083 to be swapped when porting. This incompatibility is unfortunate
4084 but is unlikely to be changed. (See <xref
4086 </para>
4087 </listitem>
4089 <listitem>
4090 <para>
4092 differently: the target variable(s) must have been declared,
4094 An advantage of this is that the variable values are still accessible
4095 after the loop exits.
4096 </para>
4097 </listitem>
4099 <listitem>
4100 <para>
4101 There are various notational differences for the use of cursor
4102 variables.
4103 </para>
4104 </listitem>
4106 </itemizedlist>
4107 </para>
4109 <sect2>
4112 <para>
4115 </para>
4118 <title>Porting a Simple Function from <application>PL/SQL</> to <application>PL/pgSQL</></title>
4120 <para>
4122 <programlisting>
4123 CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
4124 v_version varchar)
4125 RETURN varchar IS
4126 BEGIN
4127 IF v_version IS NULL THEN
4128 RETURN v_name;
4129 END IF;
4130 RETURN v_name || '/' || v_version;
4131 END;
4132 /
4133 show errors;
4134 </programlisting>
4135 </para>
4137 <para>
4138 Let's go through this function and see the differences compared to
4141 <itemizedlist>
4142 <listitem>
4143 <para>
4145 prototype (not the function body) becomes
4150 is not the only possible function language.
4151 </para>
4152 </listitem>
4154 <listitem>
4155 <para>
4157 to be a string literal, so you need to use quote marks or dollar
4159 in the Oracle approach.
4160 </para>
4161 </listitem>
4163 <listitem>
4164 <para>
4167 reported automatically.
4168 </para>
4169 </listitem>
4170 </itemizedlist>
4171 </para>
4173 <para>
4174 This is how this function would look when ported to
4177 <programlisting>
4178 CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
4179 v_version varchar)
4180 RETURNS varchar AS $$
4181 BEGIN
4182 IF v_version IS NULL THEN
4183 RETURN v_name;
4184 END IF;
4185 RETURN v_name || '/' || v_version;
4186 END;
4187 $$ LANGUAGE plpgsql;
4188 </programlisting>
4189 </para>
4190 </example>
4192 <para>
4194 function that creates another function and how to handle the
4195 ensuing quoting problems.
4196 </para>
4199 <title>Porting a Function that Creates Another Function from <application>PL/SQL</> to <application>PL/pgSQL</></title>
4201 <para>
4202 The following procedure grabs rows from a
4205 sake of efficiency.
4206 </para>
4208 <para>
4209 This is the Oracle version:
4210 <programlisting>
4211 CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
4212 CURSOR referrer_keys IS
4213 SELECT * FROM cs_referrer_keys
4214 ORDER BY try_order;
4215 func_cmd VARCHAR(4000);
4216 BEGIN
4217 func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR,
4218 v_domain IN VARCHAR, v_url IN VARCHAR) RETURN VARCHAR IS BEGIN';
4220 FOR referrer_key IN referrer_keys LOOP
4221 func_cmd := func_cmd ||
4222 ' IF v_' || referrer_key.kind
4223 || ' LIKE ''' || referrer_key.key_string
4224 || ''' THEN RETURN ''' || referrer_key.referrer_type
4225 || '''; END IF;';
4226 END LOOP;
4228 func_cmd := func_cmd || ' RETURN NULL; END;';
4230 EXECUTE IMMEDIATE func_cmd;
4231 END;
4232 /
4233 show errors;
4234 </programlisting>
4235 </para>
4237 <para>
4239 <programlisting>
4240 CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$
4241 DECLARE
4242 CURSOR referrer_keys IS
4243 SELECT * FROM cs_referrer_keys
4244 ORDER BY try_order;
4245 func_body text;
4246 func_cmd text;
4247 BEGIN
4248 func_body := 'BEGIN';
4250 FOR referrer_key IN referrer_keys LOOP
4251 func_body := func_body ||
4252 ' IF v_' || referrer_key.kind
4253 || ' LIKE ' || quote_literal(referrer_key.key_string)
4254 || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
4255 || '; END IF;' ;
4256 END LOOP;
4258 func_body := func_body || ' RETURN NULL; END;';
4260 func_cmd :=
4261 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
4262 v_domain varchar,
4263 v_url varchar)
4264 RETURNS varchar AS '
4265 || quote_literal(func_body)
4266 || ' LANGUAGE plpgsql;' ;
4268 EXECUTE func_cmd;
4269 END;
4270 $func$ LANGUAGE plpgsql;
4271 </programlisting>
4272 Notice how the body of the function is built separately and passed
4274 technique is needed because we cannot safely use dollar quoting for
4275 defining the new function: we do not know for sure what strings will
4280 anything, in particular it might contain dollar signs.) This function
4281 is actually an improvement on the Oracle original, because it will
4284 </para>
4285 </example>
4287 <para>
4292 using a combination of other
4297 easier.
4298 </para>
4301 <title>Porting a Procedure With String Manipulation and
4305 <para>
4307 to parse a URL and return several elements (host, path, and query).
4308 </para>
4310 <para>
4311 This is the Oracle version:
4312 <programlisting>
4313 CREATE OR REPLACE PROCEDURE cs_parse_url(
4314 v_url IN VARCHAR,
4315 v_host OUT VARCHAR, -- This will be passed back
4316 v_path OUT VARCHAR, -- This one too
4317 v_query OUT VARCHAR) -- And this one
4318 IS
4319 a_pos1 INTEGER;
4320 a_pos2 INTEGER;
4321 BEGIN
4322 v_host := NULL;
4323 v_path := NULL;
4324 v_query := NULL;
4325 a_pos1 := instr(v_url, '//');
4327 IF a_pos1 = 0 THEN
4328 RETURN;
4329 END IF;
4330 a_pos2 := instr(v_url, '/', a_pos1 + 2);
4331 IF a_pos2 = 0 THEN
4332 v_host := substr(v_url, a_pos1 + 2);
4333 v_path := '/';
4334 RETURN;
4335 END IF;
4338 a_pos1 := instr(v_url, '?', a_pos2 + 1);
4340 IF a_pos1 = 0 THEN
4341 v_path := substr(v_url, a_pos2);
4342 RETURN;
4343 END IF;
4345 v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
4346 v_query := substr(v_url, a_pos1 + 1);
4347 END;
4348 /
4349 show errors;
4350 </programlisting>
4351 </para>
4353 <para>
4355 <programlisting>
4356 CREATE OR REPLACE FUNCTION cs_parse_url(
4357 v_url IN VARCHAR,
4358 v_host OUT VARCHAR, -- This will be passed back
4359 v_path OUT VARCHAR, -- This one too
4360 v_query OUT VARCHAR) -- And this one
4361 AS $$
4362 DECLARE
4363 a_pos1 INTEGER;
4364 a_pos2 INTEGER;
4365 BEGIN
4366 v_host := NULL;
4367 v_path := NULL;
4368 v_query := NULL;
4369 a_pos1 := instr(v_url, '//');
4371 IF a_pos1 = 0 THEN
4372 RETURN;
4373 END IF;
4374 a_pos2 := instr(v_url, '/', a_pos1 + 2);
4375 IF a_pos2 = 0 THEN
4376 v_host := substr(v_url, a_pos1 + 2);
4377 v_path := '/';
4378 RETURN;
4379 END IF;
4382 a_pos1 := instr(v_url, '?', a_pos2 + 1);
4384 IF a_pos1 = 0 THEN
4385 v_path := substr(v_url, a_pos2);
4386 RETURN;
4387 END IF;
4389 v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
4390 v_query := substr(v_url, a_pos1 + 1);
4391 END;
4392 $$ LANGUAGE plpgsql;
4393 </programlisting>
4395 This function could be used like this:
4396 <programlisting>
4397 SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
4398 </programlisting>
4399 </para>
4400 </example>
4402 <para>
4404 that uses numerous features that are specific to Oracle.
4405 </para>
4410 <para>
4411 The Oracle version:
4413 <programlisting>
4414 CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
4415 a_running_job_count INTEGER;
4417 BEGIN
4420 SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
4424 raise_application_error(-20000, 'Unable to create a new job: a job is currently running.');
4425 END IF;
4427 DELETE FROM cs_active_job;
4428 INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
4430 BEGIN
4431 INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, sysdate);
4432 EXCEPTION
4433 WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
4434 END;
4435 COMMIT;
4436 END;
4437 /
4438 show errors
4439 </programlisting>
4440 </para>
4442 <para>
4445 particular is interesting because it can teach us some things:
4447 <calloutlist>
4449 <para>
4451 </para>
4452 </callout>
4455 <para>
4457 the lock will not be released until the calling transaction is
4458 finished.
4459 </para>
4460 </callout>
4463 <para>
4467 would imply terminating the function's execution. However, in
4468 this particular case it is not necessary anyway, because the lock
4470 we raise an error.
4471 </para>
4472 </callout>
4473 </calloutlist>
4474 </para>
4476 <para>
4479 <programlisting>
4480 CREATE OR REPLACE FUNCTION cs_create_job(v_job_id integer) RETURNS void AS $$
4481 DECLARE
4482 a_running_job_count integer;
4483 BEGIN
4484 LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
4486 SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
4489 RAISE EXCEPTION 'Unable to create a new job: a job is currently running';<co id="co.plpgsql-porting-raise">
4490 END IF;
4492 DELETE FROM cs_active_job;
4493 INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
4495 BEGIN
4496 INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
4497 EXCEPTION
4499 -- don't worry if it already exists
4500 END;
4501 END;
4502 $$ LANGUAGE plpgsql;
4503 </programlisting>
4505 <calloutlist>
4507 <para>
4511 similarly.
4512 </para>
4513 </callout>
4515 <para>
4517 different from Oracle's. The set of built-in exception names
4519 is not currently a way to declare user-defined exception names.
4520 </para>
4521 </callout>
4522 </calloutlist>
4524 The main functional difference between this procedure and the
4526 table will be held until the calling transaction completes. Also, if
4527 the caller later aborts (for example due to an error), the effects of
4528 this procedure will be rolled back.
4529 </para>
4530 </example>
4531 </sect2>
4536 <para>
4537 This section explains a few other things to watch for when porting
4540 </para>
4545 <para>
4549 is equivalent to what you'd get in Oracle with:
4551 <programlisting>
4552 BEGIN
4553 SAVEPOINT s1;
4554 ... code here ...
4555 EXCEPTION
4556 WHEN ... THEN
4557 ROLLBACK TO s1;
4558 ... code here ...
4559 WHEN ... THEN
4560 ROLLBACK TO s1;
4561 ... code here ...
4562 END;
4563 </programlisting>
4565 If you are translating an Oracle procedure that uses
4570 then some actual thought will be required.
4571 </para>
4572 </sect3>
4574 <sect3>
4577 <para>
4585 reliably unless you use these functions.
4586 </para>
4587 </sect3>
4592 <para>
4595 the function always returns the same result when given the same
4597 returns null if any argument is null). Consult the <xref
4599 reference page for details.
4600 </para>
4602 <para>
4603 When making use of these optimization attributes, your
4605 like this:
4607 <programlisting>
4608 CREATE FUNCTION foo(...) RETURNS integer AS $$
4609 ...
4610 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
4611 </programlisting>
4612 </para>
4613 </sect3>
4614 </sect2>
4619 <para>
4620 This section contains the code for a set of Oracle-compatible
4622 your porting efforts.
4623 </para>
4625 <programlisting>
4626 --
4627 -- instr functions that mimic Oracle's counterpart
4628 -- Syntax: instr(string1, string2, [n], [m]) where [] denotes optional parameters.
4629 --
4630 -- Searches string1 beginning at the nth character for the mth occurrence
4631 -- of string2. If n is negative, search backwards. If m is not passed,
4632 -- assume 1 (search starts at first character).
4633 --
4635 CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
4636 DECLARE
4637 pos integer;
4638 BEGIN
4640 RETURN pos;
4641 END;
4642 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
4645 CREATE FUNCTION instr(string varchar, string_to_search varchar, beg_index integer)
4646 RETURNS integer AS $$
4647 DECLARE
4648 pos integer NOT NULL DEFAULT 0;
4649 temp_str varchar;
4650 beg integer;
4651 length integer;
4652 ss_length integer;
4653 BEGIN
4655 temp_str := substring(string FROM beg_index);
4656 pos := position(string_to_search IN temp_str);
4658 IF pos = 0 THEN
4659 RETURN 0;
4660 ELSE
4661 RETURN pos + beg_index - 1;
4662 END IF;
4663 ELSE
4664 ss_length := char_length(string_to_search);
4665 length := char_length(string);
4666 beg := length + beg_index - ss_length + 2;
4669 temp_str := substring(string FROM beg FOR ss_length);
4670 pos := position(string_to_search IN temp_str);
4673 RETURN beg;
4674 END IF;
4676 beg := beg - 1;
4677 END LOOP;
4679 RETURN 0;
4680 END IF;
4681 END;
4682 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
4685 CREATE FUNCTION instr(string varchar, string_to_search varchar,
4686 beg_index integer, occur_index integer)
4687 RETURNS integer AS $$
4688 DECLARE
4689 pos integer NOT NULL DEFAULT 0;
4690 occur_number integer NOT NULL DEFAULT 0;
4691 temp_str varchar;
4692 beg integer;
4693 i integer;
4694 length integer;
4695 ss_length integer;
4696 BEGIN
4698 beg := beg_index;
4699 temp_str := substring(string FROM beg_index);
4701 FOR i IN 1..occur_index LOOP
4702 pos := position(string_to_search IN temp_str);
4704 IF i = 1 THEN
4705 beg := beg + pos - 1;
4706 ELSE
4707 beg := beg + pos;
4708 END IF;
4710 temp_str := substring(string FROM beg + 1);
4711 END LOOP;
4713 IF pos = 0 THEN
4714 RETURN 0;
4715 ELSE
4716 RETURN beg;
4717 END IF;
4718 ELSE
4719 ss_length := char_length(string_to_search);
4720 length := char_length(string);
4721 beg := length + beg_index - ss_length + 2;
4724 temp_str := substring(string FROM beg FOR ss_length);
4725 pos := position(string_to_search IN temp_str);
4728 occur_number := occur_number + 1;
4730 IF occur_number = occur_index THEN
4731 RETURN beg;
4732 END IF;
4733 END IF;
4735 beg := beg - 1;
4736 END LOOP;
4738 RETURN 0;
4739 END IF;
4740 END;
4741 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
4742 </programlisting>
4743 </sect2>
4745 </sect1>
4747 </chapter>