2 $Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.9 2002/11/10 00:35:58 momjian Exp $
6 <title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
8 <indexterm zone="plpgsql">
9 <primary>PL/pgSQL</primary>
13 <application>PL/pgSQL</application> is a loadable procedural language for the
14 <productname>PostgreSQL</productname> database system.
18 This package was originally written by Jan Wieck. This
19 documentation was in part written
20 by Roberto Mello (<email>rmello@fslc.usu.edu</email>).
23 <sect1 id="plpgsql-overview">
24 <title>Overview</title>
27 The design goals of <application>PL/pgSQL</> were to create a loadable procedural
32 can be used to create functions and trigger procedures,
37 adds control structures to the <acronym>SQL</acronym> language,
42 can perform complex computations,
47 inherits all user defined types, functions and operators,
52 can be defined to be trusted by the server,
63 The <application>PL/pgSQL</> call handler parses the function's source text and
64 produces an internal binary instruction tree the first time the
65 function is called (within any one backend process). The instruction tree
67 <application>PL/pgSQL</> statement structure, but individual
68 <acronym>SQL</acronym> expressions and <acronym>SQL</acronym> queries
69 used in the function are not translated immediately.
72 As each expression and <acronym>SQL</acronym> query is first used
73 in the function, the <application>PL/pgSQL</> interpreter creates
74 a prepared execution plan (using the <acronym>SPI</acronym>
75 manager's <function>SPI_prepare</function> and
76 <function>SPI_saveplan</function> functions). Subsequent visits
77 to that expression or query re-use the prepared plan. Thus, a
78 function with conditional code that contains many statements for
79 which execution plans might be required will only prepare and save
80 those plans that are really used during the lifetime of the
81 database connection. This can substantially reduce the total
82 amount of time required to parse, and generate query plans for the
83 statements in a procedural language function. A disadvantage is
84 that errors in a specific expression or query may not be detected
85 until that part of the function is reached in execution.
88 Once <application>PL/pgSQL</> has made a query plan for a particular
89 query in a function, it will re-use that plan for the life of the
90 database connection. This is usually a win for performance, but it
91 can cause some problems if you dynamically
92 alter your database schema. For example:
95 CREATE FUNCTION populate() RETURNS INTEGER AS '
99 PERFORM my_function();
101 ' LANGUAGE 'plpgsql';
103 If you execute the above function, it will reference the OID for
104 <function>my_function()</function> in the query plan produced for
105 the <command>PERFORM</command> statement. Later, if you
106 drop and re-create <function>my_function()</function>, then
107 <function>populate()</function> will not be able to find
108 <function>my_function()</function> anymore. You would then have to
109 re-create <function>populate()</function>, or at least start a new
110 database session so that it will be compiled afresh.
114 Because <application>PL/pgSQL</application> saves execution plans
115 in this way, queries that appear directly in a
116 <application>PL/pgSQL</application> function must refer to the
117 same tables and fields on every execution; that is, you cannot use
118 a parameter as the name of a table or field in a query. To get
119 around this restriction, you can construct dynamic queries using
120 the <application>PL/pgSQL</application> <command>EXECUTE</command>
121 statement --- at the price of constructing a new query plan on
127 The <application>PL/pgSQL</application>
128 <command>EXECUTE</command> statement is not related to the
129 <command>EXECUTE</command> statement supported by the
130 <productname>PostgreSQL</productname> backend. The backend
131 <command>EXECUTE</command> statement cannot be used within
132 <application>PL/pgSQL</> functions (and is not needed).
137 Except for input/output conversion and calculation functions
138 for user defined types, anything that can be defined in C language
139 functions can also be done with <application>PL/pgSQL</application>. It is possible to
140 create complex conditional computation functions and later use
141 them to define operators or use them in functional indexes.
143 <sect2 id="plpgsql-advantages">
144 <title>Advantages of Using <application>PL/pgSQL</application></title>
149 Better performance (see <xref linkend="plpgsql-advantages-performance">)
155 SQL support (see <xref linkend="plpgsql-advantages-sqlsupport">)
161 Portability (see <xref linkend="plpgsql-advantages-portability">)
166 <sect3 id="plpgsql-advantages-performance">
167 <title>Better Performance</title>
170 <acronym>SQL</acronym> is the language
171 <productname>PostgreSQL</> (and most other relational databases)
172 use as query language. It's portable and easy to learn. But
173 every <acronym>SQL</acronym> statement must be executed
174 individually by the database server.
178 That means that your client application must send each query to
179 the database server, wait for it to process it, receive the
180 results, do some computation, then send other queries to the
181 server. All this incurs inter-process communication and may also
182 incur network overhead if your client is on a different machine
183 than the database server.
187 With <application>PL/pgSQL</application> you can group a block of computation and a
188 series of queries <emphasis>inside</emphasis> the
189 database server, thus having the power of a procedural
190 language and the ease of use of SQL, but saving lots of
191 time because you don't have the whole client/server
192 communication overhead. This can make for a
193 considerable performance increase.
197 <sect3 id="plpgsql-advantages-sqlsupport">
198 <title>SQL Support</title>
201 <application>PL/pgSQL</application> adds the power of a procedural language to the
202 flexibility and ease of <acronym>SQL</acronym>. With
203 <application>PL/pgSQL</application> you can use all the data types, columns, operators
204 and functions of SQL.
208 <sect3 id="plpgsql-advantages-portability">
209 <title>Portability</title>
212 Because <application>PL/pgSQL</application> functions run inside
213 <productname>PostgreSQL</>, these functions will run on any
214 platform where <productname>PostgreSQL</> runs. Thus you can
215 reuse code and reduce development costs.
220 <sect2 id="plpgsql-overview-developing-in-plpgsql">
221 <title>Developing in <application>PL/pgSQL</application></title>
224 Developing in <application>PL/pgSQL</application> is pretty straight forward, especially
225 if you have developed in other database procedural languages,
226 such as Oracle's <application>PL/SQL</application>. Two good ways of developing in
227 <application>PL/pgSQL</application> are:
232 Using a text editor and reloading the file with <command>psql</command>
238 Using <productname>PostgreSQL</>'s GUI Tool: <application>PgAccess</>
245 One good way to develop in <application>PL/pgSQL</> is to simply
246 use the text editor of your choice to create your functions, and
247 in another window, use <command>psql</command>
248 (<productname>PostgreSQL</>'s interactive monitor) to load those
249 functions. If you are doing it this way, it is a good idea to
250 write the function using <command>CREATE OR REPLACE
251 FUNCTION</>. That way you can reload the file to update the
252 function definition. For example:
254 CREATE OR REPLACE FUNCTION testfunc(INTEGER) RETURNS INTEGER AS '
257 ' LANGUAGE 'plpgsql';
262 While running <command>psql</command>, you can load or reload such a
263 function definition file with
267 and then immediately issue SQL commands to test the function.
271 Another good way to develop in <application>PL/pgSQL</> is using
272 <productname>PostgreSQL</>'s GUI tool: <application>PgAccess</>. It does some
273 nice things for you, like escaping single-quotes, and making
274 it easy to recreate and debug functions.
279 <sect1 id="plpgsql-structure">
280 <title>Structure of <application>PL/pgSQL</application></title>
283 <application>PL/pgSQL</application> is a <emphasis>block
284 structured</emphasis> language. The complete text of a function
285 definition must be a <firstterm>block</>. A block is defined as:
288 <optional> <<label>> </optional>
290 <replaceable>declarations</replaceable> </optional>
292 <replaceable>statements</replaceable>
298 Any <firstterm>statement</> in the statement section of a block
299 can be a <firstterm>sub-block</>. Sub-blocks can be used for
300 logical grouping or to localize variables to a small group
305 The variables declared in the declarations section preceding a
306 block are initialized to their default values every time the
307 block is entered, not only once per function call. For example:
309 CREATE FUNCTION somefunc() RETURNS INTEGER AS '
311 quantity INTEGER := 30;
313 RAISE NOTICE ''Quantity here is %'',quantity; -- Quantity here is 30
316 -- Create a sub-block
319 quantity INTEGER := 80;
321 RAISE NOTICE ''Quantity here is %'',quantity; -- Quantity here is 80
324 RAISE NOTICE ''Quantity here is %'',quantity; -- Quantity here is 50
328 ' LANGUAGE 'plpgsql';
333 It is important not to confuse the use of BEGIN/END for
334 grouping statements in <application>PL/pgSQL</> with the database commands for
335 transaction control. <application>PL/pgSQL</>'s BEGIN/END are only for grouping;
336 they do not start or end a transaction. Functions and trigger procedures
337 are always executed within a transaction established by an outer query
338 --- they cannot start or commit transactions, since
339 <productname>PostgreSQL</productname> does not have nested transactions.
343 <title>Lexical Details</title>
346 Each statement and declaration within a block is terminated
351 All keywords and identifiers can be written in mixed upper- and
352 lower-case. Identifiers are implicitly converted to lower-case
353 unless double-quoted.
357 There are two types of comments in <application>PL/pgSQL</>. A double dash <literal>--</literal>
358 starts a comment that extends to the end of the line. A <literal>/*</literal>
359 starts a block comment that extends to the next occurrence of <literal>*/</literal>.
360 Block comments cannot be nested, but double dash comments can be
361 enclosed into a block comment and a double dash can hide
362 the block comment delimiters <literal>/*</literal> and <literal>*/</literal>.
367 <sect1 id="plpgsql-declarations">
368 <title>Declarations</title>
371 All variables, rows and records used in a block must be declared in the
372 declarations section of the block.
373 (The only exception is that the loop variable of a FOR loop iterating
374 over a range of integer values is automatically declared as an integer
379 <application>PL/pgSQL</> variables can have any SQL data type, such as
380 <type>INTEGER</type>, <type>VARCHAR</type> and
385 Here are some examples of variable declarations:
390 myrow tablename%ROWTYPE;
391 myfield tablename.fieldname%TYPE;
397 The general syntax of a variable declaration is:
399 <replaceable>name</replaceable> <optional> CONSTANT </optional> <replaceable>type</replaceable> <optional> NOT NULL </optional> <optional> { DEFAULT | := } <replaceable>expression</replaceable> </optional>;
404 The DEFAULT clause, if given, specifies the initial value assigned
405 to the variable when the block is entered. If the DEFAULT clause
406 is not given then the variable is initialized to the
407 <acronym>SQL</acronym> NULL value.
411 The CONSTANT option prevents the variable from being assigned to,
412 so that its value remains constant for the duration of the block.
414 is specified, an assignment of a NULL value results in a run-time
415 error. All variables declared as NOT NULL
416 must have a non-NULL default value specified.
420 The default value is evaluated every time the block is entered. So,
421 for example, assigning '<literal>now</literal>' to a variable of type
422 <type>timestamp</type> causes the variable to have the
423 time of the current function call, not when the function was
430 quantity INTEGER DEFAULT 32;
431 url varchar := ''http://mysite.com'';
432 user_id CONSTANT INTEGER := 10;
436 <sect2 id="plpgsql-declaration-aliases">
437 <title>Aliases for Function Parameters</title>
441 <replaceable>name</replaceable> ALIAS FOR <replaceable>$n</replaceable>;
446 Parameters passed to functions are named with the identifiers
447 <literal>$1</literal>, <literal>$2</literal>,
448 etc. Optionally, aliases can be declared for <literal>$n</literal>
449 parameter names for increased readability. Either the alias or the
450 numeric identifier can then be used to refer to the parameter value.
453 CREATE FUNCTION sales_tax(REAL) RETURNS REAL AS '
455 subtotal ALIAS FOR $1;
457 return subtotal * 0.06;
459 ' LANGUAGE 'plpgsql';
462 CREATE FUNCTION instr(VARCHAR,INTEGER) RETURNS INTEGER AS '
464 v_string ALIAS FOR $1;
467 -- Some computations here
469 ' LANGUAGE 'plpgsql';
472 CREATE FUNCTION use_many_fields(tablename) RETURNS TEXT AS '
476 RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
478 ' LANGUAGE 'plpgsql';
483 <sect2 id="plpgsql-declaration-rowtypes">
484 <title>Row Types</title>
488 <replaceable>name</replaceable> <replaceable>tablename</replaceable><literal>%ROWTYPE</literal>;
493 A variable of a composite type is called a <firstterm>row</>
494 variable (or <firstterm>row-type</> variable). Such a variable can hold a
495 whole row of a SELECT or FOR
496 query result, so long as that query's column set matches the declared
497 type of the variable. The individual fields of the row value are
498 accessed using the usual dot notation, for example
499 <literal>rowvar.field</literal>.
503 Presently, a row variable can only be declared using the
504 <literal>%ROWTYPE</literal> notation; although one might expect a
505 bare table name to work as a type declaration, it won't be accepted
506 within <application>PL/pgSQL</application> functions.
510 Parameters to a function can be
511 composite types (complete table rows). In that case, the
512 corresponding identifier $n will be a row variable, and fields can
513 be selected from it, for example <literal>$1.user_id</literal>.
517 Only the user-defined attributes of a table row are accessible in a
518 row-type variable, not OID or other system attributes (because the
519 row could be from a view). The fields of the row type inherit the
520 table's field size or precision for data types such as
521 <type>char(n)</type>.
523 CREATE FUNCTION use_two_tables(tablename) RETURNS TEXT AS '
526 use_t table2name%ROWTYPE;
528 SELECT * INTO use_t FROM table2name WHERE ... ;
529 RETURN in_t.f1 || use_t.f3 || in_t.f5 || use_t.f7;
531 ' LANGUAGE 'plpgsql';
536 <sect2 id="plpgsql-declaration-records">
537 <title>Records</title>
541 <replaceable>name</replaceable> RECORD;
546 Record variables are similar to row-type variables, but they have no
547 predefined structure. They take on the actual row structure of the
548 row they are assigned during a SELECT or FOR command. The substructure
549 of a record variable can change each time it is assigned to.
550 A consequence of this is that until a record variable is first assigned
551 to, <emphasis>it has no</> substructure, and any attempt to access a
552 field in it will draw a run-time error.
556 Note that <literal>RECORD</> is not a true data type, only a placeholder.
560 <sect2 id="plpgsql-declaration-attributes">
561 <title>Attributes</title>
564 Using the <type>%TYPE</type> and <type>%ROWTYPE</type>
565 attributes, you can declare variables with the same
566 data type or structure as another database item (e.g: a
573 <replaceable>variable</replaceable>%TYPE
577 <type>%TYPE</type> provides the data type of a
578 variable or database column. You can use this to
579 declare variables that will hold database
580 values. For example, let's say you have a column
581 named <type>user_id</type> in your
582 <type>users</type> table. To declare a variable with
583 the same data type as <structname>users</>.<structfield>user_id</> you write:
585 user_id users.user_id%TYPE;
590 By using <type>%TYPE</type> you don't need to know
591 the data type of the structure you are referencing,
592 and most important, if the data type of the
593 referenced item changes in the future (e.g: you
594 change your table definition of user_id from INTEGER to
595 REAL), you may not need to change your function
603 <literal><replaceable>table</replaceable>%ROWTYPE</literal>
607 <type>%ROWTYPE</type> provides the composite data type corresponding
608 to a whole row of the specified table.
609 <replaceable>table</replaceable> must be an existing
610 table or view name of the database.
615 users_rec users%ROWTYPE;
616 user_id users.user_id%TYPE;
618 user_id := users_rec.user_id;
621 CREATE FUNCTION does_view_exist(INTEGER) RETURNS bool AS '
624 table_data cs_materialized_views%ROWTYPE;
626 SELECT INTO table_data * FROM cs_materialized_views
634 ' LANGUAGE 'plpgsql';
641 <sect2 id="plpgsql-declaration-renaming-vars">
642 <title>RENAME</title>
646 RENAME <replaceable>oldname</replaceable> TO <replaceable>newname</replaceable>;
649 Using the RENAME declaration you can change the name of a variable,
650 record or row. This is primarily useful if NEW or OLD should be
651 referenced by another name inside a trigger procedure. See also ALIAS.
657 RENAME id TO user_id;
658 RENAME this_var TO that_var;
664 RENAME appears to be broken as of <productname>PostgreSQL</>
665 7.3. Fixing this is of low priority, since ALIAS covers most of
666 the practical uses of RENAME.
673 <sect1 id="plpgsql-expressions">
674 <title>Expressions</title>
677 All expressions used in <application>PL/pgSQL</application> statements
678 are processed using the server's regular SQL executor. Expressions that
680 constants may in fact require run-time evaluation
681 (e.g. <literal>'now'</literal> for the
682 <type>timestamp</type> type) so
683 it is impossible for the <application>PL/pgSQL</application> parser
684 to identify real constant values other than the NULL keyword. All
685 expressions are evaluated internally by executing a query
687 SELECT <replaceable>expression</replaceable>
689 using the <acronym>SPI</acronym> manager. In the expression, occurrences
690 of <application>PL/pgSQL</application> variable
691 identifiers are replaced by parameters and the actual values from
692 the variables are passed to the executor in the parameter array.
693 This allows the query plan for the SELECT to be prepared just once
694 and then re-used for subsequent evaluations.
698 The evaluation done by the <productname>PostgreSQL</productname>
699 main parser has some side
700 effects on the interpretation of constant values. In detail there
701 is a difference between what these two functions do:
704 CREATE FUNCTION logfunc1 (TEXT) RETURNS TIMESTAMP AS '
708 INSERT INTO logtable VALUES (logtxt, ''now'');
711 ' LANGUAGE 'plpgsql';
717 CREATE FUNCTION logfunc2 (TEXT) RETURNS TIMESTAMP AS '
723 INSERT INTO logtable VALUES (logtxt, curtime);
726 ' LANGUAGE 'plpgsql';
729 In the case of <function>logfunc1()</function>, the
730 <productname>PostgreSQL</productname> main parser knows when
731 preparing the plan for the INSERT, that the string
732 <literal>'now'</literal> should be interpreted as
733 <type>timestamp</type> because the target field of <classname>logtable</classname>
734 is of that type. Thus, it will make a constant from it at this
735 time and this constant value is then used in all invocations of
736 <function>logfunc1()</function> during the lifetime of the
737 backend. Needless to say that this isn't what the
742 In the case of <function>logfunc2()</function>, the
743 <productname>PostgreSQL</productname> main parser does not know
744 what type <literal>'now'</literal> should become and therefore
745 it returns a data value of type <type>text</type> containing the string
746 <literal>'now'</literal>. During the ensuing assignment
747 to the local variable <varname>curtime</varname>, the
748 <application>PL/pgSQL</application> interpreter casts this
749 string to the <type>timestamp</type> type by calling the
750 <function>text_out()</function> and <function>timestamp_in()</function>
751 functions for the conversion. So, the computed time stamp is updated
752 on each execution as the programmer expects.
756 The mutable nature of record variables presents a problem in this
757 connection. When fields of a record variable are used in
758 expressions or statements, the data types of the fields must not
759 change between calls of one and the same expression, since the
760 expression will be planned using the data type that is present
761 when the expression is first reached. Keep this in mind when
762 writing trigger procedures that handle events for more than one
763 table. (<command>EXECUTE</command> can be used to get around
764 this problem when necessary.)
768 <sect1 id="plpgsql-statements">
769 <title>Basic Statements</title>
772 In this section and the following ones, we describe all the statement
773 types that are explicitly understood by
774 <application>PL/pgSQL</application>.
775 Anything not recognized as one of these statement types is presumed
776 to be an SQL query, and is sent to the main database engine to execute
777 (after substitution for any <application>PL/pgSQL</application> variables
778 used in the statement). Thus,
779 for example, SQL <command>INSERT</>, <command>UPDATE</>, and
780 <command>DELETE</> commands may be considered to be statements of
781 <application>PL/pgSQL</application>. But they are not specifically
785 <sect2 id="plpgsql-statements-assignment">
786 <title>Assignment</title>
789 An assignment of a value to a variable or row/record field is
792 <replaceable>identifier</replaceable> := <replaceable>expression</replaceable>;
794 As explained above, the expression in such a statement is evaluated
795 by means of an SQL <command>SELECT</> command sent to the main
796 database engine. The expression must yield a single value.
800 If the expression's result data type doesn't match the variable's
801 data type, or the variable has a specific size/precision
802 (like <type>char(20)</type>), the result value will be implicitly
803 converted by the <application>PL/pgSQL</application> interpreter using
804 the result type's output-function and
805 the variable type's input-function. Note that this could potentially
806 result in run-time errors generated by the input function, if the
807 string form of the result value is not acceptable to the input function.
814 tax := subtotal * 0.06;
819 <sect2 id="plpgsql-select-into">
820 <title>SELECT INTO</title>
823 The result of a SELECT command yielding multiple columns (but
824 only one row) can be assigned to a record variable, row-type
825 variable, or list of scalar variables. This is done by:
828 SELECT INTO <replaceable>target</replaceable> <replaceable>expressions</replaceable> FROM ...;
831 where <replaceable>target</replaceable> can be a record variable, a row
832 variable, or a comma-separated list of simple variables and
833 record/row fields. Note that this is quite different from
834 <productname>PostgreSQL</>'s normal interpretation of SELECT INTO, which is that the
835 INTO target is a newly created table. (If you want to create a
836 table from a SELECT result inside a <application>PL/pgSQL</application> function, use the
837 syntax <command>CREATE TABLE ... AS SELECT</command>.)
841 If a row or a variable list is used as target, the selected values
842 must exactly match the structure of the target(s), or a run-time error
843 occurs. When a record variable is the target, it automatically
844 configures itself to the row type of the query result columns.
848 Except for the INTO clause, the SELECT statement is the same as a normal
849 SQL SELECT query and can use the full power of SELECT.
853 If the SELECT query returns zero rows, null values are assigned to the
854 target(s). If the SELECT query returns multiple rows, the first
855 row is assigned to the target(s) and the rest are discarded.
856 (Note that <quote>the first row</> is not well-defined unless you've
861 At present, the INTO clause can appear almost anywhere in the SELECT
862 query, but it is recommended to place it immediately after the SELECT
863 keyword as depicted above. Future versions of
864 <application>PL/pgSQL</application> may be less forgiving about
865 placement of the INTO clause.
869 You can use <literal>FOUND</literal> immediately after a SELECT
870 INTO statement to determine whether the assignment was successful
871 (that is, at least one row was was returned by the SELECT
872 statement). For example:
875 SELECT INTO myrec * FROM EMP WHERE empname = myname;
877 RAISE EXCEPTION ''employee % not found'', myname;
881 Alternatively, you can use the <literal>IS NULL</literal> (or <literal>ISNULL</>) conditional to
882 test for whether a RECORD/ROW result is null. Note that there is no
883 way to tell whether any additional rows might have been discarded.
892 SELECT INTO users_rec * FROM users WHERE user_id=3;
894 IF users_rec.homepage IS NULL THEN
895 -- user entered no homepage, return "http://"
904 <sect2 id="plpgsql-statements-perform">
905 <title>Executing an expression or query with no result</title>
908 Sometimes one wishes to evaluate an expression or query but
909 discard the result (typically because one is calling a function
910 that has useful side-effects but no useful result value). To do
911 this in <application>PL/pgSQL</application>, use the
912 <command>PERFORM</command> statement:
915 PERFORM <replaceable>query</replaceable>;
918 This executes a <command>SELECT</command>
919 <replaceable>query</replaceable> and discards the
920 result. <application>PL/pgSQL</application> variables are
921 substituted in the query as usual. Also, the special variable
922 <literal>FOUND</literal> is set to true if the query produced at
923 least one row, or false if it produced no rows.
928 One might expect that <command>SELECT</command> with no INTO
929 clause would accomplish this result, but at present the only
930 accepted way to do it is <command>PERFORM</command>.
937 PERFORM create_mv(''cs_session_page_requests_mv'', my_query);
942 <sect2 id="plpgsql-statements-executing-dyn-queries">
943 <title>Executing dynamic queries</title>
946 Oftentimes you will want to generate dynamic queries inside your
947 <application>PL/pgSQL</application> functions, that is, queries
948 that will involve different tables or different data types each
949 time they are executed. <application>PL/pgSQL</application>'s
950 normal attempts to cache plans for queries will not work in such
951 scenarios. To handle this sort of problem, the
952 <command>EXECUTE</command> statement is provided:
955 EXECUTE <replaceable class="command">query-string</replaceable>;
958 where <replaceable>query-string</replaceable> is an expression
959 yielding a string (of type
960 <type>text</type>) containing the <replaceable>query</replaceable>
961 to be executed. This string is fed literally to the SQL engine.
965 Note in particular that no substitution of <application>PL/pgSQL</>
966 variables is done on the query string. The values of variables must
967 be inserted in the query string as it is constructed.
971 When working with dynamic queries you will have to face
972 escaping of single quotes in <application>PL/pgSQL</>. Please refer to the
973 table in <xref linkend="plpgsql-porting">
974 for a detailed explanation that will save you some effort.
978 Unlike all other queries in <application>PL/pgSQL</>, a
979 <replaceable>query</replaceable> run by an
980 <command>EXECUTE</command> statement is not prepared and saved
981 just once during the life of the server. Instead, the
982 <replaceable>query</replaceable> is prepared each time the
983 statement is run. The <replaceable>query-string</replaceable> can
984 be dynamically created within the procedure to perform actions on
985 variable tables and fields.
989 The results from <command>SELECT</command> queries are discarded
990 by <command>EXECUTE</command>, and <command>SELECT INTO</command>
991 is not currently supported within <command>EXECUTE</command>.
992 So, the only way to extract a result from a dynamically-created
993 <command>SELECT</command> is to use the FOR-IN-EXECUTE form
1001 EXECUTE ''UPDATE tbl SET ''
1002 || quote_ident(fieldname)
1004 || quote_literal(newvalue)
1011 This example shows use of the functions
1012 <function>quote_ident</function>(<type>TEXT</type>) and
1013 <function>quote_literal</function>(<type>TEXT</type>).
1014 Variables containing field and table identifiers should be
1015 passed to function <function>quote_ident()</function>.
1016 Variables containing literal elements of the dynamic query
1017 string should be passed to
1018 <function>quote_literal()</function>. Both take the
1019 appropriate steps to return the input text enclosed in single
1020 or double quotes and with any embedded special characters
1025 Here is a much larger example of a dynamic query and
1026 <command>EXECUTE</command>:
1028 CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS '
1030 referrer_keys RECORD; -- Declare a generic record to be used in a FOR
1031 a_output varchar(4000);
1033 a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar)
1034 RETURNS VARCHAR AS ''''
1036 v_host ALIAS FOR $1;
1037 v_domain ALIAS FOR $2;
1042 -- Notice how we scan through the results of a query in a FOR loop
1043 -- using the FOR <record> construct.
1046 FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
1047 a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE ''''''''''
1048 || referrer_keys.key_string || '''''''''' THEN RETURN ''''''
1049 || referrer_keys.referrer_type || ''''''; END IF;'';
1052 a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE ''''plpgsql'''';'';
1054 -- This works because we are not substituting any variables
1055 -- Otherwise it would fail. Look at PERFORM for another way to run functions
1059 ' LANGUAGE 'plpgsql';
1064 <sect2 id="plpgsql-statements-diagnostics">
1065 <title>Obtaining result status</title>
1068 There are several ways to determine the effect of a command. The
1069 first method is to use the <literal>GET DIAGNOSTICS</literal>,
1073 GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional> ;
1076 This command allows retrieval of system status indicators. Each
1077 <replaceable>item</replaceable> is a keyword identifying a state
1078 value to be assigned to the specified variable (which should be
1079 of the right data type to receive it). The currently available
1080 status items are <varname>ROW_COUNT</>, the number of rows
1081 processed by the last <acronym>SQL</acronym> query sent down to
1082 the <acronym>SQL</acronym> engine; and <varname>RESULT_OID</>,
1083 the OID of the last row inserted by the most recent
1084 <acronym>SQL</acronym> query. Note that <varname>RESULT_OID</>
1085 is only useful after an INSERT query.
1090 GET DIAGNOSTICS var_integer = ROW_COUNT;
1096 There is a special variable named <literal>FOUND</literal> of
1097 type <type>boolean</type>. <literal>FOUND</literal> starts out
1098 false within each <application>PL/pgSQL</application> function.
1099 It is set by each of the following types of statements:
1103 A SELECT INTO statement sets <literal>FOUND</literal>
1104 true if it returns a row, false if no row is returned.
1109 A PERFORM statement sets <literal>FOUND</literal>
1110 true if it produces (discards) a row, false if no row is
1116 UPDATE, INSERT, and DELETE statements set
1117 <literal>FOUND</literal> true if at least one row is
1118 affected, false if no row is affected.
1123 A FETCH statement sets <literal>FOUND</literal>
1124 true if it returns a row, false if no row is returned.
1129 A FOR statement sets <literal>FOUND</literal>
1130 true if it iterates one or more times, else false.
1131 This applies to all three variants of the FOR statement
1132 (integer FOR loops, record-set FOR loops, and dynamic
1133 record-set FOR loops). <literal>FOUND</literal> is only set
1134 when the FOR loop exits: inside the execution of the loop,
1135 <literal>FOUND</literal> is not modified by the FOR statement,
1136 although it may be changed by the execution of other
1137 statements within the loop body.
1141 <literal>FOUND</literal> is a local variable; any changes
1142 to it affect only the current <application>PL/pgSQL</application>
1149 <sect1 id="plpgsql-control-structures">
1150 <title>Control Structures</title>
1153 Control structures are probably the most useful (and
1154 important) part of <application>PL/pgSQL</>. With
1155 <application>PL/pgSQL</>'s control structures,
1156 you can manipulate <productname>PostgreSQL</> data in a very
1157 flexible and powerful way.
1160 <sect2 id="plpgsql-statements-returning">
1161 <title>Returning from a function</title>
1165 RETURN <replaceable>expression</replaceable>;
1168 <command>RETURN</command> with an expression is used to return
1169 from a <application>PL/pgSQL</> function that does not return a
1170 set. The function terminates and the value of
1171 <replaceable>expression</replaceable> is returned to the caller.
1175 To return a composite (row) value, you must write a record or row
1176 variable as the <replaceable>expression</replaceable>. When
1177 returning a scalar type, any expression can be used.
1178 The expression's result will be automatically cast into the
1179 function's return type as described for assignments.
1180 (If you have declared the function to return <type>void</>,
1181 then the expression can be omitted, and will be ignored in any case.)
1185 The return value of a function cannot be left undefined. If
1186 control reaches the end of the top-level block of the function
1187 without hitting a <command>RETURN</command> statement, a run-time
1192 When a <application>PL/pgSQL</> function is declared to return
1193 <literal>SETOF</literal> <replaceable>sometype</>, the procedure
1194 to follow is slightly different. In that case, the individual
1195 items to return are specified in <command>RETURN NEXT</command>
1196 commands, and then a final <command>RETURN</command> command with
1197 no arguments is used to indicate that the function has finished
1198 executing. <command>RETURN NEXT</command> can be used with both
1199 scalar and composite data types; in the later case, an entire
1200 "table" of results will be returned. Functions that use
1201 <command>RETURN NEXT</command> should be called in the following
1205 SELECT * FROM some_func();
1208 That is, the function is used as a table source in a FROM clause.
1211 RETURN NEXT <replaceable>expression</replaceable>;
1214 <command>RETURN NEXT</command> does not actually return from the
1215 function; it simply saves away the value of the expression (or
1216 record or row variable, as appropriate for the data type being
1217 returned). Execution then continues with the next statement in
1218 the <application>PL/pgSQL</> function. As successive
1219 <command>RETURN NEXT</command> commands are executed, the result
1220 set is built up. A final <command>RETURN</commmand>, which need
1221 have no argument, causes control to exit the function.
1226 The current implementation of <command>RETURN NEXT</command> for
1227 <application>PL/pgSQL</> stores the entire result set before
1228 returning from the function, as discussed above. That means that
1229 if a <application>PL/pgSQL</> function produces a very large result set,
1230 performance may be poor: data will be written to disk to avoid
1231 memory exhaustion, but the function itself will not return until
1232 the entire result set has been generated. A future version of
1233 <application>PL/pgSQL</> may allow users to allow users to define set-returning
1234 functions that do not have this limitation. Currently, the point
1235 at which data begins being written to disk is controlled by the
1236 <varname>SORT_MEM</> configuration variable. Administrators who
1237 have sufficient memory to store larger result sets in memory
1238 should consider increasing this parameter.
1243 <sect2 id="plpgsql-conditionals">
1244 <title>Conditionals</title>
1247 <literal>IF</> statements let you execute commands based on
1248 certain conditions. <application>PL/pgSQL</> has four forms of
1252 <para><literal>IF ... THEN</></>
1255 <para><literal>IF ... THEN ... ELSE</></>
1258 <para><literal>IF ... THEN ... ELSE IF</> and</>
1261 <para><literal>IF ... THEN ... ELSIF ... THEN ... ELSE</></>
1267 <title><literal>IF-THEN</></title>
1271 IF <replaceable>boolean-expression</replaceable> THEN
1272 <replaceable>statements</replaceable>
1276 IF-THEN statements are the simplest form of IF. The
1277 statements between THEN and END IF will be executed if
1278 the condition is true. Otherwise, they are skipped.
1281 IF v_user_id <> 0 THEN
1282 UPDATE users SET email = v_email WHERE user_id = v_user_id;
1289 <title><literal>IF-THEN-ELSE</></title>
1293 IF <replaceable>boolean-expression</replaceable> THEN
1294 <replaceable>statements</replaceable>
1296 <replaceable>statements</replaceable>
1300 IF-THEN-ELSE statements add to IF-THEN by letting you
1301 specify an alternative set of statements that should be executed if
1302 the condition evaluates to FALSE.
1305 IF parentid IS NULL or parentid = ''''
1309 return hp_true_filename(parentid) || ''/'' || fullname;
1314 INSERT INTO users_count(count) VALUES(v_count);
1324 <title><literal>IF-THEN-ELSE IF</></title>
1327 IF statements can be nested, as in the following example:
1329 IF demo_row.sex = ''m'' THEN
1330 pretty_sex := ''man'';
1332 IF demo_row.sex = ''f'' THEN
1333 pretty_sex := ''woman'';
1340 When you use this form, you are actually
1341 nesting an IF statement inside the ELSE part of an outer IF
1342 statement. Thus you need one END IF statement for each
1343 nested IF and one for the parent IF-ELSE.
1344 This is workable but grows tedious when there are many
1345 alternatives to be checked.
1350 <title><literal>IF-THEN-ELSIF-ELSE</></title>
1354 IF <replaceable>boolean-expression</replaceable> THEN
1355 <replaceable>statements</replaceable>
1356 <optional> ELSIF <replaceable>boolean-expression</replaceable> THEN
1357 <replaceable>statements</replaceable>
1358 <optional> ELSIF <replaceable>boolean-expression</replaceable> THEN
1359 <replaceable>statements</replaceable>
1364 <replaceable>statements</replaceable> </optional>
1368 <literal>IF-THEN-ELSIF-ELSE</> provides a more convenient
1369 method of checking many alternatives in one statement.
1370 Formally it is equivalent to nested
1371 <literal>IF-THEN-ELSE-IF-THEN</> commands, but only one
1372 <literal>END IF</> is needed.
1381 ELSIF number > 0 THEN
1382 result := ''positive'';
1383 ELSIF number < 0 THEN
1384 result := ''negative'';
1386 -- hmm, the only other possibility is that number IS NULL
1393 The final ELSE section is optional.
1399 <sect2 id="plpgsql-control-structures-loops">
1400 <title>Simple Loops</title>
1403 With the LOOP, EXIT, WHILE and FOR statements, you can arrange
1404 for your <application>PL/pgSQL</application> function to repeat
1405 a series of commands.
1413 <optional><<label>></optional>
1415 <replaceable>statements</replaceable>
1419 LOOP defines an unconditional loop that is repeated indefinitely
1420 until terminated by an EXIT or RETURN statement.
1421 The optional label can be used by
1422 EXIT statements in nested loops to specify which level of
1423 nesting should be terminated.
1432 EXIT <optional> <replaceable>label</replaceable> </optional> <optional> WHEN <replaceable>expression</replaceable> </optional>;
1435 If no <replaceable>label</replaceable> is given,
1436 the innermost loop is terminated and the
1437 statement following END LOOP is executed next.
1438 If <replaceable>label</replaceable> is given, it
1439 must be the label of the current or some outer level of nested loop
1440 or block. Then the named loop or block is terminated and control
1441 continues with the statement after the loop's/block's corresponding
1446 If WHEN is present, loop exit occurs only if the specified condition
1447 is true, otherwise control passes to the statement after EXIT.
1454 -- some computations
1461 -- some computations
1462 EXIT WHEN count > 0;
1466 -- some computations
1467 IF stocks > 100000 THEN
1468 EXIT; -- illegal. Can't use EXIT outside of a LOOP
1476 <title>WHILE</title>
1480 <optional><<label>></optional>
1481 WHILE <replaceable>expression</replaceable> LOOP
1482 <replaceable>statements</replaceable>
1486 The WHILE statement repeats a
1487 sequence of statements so long as the condition expression
1488 evaluates to true. The condition is checked just before
1489 each entry to the loop body.
1495 WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
1496 -- some computations here
1499 WHILE NOT boolean_expression LOOP
1500 -- some computations here
1507 <title>FOR (integer for-loop)</title>
1511 <optional><<label>></optional>
1512 FOR <replaceable>name</replaceable> IN <optional> REVERSE </optional> <replaceable>expression</replaceable> .. <replaceable>expression</replaceable> LOOP
1513 <replaceable>statements</replaceable>
1517 This form of FOR creates a loop that iterates over a range of integer
1518 values. The variable
1519 <replaceable>name</replaceable> is automatically defined as type
1520 integer and exists only inside the loop. The two expressions giving
1521 the lower and upper bound of the range are evaluated once when entering
1522 the loop. The iteration step is normally 1, but is -1 when REVERSE is
1527 Some examples of integer FOR loops:
1530 -- some expressions here
1532 RAISE NOTICE ''i is %'',i;
1535 FOR i IN REVERSE 10..1 LOOP
1536 -- some expressions here
1543 <sect2 id="plpgsql-records-iterating">
1544 <title>Looping Through Query Results</title>
1547 Using a different type of FOR loop, you can iterate through
1548 the results of a query and manipulate that data
1549 accordingly. The syntax is:
1551 <optional><<label>></optional>
1552 FOR <replaceable>record | row</replaceable> IN <replaceable>select_query</replaceable> LOOP
1553 <replaceable>statements</replaceable>
1556 The record or row variable is successively assigned all the rows
1557 resulting from the SELECT query and the loop body is executed
1558 for each row. Here is an example:
1563 CREATE FUNCTION cs_refresh_mviews () RETURNS INTEGER AS '
1567 PERFORM cs_log(''Refreshing materialized views...'');
1569 FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
1571 -- Now "mviews" has one record from cs_materialized_views
1573 PERFORM cs_log(''Refreshing materialized view '' || quote_ident(mviews.mv_name) || ''...'');
1574 EXECUTE ''TRUNCATE TABLE '' || quote_ident(mviews.mv_name);
1575 EXECUTE ''INSERT INTO '' || quote_ident(mviews.mv_name) || '' '' || mviews.mv_query;
1578 PERFORM cs_log(''Done refreshing materialized views.'');
1581 ' LANGUAGE 'plpgsql';
1584 If the loop is terminated by an EXIT statement, the last
1585 assigned row value is still accessible after the loop.
1589 The FOR-IN-EXECUTE statement is another way to iterate over
1592 <optional><<label>></optional>
1593 FOR <replaceable>record | row</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> LOOP
1594 <replaceable>statements</replaceable>
1597 This is like the previous form, except that the source
1598 <command>SELECT</command> statement is specified as a string
1599 expression, which is evaluated and re-planned on each entry to
1600 the FOR loop. This allows the programmer to choose the speed of
1601 a pre-planned query or the flexibility of a dynamic query, just
1602 as with a plain <command>EXECUTE</command> statement.
1607 The <application>PL/pgSQL</> parser presently distinguishes the
1608 two kinds of FOR loops (integer or record-returning) by checking
1609 whether the target variable mentioned just after FOR has been
1610 declared as a record/row variable. If not, it's presumed to be
1611 an integer FOR loop. This can cause rather nonintuitive error
1612 messages when the true problem is, say, that one has
1613 misspelled the FOR variable name.
1619 <sect1 id="plpgsql-cursors">
1620 <title>Cursors</title>
1623 Rather than executing a whole query at once, it is possible to set
1624 up a <firstterm>cursor</> that encapsulates the query, and then read
1625 the query result a few rows at a time. One reason for doing this is
1626 to avoid memory overrun when the result contains a large number of
1627 rows. (However, <application>PL/pgSQL</> users don't normally need
1628 to worry about that, since FOR loops automatically use a cursor
1629 internally to avoid memory problems.) A more interesting usage is to
1630 return a reference to a cursor that it has created, allowing the
1631 caller to read the rows. This provides an efficient way to return
1632 large row sets from functions.
1635 <sect2 id="plpgsql-cursor-declarations">
1636 <title>Declaring Cursor Variables</title>
1639 All access to cursors in <application>PL/pgSQL</> goes through
1640 cursor variables, which are always of the special data type
1641 <type>refcursor</>. One way to create a cursor variable
1642 is just to declare it as a variable of type <type>refcursor</>.
1643 Another way is to use the cursor declaration syntax,
1644 which in general is:
1646 <replaceable>name</replaceable> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>select_query</replaceable> ;
1648 (<literal>FOR</> may be replaced by <literal>IS</> for Oracle
1649 compatibility.) <replaceable>arguments</replaceable>, if any,
1650 are a comma-separated list of <replaceable>name</replaceable>
1651 <replaceable>datatype</replaceable> pairs that define names to
1652 be replaced by parameter values in the given query. The actual
1653 values to substitute for these names will be specified later,
1654 when the cursor is opened.
1661 curs2 CURSOR FOR SELECT * from tenk1;
1662 curs3 CURSOR (key int) IS SELECT * from tenk1 where unique1 = key;
1664 All three of these variables have the data type <type>refcursor</>,
1665 but the first may be used with any query, while the second has
1666 a fully specified query already <firstterm>bound</> to it, and the last
1667 has a parameterized query bound to it. (<literal>key</> will be
1668 replaced by an integer parameter value when the cursor is opened.)
1669 The variable <literal>curs1</>
1670 is said to be <firstterm>unbound</> since it is not bound to
1671 any particular query.
1675 <sect2 id="plpgsql-cursor-opening">
1676 <title>Opening Cursors</title>
1679 Before a cursor can be used to retrieve rows, it must be
1680 <firstterm>opened</>. (This is the equivalent action to the SQL
1681 command <command>DECLARE CURSOR</>.) <application>PL/pgSQL</> has
1682 four forms of the OPEN statement, two of which use unbound cursor
1683 variables and the other two use bound cursor variables.
1687 <title>OPEN FOR SELECT</title>
1691 OPEN <replaceable>unbound-cursor</replaceable> FOR SELECT ...;
1694 The cursor variable is opened and given the specified query
1695 to execute. The cursor cannot be open already, and it must
1696 have been declared as an unbound cursor (that is, as a simple
1697 <type>refcursor</> variable). The SELECT query is treated
1698 in the same way as other SELECT statements in <application>PL/pgSQL</>:
1699 <application>PL/pgSQL</> variable names are substituted,
1700 and the query plan is cached for possible re-use.
1703 OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
1709 <title>OPEN FOR EXECUTE</title>
1713 OPEN <replaceable>unbound-cursor</replaceable> FOR EXECUTE <replaceable class="command">query-string</replaceable>;
1716 The cursor variable is opened and given the specified query to
1717 execute. The cursor cannot be open already, and it must have been
1718 declared as an unbound cursor (that is, as a simple
1719 <type>refcursor</> variable). The query is specified as a string
1720 expression in the same way as in the <command>EXECUTE</command>
1721 command. As usual, this gives flexibility so the query can vary
1722 from one run to the next.
1725 OPEN curs1 FOR EXECUTE ''SELECT * FROM '' || quote_ident($1);
1731 <title>Opening a bound cursor</title>
1735 OPEN <replaceable>bound-cursor</replaceable> <optional> ( <replaceable>argument_values</replaceable> ) </optional>;
1738 This form of <command>OPEN</command> is used to open a cursor
1739 variable whose query was bound to it when it was declared. The
1740 cursor cannot be open already. A list of actual argument value
1741 expressions must appear if and only if the cursor was declared to
1742 take arguments. These values will be substituted in the query.
1743 The query plan for a bound cursor is always considered cacheable
1744 --- there is no equivalent of <command>EXECUTE</command> in this case.
1754 <sect2 id="plpgsql-cursor-using">
1755 <title>Using Cursors</title>
1758 Once a cursor has been opened, it can be manipulated with the
1759 statements described here.
1763 These manipulations need not occur in the same function that
1764 opened the cursor to begin with. You can return a <type>refcursor</>
1765 value out of a function and let the caller operate on the cursor.
1766 (Internally, a <type>refcursor</> value is simply the string name
1767 of a Portal containing the active query for the cursor. This name
1768 can be passed around, assigned to other <type>refcursor</> variables,
1769 and so on, without disturbing the Portal.)
1773 All Portals are implicitly closed at transaction end. Therefore
1774 a <type>refcursor</> value is useful to reference an open cursor
1775 only until the end of the transaction.
1779 <title>FETCH</title>
1783 FETCH <replaceable>cursor</replaceable> INTO <replaceable>target</replaceable>;
1786 <command>FETCH</command> retrieves the next row from the
1787 cursor into a target, which may be a row variable, a record
1788 variable, or a comma-separated list of simple variables, just like
1789 <command>SELECT INTO</command>. As with <command>SELECT
1790 INTO</command>, the special variable <literal>FOUND</literal> may
1791 be checked to see whether a row was obtained or not.
1794 FETCH curs1 INTO rowvar;
1795 FETCH curs2 INTO foo,bar,baz;
1801 <title>CLOSE</title>
1805 CLOSE <replaceable>cursor</replaceable>;
1808 CLOSE closes the Portal underlying an open cursor.
1809 This can be used to release resources earlier than end of
1810 transaction, or to free up the cursor variable to be opened again.
1819 <title>Returning Cursors</title>
1823 <application>PL/pgSQL</> functions can return cursors to the
1824 caller. This is used to return multiple rows or columns from the
1825 function. The function opens the cursor and returns the cursor
1826 name to the caller. The caller can then FETCH rows from the
1827 cursor. The cursor can be closed by the caller, or it will be
1828 closed automatically when the transaction closes.
1833 The cursor name returned by the function can be specified by the
1834 caller or automatically generated. The following example shows
1835 how a cursor name can be supplied by the caller:
1838 CREATE TABLE test (col text);
1839 INSERT INTO test VALUES ('123');
1841 CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
1843 OPEN $1 FOR SELECT col FROM test;
1846 ' LANGUAGE 'plpgsql';
1849 SELECT reffunc('funccursor');
1850 FETCH ALL IN funccursor;
1856 The following example uses automatic cursor name generation:
1859 CREATE FUNCTION reffunc2() RETURNS refcursor AS '
1863 OPEN ref FOR SELECT col FROM test;
1866 ' LANGUAGE 'plpgsql';
1872 --------------------
1873 <unnamed cursor 1>
1876 FETCH ALL IN "<unnamed cursor 1>";
1884 <sect1 id="plpgsql-errors-and-messages">
1885 <title>Errors and Messages</title>
1888 Use the RAISE statement to report messages and raise errors.
1891 RAISE <replaceable class="parameter">level</replaceable> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">variable</replaceable> <optional>...</optional></optional>;
1894 Possible levels are <literal>DEBUG</literal> (write the message to
1895 the server log), <literal>LOG</literal> (write the message to the
1896 server log with a higher priority), <literal>INFO</literal>,
1897 <literal>NOTICE</literal> and <literal>WARNING</literal> (write
1898 the message to the server log and send it to the client, with
1899 respectively higher priorities), and <literal>EXCEPTION</literal>
1900 (raise an error and abort the current transaction). Whether error
1901 messages of a particular priority are reported to the client,
1902 written to the server log, or both is controlled by the
1903 <option>SERVER_MIN_MESSAGES</option> and
1904 <option>CLIENT_MIN_MESSAGES</option> configuration variables. See
1905 the <citetitle>PostgreSQL Administrator's Guide</citetitle> for more
1910 Inside the format string, <literal>%</literal> is replaced by the
1911 next optional argument's external representation. Write
1912 <literal>%%</literal> to emit a literal <literal>%</literal>. Note
1913 that the optional arguments must presently be simple variables,
1914 not expressions, and the format must be a simple string literal.
1918 This example should work, but does not:
1919 RAISE NOTICE ''Id number '' || key || '' not found!'';
1920 Put it back when we allow non-string-literal formats.
1926 RAISE NOTICE ''Calling cs_create_job(%)'',v_job_id;
1929 In this example, the value of v_job_id will replace the
1930 <literal>%</literal> in the string.
1935 RAISE EXCEPTION ''Inexistent ID --> %'',user_id;
1937 This will abort the transaction with the given error message.
1940 <sect2 id="plpgsql-exceptions">
1941 <title>Exceptions</title>
1944 <productname>PostgreSQL</productname> does not have a very smart
1945 exception handling model. Whenever the parser, planner/optimizer
1946 or executor decide that a statement cannot be processed any longer,
1947 the whole transaction gets aborted and the system jumps back
1948 into the main loop to get the next query from the client application.
1952 It is possible to hook into the error mechanism to notice that this
1953 happens. But currently it is impossible to tell what really
1954 caused the abort (input/output conversion error, floating-point
1955 error, parse error). And it is possible that the database backend
1956 is in an inconsistent state at this point so returning to the upper
1957 executor or issuing more commands might corrupt the whole database.
1961 Thus, the only thing <application>PL/pgSQL</application>
1962 currently does when it encounters an abort during execution of a
1963 function or trigger procedure is to write some additional
1964 <literal>NOTICE</literal> level log messages telling in which
1965 function and where (line number and type of statement) this
1966 happened. The error always stops execution of the function.
1971 <sect1 id="plpgsql-trigger">
1972 <title>Trigger Procedures</title>
1975 <application>PL/pgSQL</application> can be used to define trigger
1976 procedures. A trigger procedure is created with the
1977 <command>CREATE FUNCTION</> command as a function with no
1978 arguments and a return type of <type>TRIGGER</type>. Note that
1979 the function must be declared with no arguments even if it expects
1980 to receive arguments specified in <command>CREATE TRIGGER</> ---
1981 trigger arguments are passed via <varname>TG_ARGV</>, as described
1986 When a <application>PL/pgSQL</application> function is called as a
1987 trigger, several special variables are created automatically in the
1988 top-level block. They are:
1992 <term><varname>NEW</varname></term>
1995 Data type <type>RECORD</type>; variable holding the new database row for INSERT/UPDATE
1996 operations in ROW level triggers.
2002 <term><varname>OLD</varname></term>
2005 Data type <type>RECORD</type>; variable holding the old database row for UPDATE/DELETE
2006 operations in ROW level triggers.
2012 <term><varname>TG_NAME</varname></term>
2015 Data type <type>name</type>; variable that contains the name of the trigger actually
2022 <term><varname>TG_WHEN</varname></term>
2025 Data type <type>text</type>; a string of either
2026 <literal>BEFORE</literal> or <literal>AFTER</literal>
2027 depending on the trigger's definition.
2033 <term><varname>TG_LEVEL</varname></term>
2036 Data type <type>text</type>; a string of either
2037 <literal>ROW</literal> or <literal>STATEMENT</literal> depending on the
2038 trigger's definition.
2044 <term><varname>TG_OP</varname></term>
2047 Data type <type>text</type>; a string of
2048 <literal>INSERT</literal>, <literal>UPDATE</literal>
2049 or <literal>DELETE</literal> telling
2050 for which operation the trigger is fired.
2056 <term><varname>TG_RELID</varname></term>
2059 Data type <type>oid</type>; the object ID of the table that caused the
2066 <term><varname>TG_RELNAME</varname></term>
2069 Data type <type>name</type>; the name of the table that caused the trigger
2076 <term><varname>TG_NARGS</varname></term>
2079 Data type <type>integer</type>; the number of arguments given to the trigger
2080 procedure in the <command>CREATE TRIGGER</command> statement.
2086 <term><varname>TG_ARGV[]</varname></term>
2089 Data type array of <type>text</type>; the arguments from
2090 the <command>CREATE TRIGGER</command> statement.
2091 The index counts from 0 and can be given as an expression. Invalid
2092 indices (< 0 or >= <varname>tg_nargs</>) result in a null value.
2100 A trigger function must return either NULL or a record/row value
2101 having exactly the structure of the table the trigger was fired for.
2102 Triggers fired BEFORE may return NULL to signal the trigger manager
2103 to skip the rest of the operation for this row (ie, subsequent triggers
2104 are not fired, and the INSERT/UPDATE/DELETE does not occur for this
2105 row). If a non-NULL value is returned then the operation proceeds with
2106 that row value. Note that returning a row value different from the
2107 original value of NEW alters the row that will be inserted or updated.
2108 It is possible to replace single values directly
2109 in NEW and return that, or to build a complete new record/row to
2114 The return value of a trigger fired AFTER is ignored; it may as well
2115 always return a NULL value. But an AFTER trigger can still abort the
2116 operation by raising an error.
2120 <title>A <application>PL/pgSQL</application> Trigger Procedure Example</title>
2123 This example trigger ensures that any time a row is inserted or updated
2124 in the table, the current user name and time are stamped into the
2125 row. And it ensures that an employee's name is given and that the
2126 salary is a positive value.
2132 last_date timestamp,
2136 CREATE FUNCTION emp_stamp () RETURNS TRIGGER AS '
2138 -- Check that empname and salary are given
2139 IF NEW.empname ISNULL THEN
2140 RAISE EXCEPTION ''empname cannot be NULL value'';
2142 IF NEW.salary ISNULL THEN
2143 RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
2146 -- Who works for us when she must pay for?
2147 IF NEW.salary < 0 THEN
2148 RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
2151 -- Remember who changed the payroll when
2152 NEW.last_date := ''now'';
2153 NEW.last_user := current_user;
2156 ' LANGUAGE 'plpgsql';
2158 CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
2159 FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
2165 <!-- **** PL/pgSQL Examples **** -->
2167 <sect1 id="plpgsql-examples">
2168 <title>Examples</title>
2171 Here are only a few functions to demonstrate how easy it is to
2172 write <application>PL/pgSQL</>
2173 functions. For more complex examples the programmer
2174 might look at the regression test for <application>PL/pgSQL</>.
2178 One painful detail in writing functions in
2179 <application>PL/pgSQL</application> is the handling of single
2180 quotes. The function's source text in <command>CREATE FUNCTION</>
2181 must be a literal string. Single quotes inside of literal strings
2182 must be either doubled or quoted with a backslash. We are still
2183 looking for an elegant alternative. In the meantime, doubling the
2184 single quotes as in the examples below should be used. Any
2185 solution for this in future versions of
2186 <productname>PostgreSQL</productname> will be forward compatible.
2190 For a detailed explanation and examples of how to escape single
2191 quotes in different situations, please see <xref linkend="plpgsql-quote">.
2195 <title>A Simple <application>PL/pgSQL</application> Function to Increment an Integer</title>
2198 The following two <application>PL/pgSQL</application> functions are identical to their
2199 counterparts from the C language function discussion. This
2200 function receives an <type>integer</type> and increments it by
2201 one, returning the incremented value.
2205 CREATE FUNCTION add_one (integer) RETURNS INTEGER AS '
2209 ' LANGUAGE 'plpgsql';
2214 <title>A Simple <application>PL/pgSQL</application> Function to Concatenate Text</title>
2217 This function receives two <type>text</type> parameters and
2218 returns the result of concatenating them.
2222 CREATE FUNCTION concat_text (TEXT, TEXT) RETURNS TEXT AS '
2226 ' LANGUAGE 'plpgsql';
2231 <title>A <application>PL/pgSQL</application> Function on Composite Type</title>
2234 In this example, we take <literal>EMP</> (a table) and an
2235 <type>integer</type> as arguments to our function, which returns
2236 a <type>boolean</type>. If the <structfield>salary</> field of the <structname>EMP</> table is
2237 <literal>NULL</literal>, we return <literal>f</>. Otherwise we compare with
2238 that field with the <type>integer</type> passed to the function
2239 and return the <type>boolean</type> result of the comparison (t
2240 or f). This is the <application>PL/pgSQL</application> equivalent to the example from the C
2245 CREATE FUNCTION c_overpaid (EMP, INTEGER) RETURNS BOOLEAN AS '
2247 emprec ALIAS FOR $1;
2248 sallim ALIAS FOR $2;
2250 IF emprec.salary ISNULL THEN
2253 RETURN emprec.salary > sallim;
2255 ' LANGUAGE 'plpgsql';
2260 <!-- **** Porting from Oracle PL/SQL **** -->
2262 <sect1 id="plpgsql-porting">
2269 <firstname>Roberto</firstname>
2270 <surname>Mello</surname>
2273 <email>rmello@fslc.usu.edu</email>
2278 Breaks HTML manifest file
2281 Except for portions of this document quoted from other sources,
2282 this document is licensed under the BSD License.
2288 <title>Porting from Oracle PL/SQL</title>
2290 <indexterm zone="plpgsql-porting">
2291 <primary>Oracle</primary>
2294 <indexterm zone="plpgsql-porting">
2295 <primary>PL/SQL</primary>
2299 <title>Author</title>
2301 Roberto Mello (<email>rmello@fslc.usu.edu</email>)
2306 This section explains differences between Oracle's PL/SQL and
2307 <productname>PostgreSQL</>'s <application>PL/pgSQL</application> languages in the hopes of helping developers
2308 port applications from Oracle to <productname>PostgreSQL</>. Most of the code here
2309 is from the <ulink url="http://www.arsdigita.com">ArsDigita</ulink>
2310 <ulink url="http://www.arsdigita.com/asj/clickstream">Clickstream
2311 module</ulink> that I ported to <productname>PostgreSQL</> when I took an
2312 internship with <ulink url="http://www.openforce.net">OpenForce
2313 Inc.</ulink> in the Summer of 2000.
2317 <application>PL/pgSQL</application> is similar to PL/SQL in many aspects. It is a block
2318 structured, imperative language (all variables have to be
2319 declared). PL/SQL has many more features than its <productname>PostgreSQL</>
2320 counterpart, but <application>PL/pgSQL</application> allows for a great deal of functionality
2321 and it is being improved constantly.
2325 <title>Main Differences</title>
2328 Some things you should keep in mind when porting from Oracle to <productname>PostgreSQL</>:
2333 No default parameters in <productname>PostgreSQL</>.
2339 You can overload functions in <productname>PostgreSQL</>. This is often used to work
2340 around the lack of default parameters.
2346 Assignments, loops and conditionals are similar.
2352 No need for cursors in <productname>PostgreSQL</>, just put the query in the FOR
2353 statement (see example below)
2359 In <productname>PostgreSQL</> you <emphasis>need</emphasis> to escape single
2360 quotes. See <xref linkend="plpgsql-quote">.
2366 <sect3 id="plpgsql-quote">
2367 <title>Quote Me on That: Escaping Single Quotes</title>
2370 In <productname>PostgreSQL</> you need to escape single quotes inside your
2371 function definition. This can lead to quite amusing code at
2372 times, especially if you are creating a function that generates
2373 other function(s), as in
2374 <xref linkend="plpgsql-porting-nastyquote">.
2375 One thing to keep in mind
2376 when escaping lots of single quotes is that, except for the
2377 beginning/ending quotes, all the others will come in even
2382 <xref linkend="plpgsql-quoting-table"> gives the scoop. (You'll
2383 love this little chart.)
2386 <table id="plpgsql-quoting-table">
2387 <title>Single Quotes Escaping Chart</title>
2392 <entry>No. of Quotes</entry>
2393 <entry>Usage</entry>
2394 <entry>Example</entry>
2395 <entry>Result</entry>
2402 <entry>To begin/terminate function bodies</entry>
2403 <entry><programlisting>
2404 CREATE FUNCTION foo() RETURNS INTEGER AS '...'
2406 </programlisting></entry>
2407 <entry>as is</entry>
2412 <entry>In assignments, SELECT statements, to delimit strings, etc.</entry>
2413 <entry><programlisting>
2414 a_output := ''Blah'';
2415 SELECT * FROM users WHERE f_name=''foobar'';
2416 </programlisting></entry>
2417 <entry><literal>SELECT * FROM users WHERE f_name='foobar';</literal></entry>
2423 When you need two single quotes in your resulting string
2424 without terminating that string.
2426 <entry><programlisting>
2427 a_output := a_output || '' AND name
2428 LIKE ''''foobar'''' AND ...''
2429 </programlisting></entry>
2430 <entry><literal>AND name LIKE 'foobar' AND ...</literal></entry>
2436 When you want double quotes in your resulting string
2437 <emphasis>and</emphasis> terminate that string.
2439 <entry><programlisting>
2440 a_output := a_output || '' AND name
2441 LIKE ''''foobar''''''
2442 </programlisting></entry>
2444 <literal>AND name LIKE 'foobar'</literal>
2451 When you want two single quotes in the resulting string
2452 (which accounts for 8 quotes) <emphasis>and</emphasis>
2453 terminate that string (2 more). You will probably only need
2454 that if you were using a function to generate other functions
2455 (like in <xref linkend="plpgsql-porting-nastyquote">).
2457 <entry><programlisting>
2458 a_output := a_output || '' if v_'' ||
2459 referrer_keys.kind || '' like ''''''''''
2460 || referrer_keys.key_string || ''''''''''
2461 then return '''''' || referrer_keys.referrer_type
2462 || ''''''; end if;'';
2463 </programlisting></entry>
2465 <literal>if v_<...> like ''<...>'' then return ''<...>''; end if;</literal>
2474 <sect2 id="plpgsql-porting-functions">
2485 Here is an Oracle function:
2487 CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name IN varchar, v_version IN varchar)
2490 IF v_version IS NULL THEN
2493 RETURN v_name || '/' || v_version;
2501 Let's go through this function and see the differences to <application>PL/pgSQL</>:
2506 <productname>PostgreSQL</productname> does not have named
2507 parameters. You have to explicitly alias them inside your
2514 Oracle can have <literal>IN</literal>, <literal>OUT</literal>,
2515 and <literal>INOUT</literal> parameters passed to functions.
2516 The <literal>INOUT</literal>, for example, means that the
2517 parameter will receive a value and return another. <productname>PostgreSQL</>
2518 only has <quote>IN</quote> parameters and functions can return
2519 only a single value.
2525 The <literal>RETURN</literal> key word in the function
2526 prototype (not the function body) becomes
2527 <literal>RETURNS</literal> in <productname>PostgreSQL</>.
2533 On <productname>PostgreSQL</> functions are created using single quotes as
2534 delimiters, so you have to escape single quotes inside your
2535 functions (which can be quite annoying at times; see <xref
2536 linkend="plpgsql-quote">).
2542 The <literal>/show errors</literal> command does not exist in
2543 <productname>PostgreSQL</>.
2550 So let's see how this function would look when ported to
2551 <productname>PostgreSQL</>:
2554 CREATE OR REPLACE FUNCTION cs_fmt_browser_version(VARCHAR, VARCHAR)
2555 RETURNS VARCHAR AS '
2557 v_name ALIAS FOR $1;
2558 v_version ALIAS FOR $2;
2560 IF v_version IS NULL THEN
2563 RETURN v_name || ''/'' || v_version;
2565 ' LANGUAGE 'plpgsql';
2570 <example id="plpgsql-porting-nastyquote">
2572 A Function that Creates Another Function
2576 The following procedure grabs rows from a
2577 <command>SELECT</command> statement and builds a large function
2578 with the results in <literal>IF</literal> statements, for the
2579 sake of efficiency. Notice particularly the differences in
2580 cursors, <literal>FOR</literal> loops, and the need to escape
2581 single quotes in <productname>PostgreSQL</>.
2584 CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
2585 CURSOR referrer_keys IS
2586 SELECT * FROM cs_referrer_keys
2589 a_output VARCHAR(4000);
2591 a_output := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR, v_domain IN VARCHAR,
2592 v_url IN VARCHAR) RETURN VARCHAR IS BEGIN';
2594 FOR referrer_key IN referrer_keys LOOP
2595 a_output := a_output || ' IF v_' || referrer_key.kind || ' LIKE ''' ||
2596 referrer_key.key_string || ''' THEN RETURN ''' || referrer_key.referrer_type ||
2600 a_output := a_output || ' RETURN NULL; END;';
2601 EXECUTE IMMEDIATE a_output;
2609 Here is how this function would end up in <productname>PostgreSQL</>:
2612 CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS '
2614 referrer_keys RECORD; -- Declare a generic record to be used in a FOR
2615 a_output varchar(4000);
2617 a_output := ''CREATE FUNCTION cs_find_referrer_type(VARCHAR,VARCHAR,VARCHAR)
2618 RETURNS VARCHAR AS ''''
2620 v_host ALIAS FOR $1;
2621 v_domain ALIAS FOR $2;
2626 -- Notice how we scan through the results of a query in a FOR loop
2627 -- using the FOR <record> construct.
2630 FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
2631 a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE ''''''''''
2632 || referrer_keys.key_string || '''''''''' THEN RETURN ''''''
2633 || referrer_keys.referrer_type || ''''''; END IF;'';
2636 a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE ''''plpgsql'''';'';
2638 -- This works because we are not substituting any variables
2639 -- Otherwise it would fail. Look at PERFORM for another way to run functions
2643 ' LANGUAGE 'plpgsql';
2650 A Procedure with a lot of String Manipulation and OUT Parameters
2654 The following Oracle PL/SQL procedure is used to parse a URL and
2655 return several elements (host, path and query). It is an
2656 procedure because in <application>PL/pgSQL</application> functions only one value can be returned
2657 (see <xref linkend="plpgsql-porting-procedures">). In
2658 <productname>PostgreSQL</>, one way to work around this is to split the procedure
2659 in three different functions: one to return the host, another for
2660 the path and another for the query.
2664 CREATE OR REPLACE PROCEDURE cs_parse_url(
2666 v_host OUT VARCHAR, -- This will be passed back
2667 v_path OUT VARCHAR, -- This one too
2668 v_query OUT VARCHAR) -- And this one
2676 a_pos1 := instr(v_url, '//'); -- <productname>PostgreSQL</> doesn't have an instr function
2681 a_pos2 := instr(v_url, '/', a_pos1 + 2);
2683 v_host := substr(v_url, a_pos1 + 2);
2688 v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
2689 a_pos1 := instr(v_url, '?', a_pos2 + 1);
2692 v_path := substr(v_url, a_pos2);
2696 v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
2697 v_query := substr(v_url, a_pos1 + 1);
2704 Here is how this procedure could be translated for <productname>PostgreSQL</>:
2707 CREATE OR REPLACE FUNCTION cs_parse_url_host(VARCHAR) RETURNS VARCHAR AS '
2717 a_pos1 := instr(v_url,''//'');
2720 RETURN ''''; -- Return a blank
2723 a_pos2 := instr(v_url,''/'',a_pos1 + 2);
2725 v_host := substr(v_url, a_pos1 + 2);
2730 v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2 );
2733 ' LANGUAGE 'plpgsql';
2740 <productname>PostgreSQL</> does not have an <function>instr</function> function,
2741 so you can work around it using a combination of other functions.
2742 I got tired of doing this and created my own
2743 <function>instr</function> functions that behave exactly like
2744 Oracle's (it makes life easier). See the <xref
2745 linkend="plpgsql-porting-appendix"> for the code.
2750 <sect2 id="plpgsql-porting-procedures">
2756 Oracle procedures give a little more flexibility to the developer
2757 because nothing needs to be explicitly returned, but it can be
2758 through the use of <literal>INOUT</> or <literal>OUT</> parameters.
2765 CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
2766 a_running_job_count INTEGER;
2767 PRAGMA AUTONOMOUS_TRANSACTION;<co id="co.plpgsql-porting-pragma">
2769 LOCK TABLE cs_jobs IN EXCLUSIVE MODE;<co id="co.plpgsql-porting-locktable">
2771 SELECT count(*) INTO a_running_job_count
2773 WHERE end_stamp IS NULL;
2775 IF a_running_job_count > 0 THEN
2776 COMMIT; -- free lock<co id="co.plpgsql-porting-commit">
2777 raise_application_error(-20000, 'Unable to create a new job: a job is currently running.');
2780 DELETE FROM cs_active_job;
2781 INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
2784 INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, sysdate);
2785 EXCEPTION WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists<co id="co.plpgsql-porting-exception">
2795 Procedures like this can be easily converted into <productname>PostgreSQL</>
2796 functions returning an <type>INTEGER</type>. This procedure in
2797 particular is interesting because it can teach us some things:
2800 <callout arearefs="co.plpgsql-porting-pragma">
2802 There is no <literal>pragma</literal> statement in <productname>PostgreSQL</>.
2806 <callout arearefs="co.plpgsql-porting-locktable">
2808 If you do a <command>LOCK TABLE</command> in <application>PL/pgSQL</>, the lock
2809 will not be released until the calling transaction is finished.
2813 <callout arearefs="co.plpgsql-porting-commit">
2815 You also cannot have transactions in <application>PL/pgSQL</application> procedures. The
2816 entire function (and other functions called from therein) is
2817 executed in a transaction and <productname>PostgreSQL</> rolls back the results if
2818 something goes wrong. Therefore only one
2819 <command>BEGIN</command> statement is allowed.
2823 <callout arearefs="co.plpgsql-porting-exception">
2825 The exception when would have to be replaced by an
2826 <literal>IF</literal> statement.
2833 So let's see one of the ways we could port this procedure to <application>PL/pgSQL</>:
2836 CREATE OR REPLACE FUNCTION cs_create_job(INTEGER) RETURNS INTEGER AS '
2838 v_job_id ALIAS FOR $1;
2839 a_running_job_count INTEGER;
2841 -- PRAGMA AUTONOMOUS_TRANSACTION;
2843 LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
2844 SELECT count(*) INTO a_running_job_count
2846 WHERE end_stamp IS NULL;
2848 IF a_running_job_count > 0
2850 -- COMMIT; -- free lock
2851 RAISE EXCEPTION ''Unable to create a new job: a job is currently running.'';
2854 DELETE FROM cs_active_job;
2855 INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
2857 SELECT count(*) into a_num
2859 WHERE job_id=v_job_id;
2860 IF NOT FOUND THEN -- If nothing was returned in the last query
2861 -- This job is not in the table so lets insert it.
2862 INSERT INTO cs_jobs(job_id, start_stamp) VALUES (v_job_id, sysdate());
2865 RAISE NOTICE ''Job already running.'';<co id="co.plpgsql-porting-raise">
2870 ' LANGUAGE 'plpgsql';
2874 <callout arearefs="co.plpgsql-porting-raise">
2876 Notice how you can raise notices (or errors) in <application>PL/pgSQL</>.
2883 <sect2 id="plpgsql-porting-packages">
2890 I haven't done much with packages myself, so if there are
2891 mistakes here, please let me know.
2896 Packages are a way Oracle gives you to encapsulate PL/SQL
2897 statements and functions into one entity, like Java classes, where
2898 you define methods and objects. You can access these
2899 objects/methods with a <quote><literal>.</literal></quote>
2900 (dot). Here is an example of an Oracle package from ACS 4 (the
2901 <ulink url="http://www.arsdigita.com/doc/">ArsDigita Community
2905 CREATE OR REPLACE PACKAGE BODY acs
2908 user_id IN users.user_id%TYPE DEFAULT NULL,
2909 object_type IN acs_objects.object_type%TYPE DEFAULT 'user',
2910 creation_date IN acs_objects.creation_date%TYPE DEFAULT sysdate,
2911 creation_user IN acs_objects.creation_user%TYPE DEFAULT NULL,
2912 creation_ip IN acs_objects.creation_ip%TYPE DEFAULT NULL,
2914 ) RETURN users.user_id%TYPE
2916 v_user_id users.user_id%TYPE;
2917 v_rel_id membership_rels.rel_id%TYPE;
2919 v_user_id := acs_user.new (user_id, object_type, creation_date,
2920 creation_user, creation_ip, email, ...
2930 We port this to <productname>PostgreSQL</> by creating the different objects of
2931 the Oracle package as functions with a standard naming
2932 convention. We have to pay attention to some other details, like
2933 the lack of default parameters in <productname>PostgreSQL</> functions. The above
2934 package would become something like this:
2937 CREATE FUNCTION acs__add_user(INTEGER,INTEGER,VARCHAR,TIMESTAMP,INTEGER,INTEGER,...)
2938 RETURNS INTEGER AS '
2940 user_id ALIAS FOR $1;
2941 object_type ALIAS FOR $2;
2942 creation_date ALIAS FOR $3;
2943 creation_user ALIAS FOR $4;
2944 creation_ip ALIAS FOR $5;
2946 v_user_id users.user_id%TYPE;
2947 v_rel_id membership_rels.rel_id%TYPE;
2949 v_user_id := acs_user__new(user_id,object_type,creation_date,creation_user,creation_ip, ...);
2954 ' LANGUAGE 'plpgsql';
2959 <sect2 id="plpgsql-porting-other">
2961 Other Things to Watch For
2965 <title>EXECUTE</title>
2968 The <productname>PostgreSQL</> version of <command>EXECUTE</command> works
2969 nicely, but you have to remember to use
2970 <function>quote_literal(TEXT)</function> and
2971 <function>quote_string(TEXT)</function> as described in <xref
2972 linkend="plpgsql-statements-executing-dyn-queries">. Constructs of the type
2973 <literal>EXECUTE ''SELECT * from $1'';</literal> will not work
2974 unless you use these functions.
2978 <sect3 id="plpgsql-porting-optimization">
2979 <title>Optimizing <application>PL/pgSQL</application> Functions</title>
2982 <productname>PostgreSQL</> gives you two function creation modifiers to optimize
2983 execution: <literal>iscachable</literal> (function always returns
2984 the same result when given the same arguments) and
2985 <literal>isstrict</literal> (function returns NULL if any
2986 argument is NULL). Consult the <command>CREATE
2987 FUNCTION</command> reference for details.
2991 To make use of these optimization attributes, you have to use the
2992 <literal>WITH</literal> modifier in your <command>CREATE
2993 FUNCTION</command> statement. Something like:
2996 CREATE FUNCTION foo(...) RETURNS INTEGER AS '
2998 ' LANGUAGE 'plpgsql'
2999 WITH (isstrict, iscachable);
3005 <sect2 id="plpgsql-porting-appendix">
3012 Code for my <function>instr</function> functions
3016 This function should probably be integrated into the core.
3021 -- instr functions that mimic Oracle's counterpart
3022 -- Syntax: instr(string1,string2,[n],[m]) where [] denotes optional params.
3024 -- Searches string1 beginning at the nth character for the mth
3025 -- occurrence of string2. If n is negative, search backwards. If m is
3026 -- not passed, assume 1 (search starts at first character).
3028 -- by Roberto Mello (rmello@fslc.usu.edu)
3029 -- modified by Robert Gaszewski (graszew@poland.com)
3030 -- Licensed under the GPL v2 or later.
3033 CREATE FUNCTION instr(VARCHAR,VARCHAR) RETURNS INTEGER AS '
3037 pos:= instr($1,$2,1);
3040 ' LANGUAGE 'plpgsql';
3043 CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER) RETURNS INTEGER AS '
3045 string ALIAS FOR $1;
3046 string_to_search ALIAS FOR $2;
3047 beg_index ALIAS FOR $3;
3048 pos integer NOT NULL DEFAULT 0;
3054 IF beg_index > 0 THEN
3056 temp_str := substring(string FROM beg_index);
3057 pos := position(string_to_search IN temp_str);
3062 RETURN pos + beg_index - 1;
3065 ss_length := char_length(string_to_search);
3066 length := char_length(string);
3067 beg := length + beg_index - ss_length + 2;
3070 temp_str := substring(string FROM beg FOR ss_length);
3071 pos := position(string_to_search IN temp_str);
3082 ' LANGUAGE 'plpgsql';
3085 -- Written by Robert Gaszewski (graszew@poland.com)
3086 -- Licensed under the GPL v2 or later.
3088 CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER,INTEGER) RETURNS INTEGER AS '
3090 string ALIAS FOR $1;
3091 string_to_search ALIAS FOR $2;
3092 beg_index ALIAS FOR $3;
3093 occur_index ALIAS FOR $4;
3094 pos integer NOT NULL DEFAULT 0;
3095 occur_number INTEGER NOT NULL DEFAULT 0;
3102 IF beg_index > 0 THEN
3104 temp_str := substring(string FROM beg_index);
3106 FOR i IN 1..occur_index LOOP
3107 pos := position(string_to_search IN temp_str);
3110 beg := beg + pos - 1;
3115 temp_str := substring(string FROM beg + 1);
3124 ss_length := char_length(string_to_search);
3125 length := char_length(string);
3126 beg := length + beg_index - ss_length + 2;
3129 temp_str := substring(string FROM beg FOR ss_length);
3130 pos := position(string_to_search IN temp_str);
3133 occur_number := occur_number + 1;
3135 IF occur_number = occur_index THEN
3146 ' LANGUAGE 'plpgsql';
3155 <!-- Keep this comment at the end of the file
3160 sgml-minimize-attributes:nil
3161 sgml-always-quote-attributes:t
3164 sgml-parent-document:nil
3165 sgml-default-dtd-file:"./reference.ced"
3166 sgml-exposed-tags:nil
3167 sgml-local-catalogs:("/usr/lib/sgml/catalog")
3168 sgml-local-ecat-files:nil
projects / postgresql / blob