plpgsql.sgml 159 KB
Newer Older
1
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.128 2008/05/03 00:11:36 tgl Exp $ -->
2

3
<chapter id="plpgsql">
4
  <title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
5

6 7 8 9
 <indexterm zone="plpgsql">
  <primary>PL/pgSQL</primary>
 </indexterm>

10 11 12
 <sect1 id="plpgsql-overview">
  <title>Overview</title>

13
 <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
14 15 16 17
  <application>PL/pgSQL</application> is a loadable procedural
  language for the <productname>PostgreSQL</productname> database
  system.  The design goals of <application>PL/pgSQL</> were to create
  a loadable procedural language that
18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

    <itemizedlist>
     <listitem>
      <para>
       can be used to create functions and trigger procedures,
      </para>
     </listitem>
     <listitem>
      <para>
       adds control structures to the <acronym>SQL</acronym> language,
      </para>
     </listitem>
     <listitem>
      <para>
       can perform complex computations,
      </para>
     </listitem>
     <listitem>
      <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
37
       inherits all user-defined types, functions, and operators,
38 39 40 41 42 43 44 45 46 47 48 49 50 51
      </para>
     </listitem>
     <listitem>
      <para>
       can be defined to be trusted by the server,
      </para>
     </listitem>
     <listitem>
      <para>
       is easy to use.
      </para>
     </listitem>
    </itemizedlist>
   </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
52

53
   <para>
54 55
    Functions created with <application>PL/pgSQL</application> can be
    used anywhere that built-in functions could be used.
56 57 58 59 60
    For example, it is possible to
    create complex conditional computation functions and later use
    them to define operators or use them in index expressions.
   </para>

61
  <sect2 id="plpgsql-advantages">
62
   <title>Advantages of Using <application>PL/pgSQL</application></title>
63 64

    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
65
     <acronym>SQL</acronym> is the language <productname>PostgreSQL</>
66
     and most other relational databases use as query language. It's
Peter Eisentraut's avatar
Peter Eisentraut committed
67 68
     portable and easy to learn. But every <acronym>SQL</acronym>
     statement must be executed individually by the database server.
69
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
70

71
    <para>
72
     That means that your client application must send each query to
Bruce Momjian's avatar
Bruce Momjian committed
73 74 75 76 77
     the database server, wait for it to be processed, receive and
     process the results, do some computation, then send further
     queries to the server.  All this incurs interprocess
     communication and will also incur network overhead if your client
     is on a different machine than the database server.
78
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
79

80
    <para>
Bruce Momjian's avatar
Bruce Momjian committed
81 82 83 84
     With <application>PL/pgSQL</application> you can group a block of
     computation and a series of queries <emphasis>inside</emphasis>
     the database server, thus having the power of a procedural
     language and the ease of use of SQL, but with considerable
85
     savings of client/server communication overhead.
Bruce Momjian's avatar
Bruce Momjian committed
86 87 88
    </para>
    <itemizedlist>

89 90
     <listitem><para> Extra round trips between
     client and server are eliminated </para></listitem>
Bruce Momjian's avatar
Bruce Momjian committed
91 92

     <listitem><para> Intermediate results that the client does not
93
     need do not have to be marshaled or transferred between server
Bruce Momjian's avatar
Bruce Momjian committed
94 95
     and client </para></listitem>

96 97
     <listitem><para> Multiple rounds of query
     parsing can be avoided </para></listitem>
Bruce Momjian's avatar
Bruce Momjian committed
98 99

    </itemizedlist>
100
    <para> This can result in a considerable performance increase as
Bruce Momjian's avatar
Bruce Momjian committed
101
    compared to an application that does not use stored functions.
102
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
103

104
    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
105 106
     Also, with <application>PL/pgSQL</application> you can use all
     the data types, operators and functions of SQL.
107 108 109
    </para>
  </sect2>

110
  <sect2 id="plpgsql-args-results">
111
   <title>Supported Argument and Result Data Types</title>
112 113 114

    <para>
     Functions written in <application>PL/pgSQL</application> can accept
115
     as arguments any scalar or array data type supported by the server,
116 117 118 119 120 121 122 123 124
     and they can return a result of any of these types.  They can also
     accept or return any composite type (row type) specified by name.
     It is also possible to declare a <application>PL/pgSQL</application>
     function as returning <type>record</>, which means that the result
     is a row type whose columns are determined by specification in the
     calling query, as discussed in <xref linkend="queries-tablefunctions">.
    </para>

    <para>
125
     <application>PL/pgSQL</> functions can also be declared to accept
Peter Eisentraut's avatar
Peter Eisentraut committed
126
     and return the polymorphic types
127 128
     <type>anyelement</type>, <type>anyarray</type>, <type>anynonarray</type>,
     and <type>anyenum</>.  The actual
129
     data types handled by a polymorphic function can vary from call to
Peter Eisentraut's avatar
Peter Eisentraut committed
130
     call, as discussed in <xref linkend="extend-types-polymorphic">.
131 132 133 134
     An example is shown in <xref linkend="plpgsql-declaration-aliases">.
    </para>

    <para>
135 136 137 138 139
     <application>PL/pgSQL</> functions can also be declared to return
     a <quote>set</> (or table) of any data type that can be returned as
     a single instance.  Such a function generates its output by executing
     <command>RETURN NEXT</> for each desired element of the result
     set, or by using <command>RETURN QUERY</> to output the result of
140
     evaluating a query.
141 142 143
    </para>

    <para>
144
     Finally, a <application>PL/pgSQL</> function can be declared to return
145 146
     <type>void</> if it has no useful return value.
    </para>
147

148 149 150 151 152 153 154 155 156 157 158 159
    <para>
     <application>PL/pgSQL</> functions can also be declared with output
     parameters in place of an explicit specification of the return type.
     This does not add any fundamental capability to the language, but
     it is often convenient, especially for returning multiple values.
    </para>

    <para>
     Specific examples appear in
     <xref linkend="plpgsql-declaration-aliases"> and
     <xref linkend="plpgsql-statements-returning">.
    </para>
160 161 162
  </sect2>
 </sect1>

163 164
 <sect1 id="plpgsql-structure">
  <title>Structure of <application>PL/pgSQL</application></title>
165

166 167 168 169
  <para>
   <application>PL/pgSQL</application> is a block-structured language.
   The complete text of a function definition must be a
   <firstterm>block</>. A block is defined as:
170

171 172 173 174 175 176 177 178 179
<synopsis>
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
<optional> DECLARE
    <replaceable>declarations</replaceable> </optional>
BEGIN
    <replaceable>statements</replaceable>
END <optional> <replaceable>label</replaceable> </optional>;
</synopsis>
    </para>
180

181 182 183 184 185 186 187
    <para>
     Each declaration and each statement within a block is terminated
     by a semicolon.  A block that appears within another block must
     have a semicolon after <literal>END</literal>, as shown above;
     however the final <literal>END</literal> that
     concludes a function body does not require a semicolon.
    </para>
188

189 190 191 192 193 194
    <tip>
     <para>
      A common mistake is to write a semicolon immediately after
      <literal>BEGIN</>.  This is incorrect and will result in a syntax error.
     </para>
    </tip>
Peter Eisentraut's avatar
Peter Eisentraut committed
195

196 197
    <para>
     A <replaceable>label</replaceable> is only needed if you want to
198
     identify the block for use
199 200 201 202
     in an <literal>EXIT</> statement, or to qualify the names of the
     variables declared in the block.  If a label is given after
     <literal>END</>, it must match the label at the block's beginning.
    </para>
203

204 205 206 207 208
    <para>
     All key words are case-insensitive.
     Identifiers are implicitly converted to lowercase
     unless double-quoted, just as they are in ordinary SQL commands.
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
209

210 211 212 213 214 215 216 217 218
    <para>
     There are two types of comments in <application>PL/pgSQL</>. A double
     dash (<literal>--</literal>) starts a comment that extends to the end of
     the line. A <literal>/*</literal> starts a block comment that extends to
     the next occurrence of <literal>*/</literal>.  Block comments cannot be
     nested, but double dash comments can be enclosed into a block comment and
     a double dash can hide the block comment delimiters <literal>/*</literal>
     and <literal>*/</literal>.
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
219

220 221 222 223 224 225 226 227
    <para>
     Any statement in the statement section of a block
     can be a <firstterm>subblock</>.  Subblocks can be used for
     logical grouping or to localize variables to a small group
     of statements.  Variables declared in a subblock mask any
     similarly-named variables of outer blocks for the duration
     of the subblock; but you can access the outer variables anyway
     if you qualify their names with their block's label. For example:
Peter Eisentraut's avatar
Peter Eisentraut committed
228
<programlisting>
229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244
CREATE FUNCTION somefunc() RETURNS integer AS $$
&lt;&lt; outerblock &gt;&gt;
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
    quantity := 50;
    --
    -- Create a subblock
    --
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50
    END;
Peter Eisentraut's avatar
Peter Eisentraut committed
245

246 247 248 249 250
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50

    RETURN quantity;
END;
$$ LANGUAGE plpgsql;
251
</programlisting>
252
    </para>
253

254 255 256 257 258 259 260 261 262 263 264 265
    <note>
     <para>
      There is actually a hidden <quote>outer block</> surrounding the body
      of any <application>PL/pgSQL</> function.  This block provides the
      declarations of the function's parameters (if any), as well as some
      special variables such as <literal>FOUND</literal> (see
      <xref linkend="plpgsql-statements-diagnostics">).  The outer block is
      labeled with the function's name, meaning that parameters and special
      variables can be qualified with the function's name.
     </para>
    </note>

266
    <para>
267 268
     It is important not to confuse the use of
     <command>BEGIN</>/<command>END</> for grouping statements in
269 270
     <application>PL/pgSQL</> with the similarly-named SQL commands
     for transaction
271 272 273
     control.  <application>PL/pgSQL</>'s <command>BEGIN</>/<command>END</>
     are only for grouping; they do not start or end a transaction.
     Functions and trigger procedures are always executed within a transaction
274
     established by an outer query &mdash; they cannot start or commit that
275 276 277
     transaction, since there would be no context for them to execute in.
     However, a block containing an <literal>EXCEPTION</> clause effectively
     forms a subtransaction that can be rolled back without affecting the
278
     outer transaction.  For more about that see <xref
279
     linkend="plpgsql-error-trapping">.
280
    </para>
281
  </sect1>
282

283 284
  <sect1 id="plpgsql-declarations">
    <title>Declarations</title>
285 286

    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
287
     All variables used in a block must be declared in the
288 289
     declarations section of the block.
     (The only exceptions are that the loop variable of a <literal>FOR</> loop
290
     iterating over a range of integer values is automatically declared as an
291 292 293
     integer variable, and likewise the loop variable of a <literal>FOR</> loop
     iterating over a cursor's result is automatically declared as a
     record variable.)
Peter Eisentraut's avatar
Peter Eisentraut committed
294 295 296
    </para>

    <para>
297
     <application>PL/pgSQL</> variables can have any SQL data type, such as
Peter Eisentraut's avatar
Peter Eisentraut committed
298 299
     <type>integer</type>, <type>varchar</type>, and
     <type>char</type>.
300
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
301 302

    <para>
303
     Here are some examples of variable declarations:
Peter Eisentraut's avatar
Peter Eisentraut committed
304
<programlisting>
Peter Eisentraut's avatar
Peter Eisentraut committed
305 306 307
user_id integer;
quantity numeric(5);
url varchar;
308
myrow tablename%ROWTYPE;
Peter Eisentraut's avatar
Peter Eisentraut committed
309
myfield tablename.columnname%TYPE;
310
arow RECORD;
Peter Eisentraut's avatar
Peter Eisentraut committed
311 312
</programlisting>
    </para>
313

314 315
    <para>
     The general syntax of a variable declaration is:
Peter Eisentraut's avatar
Peter Eisentraut committed
316
<synopsis>
317
<replaceable>name</replaceable> <optional> CONSTANT </optional> <replaceable>type</replaceable> <optional> NOT NULL </optional> <optional> { DEFAULT | := } <replaceable>expression</replaceable> </optional>;
Peter Eisentraut's avatar
Peter Eisentraut committed
318
</synopsis>
Peter Eisentraut's avatar
Peter Eisentraut committed
319 320
      The <literal>DEFAULT</> clause, if given, specifies the initial value assigned
      to the variable when the block is entered.  If the <literal>DEFAULT</> clause
321
      is not given then the variable is initialized to the
322
      <acronym>SQL</acronym> null value.
Peter Eisentraut's avatar
Peter Eisentraut committed
323
      The <literal>CONSTANT</> option prevents the variable from being assigned to,
324
      so that its value remains constant for the duration of the block.
Peter Eisentraut's avatar
Peter Eisentraut committed
325 326 327 328
      If <literal>NOT NULL</>
      is specified, an assignment of a null value results in a run-time
      error. All variables declared as <literal>NOT NULL</>
      must have a nonnull default value specified.
329
     </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
330

331
     <para>
332 333 334
      A variable's default value is evaluated and assigned to the variable
      each time the block is entered (not just once per function call).
      So, for example, assigning <literal>now()</literal> to a variable of type
335
      <type>timestamp</type> causes the variable to have the
Peter Eisentraut's avatar
Peter Eisentraut committed
336
      time of the current function call, not the time when the function was
337
      precompiled.
338
     </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
339

340 341
     <para>
      Examples:
Peter Eisentraut's avatar
Peter Eisentraut committed
342
<programlisting>
Peter Eisentraut's avatar
Peter Eisentraut committed
343
quantity integer DEFAULT 32;
344
url varchar := 'http://mysite.com';
Peter Eisentraut's avatar
Peter Eisentraut committed
345
user_id CONSTANT integer := 10;
Peter Eisentraut's avatar
Peter Eisentraut committed
346 347
</programlisting>
     </para>
348

349
    <sect2 id="plpgsql-declaration-aliases">
350 351
     <title>Aliases for Function Parameters</title>

352
     <para>
353
      Parameters passed to functions are named with the identifiers
354
      <literal>$1</literal>, <literal>$2</literal>,
355 356
      etc.  Optionally, aliases can be declared for
      <literal>$<replaceable>n</replaceable></literal>
357 358
      parameter names for increased readability.  Either the alias or the
      numeric identifier can then be used to refer to the parameter value.
359 360 361
     </para>

     <para>
362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379
      There are two ways to create an alias.  The preferred way is to give a
      name to the parameter in the <command>CREATE FUNCTION</command> command,
      for example:
<programlisting>
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
</programlisting>
      The other way, which was the only way available before
      <productname>PostgreSQL</productname> 8.0, is to explicitly
      declare an alias, using the declaration syntax

<synopsis>
<replaceable>name</replaceable> ALIAS FOR $<replaceable>n</replaceable>;
</synopsis>

380
      The same example in this style looks like:
Peter Eisentraut's avatar
Peter Eisentraut committed
381
<programlisting>
382
CREATE FUNCTION sales_tax(real) RETURNS real AS $$
383 384 385
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
Peter Eisentraut's avatar
Peter Eisentraut committed
386
    RETURN subtotal * 0.06;
387
END;
388
$$ LANGUAGE plpgsql;
389
</programlisting>
390 391 392 393 394 395 396 397 398 399 400 401 402
     </para>

    <note>
     <para>
      These two examples are not perfectly equivalent.  In the first case,
      <literal>subtotal</> could be referenced as
      <literal>sales_tax.subtotal</>, but in the second case it could not.
      (Had we attached a label to the block, <literal>subtotal</> could
      be qualified with that label, instead.)
     </para>
    </note>

     <para>
403 404
      Some more examples:
<programlisting>
405
CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
406 407 408 409
DECLARE
    v_string ALIAS FOR $1;
    index ALIAS FOR $2;
BEGIN
410
    -- some computations using v_string and index here
411
END;
412
$$ LANGUAGE plpgsql;
413 414


415
CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
416 417 418
BEGIN
    RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;
419
$$ LANGUAGE plpgsql;
420 421 422
</programlisting>
     </para>

423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463
     <para>
      When a <application>PL/pgSQL</application> function is declared
      with output parameters, the output parameters are given
      <literal>$<replaceable>n</replaceable></literal> names and optional
      aliases in just the same way as the normal input parameters.  An
      output parameter is effectively a variable that starts out NULL;
      it should be assigned to during the execution of the function.
      The final value of the parameter is what is returned.  For instance,
      the sales-tax example could also be done this way:

<programlisting>
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
    tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
</programlisting>

      Notice that we omitted <literal>RETURNS real</> &mdash; we could have
      included it, but it would be redundant.
     </para>

     <para>
      Output parameters are most useful when returning multiple values.
      A trivial example is:

<programlisting>
CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
    sum := x + y;
    prod := x * y;
END;
$$ LANGUAGE plpgsql;
</programlisting>

      As discussed in <xref linkend="xfunc-output-parameters">, this
      effectively creates an anonymous record type for the function's
      results.  If a <literal>RETURNS</> clause is given, it must say
      <literal>RETURNS record</>.
     </para>

464 465
     <para>
      When the return type of a <application>PL/pgSQL</application>
466
      function is declared as a polymorphic type (<type>anyelement</type>,
467
      <type>anyarray</type>, <type>anynonarray</type>, or <type>anyenum</>),
468
      a special parameter <literal>$0</literal>
469
      is created.  Its data type is the actual return type of the function,
470
      as deduced from the actual input types (see <xref
Peter Eisentraut's avatar
Peter Eisentraut committed
471
      linkend="extend-types-polymorphic">).
472 473
      This allows the function to access its actual return type
      as shown in <xref linkend="plpgsql-declaration-type">.
Peter Eisentraut's avatar
Peter Eisentraut committed
474
      <literal>$0</literal> is initialized to null and can be modified by
475 476
      the function, so it can be used to hold the return value if desired,
      though that is not required.  <literal>$0</literal> can also be
477
      given an alias.  For example, this function works on any data type
478
      that has a <literal>+</> operator:
479

480
<programlisting>
481
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
482
RETURNS anyelement AS $$
483 484 485
DECLARE
    result ALIAS FOR $0;
BEGIN
486
    result := v1 + v2 + v3;
487 488
    RETURN result;
END;
489
$$ LANGUAGE plpgsql;
490 491 492 493 494
</programlisting>
     </para>

     <para>
      The same effect can be had by declaring one or more output parameters as
495
      polymorphic types.  In this case the
496 497 498 499 500 501 502 503 504 505 506
      special <literal>$0</literal> parameter is not used; the output
      parameters themselves serve the same purpose.  For example:

<programlisting>
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
                                 OUT sum anyelement)
AS $$
BEGIN
    sum := v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;
Peter Eisentraut's avatar
Peter Eisentraut committed
507 508
</programlisting>
     </para>
509
    </sect2>
510

Peter Eisentraut's avatar
Peter Eisentraut committed
511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534
  <sect2 id="plpgsql-declaration-type">
   <title>Copying Types</title>

<synopsis>
<replaceable>variable</replaceable>%TYPE
</synopsis>

   <para>
    <literal>%TYPE</literal> provides the data type of a variable or
    table column. You can use this to declare variables that will hold
    database values. For example, let's say you have a column named
    <literal>user_id</literal> in your <literal>users</literal>
    table. To declare a variable with the same data type as
    <literal>users.user_id</> you write:
<programlisting>
user_id users.user_id%TYPE;
</programlisting>
   </para>

   <para>
    By using <literal>%TYPE</literal> you don't need to know the data
    type of the structure you are referencing, and most importantly,
    if the data type of the referenced item changes in the future (for
    instance: you change the type of <literal>user_id</>
535
    from <type>integer</type> to <type>real</type>), you might not need
Peter Eisentraut's avatar
Peter Eisentraut committed
536 537
    to change your function definition.
   </para>
538 539 540

   <para>
    <literal>%TYPE</literal> is particularly valuable in polymorphic
541
    functions, since the data types needed for internal variables can
542 543 544 545 546
    change from one call to the next.  Appropriate variables can be
    created by applying <literal>%TYPE</literal> to the function's
    arguments or result placeholders.
   </para>

Peter Eisentraut's avatar
Peter Eisentraut committed
547 548
  </sect2>

549
    <sect2 id="plpgsql-declaration-rowtypes">
550
     <title>Row Types</title>
551 552

<synopsis>
553 554
<replaceable>name</replaceable> <replaceable>table_name</replaceable><literal>%ROWTYPE</literal>;
<replaceable>name</replaceable> <replaceable>composite_type_name</replaceable>;
555 556 557
</synopsis>

   <para>
558
    A variable of a composite type is called a <firstterm>row</>
Peter Eisentraut's avatar
Peter Eisentraut committed
559 560 561 562
    variable (or <firstterm>row-type</> variable).  Such a variable
    can hold a whole row of a <command>SELECT</> or <command>FOR</>
    query result, so long as that query's column set matches the
    declared type of the variable.
563
    The individual fields of the row value
Peter Eisentraut's avatar
Peter Eisentraut committed
564
    are accessed using the usual dot notation, for example
565 566 567
    <literal>rowvar.field</literal>.
   </para>

568
   <para>
569 570 571 572
    A row variable can be declared to have the same type as the rows of
    an existing table or view, by using the
    <replaceable>table_name</replaceable><literal>%ROWTYPE</literal>
    notation; or it can be declared by giving a composite type's name.
573
    (Since every table has an associated composite type of the same name,
574 575 576
    it actually does not matter in <productname>PostgreSQL</> whether you
    write <literal>%ROWTYPE</literal> or not.  But the form with
    <literal>%ROWTYPE</literal> is more portable.)
577 578
   </para>

579 580 581
   <para>
    Parameters to a function can be
    composite types (complete table rows). In that case, the
Peter Eisentraut's avatar
Peter Eisentraut committed
582
    corresponding identifier <literal>$<replaceable>n</replaceable></> will be a row variable, and fields can
583 584 585 586
    be selected from it, for example <literal>$1.user_id</literal>.
   </para>

   <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
587 588
    Only the user-defined columns of a table row are accessible in a
    row-type variable, not the OID or other system columns (because the
589
    row could be from a view).  The fields of the row type inherit the
590
    table's field size or precision for data types such as
Peter Eisentraut's avatar
Peter Eisentraut committed
591 592 593 594
    <type>char(<replaceable>n</>)</type>.
   </para>

   <para>
595 596 597 598
    Here is an example of using composite types.  <structname>table1</>
    and <structname>table2</> are existing tables having at least the
    mentioned fields:

599
<programlisting>
600
CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
601
DECLARE
602
    t2_row table2%ROWTYPE;
603
BEGIN
604
    SELECT * INTO t2_row FROM table2 WHERE ... ;
605
    RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
606
END;
607
$$ LANGUAGE plpgsql;
608

609
SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
610
</programlisting>
611 612 613
   </para>
  </sect2>

Peter Eisentraut's avatar
Peter Eisentraut committed
614 615
  <sect2 id="plpgsql-declaration-records">
   <title>Record Types</title>
616 617 618 619 620 621

<synopsis>
<replaceable>name</replaceable> RECORD;
</synopsis>

   <para>
622
    Record variables are similar to row-type variables, but they have no
623
    predefined structure.  They take on the actual row structure of the
Peter Eisentraut's avatar
Peter Eisentraut committed
624
    row they are assigned during a <command>SELECT</> or <command>FOR</> command.  The substructure
625 626
    of a record variable can change each time it is assigned to.
    A consequence of this is that until a record variable is first assigned
Peter Eisentraut's avatar
Peter Eisentraut committed
627
    to, it has no substructure, and any attempt to access a
628
    field in it will draw a run-time error.
629
   </para>
630 631

   <para>
632
    Note that <literal>RECORD</> is not a true data type, only a placeholder.
633 634
    One should also realize that when a <application>PL/pgSQL</application>
    function is declared to return type <type>record</>, this is not quite the
635
    same concept as a record variable, even though such a function might
636 637 638 639 640
    use a record variable to hold its result.  In both cases the actual row
    structure is unknown when the function is written, but for a function
    returning <type>record</> the actual structure is determined when the
    calling query is parsed, whereas a record variable can change its row
    structure on-the-fly.
641
   </para>
642 643
  </sect2>

Peter Eisentraut's avatar
Peter Eisentraut committed
644 645
  <sect2 id="plpgsql-declaration-renaming-vars">
   <title><literal>RENAME</></title>
Peter Eisentraut's avatar
Peter Eisentraut committed
646

647 648 649 650
<synopsis>
RENAME <replaceable>oldname</replaceable> TO <replaceable>newname</replaceable>;
</synopsis>

Peter Eisentraut's avatar
Peter Eisentraut committed
651 652 653
   <para>
    Using the <literal>RENAME</literal> declaration you can change the
    name of a variable, record or row. This is primarily useful if
654
    <varname>NEW</varname> or <varname>OLD</varname> should be
Peter Eisentraut's avatar
Peter Eisentraut committed
655 656 657
    referenced by another name inside a trigger procedure.  See also
    <literal>ALIAS</literal>.
   </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
658

Peter Eisentraut's avatar
Peter Eisentraut committed
659 660
   <para>
    Examples:
Peter Eisentraut's avatar
Peter Eisentraut committed
661
<programlisting>
662 663
RENAME id TO user_id;
RENAME this_var TO that_var;
Peter Eisentraut's avatar
Peter Eisentraut committed
664
</programlisting>
Peter Eisentraut's avatar
Peter Eisentraut committed
665
   </para>
666 667

    <note>
Peter Eisentraut's avatar
Peter Eisentraut committed
668 669 670 671 672 673
     <para>
      <literal>RENAME</literal> appears to be broken as of
      <productname>PostgreSQL</> 7.3.  Fixing this is of low priority,
      since <literal>ALIAS</literal> covers most of the practical uses
      of <literal>RENAME</literal>.
     </para>
674
    </note>
Peter Eisentraut's avatar
Peter Eisentraut committed
675
   </sect2>
676
  </sect1>
677

678 679
  <sect1 id="plpgsql-expressions">
  <title>Expressions</title>
680 681

    <para>
682
     All expressions used in <application>PL/pgSQL</application>
683 684 685 686 687 688 689 690
     statements are processed using the server's main
     <acronym>SQL</acronym> executor.  For example, when you write
     a <application>PL/pgSQL</application> statement like
<synopsis>
IF <replaceable>expression</replaceable> THEN ...
</synopsis>
     <application>PL/pgSQL</application> will evaluate the expression by
     feeding a query like
Peter Eisentraut's avatar
Peter Eisentraut committed
691
<synopsis>
692
SELECT <replaceable>expression</replaceable>
Peter Eisentraut's avatar
Peter Eisentraut committed
693
</synopsis>
694 695 696 697
     to the main SQL engine.  While forming the <command>SELECT</> command,
     any occurrences of <application>PL/pgSQL</application> variable names
     are replaced by parameters, as discussed in detail in
     <xref linkend="plpgsql-var-subst">.
698
     This allows the query plan for the <command>SELECT</command> to
Peter Eisentraut's avatar
Peter Eisentraut committed
699
     be prepared just once and then reused for subsequent
700 701 702 703
     evaluations with different values of the variables.  Thus, what
     really happens on first use of an expression is essentially a
     <command>PREPARE</> command.  For example, if we have declared
     two integer variables <literal>x</> and <literal>y</>, and we write
Peter Eisentraut's avatar
Peter Eisentraut committed
704
<programlisting>
705
IF x &lt; y THEN ...
Peter Eisentraut's avatar
Peter Eisentraut committed
706
</programlisting>
707
     what happens behind the scenes is
Peter Eisentraut's avatar
Peter Eisentraut committed
708
<programlisting>
709
PREPARE <replaceable>statement_name</>(integer, integer) AS SELECT $1 &lt; $2;
Peter Eisentraut's avatar
Peter Eisentraut committed
710
</programlisting>
711 712 713 714 715 716 717 718 719
     and then this prepared statement is <command>EXECUTE</>d for each
     execution of the <command>IF</> statement, with the current values
     of the <application>PL/pgSQL</application> variables supplied as
     parameter values.
     The query plan prepared in this way is saved for the life of the database
     connection, as described in
     <xref linkend="plpgsql-plan-caching">.  Normally these details are
     not important to a <application>PL/pgSQL</application> user, but
     they are useful to know when trying to diagnose a problem.
720
    </para>
721
  </sect1>
722

723
  <sect1 id="plpgsql-statements">
724
  <title>Basic Statements</title>
725

726
   <para>
727 728 729 730
    In this section and the following ones, we describe all the statement
    types that are explicitly understood by
    <application>PL/pgSQL</application>.
    Anything not recognized as one of these statement types is presumed
731 732 733
    to be an SQL command and is sent to the main database engine to execute,
    as described in <xref linkend="plpgsql-statements-sql-noresult">
    and <xref linkend="plpgsql-statements-sql-onerow">.
734
   </para>
735

736
   <sect2 id="plpgsql-statements-assignment">
737
    <title>Assignment</title>
738

739
    <para>
740 741
     An assignment of a value to a <application>PL/pgSQL</application>
     variable or row/record field is written as:
Peter Eisentraut's avatar
Peter Eisentraut committed
742
<synopsis>
743
<replaceable>variable</replaceable> := <replaceable>expression</replaceable>;
Peter Eisentraut's avatar
Peter Eisentraut committed
744
</synopsis>
745 746 747 748
     As explained above, the expression in such a statement is evaluated
     by means of an SQL <command>SELECT</> command sent to the main
     database engine.  The expression must yield a single value.
    </para>
749

750 751 752
    <para>
     If the expression's result data type doesn't match the variable's
     data type, or the variable has a specific size/precision
753
     (like <type>char(20)</type>), the result value will be implicitly
754
     converted by the <application>PL/pgSQL</application> interpreter using
755
     the result type's output-function and
756
     the variable type's input-function. Note that this could potentially
757
     result in run-time errors generated by the input function, if the
758
     string form of the result value is not acceptable to the input function.
759
    </para>
760

761 762
    <para>
     Examples:
Peter Eisentraut's avatar
Peter Eisentraut committed
763
<programlisting>
764
tax := subtotal * 0.06;
765
my_record.user_id := 20;
Peter Eisentraut's avatar
Peter Eisentraut committed
766
</programlisting>
767 768
    </para>
   </sect2>
769

770
   <sect2 id="plpgsql-statements-sql-noresult">
771
    <title>Executing a Command With No Result</title>
772 773

    <para>
774
     For any SQL command that does not return rows, for example
775
     <command>INSERT</> without a <literal>RETURNING</> clause, you can
776 777
     execute the command within a <application>PL/pgSQL</application> function
     just by writing the command.
778 779 780 781
    </para>

    <para>
     Any <application>PL/pgSQL</application> variable name appearing
782
     in the command text is replaced by a parameter symbol, and then the
783
     current value of the variable is provided as the parameter value
784 785 786
     at run time.  This is exactly like the processing described earlier
     for expressions; for details see <xref linkend="plpgsql-var-subst">.
     As an example, if you write:
787 788 789 790 791 792 793 794
<programlisting>
DECLARE
    key TEXT;
    delta INTEGER;
BEGIN
    ...
    UPDATE mytab SET val = val + delta WHERE id = key;
</programlisting>
795
      the command text seen by the main SQL engine will look like:
796 797 798
<programlisting>
    UPDATE mytab SET val = val + $1 WHERE id = $2;
</programlisting>
799 800 801
     Although you don't normally have to think about this, it's helpful
     to know it when you need to make sense of syntax-error messages.
    </para>
802 803 804 805 806 807

    <caution>
     <para>
      <application>PL/pgSQL</application> will substitute for any identifier
      matching one of the function's declared variables; it is not bright
      enough to know whether that's what you meant!  Thus, it is a bad idea
808 809 810
      to use a variable name that is the same as any table, column, or
      function name that you need to reference in commands within the
      function.  For more discussion see <xref linkend="plpgsql-var-subst">.
811 812 813
     </para>
    </caution>

814 815 816 817 818 819 820 821
    <para>
     When executing a SQL command in this way,
     <application>PL/pgSQL</application> plans the command just once
     and re-uses the plan on subsequent executions, for the life of
     the database connection.  The implications of this are discussed
     in detail in <xref linkend="plpgsql-plan-caching">.
    </para>

822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837
    <para>
     Sometimes it is useful to evaluate an expression or <command>SELECT</>
     query but discard the result, for example when calling a function
     that has side-effects but no useful result value.  To do
     this in <application>PL/pgSQL</application>, use the
     <command>PERFORM</command> statement:

<synopsis>
PERFORM <replaceable>query</replaceable>;
</synopsis>

     This executes <replaceable>query</replaceable> and discards the
     result.  Write the <replaceable>query</replaceable> the same
     way you would write an SQL <command>SELECT</> command, but replace the
     initial keyword <command>SELECT</> with <command>PERFORM</command>.
     <application>PL/pgSQL</application> variables will be
838 839
     substituted into the query just as for commands that return no result,
     and the plan is cached in the same way.  Also, the special variable
840
     <literal>FOUND</literal> is set to true if the query produced at
841 842
     least one row, or false if it produced no rows (see
     <xref linkend="plpgsql-statements-diagnostics">).
843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866
    </para>

    <note>
     <para>
      One might expect that writing <command>SELECT</command> directly
      would accomplish this result, but at
      present the only accepted way to do it is
      <command>PERFORM</command>.  A SQL command that can return rows,
      such as <command>SELECT</command>, will be rejected as an error
      unless it has an <literal>INTO</> clause as discussed in the
      next section.
     </para>
    </note>

    <para>
     An example:
<programlisting>
PERFORM create_mv('cs_session_page_requests_mv', my_query);
</programlisting>
    </para>
   </sect2>

   <sect2 id="plpgsql-statements-sql-onerow">
    <title>Executing a Query with a Single-Row Result</title>
867

868
    <indexterm zone="plpgsql-statements-sql-onerow">
Peter Eisentraut's avatar
Peter Eisentraut committed
869 870 871 872
     <primary>SELECT INTO</primary>
     <secondary>in PL/pgSQL</secondary>
    </indexterm>

873 874 875 876 877
    <indexterm zone="plpgsql-statements-sql-onerow">
     <primary>RETURNING INTO</primary>
     <secondary>in PL/pgSQL</secondary>
    </indexterm>

878
    <para>
879 880 881 882
     The result of a SQL command yielding a single row (possibly of multiple
     columns) can be assigned to a record variable, row-type variable, or list
     of scalar variables.  This is done by writing the base SQL command and
     adding an <literal>INTO</> clause.  For example,
883 884

<synopsis>
885 886 887 888
SELECT <replaceable>select_expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable> FROM ...;
INSERT ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
UPDATE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
DELETE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRICT</optional> <replaceable>target</replaceable>;
889 890 891 892
</synopsis>

     where <replaceable>target</replaceable> can be a record variable, a row
     variable, or a comma-separated list of simple variables and
893 894
     record/row fields.
     <application>PL/pgSQL</application> variables will be
895 896
     substituted into the rest of the query, and the plan is cached,
     just as described above for commands that do not return rows.
897 898
     This works for <command>SELECT</>,
     <command>INSERT</>/<command>UPDATE</>/<command>DELETE</> with
Peter Eisentraut's avatar
Peter Eisentraut committed
899
     <literal>RETURNING</>, and utility commands that return row-set
900 901 902
     results (such as <command>EXPLAIN</>).
     Except for the <literal>INTO</> clause, the SQL command is the same
     as it would be written outside <application>PL/pgSQL</application>.
Peter Eisentraut's avatar
Peter Eisentraut committed
903 904
    </para>

905
   <tip>
Peter Eisentraut's avatar
Peter Eisentraut committed
906
    <para>
907 908 909 910
     Note that this interpretation of <command>SELECT</> with <literal>INTO</>
     is quite different from <productname>PostgreSQL</>'s regular
     <command>SELECT INTO</command> command, wherein the <literal>INTO</>
     target is a newly created table.  If you want to create a table from a
Peter Eisentraut's avatar
Peter Eisentraut committed
911 912 913
     <command>SELECT</> result inside a
     <application>PL/pgSQL</application> function, use the syntax
     <command>CREATE TABLE ... AS SELECT</command>.
914
    </para>
915
   </tip>
Peter Eisentraut's avatar
Peter Eisentraut committed
916

917
    <para>
918 919 920
     If a row or a variable list is used as target, the query's result columns
     must exactly match the structure of the target as to number and data
     types, or a run-time error
921
     occurs.  When a record variable is the target, it automatically
922
     configures itself to the row type of the query result columns.
923 924 925
    </para>

    <para>
926 927 928 929 930 931 932
     The <literal>INTO</> clause can appear almost anywhere in the SQL
     command.  Customarily it is written either just before or just after
     the list of <replaceable>select_expressions</replaceable> in a
     <command>SELECT</> command, or at the end of the command for other
     command types.  It is recommended that you follow this convention
     in case the <application>PL/pgSQL</application> parser becomes
     stricter in future versions.
933 934
    </para>

935
    <para>
936 937 938
     If <literal>STRICT</literal> is not specified in the <literal>INTO</>
     clause, then <replaceable>target</replaceable> will be set to the first
     row returned by the query, or to nulls if the query returned no rows.
939
     (Note that <quote>the first row</> is not
940 941 942 943 944
     well-defined unless you've used <literal>ORDER BY</>.)  Any result rows
     after the first row are discarded.
     You can check the special <literal>FOUND</literal> variable (see
     <xref linkend="plpgsql-statements-diagnostics">) to
     determine whether a row was returned:
945

946
<programlisting>
947
SELECT * INTO myrec FROM emp WHERE empname = myname;
948
IF NOT FOUND THEN
949
    RAISE EXCEPTION 'employee % not found', myname;
950 951 952
END IF;
</programlisting>

953
     If the <literal>STRICT</literal> option is specified, the query must
954
     return exactly one row or a run-time error will be reported, either
955
     <literal>NO_DATA_FOUND</> (no rows) or <literal>TOO_MANY_ROWS</>
956 957
     (more than one row). You can use an exception block if you wish
     to catch the error, for example:
958

959
<programlisting>
960
BEGIN
961
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
962 963 964 965 966
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'employee % not found', myname;
        WHEN TOO_MANY_ROWS THEN
            RAISE EXCEPTION 'employee % not unique', myname;
967 968
END;
</programlisting>
969
     Successful execution of a command with <literal>STRICT</>
970
     always sets <literal>FOUND</literal> to true.
971
    </para>
972

973
    <para>
974 975 976 977 978
     For <command>INSERT</>/<command>UPDATE</>/<command>DELETE</> with
     <literal>RETURNING</>, <application>PL/pgSQL</application> reports
     an error for more than one returned row, even when
     <literal>STRICT</literal> is not specified.  This is because there
     is no option such as <literal>ORDER BY</> with which to determine
979
     which affected row should be returned.
980
    </para>
981 982

    <note>
Peter Eisentraut's avatar
Peter Eisentraut committed
983
     <para>
984 985
      The <literal>STRICT</> option matches the behavior of
      Oracle PL/SQL's <command>SELECT INTO</command> and related statements.
Peter Eisentraut's avatar
Peter Eisentraut committed
986 987
     </para>
    </note>
988 989

    <para>
990 991
     To handle cases where you need to process multiple result rows
     from a SQL query, see <xref linkend="plpgsql-records-iterating">.
992
    </para>
993

994
   </sect2>
995

Peter Eisentraut's avatar
Peter Eisentraut committed
996 997
   <sect2 id="plpgsql-statements-executing-dyn">
    <title>Executing Dynamic Commands</title>
998

999
    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1000 1001
     Oftentimes you will want to generate dynamic commands inside your
     <application>PL/pgSQL</application> functions, that is, commands
1002 1003
     that will involve different tables or different data types each
     time they are executed.  <application>PL/pgSQL</application>'s
1004 1005
     normal attempts to cache plans for commands (as discussed in
     <xref linkend="plpgsql-plan-caching">) will not work in such
1006 1007
     scenarios.  To handle this sort of problem, the
     <command>EXECUTE</command> statement is provided:
Peter Eisentraut's avatar
Peter Eisentraut committed
1008 1009

<synopsis>
Tom Lane's avatar
Tom Lane committed
1010
EXECUTE <replaceable class="command">command-string</replaceable> <optional> INTO <optional>STRICT</optional> <replaceable>target</replaceable> </optional> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;
Peter Eisentraut's avatar
Peter Eisentraut committed
1011
</synopsis>
1012

Peter Eisentraut's avatar
Peter Eisentraut committed
1013
     where <replaceable>command-string</replaceable> is an expression
1014
     yielding a string (of type <type>text</type>) containing the
Tom Lane's avatar
Tom Lane committed
1015 1016 1017 1018 1019
     command to be executed.  The optional <replaceable>target</replaceable>
     is a record variable, a row variable, or a comma-separated list of
     simple variables and record/row fields, into which the results of
     the command will be stored.  The optional <literal>USING</> expressions
     supply values to be inserted into the command.
1020 1021 1022
    </para>

    <para>
1023 1024
     No substitution of <application>PL/pgSQL</> variables is done on the
     computed command string.  Any required variable values must be inserted
Tom Lane's avatar
Tom Lane committed
1025 1026
     in the command string as it is constructed; or you can use parameters
     as described below.
Peter Eisentraut's avatar
Peter Eisentraut committed
1027 1028 1029
    </para>

    <para>
1030 1031 1032
     Also, there is no plan caching for commands executed via
     <command>EXECUTE</command>.  Instead, the
     command is prepared each time the statement is run. Thus the command
Peter Eisentraut's avatar
Peter Eisentraut committed
1033
     string can be dynamically created within the function to perform
1034
     actions on different tables and columns.
Peter Eisentraut's avatar
Peter Eisentraut committed
1035
    </para>
1036

Peter Eisentraut's avatar
Peter Eisentraut committed
1037
    <para>
1038
     The <literal>INTO</literal> clause specifies where the results of
1039
     a SQL command returning rows should be assigned. If a row
1040
     or variable list is provided, it must exactly match the structure
1041
     of the query's results (when a
1042
     record variable is used, it will configure itself to match the
1043
     result structure automatically). If multiple rows are returned,
1044 1045
     only the first will be assigned to the <literal>INTO</literal>
     variable. If no rows are returned, NULL is assigned to the
1046
     <literal>INTO</literal> variable(s). If no <literal>INTO</literal>
1047 1048 1049 1050 1051 1052
     clause is specified, the query results are discarded.
    </para>

    <para>
     If the <literal>STRICT</> option is given, an error is reported
     unless the query produces exactly one row.
1053
    </para>
1054

Tom Lane's avatar
Tom Lane committed
1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086
    <para>
     The command string can use parameter values, which are referenced
     in the command as <literal>$1</>, <literal>$2</>, etc.
     These symbols refer to values supplied in the <literal>USING</>
     clause.  This method is often preferable to inserting data values
     into the command string as text: it avoids run-time overhead of
     converting the values to text and back, and it is much less prone
     to SQL-injection attacks since there is no need for quoting or escaping.
     An example is:
<programlisting>
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;
</programlisting>

     Note that parameter symbols can only be used for data values
     &mdash; if you want to use dynamically determined table or column
     names, you must insert them into the command string textually.
     For example, if the preceding query needed to be done against a
     dynamically selected table, you could do this:
<programlisting>
EXECUTE 'SELECT count(*) FROM '
    || tabname::regclass
    || ' WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;
</programlisting>
    </para>

    <para>
     An <command>EXECUTE</> with a simple constant command string and some
     <literal>USING</> parameters, as in the first example above, is
1087 1088
     functionally equivalent to just writing the command directly in
     <application>PL/pgSQL</application> and allowing replacement of
Tom Lane's avatar
Tom Lane committed
1089 1090 1091 1092 1093 1094 1095 1096 1097 1098
     <application>PL/pgSQL</application> variables to happen automatically.
     The important difference is that <command>EXECUTE</> will re-plan
     the command on each execution, generating a plan that is specific
     to the current parameter values; whereas
     <application>PL/pgSQL</application> normally creates a generic plan
     and caches it for re-use.  In situations where the best plan depends
     strongly on the parameter values, <command>EXECUTE</> can be
     significantly faster; while when the plan is not sensitive to parameter
     values, re-planning will be a waste.
    </para>
1099 1100 1101

    <para>
     <command>SELECT INTO</command> is not currently supported within
1102 1103 1104
     <command>EXECUTE</command>; instead, execute a plain <command>SELECT</>
     command and specify <literal>INTO</> as part of the <command>EXECUTE</>
     itself.
Peter Eisentraut's avatar
Peter Eisentraut committed
1105
    </para>
1106

1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118
   <note>
    <para>
     The <application>PL/pgSQL</application>
     <command>EXECUTE</command> statement is not related to the
     <xref linkend="sql-execute" endterm="sql-execute-title"> SQL
     statement supported by the
     <productname>PostgreSQL</productname> server. The server's
     <command>EXECUTE</command> statement cannot be used directly within
     <application>PL/pgSQL</> functions (and is not needed).
    </para>
   </note>

1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136
   <example id="plpgsql-quote-literal-example">
   <title>Quoting values in dynamic queries</title>

    <indexterm>
     <primary>quote_ident</primary>
     <secondary>use in PL/PgSQL</secondary>
    </indexterm>

    <indexterm>
     <primary>quote_literal</primary>
     <secondary>use in PL/PgSQL</secondary>
    </indexterm>

    <indexterm>
     <primary>quote_nullable</primary>
     <secondary>use in PL/PgSQL</secondary>
    </indexterm>

Peter Eisentraut's avatar
Peter Eisentraut committed
1137
    <para>
1138 1139 1140 1141 1142 1143 1144 1145 1146 1147
     When working with dynamic commands you will often have to handle escaping
     of single quotes.  The recommended method for quoting fixed text in your
     function body is dollar quoting.  (If you have legacy code that does
     not use dollar quoting, please refer to the
     overview in <xref linkend="plpgsql-quote-tips">, which can save you
     some effort when translating said code to a more reasonable scheme.)
    </para>

    <para>
     Dynamic values that are to be inserted into the constructed
1148
     query require careful handling since they might themselves contain
1149
     quote characters.
1150 1151
     An example (this assumes that you are using dollar quoting for the
     function as a whole, so the quote marks need not be doubled):
1152
<programlisting>
1153
EXECUTE 'UPDATE tbl SET '
Peter Eisentraut's avatar
Peter Eisentraut committed
1154
        || quote_ident(colname)
1155
        || ' = '
1156
        || quote_literal(newvalue)
1157 1158
        || ' WHERE key = '
        || quote_literal(keyvalue);
1159
</programlisting>
1160
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1161

1162
    <para>
1163 1164
     This example demonstrates the use of the
     <function>quote_ident</function> and
1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202
     <function>quote_literal</function> functions (see <xref
     linkend="functions-string">).  For safety, expressions containing column
     or table identifiers should be passed through
     <function>quote_ident</function> before insertion in a dynamic query.
     Expressions containing values that should be literal strings in the
     constructed command should be passed through <function>quote_literal</>.
     These functions take the appropriate steps to return the input text
     enclosed in double or single quotes respectively, with any embedded
     special characters properly escaped.
    </para>

    <para>
     Because <function>quote_literal</function> is labelled
     <literal>STRICT</literal>, it will always return null when called with a
     null argument.  In the above example, if <literal>newvalue</> or
     <literal>keyvalue</> were null, the entire dynamic query string would
     become null, leading to an error from <command>EXECUTE</command>.
     You can avoid this problem by using the <function>quote_nullable</>
     function, which works the same as <function>quote_literal</> except that
     when called with a null argument it returns the string <literal>NULL</>.
     For example,
<programlisting>
EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_nullable(newvalue)
        || ' WHERE key = '
        || quote_nullable(keyvalue);
</programlisting>
     If you are dealing with values that might be null, you should usually
     use <function>quote_nullable</> in place of <function>quote_literal</>.
    </para>

    <para>
     As always, care must be taken to ensure that null values in a query do
     not deliver unintended results.  For example the <literal>WHERE</> clause
<programlisting>
     'WHERE key = ' || quote_nullable(keyvalue)
1203
</programlisting>
1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214
     will never succeed if <literal>keyvalue</> is null, because the
     result of using the equality operator <literal>=</> with a null operand
     is always null.  If you wish null to work like an ordinary key value,
     you would need to rewrite the above as
<programlisting>
     'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
</programlisting>
     (At present, <literal>IS NOT DISTINCT FROM</> is handled much less
     efficiently than <literal>=</>, so don't do this unless you must.
     See <xref linkend="functions-comparison"> for
     more information on nulls and <literal>IS DISTINCT</>.)
Peter Eisentraut's avatar
Peter Eisentraut committed
1215 1216
    </para>

1217
    <para>
1218
     Note that dollar quoting is only useful for quoting fixed text.
1219
     It would be a very bad idea to try to write this example as:
Peter Eisentraut's avatar
Peter Eisentraut committed
1220
<programlisting>
1221 1222 1223 1224
EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = $$'
        || newvalue
1225 1226
        || '$$ WHERE key = '
        || quote_literal(keyvalue);
1227
</programlisting>
1228 1229 1230 1231
     because it would break if the contents of <literal>newvalue</>
     happened to contain <literal>$$</>.  The same objection would
     apply to any other dollar-quoting delimiter you might pick.
     So, to safely quote text that is not known in advance, you
1232 1233
     <emphasis>must</> use <function>quote_literal</>,
     <function>quote_nullable</>, or <function>quote_ident</>, as appropriate.
1234
    </para>
1235
   </example>
1236

1237 1238 1239 1240 1241
    <para>
     A much larger example of a dynamic command and
     <command>EXECUTE</command> can be seen in <xref
     linkend="plpgsql-porting-ex2">, which builds and executes a
     <command>CREATE FUNCTION</> command to define a new function.
1242
    </para>
1243
   </sect2>
1244

1245
   <sect2 id="plpgsql-statements-diagnostics">
Peter Eisentraut's avatar
Peter Eisentraut committed
1246
    <title>Obtaining the Result Status</title>
Peter Eisentraut's avatar
Peter Eisentraut committed
1247 1248

    <para>
1249 1250 1251
     There are several ways to determine the effect of a command. The
     first method is to use the <command>GET DIAGNOSTICS</command>
     command, which has the form:
1252

Peter Eisentraut's avatar
Peter Eisentraut committed
1253
<synopsis>
1254
GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
Peter Eisentraut's avatar
Peter Eisentraut committed
1255 1256 1257
</synopsis>

     This command allows retrieval of system status indicators.  Each
Peter Eisentraut's avatar
Peter Eisentraut committed
1258
     <replaceable>item</replaceable> is a key word identifying a state
Peter Eisentraut's avatar
Peter Eisentraut committed
1259
     value to be assigned to the specified variable (which should be
1260
     of the right data type to receive it).  The currently available
Peter Eisentraut's avatar
Peter Eisentraut committed
1261
     status items are <varname>ROW_COUNT</>, the number of rows
Peter Eisentraut's avatar
Peter Eisentraut committed
1262 1263
     processed by the last <acronym>SQL</acronym> command sent down to
     the <acronym>SQL</acronym> engine, and <varname>RESULT_OID</>,
1264
     the OID of the last row inserted by the most recent
Peter Eisentraut's avatar
Peter Eisentraut committed
1265
     <acronym>SQL</acronym> command.  Note that <varname>RESULT_OID</>
1266 1267
     is only useful after an <command>INSERT</command> command into a
     table containing OIDs.
Peter Eisentraut's avatar
Peter Eisentraut committed
1268
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1269

1270
    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1271
     An example:
1272
<programlisting>
1273
GET DIAGNOSTICS integer_var = ROW_COUNT;
1274 1275
</programlisting>
    </para>
1276 1277

    <para>
1278 1279
     The second method to determine the effects of a command is to check the
     special variable named <literal>FOUND</literal>, which is of
1280
     type <type>boolean</type>.  <literal>FOUND</literal> starts out
1281
     false within each <application>PL/pgSQL</application> function call.
1282
     It is set by each of the following types of statements:
Peter Eisentraut's avatar
Peter Eisentraut committed
1283 1284 1285
         <itemizedlist>
          <listitem>
           <para>
1286 1287 1288
            A <command>SELECT INTO</command> statement sets
            <literal>FOUND</literal> true if a row is assigned, false if no
            row is returned.
Peter Eisentraut's avatar
Peter Eisentraut committed
1289 1290 1291 1292
           </para>
          </listitem>
          <listitem>
           <para>
1293 1294 1295
            A <command>PERFORM</> statement sets <literal>FOUND</literal>
            true if it produces (and discards) one or more rows, false if
            no row is produced.
Peter Eisentraut's avatar
Peter Eisentraut committed
1296 1297 1298 1299
           </para>
          </listitem>
          <listitem>
           <para>
1300 1301 1302
            <command>UPDATE</>, <command>INSERT</>, and <command>DELETE</>
            statements set <literal>FOUND</literal> true if at least one
            row is affected, false if no row is affected.
Peter Eisentraut's avatar
Peter Eisentraut committed
1303 1304 1305 1306
           </para>
          </listitem>
          <listitem>
           <para>
1307 1308
            A <command>FETCH</> statement sets <literal>FOUND</literal>
            true if it returns a row, false if no row is returned.
Peter Eisentraut's avatar
Peter Eisentraut committed
1309 1310
           </para>
          </listitem>
1311 1312
          <listitem>
           <para>
1313 1314
            A <command>MOVE</> statement sets <literal>FOUND</literal>
            true if it successfully repositions the cursor, false otherwise.
1315 1316 1317
           </para>
          </listitem>

Peter Eisentraut's avatar
Peter Eisentraut committed
1318 1319
          <listitem>
           <para>
1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330
            A <command>FOR</> statement sets <literal>FOUND</literal> true
            if it iterates one or more times, else false.  This applies to
            all four variants of the <command>FOR</> statement (integer
            <command>FOR</> loops, record-set <command>FOR</> loops,
            dynamic record-set <command>FOR</> loops, and cursor
            <command>FOR</> loops).
            <literal>FOUND</literal> is set this way when the
            <command>FOR</> loop exits; inside the execution of the loop,
            <literal>FOUND</literal> is not modified by the
            <command>FOR</> statement, although it might be changed by the
            execution of other statements within the loop body.
Peter Eisentraut's avatar
Peter Eisentraut committed
1331 1332 1333
           </para>
          </listitem>
         </itemizedlist>
1334 1335

     <literal>FOUND</literal> is a local variable within each
1336 1337
     <application>PL/pgSQL</application> function; any changes to it
     affect only the current function.
1338 1339
    </para>

1340
   </sect2>
1341

1342 1343
   <sect2 id="plpgsql-statements-null">
    <title>Doing Nothing At All</title>
1344

1345
    <para>
1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396
     Sometimes a placeholder statement that does nothing is useful.
     For example, it can indicate that one arm of an if/then/else
     chain is deliberately empty.  For this purpose, use the
     <command>NULL</command> statement:

<synopsis>
NULL;
</synopsis>
    </para>

    <para>
     For example, the following two fragments of code are equivalent:
<programlisting>
    BEGIN
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN
            NULL;  -- ignore the error
    END;
</programlisting>

<programlisting>
    BEGIN
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN  -- ignore the error
    END;
</programlisting>
     Which is preferable is a matter of taste.
    </para>

    <note>
     <para>
      In Oracle's PL/SQL, empty statement lists are not allowed, and so
      <command>NULL</> statements are <emphasis>required</> for situations
      such as this.  <application>PL/pgSQL</application> allows you to
      just write nothing, instead.
     </para>
    </note>

   </sect2>
  </sect1>

  <sect1 id="plpgsql-control-structures">
   <title>Control Structures</title>

   <para>
    Control structures are probably the most useful (and
    important) part of <application>PL/pgSQL</>. With
    <application>PL/pgSQL</>'s control structures,
    you can manipulate <productname>PostgreSQL</> data in a very
1397
    flexible and powerful way.
1398 1399 1400 1401 1402 1403 1404 1405 1406
   </para>

   <sect2 id="plpgsql-statements-returning">
    <title>Returning From a Function</title>

    <para>
     There are two commands available that allow you to return data
     from a function: <command>RETURN</command> and <command>RETURN
     NEXT</command>.
1407 1408 1409 1410 1411
    </para>

    <sect3>
     <title><command>RETURN</></title>

Peter Eisentraut's avatar
Peter Eisentraut committed
1412
<synopsis>
1413
RETURN <replaceable>expression</replaceable>;
Peter Eisentraut's avatar
Peter Eisentraut committed
1414
</synopsis>
1415

Peter Eisentraut's avatar
Peter Eisentraut committed
1416 1417 1418 1419
     <para>
      <command>RETURN</command> with an expression terminates the
      function and returns the value of
      <replaceable>expression</replaceable> to the caller.  This form
1420
      is to be used for <application>PL/pgSQL</> functions that do
Peter Eisentraut's avatar
Peter Eisentraut committed
1421
      not return a set.
1422
     </para>
1423

1424
     <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1425 1426 1427 1428 1429
      When returning a scalar type, any expression can be used. The
      expression's result will be automatically cast into the
      function's return type as described for assignments. To return a
      composite (row) value, you must write a record or row variable
      as the <replaceable>expression</replaceable>.
1430
     </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1431

1432 1433 1434 1435 1436 1437
     <para>
      If you declared the function with output parameters, write just
      <command>RETURN</command> with no expression.  The current values
      of the output parameter variables will be returned.
     </para>

1438
     <para>
1439 1440 1441 1442
      If you declared the function to return <type>void</type>, a
      <command>RETURN</command> statement can be used to exit the function
      early; but do not write an expression following
      <command>RETURN</command>.
1443 1444 1445
     </para>

     <para>
1446 1447 1448 1449 1450 1451 1452
      The return value of a function cannot be left undefined. If
      control reaches the end of the top-level block of the function
      without hitting a <command>RETURN</command> statement, a run-time
      error will occur.  This restriction does not apply to functions
      with output parameters and functions returning <type>void</type>,
      however.  In those cases a <command>RETURN</command> statement is
      automatically executed if the top-level block finishes.
1453 1454
     </para>
    </sect3>
1455

1456
    <sect3>
1457 1458 1459 1460 1461 1462 1463 1464 1465
     <title><command>RETURN NEXT</> and <command>RETURN QUERY</command></title>
    <indexterm>
     <primary>RETURN NEXT</primary>
     <secondary>in PL/PgSQL</secondary>
    </indexterm>
    <indexterm>
     <primary>RETURN QUERY</primary>
     <secondary>in PL/PgSQL</secondary>
    </indexterm>
1466 1467 1468

<synopsis>
RETURN NEXT <replaceable>expression</replaceable>;
1469
RETURN QUERY <replaceable>query</replaceable>;
1470
RETURN QUERY EXECUTE <replaceable class="command">command-string</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;
1471 1472 1473 1474
</synopsis>

     <para>
      When a <application>PL/pgSQL</> function is declared to return
Peter Eisentraut's avatar
Peter Eisentraut committed
1475
      <literal>SETOF <replaceable>sometype</></literal>, the procedure
1476
      to follow is slightly different.  In that case, the individual
1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488
      items to return are specified by a sequence of <command>RETURN
      NEXT</command> or <command>RETURN QUERY</command> commands, and
      then a final <command>RETURN</command> command with no argument
      is used to indicate that the function has finished executing.
      <command>RETURN NEXT</command> can be used with both scalar and
      composite data types; with a composite result type, an entire
      <quote>table</quote> of results will be returned.
      <command>RETURN QUERY</command> appends the results of executing
      a query to the function's result set. <command>RETURN
      NEXT</command> and <command>RETURN QUERY</command> can be freely
      intermixed in a single set-returning function, in which case
      their results will be concatenated.
1489 1490 1491
     </para>

     <para>
1492 1493 1494 1495 1496 1497 1498 1499 1500 1501
      <command>RETURN NEXT</command> and <command>RETURN
      QUERY</command> do not actually return from the function &mdash;
      they simply append zero or more rows to the function's result
      set.  Execution then continues with the next statement in the
      <application>PL/pgSQL</> function.  As successive
      <command>RETURN NEXT</command> or <command>RETURN
      QUERY</command> commands are executed, the result set is built
      up.  A final <command>RETURN</command>, which should have no
      argument, causes control to exit the function (or you can just
      let control reach the end of the function).
1502 1503
     </para>

1504 1505 1506 1507 1508 1509 1510 1511
     <para>
      <command>RETURN QUERY</command> has a variant
      <command>RETURN QUERY EXECUTE</command>, which specifies the
      query to be executed dynamically.  Parameter expressions can
      be inserted into the computed query string via <literal>USING</>,
      in just the same way as in the <command>EXECUTE</> command.
     </para>

1512 1513
     <para>
      If you declared the function with output parameters, write just
1514
      <command>RETURN NEXT</command> with no expression.  On each
1515 1516 1517 1518 1519 1520 1521 1522
      execution, the current values of the output parameter
      variable(s) will be saved for eventual return as a row of the
      result.  Note that you must declare the function as returning
      <literal>SETOF record</literal> when there are multiple output
      parameters, or <literal>SETOF <replaceable>sometype</></literal>
      when there is just one output parameter of type
      <replaceable>sometype</>, in order to create a set-returning
      function with output parameters.
Peter Eisentraut's avatar
Peter Eisentraut committed
1523 1524 1525
     </para>

     <para>
1526 1527
      Here is an example of a function using <command>RETURN
      NEXT</command>:
1528 1529

<programlisting>
1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');

CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN SELECT * FROM foo
    WHERE fooid &gt; 0
    LOOP
        -- can do some processing here
Bruce Momjian's avatar
Bruce Momjian committed
1543
        RETURN NEXT r; -- return current row of SELECT
1544 1545 1546 1547 1548 1549 1550
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;

SELECT * FROM getallfoo();
1551 1552
</programlisting>

1553 1554 1555
      Note that functions using <command>RETURN NEXT</command> or
      <command>RETURN QUERY</command> must be called as a table source in
      a <literal>FROM</literal> clause.
1556
     </para>
1557

1558 1559 1560
     <note>
      <para>
       The current implementation of <command>RETURN NEXT</command>
1561
       and <command>RETURN QUERY</command> stores the entire result set
1562 1563
       before returning from the function, as discussed above.  That
       means that if a <application>PL/pgSQL</> function produces a
1564
       very large result set, performance might be poor: data will be
1565 1566
       written to disk to avoid memory exhaustion, but the function
       itself will not return until the entire result set has been
1567
       generated.  A future version of <application>PL/pgSQL</> might
1568
       allow users to define set-returning functions
1569 1570
       that do not have this limitation.  Currently, the point at
       which data begins being written to disk is controlled by the
Neil Conway's avatar
Neil Conway committed
1571
       <xref linkend="guc-work-mem">
1572 1573 1574
       configuration variable.  Administrators who have sufficient
       memory to store larger result sets in memory should consider
       increasing this parameter.
1575 1576 1577 1578
      </para>
     </note>
    </sect3>
   </sect2>
1579

1580 1581
   <sect2 id="plpgsql-conditionals">
    <title>Conditionals</title>
1582 1583

    <para>
1584
     <literal>IF</> statements let you execute commands based on
1585
     certain conditions.  <application>PL/pgSQL</> has five forms of
1586 1587 1588 1589 1590 1591 1592 1593 1594
     <literal>IF</>:
    <itemizedlist>
     <listitem>
      <para><literal>IF ... THEN</></>
     </listitem>
     <listitem>
      <para><literal>IF ... THEN ... ELSE</></>
     </listitem>
     <listitem>
Peter Eisentraut's avatar
Peter Eisentraut committed
1595
      <para><literal>IF ... THEN ... ELSE IF</></>
1596 1597 1598 1599
     </listitem>
     <listitem>
      <para><literal>IF ... THEN ... ELSIF ... THEN ... ELSE</></>
     </listitem>
1600 1601 1602
     <listitem>
      <para><literal>IF ... THEN ... ELSEIF ... THEN ... ELSE</></>
     </listitem>
1603
    </itemizedlist>
1604 1605
    </para>

1606
    <sect3>
1607
     <title><literal>IF-THEN</></title>
Peter Eisentraut's avatar
Peter Eisentraut committed
1608

1609 1610 1611 1612 1613 1614
<synopsis>
IF <replaceable>boolean-expression</replaceable> THEN
    <replaceable>statements</replaceable>
END IF;
</synopsis>

Peter Eisentraut's avatar
Peter Eisentraut committed
1615
       <para>
1616 1617 1618 1619 1620
        <literal>IF-THEN</literal> statements are the simplest form of
        <literal>IF</literal>. The statements between
        <literal>THEN</literal> and <literal>END IF</literal> will be
        executed if the condition is true. Otherwise, they are
        skipped.
Peter Eisentraut's avatar
Peter Eisentraut committed
1621
       </para>
1622

Peter Eisentraut's avatar
Peter Eisentraut committed
1623 1624
       <para>
        Example:
Peter Eisentraut's avatar
Peter Eisentraut committed
1625
<programlisting>
1626 1627 1628
IF v_user_id &lt;&gt; 0 THEN
    UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;
Peter Eisentraut's avatar
Peter Eisentraut committed
1629
</programlisting>
1630
       </para>
1631
     </sect3>
1632

1633
     <sect3>
1634
      <title><literal>IF-THEN-ELSE</></title>
Peter Eisentraut's avatar
Peter Eisentraut committed
1635

1636 1637 1638 1639 1640 1641 1642 1643
<synopsis>
IF <replaceable>boolean-expression</replaceable> THEN
    <replaceable>statements</replaceable>
ELSE
    <replaceable>statements</replaceable>
END IF;
</synopsis>

Peter Eisentraut's avatar
Peter Eisentraut committed
1644
       <para>
1645 1646 1647
        <literal>IF-THEN-ELSE</literal> statements add to
        <literal>IF-THEN</literal> by letting you specify an
        alternative set of statements that should be executed if the
Peter Eisentraut's avatar
Peter Eisentraut committed
1648 1649
        condition evaluates to false.
       </para>
1650

Peter Eisentraut's avatar
Peter Eisentraut committed
1651 1652
       <para>
        Examples:
Peter Eisentraut's avatar
Peter Eisentraut committed
1653
<programlisting>
1654
IF parentid IS NULL OR parentid = ''
Peter Eisentraut's avatar
Peter Eisentraut committed
1655 1656
THEN
    RETURN fullname;
1657
ELSE
1658
    RETURN hp_true_filename(parentid) || '/' || fullname;
1659
END IF;
Peter Eisentraut's avatar
Peter Eisentraut committed
1660
</programlisting>
1661

Peter Eisentraut's avatar
Peter Eisentraut committed
1662
<programlisting>
1663
IF v_count &gt; 0 THEN
Peter Eisentraut's avatar
Peter Eisentraut committed
1664
    INSERT INTO users_count (count) VALUES (v_count);
1665
    RETURN 't';
Peter Eisentraut's avatar
Peter Eisentraut committed
1666
ELSE
1667
    RETURN 'f';
1668
END IF;
Peter Eisentraut's avatar
Peter Eisentraut committed
1669
</programlisting>
1670 1671
     </para>
    </sect3>
1672

1673
     <sect3>
1674
      <title><literal>IF-THEN-ELSE IF</></title>
Peter Eisentraut's avatar
Peter Eisentraut committed
1675

1676
       <para>
1677 1678 1679
        <literal>IF</literal> statements can be nested, as in the
        following example:

Peter Eisentraut's avatar
Peter Eisentraut committed
1680
<programlisting>
1681 1682
IF demo_row.sex = 'm' THEN
    pretty_sex := 'man';
1683
ELSE
1684 1685
    IF demo_row.sex = 'f' THEN
        pretty_sex := 'woman';
Peter Eisentraut's avatar
Peter Eisentraut committed
1686
    END IF;
1687
END IF;
Peter Eisentraut's avatar
Peter Eisentraut committed
1688
</programlisting>
1689 1690 1691
       </para>

       <para>
1692 1693 1694 1695
        When you use this form, you are actually nesting an
        <literal>IF</literal> statement inside the
        <literal>ELSE</literal> part of an outer <literal>IF</literal>
        statement. Thus you need one <literal>END IF</literal>
Peter Eisentraut's avatar
Peter Eisentraut committed
1696
        statement for each nested <literal>IF</literal> and one for the parent
1697 1698
        <literal>IF-ELSE</literal>.  This is workable but grows
        tedious when there are many alternatives to be checked.
Peter Eisentraut's avatar
Peter Eisentraut committed
1699
        Hence the next form.
Peter Eisentraut's avatar
Peter Eisentraut committed
1700
       </para>
1701
     </sect3>
Bruce Momjian's avatar
Bruce Momjian committed
1702

1703
     <sect3>
1704
      <title><literal>IF-THEN-ELSIF-ELSE</></title>
Bruce Momjian's avatar
Bruce Momjian committed
1705

1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720
<synopsis>
IF <replaceable>boolean-expression</replaceable> THEN
    <replaceable>statements</replaceable>
<optional> ELSIF <replaceable>boolean-expression</replaceable> THEN
    <replaceable>statements</replaceable>
<optional> ELSIF <replaceable>boolean-expression</replaceable> THEN
    <replaceable>statements</replaceable>
    ...
</optional>
</optional>
<optional> ELSE
    <replaceable>statements</replaceable> </optional>
END IF;
</synopsis>

Peter Eisentraut's avatar
Peter Eisentraut committed
1721
       <para>
1722 1723
        <literal>IF-THEN-ELSIF-ELSE</> provides a more convenient
        method of checking many alternatives in one statement.
1724
        Functionally it is equivalent to nested
1725 1726
        <literal>IF-THEN-ELSE-IF-THEN</> commands, but only one
        <literal>END IF</> is needed.
Bruce Momjian's avatar
Bruce Momjian committed
1727 1728 1729 1730 1731 1732 1733
       </para>

       <para>
        Here is an example:

<programlisting>
IF number = 0 THEN
1734
    result := 'zero';
1735
ELSIF number &gt; 0 THEN
1736
    result := 'positive';
1737
ELSIF number &lt; 0 THEN
1738
    result := 'negative';
Bruce Momjian's avatar
Bruce Momjian committed
1739
ELSE
Peter Eisentraut's avatar
Peter Eisentraut committed
1740
    -- hmm, the only other possibility is that number is null
1741
    result := 'NULL';
Bruce Momjian's avatar
Bruce Momjian committed
1742 1743 1744
END IF;
</programlisting>
       </para>
1745
     </sect3>
1746 1747 1748 1749 1750 1751

     <sect3>
      <title><literal>IF-THEN-ELSEIF-ELSE</></title>

      <para>
       <literal>ELSEIF</> is an alias for <literal>ELSIF</>.
Tom Lane's avatar
Tom Lane committed
1752 1753
      </para>
     </sect3>
1754
   </sect2>
1755

1756 1757
   <sect2 id="plpgsql-control-structures-loops">
    <title>Simple Loops</title>
1758

Peter Eisentraut's avatar
Peter Eisentraut committed
1759 1760 1761 1762 1763
    <indexterm zone="plpgsql-control-structures-loops">
     <primary>loop</primary>
     <secondary>in PL/pgSQL</secondary>
    </indexterm>

1764
    <para>
1765 1766 1767 1768
     With the <literal>LOOP</>, <literal>EXIT</>,
     <literal>CONTINUE</>, <literal>WHILE</>, and <literal>FOR</>
     statements, you can arrange for your <application>PL/pgSQL</>
     function to repeat a series of commands.
1769
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1770

1771
    <sect3>
Peter Eisentraut's avatar
Peter Eisentraut committed
1772
     <title><literal>LOOP</></title>
Peter Eisentraut's avatar
Peter Eisentraut committed
1773 1774

<synopsis>
1775
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
1776 1777
LOOP
    <replaceable>statements</replaceable>
1778
END LOOP <optional> <replaceable>label</replaceable> </optional>;
Peter Eisentraut's avatar
Peter Eisentraut committed
1779
</synopsis>
1780

Peter Eisentraut's avatar
Peter Eisentraut committed
1781
     <para>
1782 1783 1784 1785 1786 1787
      <literal>LOOP</> defines an unconditional loop that is repeated
      indefinitely until terminated by an <literal>EXIT</> or
      <command>RETURN</command> statement.  The optional
      <replaceable>label</replaceable> can be used by <literal>EXIT</>
      and <literal>CONTINUE</literal> statements in nested loops to
      specify which loop the statement should be applied to.
1788 1789
     </para>
    </sect3>
1790

1791
     <sect3>
Peter Eisentraut's avatar
Peter Eisentraut committed
1792
      <title><literal>EXIT</></title>
Peter Eisentraut's avatar
Peter Eisentraut committed
1793

1794 1795 1796 1797 1798
     <indexterm>
      <primary>EXIT</primary>
      <secondary>in PL/pgSQL</secondary>
     </indexterm>

Peter Eisentraut's avatar
Peter Eisentraut committed
1799
<synopsis>
1800
EXIT <optional> <replaceable>label</replaceable> </optional> <optional> WHEN <replaceable>boolean-expression</replaceable> </optional>;
Peter Eisentraut's avatar
Peter Eisentraut committed
1801
</synopsis>
1802

Peter Eisentraut's avatar
Peter Eisentraut committed
1803
       <para>
1804 1805 1806 1807 1808 1809 1810
        If no <replaceable>label</replaceable> is given, the innermost
        loop is terminated and the statement following <literal>END
        LOOP</> is executed next.  If <replaceable>label</replaceable>
        is given, it must be the label of the current or some outer
        level of nested loop or block. Then the named loop or block is
        terminated and control continues with the statement after the
        loop's/block's corresponding <literal>END</>.
1811
       </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1812

1813
       <para>
1814
        If <literal>WHEN</> is specified, the loop exit occurs only if
1815
        <replaceable>boolean-expression</> is true. Otherwise, control passes
1816
        to the statement after <literal>EXIT</>.
1817 1818 1819
       </para>

       <para>
1820 1821 1822 1823
        <literal>EXIT</> can be used with all types of loops; it is
        not limited to use with unconditional loops. When used with a
        <literal>BEGIN</literal> block, <literal>EXIT</literal> passes
        control to the next statement after the end of the block.
1824 1825
       </para>

1826
       <para>
1827
        Examples:
Peter Eisentraut's avatar
Peter Eisentraut committed
1828
<programlisting>
1829 1830
LOOP
    -- some computations
1831
    IF count &gt; 0 THEN
1832 1833 1834 1835 1836 1837
        EXIT;  -- exit loop
    END IF;
END LOOP;

LOOP
    -- some computations
1838
    EXIT WHEN count &gt; 0;  -- same result as previous example
1839 1840 1841 1842
END LOOP;

BEGIN
    -- some computations
1843
    IF stocks &gt; 100000 THEN
1844
        EXIT;  -- causes exit from the BEGIN block
1845 1846
    END IF;
END;
Peter Eisentraut's avatar
Peter Eisentraut committed
1847
</programlisting>
1848
       </para>
1849
     </sect3>
1850

1851 1852 1853 1854 1855 1856 1857 1858 1859
     <sect3>
      <title><literal>CONTINUE</></title>

     <indexterm>
      <primary>CONTINUE</primary>
      <secondary>in PL/pgSQL</secondary>
     </indexterm>

<synopsis>
1860
CONTINUE <optional> <replaceable>label</replaceable> </optional> <optional> WHEN <replaceable>boolean-expression</replaceable> </optional>;
1861 1862 1863 1864
</synopsis>

       <para>
        If no <replaceable>label</> is given, the next iteration of
1865 1866 1867 1868 1869
        the innermost loop is begun. That is, all statements remaining
        in the loop body are skipped, and control returns
        to the loop control expression (if any) to determine whether
        another loop iteration is needed.
        If <replaceable>label</> is present, it
1870 1871 1872 1873 1874 1875
        specifies the label of the loop whose execution will be
        continued.
       </para>

       <para>
        If <literal>WHEN</> is specified, the next iteration of the
1876
        loop is begun only if <replaceable>boolean-expression</> is
1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892
        true. Otherwise, control passes to the statement after
        <literal>CONTINUE</>.
       </para>

       <para>
        <literal>CONTINUE</> can be used with all types of loops; it
        is not limited to use with unconditional loops.
       </para>

       <para>
        Examples:
<programlisting>
LOOP
    -- some computations
    EXIT WHEN count &gt; 100;
    CONTINUE WHEN count &lt; 50;
1893
    -- some computations for count IN [50 .. 100]
1894 1895 1896 1897 1898 1899
END LOOP;
</programlisting>
       </para>
     </sect3>


1900
     <sect3>
Peter Eisentraut's avatar
Peter Eisentraut committed
1901
      <title><literal>WHILE</></title>
Peter Eisentraut's avatar
Peter Eisentraut committed
1902

1903 1904 1905 1906 1907
     <indexterm>
      <primary>WHILE</primary>
      <secondary>in PL/pgSQL</secondary>
     </indexterm>

Peter Eisentraut's avatar
Peter Eisentraut committed
1908
<synopsis>
1909
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
1910
WHILE <replaceable>boolean-expression</replaceable> LOOP
1911
    <replaceable>statements</replaceable>
1912
END LOOP <optional> <replaceable>label</replaceable> </optional>;
Peter Eisentraut's avatar
Peter Eisentraut committed
1913
</synopsis>
1914

Peter Eisentraut's avatar
Peter Eisentraut committed
1915 1916
       <para>
        The <literal>WHILE</> statement repeats a
1917 1918 1919
        sequence of statements so long as the
        <replaceable>boolean-expression</replaceable>
        evaluates to true.  The expression is checked just before
Peter Eisentraut's avatar
Peter Eisentraut committed
1920
        each entry to the loop body.
1921 1922 1923
       </para>

       <para>
1924
        For example:
Peter Eisentraut's avatar
Peter Eisentraut committed
1925
<programlisting>
1926
WHILE amount_owed &gt; 0 AND gift_certificate_balance &gt; 0 LOOP
1927 1928 1929
    -- some computations here
END LOOP;

1930
WHILE NOT done LOOP
1931 1932
    -- some computations here
END LOOP;
Peter Eisentraut's avatar
Peter Eisentraut committed
1933
</programlisting>
1934
       </para>
1935
     </sect3>
1936

1937
     <sect3 id="plpgsql-integer-for">
Peter Eisentraut's avatar
Peter Eisentraut committed
1938
      <title><literal>FOR</> (integer variant)</title>
Peter Eisentraut's avatar
Peter Eisentraut committed
1939 1940

<synopsis>
1941
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
1942
FOR <replaceable>name</replaceable> IN <optional> REVERSE </optional> <replaceable>expression</replaceable> .. <replaceable>expression</replaceable> <optional> BY <replaceable>expression</replaceable> </optional> LOOP
1943
    <replaceable>statements</replaceable>
1944
END LOOP <optional> <replaceable>label</replaceable> </optional>;
Peter Eisentraut's avatar
Peter Eisentraut committed
1945
</synopsis>
1946

Peter Eisentraut's avatar
Peter Eisentraut committed
1947
       <para>
1948
        This form of <literal>FOR</> creates a loop that iterates over a range
1949
        of integer values. The variable
1950
        <replaceable>name</replaceable> is automatically defined as type
1951 1952 1953
        <type>integer</> and exists only inside the loop (any existing
        definition of the variable name is ignored within the loop).
        The two expressions giving
1954
        the lower and upper bound of the range are evaluated once when entering
1955 1956
        the loop. If the <literal>BY</> clause isn't specified the iteration
        step is 1, otherwise it's the value specified in the <literal>BY</>
1957
        clause, which again is evaluated once on loop entry.
1958
        If <literal>REVERSE</> is specified then the step value is
1959
        subtracted, rather than added, after each iteration.
1960
       </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1961

1962
       <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1963
        Some examples of integer <literal>FOR</> loops:
Peter Eisentraut's avatar
Peter Eisentraut committed
1964
<programlisting>
1965
FOR i IN 1..10 LOOP
1966
    -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
1967 1968
END LOOP;

1969
FOR i IN REVERSE 10..1 LOOP
1970
    -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
1971
END LOOP;
1972 1973

FOR i IN REVERSE 10..1 BY 2 LOOP
1974
    -- i will take on the values 10,8,6,4,2 within the loop
1975
END LOOP;
Peter Eisentraut's avatar
Peter Eisentraut committed
1976
</programlisting>
1977
       </para>
1978 1979

       <para>
1980
        If the lower bound is greater than the upper bound (or less than,
Peter Eisentraut's avatar
Peter Eisentraut committed
1981
        in the <literal>REVERSE</> case), the loop body is not
1982
        executed at all.  No error is raised.
1983
       </para>
1984 1985 1986 1987 1988 1989 1990

       <para>
        If a <replaceable>label</replaceable> is attached to the
        <literal>FOR</> loop then the integer loop variable can be
        referenced with a qualified name, using that
        <replaceable>label</replaceable>.
       </para>
1991 1992
     </sect3>
   </sect2>
Peter Eisentraut's avatar
Peter Eisentraut committed
1993

1994 1995
   <sect2 id="plpgsql-records-iterating">
    <title>Looping Through Query Results</title>
1996 1997

    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1998
     Using a different type of <literal>FOR</> loop, you can iterate through
1999
     the results of a query and manipulate that data
2000
     accordingly. The syntax is:
Peter Eisentraut's avatar
Peter Eisentraut committed
2001
<synopsis>
2002
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
2003
FOR <replaceable>target</replaceable> IN <replaceable>query</replaceable> LOOP
2004
    <replaceable>statements</replaceable>
2005
END LOOP <optional> <replaceable>label</replaceable> </optional>;
Peter Eisentraut's avatar
Peter Eisentraut committed
2006
</synopsis>
2007 2008 2009
     The <replaceable>target</replaceable> is a record variable, row variable,
     or comma-separated list of scalar variables.
     The <replaceable>target</replaceable> is successively assigned each row
2010 2011
     resulting from the <replaceable>query</replaceable> and the loop body is
     executed for each row. Here is an example:
Peter Eisentraut's avatar
Peter Eisentraut committed
2012
<programlisting>
2013
CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
2014
DECLARE
Peter Eisentraut's avatar
Peter Eisentraut committed
2015
    mviews RECORD;
2016
BEGIN
2017
    PERFORM cs_log('Refreshing materialized views...');
2018

Peter Eisentraut's avatar
Peter Eisentraut committed
2019
    FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
2020

Peter Eisentraut's avatar
Peter Eisentraut committed
2021
        -- Now "mviews" has one record from cs_materialized_views
2022

2023 2024
        PERFORM cs_log('Refreshing materialized view ' || quote_ident(mviews.mv_name) || ' ...');
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
2025
        EXECUTE 'INSERT INTO ' || quote_ident(mviews.mv_name) || ' ' || mviews.mv_query;
Peter Eisentraut's avatar
Peter Eisentraut committed
2026
    END LOOP;
2027

2028
    PERFORM cs_log('Done refreshing materialized views.');
Peter Eisentraut's avatar
Peter Eisentraut committed
2029 2030
    RETURN 1;
END;
2031
$$ LANGUAGE plpgsql;
Peter Eisentraut's avatar
Peter Eisentraut committed
2032
</programlisting>
2033

Peter Eisentraut's avatar
Peter Eisentraut committed
2034
     If the loop is terminated by an <literal>EXIT</> statement, the last
2035
     assigned row value is still accessible after the loop.
2036
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
2037

2038 2039
    <para>
     The <replaceable>query</replaceable> used in this type of <literal>FOR</>
2040 2041
     statement can be any SQL command that returns rows to the caller:
     <command>SELECT</> is the most common case,
2042 2043 2044 2045 2046
     but you can also use <command>INSERT</>, <command>UPDATE</>, or
     <command>DELETE</> with a <literal>RETURNING</> clause.  Some utility
     commands such as <command>EXPLAIN</> will work too.
    </para>

2047 2048 2049 2050 2051 2052 2053
    <para>
     <application>PL/pgSQL</> variables are substituted into the query text,
     and the query plan is cached for possible re-use, as discussed in
     detail in <xref linkend="plpgsql-var-subst"> and
     <xref linkend="plpgsql-plan-caching">.
    </para>

Peter Eisentraut's avatar
Peter Eisentraut committed
2054
    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
2055
     The <literal>FOR-IN-EXECUTE</> statement is another way to iterate over
2056
     rows:
Peter Eisentraut's avatar
Peter Eisentraut committed
2057
<synopsis>
2058
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
2059
FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional> LOOP
2060
    <replaceable>statements</replaceable>
2061
END LOOP <optional> <replaceable>label</replaceable> </optional>;
Peter Eisentraut's avatar
Peter Eisentraut committed
2062
</synopsis>
2063 2064 2065 2066 2067
     This is like the previous form, except that the source query
     is specified as a string expression, which is evaluated and replanned
     on each entry to the <literal>FOR</> loop.  This allows the programmer to
     choose the speed of a preplanned query or the flexibility of a dynamic
     query, just as with a plain <command>EXECUTE</command> statement.
Tom Lane's avatar
Tom Lane committed
2068 2069
     As with <command>EXECUTE</command>, parameter values can be inserted
     into the dynamic command via <literal>USING</>.
Peter Eisentraut's avatar
Peter Eisentraut committed
2070
    </para>
2071 2072 2073 2074 2075 2076

    <para>
     Another way to specify the query whose results should be iterated
     through is to declare it as a cursor.  This is described in
     <xref linkend="plpgsql-cursor-for-loop">.
    </para>
Tom Lane's avatar
Tom Lane committed
2077 2078 2079 2080 2081
   </sect2>

   <sect2 id="plpgsql-error-trapping">
    <title>Trapping Errors</title>

2082 2083 2084 2085 2086
    <indexterm>
     <primary>exceptions</primary>
     <secondary>in PL/PgSQL</secondary>
    </indexterm>

Tom Lane's avatar
Tom Lane committed
2087
    <para>
2088 2089 2090 2091 2092 2093
     By default, any error occurring in a <application>PL/pgSQL</>
     function aborts execution of the function, and indeed of the
     surrounding transaction as well.  You can trap errors and recover
     from them by using a <command>BEGIN</> block with an
     <literal>EXCEPTION</> clause.  The syntax is an extension of the
     normal syntax for a <command>BEGIN</> block:
Tom Lane's avatar
Tom Lane committed
2094 2095 2096 2097 2098 2099 2100 2101

<synopsis>
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
<optional> DECLARE
    <replaceable>declarations</replaceable> </optional>
BEGIN
    <replaceable>statements</replaceable>
EXCEPTION
2102
    WHEN <replaceable>condition</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
Tom Lane's avatar
Tom Lane committed
2103
        <replaceable>handler_statements</replaceable>
2104 2105
    <optional> WHEN <replaceable>condition</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
          <replaceable>handler_statements</replaceable>
2106
      ... </optional>
Tom Lane's avatar
Tom Lane committed
2107 2108 2109 2110 2111 2112 2113 2114 2115 2116 2117 2118 2119 2120 2121 2122 2123 2124 2125
END;
</synopsis>
    </para>

    <para>
     If no error occurs, this form of block simply executes all the
     <replaceable>statements</replaceable>, and then control passes
     to the next statement after <literal>END</>.  But if an error
     occurs within the <replaceable>statements</replaceable>, further
     processing of the <replaceable>statements</replaceable> is
     abandoned, and control passes to the <literal>EXCEPTION</> list.
     The list is searched for the first <replaceable>condition</replaceable>
     matching the error that occurred.  If a match is found, the
     corresponding <replaceable>handler_statements</replaceable> are
     executed, and then control passes to the next statement after
     <literal>END</>.  If no match is found, the error propagates out
     as though the <literal>EXCEPTION</> clause were not there at all:
     the error can be caught by an enclosing block with
     <literal>EXCEPTION</>, or if there is none it aborts processing
2126 2127 2128 2129
     of the function.
    </para>

    <para>
2130 2131 2132 2133 2134 2135 2136
     The <replaceable>condition</replaceable> names can be any of
     those shown in <xref linkend="errcodes-appendix">.  A category
     name matches any error within its category.  The special
     condition name <literal>OTHERS</> matches every error type except
     <literal>QUERY_CANCELED</>.  (It is possible, but often unwise,
     to trap <literal>QUERY_CANCELED</> by name.)  Condition names are
     not case-sensitive.
Tom Lane's avatar
Tom Lane committed
2137 2138 2139 2140 2141 2142 2143 2144 2145 2146 2147 2148 2149 2150 2151 2152 2153 2154 2155 2156 2157 2158 2159 2160 2161 2162 2163 2164 2165 2166 2167 2168 2169 2170
    </para>

    <para>
     If a new error occurs within the selected
     <replaceable>handler_statements</replaceable>, it cannot be caught
     by this <literal>EXCEPTION</> clause, but is propagated out.
     A surrounding <literal>EXCEPTION</> clause could catch it.
    </para>

    <para>
     When an error is caught by an <literal>EXCEPTION</> clause,
     the local variables of the <application>PL/pgSQL</> function
     remain as they were when the error occurred, but all changes
     to persistent database state within the block are rolled back.
     As an example, consider this fragment:

<programlisting>
    INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
    BEGIN
        UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
        x := x + 1;
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN
            RAISE NOTICE 'caught division_by_zero';
            RETURN x;
    END;
</programlisting>

     When control reaches the assignment to <literal>y</>, it will
     fail with a <literal>division_by_zero</> error.  This will be caught by
     the <literal>EXCEPTION</> clause.  The value returned in the
     <command>RETURN</> statement will be the incremented value of
     <literal>x</>, but the effects of the <command>UPDATE</> command will
2171 2172 2173
     have been rolled back.  The <command>INSERT</> command preceding the
     block is not rolled back, however, so the end result is that the database
     contains <literal>Tom Jones</> not <literal>Joe Jones</>.
Tom Lane's avatar
Tom Lane committed
2174 2175 2176 2177 2178 2179 2180 2181 2182
    </para>

    <tip>
     <para>
      A block containing an <literal>EXCEPTION</> clause is significantly
      more expensive to enter and exit than a block without one.  Therefore,
      don't use <literal>EXCEPTION</> without need.
     </para>
    </tip>
2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193

    <para>
     Within an exception handler, the <varname>SQLSTATE</varname>
     variable contains the error code that corresponds to the
     exception that was raised (refer to <xref
     linkend="errcodes-table"> for a list of possible error
     codes). The <varname>SQLERRM</varname> variable contains the
     error message associated with the exception. These variables are
     undefined outside exception handlers.
    </para>

2194
    <example id="plpgsql-upsert-example">
2195
    <title>Exceptions with <command>UPDATE</>/<command>INSERT</></title>
2196
    <para>
2197 2198

    This example uses exception handling to perform either
2199
    <command>UPDATE</> or <command>INSERT</>, as appropriate:
2200 2201 2202 2203

<programlisting>
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

2204
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
2205 2206 2207
$$
BEGIN
    LOOP
2208
        -- first try to update the key
2209 2210 2211 2212
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
2213 2214 2215
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
2216 2217 2218 2219
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
2220
            -- do nothing, and loop to try the UPDATE again
2221 2222 2223 2224 2225 2226
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

2227 2228
SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');
2229 2230 2231 2232
</programlisting>

    </para>
    </example>
2233
  </sect2>
2234
  </sect1>
2235

2236 2237 2238
  <sect1 id="plpgsql-cursors">
   <title>Cursors</title>

Peter Eisentraut's avatar
Peter Eisentraut committed
2239 2240 2241 2242 2243
   <indexterm zone="plpgsql-cursors">
    <primary>cursor</primary>
    <secondary>in PL/pgSQL</secondary>
   </indexterm>

2244
   <para>
2245 2246 2247 2248
    Rather than executing a whole query at once, it is possible to set
    up a <firstterm>cursor</> that encapsulates the query, and then read
    the query result a few rows at a time. One reason for doing this is
    to avoid memory overrun when the result contains a large number of
2249
    rows. (However, <application>PL/pgSQL</> users do not normally need
Peter Eisentraut's avatar
Peter Eisentraut committed
2250
    to worry about that, since <literal>FOR</> loops automatically use a cursor
2251
    internally to avoid memory problems.) A more interesting usage is to
2252
    return a reference to a cursor that a function has created, allowing the
2253 2254
    caller to read the rows. This provides an efficient way to return
    large row sets from functions.
2255
   </para>
2256

2257 2258 2259 2260 2261
   <sect2 id="plpgsql-cursor-declarations">
    <title>Declaring Cursor Variables</title>

    <para>
     All access to cursors in <application>PL/pgSQL</> goes through
2262
     cursor variables, which are always of the special data type
2263 2264 2265 2266 2267
     <type>refcursor</>.  One way to create a cursor variable
     is just to declare it as a variable of type <type>refcursor</>.
     Another way is to use the cursor declaration syntax,
     which in general is:
<synopsis>
2268
<replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>;
2269
</synopsis>
2270
     (<literal>FOR</> can be replaced by <literal>IS</> for
2271
     <productname>Oracle</productname> compatibility.)
2272 2273 2274 2275
     If <literal>SCROLL</> is specified, the cursor will be capable of
     scrolling backward; if <literal>NO SCROLL</> is specified, backward
     fetches will be rejected; if neither specification appears, it is
     query-dependent whether backward fetches will be allowed.
Peter Eisentraut's avatar
Peter Eisentraut committed
2276 2277 2278
     <replaceable>arguments</replaceable>, if specified, is a
     comma-separated list of pairs <literal><replaceable>name</replaceable>
     <replaceable>datatype</replaceable></literal> that define names to be
2279
     replaced by parameter values in the given query.  The actual
2280 2281 2282 2283 2284 2285 2286 2287
     values to substitute for these names will be specified later,
     when the cursor is opened.
    </para>
    <para>
     Some examples:
<programlisting>
DECLARE
    curs1 refcursor;
Peter Eisentraut's avatar
Peter Eisentraut committed
2288 2289
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
2290
</programlisting>
2291
     All three of these variables have the data type <type>refcursor</>,
2292
     but the first can be used with any query, while the second has
2293 2294 2295 2296 2297 2298 2299 2300 2301 2302 2303 2304 2305 2306
     a fully specified query already <firstterm>bound</> to it, and the last
     has a parameterized query bound to it.  (<literal>key</> will be
     replaced by an integer parameter value when the cursor is opened.)
     The variable <literal>curs1</>
     is said to be <firstterm>unbound</> since it is not bound to
     any particular query.
    </para>
   </sect2>

   <sect2 id="plpgsql-cursor-opening">
    <title>Opening Cursors</title>

    <para>
     Before a cursor can be used to retrieve rows, it must be
2307 2308
     <firstterm>opened</>. (This is the equivalent action to the SQL
     command <command>DECLARE CURSOR</>.) <application>PL/pgSQL</> has
2309 2310
     three forms of the <command>OPEN</> statement, two of which use unbound
     cursor variables while the third uses a bound cursor variable.
2311 2312
    </para>

2313 2314 2315 2316 2317 2318 2319 2320
    <note>
     <para>
      Bound cursors can also be used without explicitly opening them,
      via the <command>FOR</> statement described in
      <xref linkend="plpgsql-cursor-for-loop">.
     </para>
    </note>

2321
    <sect3>
2322
     <title><command>OPEN FOR</command> <replaceable>query</replaceable></title>
2323 2324

<synopsis>
2325
OPEN <replaceable>unbound_cursor</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR <replaceable>query</replaceable>;
2326 2327
</synopsis>

Peter Eisentraut's avatar
Peter Eisentraut committed
2328
       <para>
2329
        The cursor variable is opened and given the specified query to
Peter Eisentraut's avatar
Peter Eisentraut committed
2330 2331
        execute.  The cursor cannot be open already, and it must have been
        declared as an unbound cursor (that is, as a simple
2332 2333 2334 2335 2336
        <type>refcursor</> variable).  The query must be a
        <command>SELECT</command>, or something else that returns rows
        (such as <command>EXPLAIN</>).  The query
        is treated in the same way as other SQL commands in
        <application>PL/pgSQL</>: <application>PL/pgSQL</>
Peter Eisentraut's avatar
Peter Eisentraut committed
2337
        variable names are substituted, and the query plan is cached for
2338 2339 2340 2341 2342 2343
        possible reuse.  When a <application>PL/pgSQL</>
        variable is substituted into the cursor query, the value that is
        substituted is the one it has at the time of the <command>OPEN</>;
        subsequent changes to the variable will not affect the cursor's
        behavior.
        The <literal>SCROLL</> and <literal>NO SCROLL</>
2344
        options have the same meanings as for a bound cursor.
Peter Eisentraut's avatar
Peter Eisentraut committed
2345
       </para>
2346

Peter Eisentraut's avatar
Peter Eisentraut committed
2347 2348
       <para>
        An example:
2349 2350 2351 2352 2353 2354 2355
<programlisting>
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
</programlisting>
       </para>
     </sect3>

    <sect3>
Peter Eisentraut's avatar
Peter Eisentraut committed
2356
     <title><command>OPEN FOR EXECUTE</command></title>
2357 2358

<synopsis>
2359
OPEN <replaceable>unbound_cursor</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR EXECUTE <replaceable class="command">query_string</replaceable>;
2360 2361
</synopsis>

Peter Eisentraut's avatar
Peter Eisentraut committed
2362 2363 2364 2365 2366
         <para>
          The cursor variable is opened and given the specified query to
          execute.  The cursor cannot be open already, and it must have been
          declared as an unbound cursor (that is, as a simple
          <type>refcursor</> variable).  The query is specified as a string
2367
          expression, in the same way as in the <command>EXECUTE</command>
2368 2369 2370 2371 2372
          command.  As usual, this gives flexibility so the query plan can vary
          from one run to the next (see <xref linkend="plpgsql-plan-caching">),
          and it also means that variable substitution is not done on the
          command string.
          The <literal>SCROLL</> and
2373 2374 2375
          <literal>NO SCROLL</> options have the same meanings as for a bound
          cursor.
         </para>
2376

Peter Eisentraut's avatar
Peter Eisentraut committed
2377 2378
       <para>
        An example:
2379
<programlisting>
2380
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
2381 2382 2383 2384 2385
</programlisting>
       </para>
     </sect3>

    <sect3>
Peter Eisentraut's avatar
Peter Eisentraut committed
2386
     <title>Opening a Bound Cursor</title>
2387 2388

<synopsis>
2389
OPEN <replaceable>bound_cursor</replaceable> <optional> ( <replaceable>argument_values</replaceable> ) </optional>;
2390 2391
</synopsis>

Peter Eisentraut's avatar
Peter Eisentraut committed
2392 2393 2394 2395 2396 2397 2398 2399
         <para>
          This form of <command>OPEN</command> is used to open a cursor
          variable whose query was bound to it when it was declared.  The
          cursor cannot be open already.  A list of actual argument value
          expressions must appear if and only if the cursor was declared to
          take arguments.  These values will be substituted in the query.
          The query plan for a bound cursor is always considered cacheable;
          there is no equivalent of <command>EXECUTE</command> in this case.
2400 2401 2402
          Notice that <literal>SCROLL</> and
          <literal>NO SCROLL</> cannot be specified, as the cursor's scrolling
          behavior was already determined.
Peter Eisentraut's avatar
Peter Eisentraut committed
2403
         </para>
2404

2405 2406 2407 2408 2409 2410 2411 2412 2413 2414 2415
         <para>
          Note that because variable substitution is done on the bound
          cursor's query, there are two ways to pass values into the cursor:
          either with an explicit argument to <command>OPEN</>, or
          implicitly by referencing a <application>PL/pgSQL</> variable
          in the query.  However, only variables declared before the bound
          cursor was declared will be substituted into it.  In either case
          the value to be passed is determined at the time of the
          <command>OPEN</>.
         </para>

Peter Eisentraut's avatar
Peter Eisentraut committed
2416 2417
    <para>
     Examples:
2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 2433 2434 2435 2436 2437 2438
<programlisting>
OPEN curs2;
OPEN curs3(42);
</programlisting>
       </para>
     </sect3>
   </sect2>

   <sect2 id="plpgsql-cursor-using">
    <title>Using Cursors</title>

    <para>
     Once a cursor has been opened, it can be manipulated with the
     statements described here.
    </para>

    <para>
     These manipulations need not occur in the same function that
     opened the cursor to begin with.  You can return a <type>refcursor</>
     value out of a function and let the caller operate on the cursor.
     (Internally, a <type>refcursor</> value is simply the string name
Peter Eisentraut's avatar
Peter Eisentraut committed
2439
     of a so-called portal containing the active query for the cursor.  This name
2440
     can be passed around, assigned to other <type>refcursor</> variables,
Peter Eisentraut's avatar
Peter Eisentraut committed
2441
     and so on, without disturbing the portal.)
2442 2443 2444
    </para>

    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
2445 2446
     All portals are implicitly closed at transaction end.  Therefore
     a <type>refcursor</> value is usable to reference an open cursor
2447 2448 2449 2450
     only until the end of the transaction.
    </para>

    <sect3>
Peter Eisentraut's avatar
Peter Eisentraut committed
2451
     <title><literal>FETCH</></title>
2452 2453

<synopsis>
2454
FETCH <optional> <replaceable>direction</replaceable> { FROM | IN } </optional> <replaceable>cursor</replaceable> INTO <replaceable>target</replaceable>;
2455 2456
</synopsis>

2457 2458 2459 2460
    <para>
     <command>FETCH</command> retrieves the next row from the
     cursor into a target, which might be a row variable, a record
     variable, or a comma-separated list of simple variables, just like
2461 2462
     <command>SELECT INTO</command>.  If there is no next row, the
     target is set to NULL(s).  As with <command>SELECT
2463 2464 2465
     INTO</command>, the special variable <literal>FOUND</literal> can
     be checked to see whether a row was obtained or not.
    </para>
2466

Peter Eisentraut's avatar
Peter Eisentraut committed
2467
    <para>
2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 2484 2485 2486
     The <replaceable>direction</replaceable> clause can be any of the
     variants allowed in the SQL <xref linkend="sql-fetch"
     endterm="sql-fetch-title"> command except the ones that can fetch
     more than one row; namely, it can be
     <literal>NEXT</>,
     <literal>PRIOR</>,
     <literal>FIRST</>,
     <literal>LAST</>,
     <literal>ABSOLUTE</> <replaceable>count</replaceable>,
     <literal>RELATIVE</> <replaceable>count</replaceable>,
     <literal>FORWARD</>, or
     <literal>BACKWARD</>.
     Omitting <replaceable>direction</replaceable> is the same
     as specifying <literal>NEXT</>.
     <replaceable>direction</replaceable> values that require moving
     backward are likely to fail unless the cursor was declared or opened
     with the <literal>SCROLL</> option.
    </para>

2487 2488 2489 2490 2491
    <para>
     <replaceable>cursor</replaceable> must be the name of a <type>refcursor</>
     variable that references an open cursor portal.
    </para>

2492 2493
    <para>
     Examples:
2494 2495
<programlisting>
FETCH curs1 INTO rowvar;
Peter Eisentraut's avatar
Peter Eisentraut committed
2496
FETCH curs2 INTO foo, bar, baz;
2497 2498
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;
2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512
</programlisting>
       </para>
     </sect3>

    <sect3>
     <title><literal>MOVE</></title>

<synopsis>
MOVE <optional> <replaceable>direction</replaceable> { FROM | IN } </optional> <replaceable>cursor</replaceable>;
</synopsis>

    <para>
     <command>MOVE</command> repositions a cursor without retrieving
     any data. <command>MOVE</command> works exactly like the
Tom Lane's avatar
Tom Lane committed
2513 2514 2515
     <command>FETCH</command> command, except it only repositions the
     cursor and does not return the row moved to. As with <command>SELECT
     INTO</command>, the special variable <literal>FOUND</literal> can
2516
     be checked to see whether there was a next row to move to.
2517 2518 2519
    </para>

    <para>
Tom Lane's avatar
Tom Lane committed
2520 2521
     The options for the <replaceable>direction</replaceable> clause are
     the same as for <command>FETCH</>, namely
2522 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 2534 2535 2536 2537 2538 2539 2540 2541 2542
     <literal>NEXT</>,
     <literal>PRIOR</>,
     <literal>FIRST</>,
     <literal>LAST</>,
     <literal>ABSOLUTE</> <replaceable>count</replaceable>,
     <literal>RELATIVE</> <replaceable>count</replaceable>,
     <literal>FORWARD</>, or
     <literal>BACKWARD</>.
     Omitting <replaceable>direction</replaceable> is the same
     as specifying <literal>NEXT</>.
     <replaceable>direction</replaceable> values that require moving
     backward are likely to fail unless the cursor was declared or opened
     with the <literal>SCROLL</> option.
    </para>

    <para>
     Examples:
<programlisting>
MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 2564 2565 2566 2567
</programlisting>
       </para>
     </sect3>

    <sect3>
     <title><literal>UPDATE/DELETE WHERE CURRENT OF</></title>

<synopsis>
UPDATE <replaceable>table</replaceable> SET ... WHERE CURRENT OF <replaceable>cursor</replaceable>;
DELETE FROM <replaceable>table</replaceable> WHERE CURRENT OF <replaceable>cursor</replaceable>;
</synopsis>

       <para>
        When a cursor is positioned on a table row, that row can be updated
        or deleted using the cursor to identify the row.  Note that this
        only works for simple (non-join, non-grouping) cursor queries.
        For additional information see the
        <xref linkend="sql-declare" endterm="sql-declare-title">
        reference page.
       </para>

       <para>
        An example:
<programlisting>
UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
2568 2569 2570 2571 2572
</programlisting>
       </para>
     </sect3>

    <sect3>
Peter Eisentraut's avatar
Peter Eisentraut committed
2573
     <title><literal>CLOSE</></title>
2574 2575 2576 2577 2578

<synopsis>
CLOSE <replaceable>cursor</replaceable>;
</synopsis>

Peter Eisentraut's avatar
Peter Eisentraut committed
2579
       <para>
2580
        <command>CLOSE</command> closes the portal underlying an open
Peter Eisentraut's avatar
Peter Eisentraut committed
2581 2582
        cursor.  This can be used to release resources earlier than end of
        transaction, or to free up the cursor variable to be opened again.
Peter Eisentraut's avatar
Peter Eisentraut committed
2583
       </para>
2584

Peter Eisentraut's avatar
Peter Eisentraut committed
2585 2586
       <para>
        An example:
2587 2588
<programlisting>
CLOSE curs1;
2589 2590 2591
</programlisting>
       </para>
     </sect3>
2592

2593 2594 2595 2596 2597
    <sect3>
     <title>Returning Cursors</title>

       <para>
        <application>PL/pgSQL</> functions can return cursors to the
2598
        caller. This is useful to return multiple rows or columns,
Peter Eisentraut's avatar
Peter Eisentraut committed
2599 2600 2601 2602 2603
        especially with very large result sets.  To do this, the function
        opens the cursor and returns the cursor name to the caller (or simply
        opens the cursor using a portal name specified by or otherwise known
        to the caller).  The caller can then fetch rows from the cursor. The
        cursor can be closed by the caller, or it will be closed automatically
2604
        when the transaction closes.
2605 2606 2607
       </para>

       <para>
2608
        The portal name used for a cursor can be specified by the
Peter Eisentraut's avatar
Peter Eisentraut committed
2609 2610 2611 2612 2613 2614 2615 2616
        programmer or automatically generated.  To specify a portal name,
        simply assign a string to the <type>refcursor</> variable before
        opening it.  The string value of the <type>refcursor</> variable
        will be used by <command>OPEN</> as the name of the underlying portal.
        However, if the <type>refcursor</> variable is null,
        <command>OPEN</> automatically generates a name that does not
        conflict with any existing portal, and assigns it to the
        <type>refcursor</> variable.
2617 2618 2619 2620
       </para>

       <note>
        <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
2621 2622 2623 2624
         A bound cursor variable is initialized to the string value
         representing its name, so that the portal name is the same as
         the cursor variable name, unless the programmer overrides it
         by assignment before opening the cursor.  But an unbound cursor
2625
         variable defaults to the null value initially, so it will receive
Peter Eisentraut's avatar
Peter Eisentraut committed
2626
         an automatically-generated unique name, unless overridden.
2627 2628 2629 2630 2631
        </para>
       </note>

       <para>
        The following example shows one way a cursor name can be supplied by
Peter Eisentraut's avatar
Peter Eisentraut committed
2632
        the caller:
2633 2634 2635 2636 2637 2638 2639

<programlisting>
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
Peter Eisentraut's avatar
Peter Eisentraut committed
2640 2641
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
2642
END;
Peter Eisentraut's avatar
Peter Eisentraut committed
2643
' LANGUAGE plpgsql;
2644 2645 2646 2647 2648 2649 2650 2651 2652 2653

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;
</programlisting>
       </para>

       <para>
        The following example uses automatic cursor name generation:
2654

2655 2656 2657
<programlisting>
CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
Peter Eisentraut's avatar
Peter Eisentraut committed
2658
    ref refcursor;
2659
BEGIN
Peter Eisentraut's avatar
Peter Eisentraut committed
2660 2661
    OPEN ref FOR SELECT col FROM test;
    RETURN ref;
2662
END;
Peter Eisentraut's avatar
Peter Eisentraut committed
2663
' LANGUAGE plpgsql;
2664 2665 2666

BEGIN;
SELECT reffunc2();
2667

2668
      reffunc2
Peter Eisentraut's avatar
Peter Eisentraut committed
2669 2670 2671
--------------------
 &lt;unnamed cursor 1&gt;
(1 row)
2672

2673
FETCH ALL IN "&lt;unnamed cursor 1&gt;";
2674
COMMIT;
2675 2676 2677 2678 2679 2680 2681 2682 2683 2684 2685 2686 2687 2688 2689 2690 2691 2692 2693 2694 2695 2696 2697 2698 2699
</programlisting>
       </para>

       <para>
        The following example shows one way to return multiple cursors
        from a single function:

<programlisting>
CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT * FROM table_1;
    RETURN NEXT $1;
    OPEN $2 FOR SELECT * FROM table_2;
    RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;

-- need to be in a transaction to use cursors.
BEGIN;

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;
2700 2701 2702 2703
</programlisting>
       </para>
     </sect3>
   </sect2>
2704 2705 2706 2707 2708 2709 2710 2711 2712 2713 2714 2715 2716 2717 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 2730 2731 2732 2733

   <sect2 id="plpgsql-cursor-for-loop">
    <title>Looping Through a Cursor's Result</title>

    <para>
     There is a variant of the <command>FOR</> statement that allows
     iterating through the rows returned by a cursor.  The syntax is:

<synopsis>
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
FOR <replaceable>recordvar</replaceable> IN <replaceable>bound_cursor</replaceable> <optional> ( <replaceable>argument_values</replaceable> ) </optional> LOOP
    <replaceable>statements</replaceable>
END LOOP <optional> <replaceable>label</replaceable> </optional>;
</synopsis>

     The cursor variable must have been bound to some query when it was
     declared, and it <emphasis>cannot</> be open already.  The
     <command>FOR</> statement automatically opens the cursor, and it closes
     the cursor again when the loop exits.  A list of actual argument value
     expressions must appear if and only if the cursor was declared to take
     arguments.  These values will be substituted in the query, in just
     the same way as during an <command>OPEN</>.
     The variable <replaceable>recordvar</replaceable> is automatically
     defined as type <type>record</> and exists only inside the loop (any
     existing definition of the variable name is ignored within the loop).
     Each row returned by the cursor is successively assigned to this
     record variable and the loop body is executed.
    </para>
   </sect2>

2734 2735
  </sect1>

2736 2737
  <sect1 id="plpgsql-errors-and-messages">
   <title>Errors and Messages</title>
2738

2739 2740 2741 2742 2743 2744 2745 2746 2747
   <indexterm>
    <primary>RAISE</primary>
   </indexterm>

   <indexterm>
    <primary>reporting errors</primary>
    <secondary>in PL/PgSQL</secondary>
   </indexterm>

2748
   <para>
2749 2750
    Use the <command>RAISE</command> statement to report messages and
    raise errors.
2751

Peter Eisentraut's avatar
Peter Eisentraut committed
2752
<synopsis>
2753
RAISE <replaceable class="parameter">level</replaceable> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ...</optional></optional>;
Peter Eisentraut's avatar
Peter Eisentraut committed
2754 2755
</synopsis>

2756
    Possible levels are <literal>DEBUG</literal>,
Tom Lane's avatar
Tom Lane committed
2757
    <literal>LOG</literal>, <literal>INFO</literal>,
2758 2759
    <literal>NOTICE</literal>, <literal>WARNING</literal>,
    and <literal>EXCEPTION</literal>.
Tom Lane's avatar
Tom Lane committed
2760 2761
    <literal>EXCEPTION</literal> raises an error (which normally aborts the
    current transaction); the other levels only generate messages of different
2762 2763
    priority levels.
    Whether messages of a particular priority are reported to the client,
2764
    written to the server log, or both is controlled by the
2765 2766
    <xref linkend="guc-log-min-messages"> and
    <xref linkend="guc-client-min-messages"> configuration
2767 2768
    variables. See <xref linkend="runtime-config"> for more
    information.
2769 2770 2771
   </para>

   <para>
2772
    Inside the format string, <literal>%</literal> is replaced by the
Peter Eisentraut's avatar
Peter Eisentraut committed
2773
    next optional argument's string representation. Write
2774 2775
    <literal>%%</literal> to emit a literal <literal>%</literal>.
    Arguments can be simple variables or expressions,
2776
    but the format must be a simple string literal.
2777
   </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
2778

2779 2780
   <!--
   This example should work, but does not:
2781
        RAISE NOTICE 'Id number ' || key || ' not found!';
2782 2783 2784
   Put it back when we allow non-string-literal formats.
    -->

2785
   <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
2786
    In this example, the value of <literal>v_job_id</> will replace the
2787
    <literal>%</literal> in the string:
Peter Eisentraut's avatar
Peter Eisentraut committed
2788
<programlisting>
2789
RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
Peter Eisentraut's avatar
Peter Eisentraut committed
2790
</programlisting>
2791
   </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
2792

2793
   <para>
2794
    This example will abort the transaction with the given error message:
Peter Eisentraut's avatar
Peter Eisentraut committed
2795
<programlisting>
2796
RAISE EXCEPTION 'Nonexistent ID --> %', user_id;
Peter Eisentraut's avatar
Peter Eisentraut committed
2797
</programlisting>
2798
   </para>
2799 2800

    <para>
Tom Lane's avatar
Tom Lane committed
2801
     <command>RAISE EXCEPTION</command> presently always generates
Peter Eisentraut's avatar
Peter Eisentraut committed
2802
     the same <varname>SQLSTATE</varname> code, <literal>P0001</>, no matter what message
Tom Lane's avatar
Tom Lane committed
2803 2804 2805
     it is invoked with.  It is possible to trap this exception with
     <literal>EXCEPTION ... WHEN RAISE_EXCEPTION THEN ...</> but there
     is no way to tell one <command>RAISE</> from another.
2806
    </para>
2807
 </sect1>
2808

2809
 <sect1 id="plpgsql-trigger">
Peter Eisentraut's avatar
Peter Eisentraut committed
2810
  <title>Trigger Procedures</title>
2811

Peter Eisentraut's avatar
Peter Eisentraut committed
2812 2813 2814 2815 2816
  <indexterm zone="plpgsql-trigger">
   <primary>trigger</primary>
   <secondary>in PL/pgSQL</secondary>
  </indexterm>

Peter Eisentraut's avatar
Peter Eisentraut committed
2817
  <para>
2818 2819 2820 2821 2822 2823 2824 2825
    <application>PL/pgSQL</application> can be used to define trigger
    procedures. A trigger procedure is created with the
    <command>CREATE FUNCTION</> command, declaring it as a function with
    no arguments and a return type of <type>trigger</type>.  Note that
    the function must be declared with no arguments even if it expects
    to receive arguments specified in <command>CREATE TRIGGER</> &mdash;
    trigger arguments are passed via <varname>TG_ARGV</>, as described
    below.
Peter Eisentraut's avatar
Peter Eisentraut committed
2826 2827 2828
  </para>

  <para>
2829
   When a <application>PL/pgSQL</application> function is called as a
2830
   trigger, several special variables are created automatically in the
2831
   top-level block. They are:
2832

2833 2834 2835 2836 2837
   <variablelist>
    <varlistentry>
     <term><varname>NEW</varname></term>
     <listitem>
      <para>
2838
       Data type <type>RECORD</type>; variable holding the new
Peter Eisentraut's avatar
Peter Eisentraut committed
2839
       database row for <command>INSERT</>/<command>UPDATE</> operations in row-level
2840
       triggers. This variable is <symbol>NULL</symbol> in statement-level triggers.
2841 2842 2843
      </para>
     </listitem>
    </varlistentry>
2844

2845 2846 2847 2848
    <varlistentry>
     <term><varname>OLD</varname></term>
     <listitem>
      <para>
2849
       Data type <type>RECORD</type>; variable holding the old
Peter Eisentraut's avatar
Peter Eisentraut committed
2850
       database row for <command>UPDATE</>/<command>DELETE</> operations in row-level
2851
       triggers. This variable is <symbol>NULL</symbol> in statement-level triggers.
2852 2853 2854
      </para>
     </listitem>
    </varlistentry>
2855

2856 2857 2858 2859 2860 2861 2862 2863 2864
    <varlistentry>
     <term><varname>TG_NAME</varname></term>
     <listitem>
      <para>
       Data type <type>name</type>; variable that contains the name of the trigger actually
       fired.
      </para>
     </listitem>
    </varlistentry>
2865

2866 2867 2868 2869
    <varlistentry>
     <term><varname>TG_WHEN</varname></term>
     <listitem>
      <para>
2870
       Data type <type>text</type>; a string of either
2871 2872
       <literal>BEFORE</literal> or <literal>AFTER</literal>
       depending on the trigger's definition.
2873 2874 2875
      </para>
     </listitem>
    </varlistentry>
2876

2877 2878 2879 2880
    <varlistentry>
     <term><varname>TG_LEVEL</varname></term>
     <listitem>
      <para>
2881 2882 2883
       Data type <type>text</type>; a string of either
       <literal>ROW</literal> or <literal>STATEMENT</literal>
       depending on the trigger's definition.
2884 2885 2886
      </para>
     </listitem>
    </varlistentry>
2887

2888 2889 2890 2891
    <varlistentry>
     <term><varname>TG_OP</varname></term>
     <listitem>
      <para>
2892
       Data type <type>text</type>; a string of
2893 2894 2895
       <literal>INSERT</literal>, <literal>UPDATE</literal>,
       <literal>DELETE</literal>, or <literal>TRUNCATE</>
       telling for which operation the trigger was fired.
2896 2897 2898
      </para>
     </listitem>
    </varlistentry>
2899

2900 2901 2902 2903 2904 2905 2906 2907 2908
    <varlistentry>
     <term><varname>TG_RELID</varname></term>
     <listitem>
      <para>
       Data type <type>oid</type>; the object ID of the table that caused the
       trigger invocation.
      </para>
     </listitem>
    </varlistentry>
2909

2910 2911 2912 2913 2914
    <varlistentry>
     <term><varname>TG_RELNAME</varname></term>
     <listitem>
      <para>
       Data type <type>name</type>; the name of the table that caused the trigger
2915
       invocation. This is now deprecated, and could disappear in a future
2916
       release. Use <literal>TG_TABLE_NAME</> instead.
2917 2918 2919 2920 2921 2922 2923 2924
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><varname>TG_TABLE_NAME</varname></term>
     <listitem>
      <para>
2925
       Data type <type>name</type>; the name of the table that
2926
       caused the trigger invocation.
2927 2928 2929 2930 2931 2932 2933 2934
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><varname>TG_TABLE_SCHEMA</varname></term>
     <listitem>
      <para>
2935
       Data type <type>name</type>; the name of the schema of the
2936
       table that caused the trigger invocation.
2937 2938 2939
      </para>
     </listitem>
    </varlistentry>
2940

2941 2942 2943 2944 2945 2946 2947 2948 2949
    <varlistentry>
     <term><varname>TG_NARGS</varname></term>
     <listitem>
      <para>
       Data type <type>integer</type>; the number of arguments given to the trigger
       procedure in the <command>CREATE TRIGGER</command> statement.
      </para>
     </listitem>
    </varlistentry>
2950

2951 2952 2953 2954 2955 2956
    <varlistentry>
     <term><varname>TG_ARGV[]</varname></term>
     <listitem>
      <para>
       Data type array of <type>text</type>; the arguments from
              the <command>CREATE TRIGGER</command> statement.
Peter Eisentraut's avatar
Peter Eisentraut committed
2957 2958
       The index counts from 0. Invalid
       indices (less than 0 or greater than or equal to <varname>tg_nargs</>) result in a null value.
2959 2960 2961 2962
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
Peter Eisentraut's avatar
Peter Eisentraut committed
2963
  </para>
2964 2965

   <para>
2966 2967 2968
    A trigger function must return either <symbol>NULL</symbol> or a
    record/row value having exactly the structure of the table the
    trigger was fired for.
2969
   </para>
2970

2971
   <para>
2972
    Row-level triggers fired <literal>BEFORE</> can return null to signal the
2973
    trigger manager to skip the rest of the operation for this row
Peter Eisentraut's avatar
Peter Eisentraut committed
2974
    (i.e., subsequent triggers are not fired, and the
2975
    <command>INSERT</>/<command>UPDATE</>/<command>DELETE</> does not occur
2976
    for this row).  If a nonnull
2977
    value is returned then the operation proceeds with that row value.
Peter Eisentraut's avatar
Peter Eisentraut committed
2978
    Returning a row value different from the original value
2979 2980 2981 2982
    of <varname>NEW</> alters the row that will be inserted or updated
    (but has no direct effect in the <command>DELETE</> case).
    To alter the row to be stored, it is possible to replace single values
    directly in <varname>NEW</> and return the modified <varname>NEW</>,
2983
    or to build a complete new record/row to return.
2984 2985
   </para>

2986 2987 2988
   <para>
    The return value of a <literal>BEFORE</> or <literal>AFTER</>
    statement-level trigger or an <literal>AFTER</> row-level trigger is
2989 2990
    always ignored; it might as well be null. However, any of these types of
    triggers might still abort the entire operation by raising an error.
2991 2992
   </para>

Peter Eisentraut's avatar
Peter Eisentraut committed
2993 2994 2995 2996 2997 2998 2999
   <para>
    <xref linkend="plpgsql-trigger-example"> shows an example of a
    trigger procedure in <application>PL/pgSQL</application>.
   </para>

   <example id="plpgsql-trigger-example">
    <title>A <application>PL/pgSQL</application> Trigger Procedure</title>
3000 3001

    <para>
3002
     This example trigger ensures that any time a row is inserted or updated
3003
     in the table, the current user name and time are stamped into the
3004
     row. And it checks that an employee's name is given and that the
3005
     salary is a positive value.
Peter Eisentraut's avatar
Peter Eisentraut committed
3006
    </para>
3007

Peter Eisentraut's avatar
Peter Eisentraut committed
3008
<programlisting>
3009 3010
CREATE TABLE emp (
    empname text,
3011 3012
    salary integer,
    last_date timestamp,
Peter Eisentraut's avatar
Peter Eisentraut committed
3013 3014
    last_user text
);
3015

3016
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
3017 3018
    BEGIN
        -- Check that empname and salary are given
Peter Eisentraut's avatar
Peter Eisentraut committed
3019
        IF NEW.empname IS NULL THEN
3020
            RAISE EXCEPTION 'empname cannot be null';
3021
        END IF;
Peter Eisentraut's avatar
Peter Eisentraut committed
3022
        IF NEW.salary IS NULL THEN
3023
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
3024 3025
        END IF;

3026
        -- Who works for us when she must pay for it?
Peter Eisentraut's avatar
Peter Eisentraut committed
3027
        IF NEW.salary &lt; 0 THEN
3028
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
3029 3030 3031
        END IF;

        -- Remember who changed the payroll when
3032
        NEW.last_date := current_timestamp;
3033
        NEW.last_user := current_user;
3034 3035
        RETURN NEW;
    END;
3036
$emp_stamp$ LANGUAGE plpgsql;
3037 3038 3039

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
Peter Eisentraut's avatar
Peter Eisentraut committed
3040
</programlisting>
3041 3042 3043 3044
   </example>

   <para>
    Another way to log changes to a table involves creating a new table that
3045 3046
    holds a row for each insert, update, or delete that occurs. This approach
    can be thought of as auditing changes to a table.
3047 3048 3049 3050 3051 3052 3053 3054 3055
    <xref linkend="plpgsql-trigger-audit-example"> shows an example of an
    audit trigger procedure in <application>PL/pgSQL</application>.
   </para>

   <example id="plpgsql-trigger-audit-example">
    <title>A <application>PL/pgSQL</application> Trigger Procedure For Auditing</title>

    <para>
     This example trigger ensures that any insert, update or delete of a row
3056 3057
     in the <literal>emp</literal> table is recorded (i.e., audited) in the <literal>emp_audit</literal> table.
     The current time and user name are stamped into the row, together with
3058 3059 3060 3061 3062 3063 3064 3065 3066
     the type of operation performed on it.
    </para>

<programlisting>
CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);

3067
CREATE TABLE emp_audit(
3068 3069 3070 3071 3072 3073 3074
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary integer
);

3075
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
3076 3077 3078 3079 3080 3081 3082 3083 3084 3085 3086 3087 3088 3089 3090
    BEGIN
        --
        -- Create a row in emp_audit to reflect the operation performed on emp,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
            RETURN NEW;
        END IF;
3091
        RETURN NULL; -- result is ignored since this is an AFTER trigger
3092
    END;
Neil Conway's avatar
Neil Conway committed
3093
$emp_audit$ LANGUAGE plpgsql;
3094 3095 3096

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
3097
    FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
3098
</programlisting>
Peter Eisentraut's avatar
Peter Eisentraut committed
3099
   </example>
3100 3101 3102

   <para>
    One use of triggers is to maintain a summary table
3103 3104
    of another table. The resulting summary can be used in place of the
    original table for certain queries &mdash; often with vastly reduced run
3105 3106
    times.
    This technique is commonly used in Data Warehousing, where the tables
3107
    of measured or observed data (called fact tables) might be extremely large.
3108 3109 3110 3111 3112 3113 3114 3115 3116 3117 3118
    <xref linkend="plpgsql-trigger-summary-example"> shows an example of a
    trigger procedure in <application>PL/pgSQL</application> that maintains
    a summary table for a fact table in a data warehouse.
   </para>


   <example id="plpgsql-trigger-summary-example">
    <title>A <application>PL/pgSQL</application> Trigger Procedure For Maintaining A Summary Table</title>

    <para>
     The schema detailed here is partly based on the <emphasis>Grocery Store
3119
     </emphasis> example from <emphasis>The Data Warehouse Toolkit</emphasis>
3120 3121 3122 3123 3124 3125 3126 3127 3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 3138 3139 3140 3141 3142 3143 3144 3145 3146 3147 3148 3149 3150 3151 3152 3153 3154 3155 3156 3157 3158 3159 3160 3161 3162 3163 3164 3165 3166 3167 3168 3169 3170 3171 3172 3173 3174 3175 3176 3177 3178 3179
     by Ralph Kimball.
    </para>

<programlisting>
--
-- Main tables - time dimension and sales fact.
--
CREATE TABLE time_dimension (
    time_key                    integer NOT NULL,
    day_of_week                 integer NOT NULL,
    day_of_month                integer NOT NULL,
    month                       integer NOT NULL,
    quarter                     integer NOT NULL,
    year                        integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);

CREATE TABLE sales_fact (
    time_key                    integer NOT NULL,
    product_key                 integer NOT NULL,
    store_key                   integer NOT NULL,
    amount_sold                 numeric(12,2) NOT NULL,
    units_sold                  integer NOT NULL,
    amount_cost                 numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);

--
-- Summary table - sales by time.
--
CREATE TABLE sales_summary_bytime (
    time_key                    integer NOT NULL,
    amount_sold                 numeric(15,2) NOT NULL,
    units_sold                  numeric(12) NOT NULL,
    amount_cost                 numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);

--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $maint_sales_summary_bytime$
    DECLARE
        delta_time_key          integer;
        delta_amount_sold       numeric(15,2);
        delta_units_sold        numeric(12);
        delta_amount_cost       numeric(15,2);
    BEGIN

        -- Work out the increment/decrement amount(s).
        IF (TG_OP = 'DELETE') THEN

            delta_time_key = OLD.time_key;
            delta_amount_sold = -1 * OLD.amount_sold;
            delta_units_sold = -1 * OLD.units_sold;
            delta_amount_cost = -1 * OLD.amount_cost;

        ELSIF (TG_OP = 'UPDATE') THEN

            -- forbid updates that change the time_key -
3180
            -- (probably not too onerous, as DELETE + INSERT is how most
3181 3182 3183 3184 3185 3186 3187 3188 3189 3190 3191 3192 3193 3194 3195 3196 3197 3198 3199 3200
            -- changes will be made).
            IF ( OLD.time_key != NEW.time_key) THEN
                RAISE EXCEPTION 'Update of time_key : % -&gt; % not allowed', OLD.time_key, NEW.time_key;
            END IF;

            delta_time_key = OLD.time_key;
            delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
            delta_units_sold = NEW.units_sold - OLD.units_sold;
            delta_amount_cost = NEW.amount_cost - OLD.amount_cost;

        ELSIF (TG_OP = 'INSERT') THEN

            delta_time_key = NEW.time_key;
            delta_amount_sold = NEW.amount_sold;
            delta_units_sold = NEW.units_sold;
            delta_amount_cost = NEW.amount_cost;

        END IF;


3201 3202 3203 3204 3205 3206 3207 3208
        -- Insert or update the summary row with the new values.
        &lt;&lt;insert_update&gt;&gt;
        LOOP
            UPDATE sales_summary_bytime
                SET amount_sold = amount_sold + delta_amount_sold,
                    units_sold = units_sold + delta_units_sold,
                    amount_cost = amount_cost + delta_amount_cost
                WHERE time_key = delta_time_key;
3209

3210
            EXIT insert_update WHEN found;
3211 3212 3213

            BEGIN
                INSERT INTO sales_summary_bytime (
3214 3215 3216
                            time_key,
                            amount_sold,
                            units_sold,
3217
                            amount_cost)
3218
                    VALUES (
3219 3220 3221 3222 3223
                            delta_time_key,
                            delta_amount_sold,
                            delta_units_sold,
                            delta_amount_cost
                           );
3224 3225 3226

                EXIT insert_update;

3227 3228
            EXCEPTION
                WHEN UNIQUE_VIOLATION THEN
3229
                    -- do nothing
3230
            END;
3231 3232
        END LOOP insert_update;

3233 3234 3235 3236 3237 3238 3239 3240
        RETURN NULL;

    END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;

CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
    FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();
3241 3242 3243 3244 3245 3246 3247 3248 3249 3250

INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;
3251 3252 3253
</programlisting>
   </example>

3254
  </sect1>
3255

3256 3257 3258 3259 3260 3261 3262 3263 3264 3265 3266 3267 3268 3269 3270 3271 3272 3273 3274 3275 3276 3277 3278 3279 3280 3281 3282 3283 3284 3285 3286 3287 3288 3289 3290 3291 3292 3293 3294 3295 3296 3297 3298 3299 3300 3301 3302 3303 3304 3305 3306 3307 3308 3309 3310 3311 3312 3313 3314 3315 3316 3317 3318 3319 3320 3321 3322 3323 3324 3325 3326 3327 3328 3329 3330 3331 3332 3333 3334 3335 3336 3337 3338 3339 3340 3341 3342 3343 3344 3345 3346 3347 3348 3349 3350 3351 3352 3353 3354 3355 3356 3357 3358 3359 3360 3361 3362 3363 3364 3365 3366 3367 3368 3369 3370 3371 3372 3373 3374 3375 3376 3377 3378 3379 3380 3381 3382 3383 3384 3385 3386 3387 3388 3389 3390 3391 3392 3393 3394 3395 3396 3397 3398 3399 3400 3401 3402 3403 3404 3405 3406 3407 3408 3409 3410 3411 3412 3413 3414
  <sect1 id="plpgsql-implementation">
   <title><application>PL/pgSQL</> Under the Hood</title>

   <para>
    This section discusses some implementation details that are
    frequently important for <application>PL/pgSQL</> users to know.
   </para>

  <sect2 id="plpgsql-var-subst">
   <title>Variable Substitution</title>

   <para>
    When <application>PL/pgSQL</> prepares a SQL statement or expression
    for execution, any <application>PL/pgSQL</application> variable name
    appearing in the statement or expression is replaced by a parameter symbol,
    <literal>$<replaceable>n</replaceable></literal>.  The current value
    of the variable is then provided as the value for the parameter whenever
    the statement or expression is executed.  As an example, consider the
    function
<programlisting>
CREATE FUNCTION logfunc(logtxt text) RETURNS void AS $$
    DECLARE
        curtime timestamp := now();
    BEGIN
        INSERT INTO logtable VALUES (logtxt, curtime);
    END;
$$ LANGUAGE plpgsql;
</programlisting>
    The <command>INSERT</> statement will effectively be processed as
<programlisting>
PREPARE <replaceable>statement_name</>(text, timestamp) AS
  INSERT INTO logtable VALUES ($1, $2);
</programlisting>
    followed on each execution by <command>EXECUTE</> with the current
    actual values of the two variables.  (Note: here we are speaking of
    the main SQL engine's
    <xref linkend="sql-execute" endterm="sql-execute-title"> command,
    not <application>PL/pgSQL</application>'s <command>EXECUTE</>.)
   </para>

   <para>
    <emphasis>The substitution mechanism will replace any token that matches a
    known variable's name.</>  This poses various traps for the unwary.
    For example, it is a bad idea
    to use a variable name that is the same as any table or column name
    that you need to reference in queries within the function, because
    what you think is a table or column name will still get replaced.
    In the above example, suppose that <structname>logtable</> has
    column names <structfield>logtxt</> and <structfield>logtime</>,
    and we try to write the <command>INSERT</> as
<programlisting>
        INSERT INTO logtable (logtxt, logtime) VALUES (logtxt, curtime);
</programlisting>
    This will be fed to the main SQL parser as
<programlisting>
        INSERT INTO logtable ($1, logtime) VALUES ($1, $2);
</programlisting>
    resulting in a syntax error like this:
<screen>
ERROR:  syntax error at or near "$1"
LINE 1: INSERT INTO logtable ( $1 , logtime) VALUES ( $1 ,  $2 )
                               ^
QUERY:  INSERT INTO logtable ( $1 , logtime) VALUES ( $1 ,  $2 )
CONTEXT:  SQL statement in PL/PgSQL function "logfunc2" near line 5
</screen>
   </para>

   <para>
    This example is fairly easy to diagnose, since it leads to an
    obvious syntax error.  Much nastier are cases where the substitution
    is syntactically permissible, since the only symptom may be misbehavior
    of the function.  In one case, a user wrote something like this:
<programlisting>
    DECLARE
        val text;
        search_key integer;
    BEGIN
        ...
        FOR val IN SELECT val FROM table WHERE key = search_key LOOP ...
</programlisting>
    and wondered why all his table entries seemed to be NULL.  Of course
    what happened here was that the query became
<programlisting>
        SELECT $1 FROM table WHERE key = $2
</programlisting>
    and thus it was just an expensive way of assigning <literal>val</>'s
    current value back to itself for each row.
   </para>

   <para>
    A commonly used coding rule for avoiding such traps is to use a
    different naming convention for <application>PL/pgSQL</application>
    variables than you use for table and column names.  For example,
    if all your variables are named
    <literal>v_<replaceable>something</></literal> while none of your
    table or column names start with <literal>v_</>, you're pretty safe.
   </para>

   <para>
    Another workaround is to use qualified (dotted) names for SQL entities.
    For instance we could safely have written the above example as
<programlisting>
        FOR val IN SELECT table.val FROM table WHERE key = search_key LOOP ...
</programlisting>
    because <application>PL/pgSQL</application> will not substitute a
    variable for a trailing component of a qualified name.
    However this solution does not work in every case &mdash; you can't
    qualify a name in an <command>INSERT</>'s column name list, for instance.
    Another point is that record and row variable names will be matched to
    the first components of qualified names, so a qualified SQL name is
    still vulnerable in some cases.
    In such cases choosing a non-conflicting variable name is the only way.
   </para>

   <para>
    Another technique you can use is to attach a label to the block in
    which your variables are declared, and then qualify the variable names
    in your SQL commands (see <xref linkend="plpgsql-structure">).
    For example,
<programlisting>
    &lt;&lt;pl&gt;&gt;
    DECLARE
        val text;
    BEGIN
        ...
        UPDATE table SET col = pl.val WHERE ...
</programlisting>
    This is not in itself a solution to the problem of conflicts,
    since an unqualified name in a SQL command is still at risk of being
    interpreted the <quote>wrong</> way.  But it is useful for clarifying
    the intent of potentially-ambiguous code.
   </para>

   <para>
    Variable substitution does not happen in the command string given
    to <command>EXECUTE</> or one of its variants.  If you need to
    insert a varying value into such a command, do so as part of
    constructing the string value, as illustrated in
    <xref linkend="plpgsql-statements-executing-dyn">.
   </para>

   <para>
    Variable substitution currently works only in <command>SELECT</>,
    <command>INSERT</>, <command>UPDATE</>, and <command>DELETE</> commands,
    because the main SQL engine allows parameter symbols only in these
    commands.  To use a non-constant name or value in other statement
    types (generically called utility statements), you must construct
    the utility statement as a string and <command>EXECUTE</> it.
   </para>

  </sect2>

  <sect2 id="plpgsql-plan-caching">
   <title>Plan Caching</title>

   <para>
    The <application>PL/pgSQL</> interpreter parses the function's source
    text and produces an internal binary instruction tree the first time the
    function is called (within each session).  The instruction tree
3415
    fully translates the
3416 3417 3418 3419 3420 3421 3422 3423 3424 3425 3426 3427 3428 3429 3430 3431 3432 3433 3434 3435 3436 3437 3438 3439 3440 3441 3442 3443 3444 3445 3446 3447 3448 3449 3450 3451 3452 3453 3454 3455 3456 3457 3458 3459 3460 3461 3462 3463 3464 3465 3466 3467
    <application>PL/pgSQL</> statement structure, but individual
    <acronym>SQL</acronym> expressions and <acronym>SQL</acronym> commands
    used in the function are not translated immediately.
   </para>

   <para>
    As each expression and <acronym>SQL</acronym> command is first
    executed in the function, the <application>PL/pgSQL</> interpreter
    creates a prepared execution plan (using the
    <acronym>SPI</acronym> manager's <function>SPI_prepare</function>
    and <function>SPI_saveplan</function>
    functions).<indexterm><primary>preparing a query</><secondary>in
    PL/pgSQL</></> Subsequent visits to that expression or command
    reuse the prepared plan.  Thus, a function with conditional code
    that contains many statements for which execution plans might be
    required will only prepare and save those plans that are really
    used during the lifetime of the database connection.  This can
    substantially reduce the total amount of time required to parse
    and generate execution plans for the statements in a
    <application>PL/pgSQL</> function. A disadvantage is that errors
    in a specific expression or command cannot be detected until that
    part of the function is reached in execution.  (Trivial syntax
    errors will be detected during the initial parsing pass, but
    anything deeper will not be detected until execution.)
   </para>

   <para>
    Once <application>PL/pgSQL</> has made an execution plan for a particular
    command in a function, it will reuse that plan for the life of the
    database connection.  This is usually a win for performance, but it
    can cause some problems if you dynamically
    alter your database schema. For example:

<programlisting>
CREATE FUNCTION populate() RETURNS integer AS $$
DECLARE
    -- declarations
BEGIN
    PERFORM my_function();
END;
$$ LANGUAGE plpgsql;
</programlisting>

    If you execute the above function, it will reference the OID for
    <function>my_function()</function> in the execution plan produced for
    the <command>PERFORM</command> statement. Later, if you
    drop and recreate <function>my_function()</function>, then
    <function>populate()</function> will not be able to find
    <function>my_function()</function> anymore. You would then have to
    start a new database session so that <function>populate()</function>
    will be compiled afresh, before it will work again. You can avoid
    this problem by using <command>CREATE OR REPLACE FUNCTION</command>
3468
    when updating the definition of
3469 3470 3471 3472 3473 3474 3475 3476 3477 3478 3479 3480 3481 3482 3483 3484 3485 3486 3487 3488 3489 3490 3491 3492 3493 3494 3495 3496 3497 3498 3499 3500 3501 3502 3503 3504 3505 3506 3507
    <function>my_function</function>, since when a function is
    <quote>replaced</quote>, its OID is not changed.
   </para>

   <note>
    <para>
     In <productname>PostgreSQL</productname> 8.3 and later, saved plans
     will be replaced whenever any schema changes have occurred to any
     tables they reference.  This eliminates one of the major disadvantages
     of saved plans.  However, there is no such mechanism for function
     references, and thus the above example involving a reference to a
     deleted function is still valid.
    </para>
   </note>

   <para>
    Because <application>PL/pgSQL</application> saves execution plans
    in this way, SQL commands that appear directly in a
    <application>PL/pgSQL</application> function must refer to the
    same tables and columns on every execution; that is, you cannot use
    a parameter as the name of a table or column in an SQL command.  To get
    around this restriction, you can construct dynamic commands using
    the <application>PL/pgSQL</application> <command>EXECUTE</command>
    statement &mdash; at the price of constructing a new execution plan on
    every execution.
   </para>

   <para>
    Another important point is that the prepared plans are parameterized
    to allow the values of <application>PL/pgSQL</application> variables
    to change from one use to the next, as discussed in detail above.
    Sometimes this means that a plan is less efficient than it would be
    if generated for a specific variable value.  As an example, consider
<programlisting>
SELECT * INTO myrec FROM dictionary WHERE word LIKE search_term;
</programlisting>
    where <literal>search_term</> is a <application>PL/pgSQL</application>
    variable.  The cached plan for this query will never use an index on
    <structfield>word</>, since the planner cannot assume that the
Peter Eisentraut's avatar
Peter Eisentraut committed
3508
    <literal>LIKE</> pattern will be left-anchored at run time.  To use
3509 3510 3511 3512 3513 3514 3515 3516 3517 3518 3519 3520 3521 3522 3523 3524 3525 3526 3527 3528 3529 3530 3531 3532 3533 3534 3535 3536 3537 3538 3539 3540 3541 3542 3543 3544 3545 3546 3547 3548 3549 3550 3551 3552 3553 3554 3555 3556 3557 3558 3559 3560 3561 3562 3563 3564 3565 3566 3567 3568 3569 3570 3571 3572 3573 3574 3575 3576 3577 3578 3579 3580 3581 3582 3583 3584
    an index the query must be planned with a specific constant
    <literal>LIKE</> pattern provided.  This is another situation where
    <command>EXECUTE</command> can be used to force a new plan to be
    generated for each execution.
   </para>

    <para>
     The mutable nature of record variables presents another problem in this
     connection.  When fields of a record variable are used in
     expressions or statements, the data types of the fields must not
     change from one call of the function to the next, since each
     expression will be planned using the data type that is present
     when the expression is first reached.  <command>EXECUTE</command> can be
     used to get around this problem when necessary.
    </para>

    <para>
     If the same function is used as a trigger for more than one table,
     <application>PL/pgSQL</application> prepares and caches plans
     independently for each such table &mdash; that is, there is a cache
     for each trigger function and table combination, not just for each
     function.  This alleviates some of the problems with varying
     data types; for instance, a trigger function will be able to work
     successfully with a column named <literal>key</> even if it happens
     to have different types in different tables.
    </para>

    <para>
     Likewise, functions having polymorphic argument types have a separate
     plan cache for each combination of actual argument types they have been
     invoked for, so that data type differences do not cause unexpected
     failures.
    </para>

   <para>
    Plan caching can sometimes have surprising effects on the interpretation
    of time-sensitive values.  For example there
    is a difference between what these two functions do:

<programlisting>
CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
    BEGIN
        INSERT INTO logtable VALUES (logtxt, 'now');
    END;
$$ LANGUAGE plpgsql;
</programlisting>

     and:

<programlisting>
CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
    DECLARE
        curtime timestamp;
    BEGIN
        curtime := 'now';
        INSERT INTO logtable VALUES (logtxt, curtime);
    END;
$$ LANGUAGE plpgsql;
</programlisting>
    </para>

    <para>
     In the case of <function>logfunc1</function>, the
     <productname>PostgreSQL</productname> main parser knows when
     preparing the plan for the <command>INSERT</command> that the
     string <literal>'now'</literal> should be interpreted as
     <type>timestamp</type>, because the target column of
     <classname>logtable</classname> is of that type. Thus,
     <literal>'now'</literal> will be converted to a constant when the
     <command>INSERT</command> is planned, and then used in all
     invocations of <function>logfunc1</function> during the lifetime
     of the session. Needless to say, this isn't what the programmer
     wanted.
    </para>

    <para>
3585
     In the case of <function>logfunc2</function>, the
3586
     <productname>PostgreSQL</productname> main parser does not know
3587 3588
     what type <literal>'now'</literal> should become and therefore
     it returns a data value of type <type>text</type> containing the string
3589 3590 3591 3592 3593 3594 3595 3596 3597 3598 3599 3600 3601 3602 3603 3604 3605 3606 3607 3608 3609 3610 3611 3612 3613 3614 3615 3616 3617 3618 3619 3620 3621 3622 3623 3624 3625 3626 3627 3628 3629 3630 3631 3632 3633
     <literal>now</literal>. During the ensuing assignment
     to the local variable <varname>curtime</varname>, the
     <application>PL/pgSQL</application> interpreter casts this
     string to the <type>timestamp</type> type by calling the
     <function>text_out</function> and <function>timestamp_in</function>
     functions for the conversion.  So, the computed time stamp is updated
     on each execution as the programmer expects.
    </para>

  </sect2>

  </sect1>

 <sect1 id="plpgsql-development-tips">
  <title>Tips for Developing in <application>PL/pgSQL</application></title>

   <para>
    One good way to develop in
    <application>PL/pgSQL</> is to use the text editor of your
    choice to create your functions, and in another window, use
    <application>psql</application> to load and test those functions.
    If you are doing it this way, it
    is a good idea to write the function using <command>CREATE OR
    REPLACE FUNCTION</>. That way you can just reload the file to update
    the function definition.  For example:
<programlisting>
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
          ....
$$ LANGUAGE plpgsql;
</programlisting>
   </para>

   <para>
    While running <application>psql</application>, you can load or reload such
    a function definition file with:
<programlisting>
\i filename.sql
</programlisting>
    and then immediately issue SQL commands to test the function.
   </para>

   <para>
    Another good way to develop in <application>PL/pgSQL</> is with a
    GUI database access tool that facilitates development in a
    procedural language. One example of such as a tool is
3634
    <application>pgAdmin</>, although others exist. These tools often
3635 3636 3637 3638 3639 3640 3641 3642 3643 3644 3645 3646 3647 3648 3649 3650 3651 3652 3653 3654 3655 3656 3657 3658 3659 3660 3661 3662 3663 3664 3665 3666 3667 3668 3669 3670 3671 3672 3673 3674 3675 3676 3677 3678 3679 3680 3681 3682 3683 3684 3685 3686 3687 3688 3689 3690 3691 3692 3693 3694 3695 3696 3697 3698 3699 3700 3701 3702 3703 3704 3705 3706 3707 3708 3709 3710 3711 3712 3713 3714 3715 3716 3717 3718 3719 3720 3721 3722 3723 3724 3725 3726 3727 3728 3729 3730 3731 3732 3733 3734 3735 3736 3737 3738 3739 3740 3741 3742 3743 3744 3745 3746 3747 3748 3749 3750 3751 3752 3753 3754 3755 3756 3757 3758 3759 3760 3761 3762 3763 3764 3765 3766 3767
    provide convenient features such as escaping single quotes and
    making it easier to recreate and debug functions.
   </para>

  <sect2 id="plpgsql-quote-tips">
   <title>Handling of Quotation Marks</title>

   <para>
    The code of a <application>PL/pgSQL</> function is specified in
    <command>CREATE FUNCTION</command> as a string literal.  If you
    write the string literal in the ordinary way with surrounding
    single quotes, then any single quotes inside the function body
    must be doubled; likewise any backslashes must be doubled (assuming
    escape string syntax is used).
    Doubling quotes is at best tedious, and in more complicated cases
    the code can become downright incomprehensible, because you can
    easily find yourself needing half a dozen or more adjacent quote marks.
    It's recommended that you instead write the function body as a
    <quote>dollar-quoted</> string literal (see <xref
    linkend="sql-syntax-dollar-quoting">).  In the dollar-quoting
    approach, you never double any quote marks, but instead take care to
    choose a different dollar-quoting delimiter for each level of
    nesting you need.  For example, you might write the <command>CREATE
    FUNCTION</command> command as:
<programlisting>
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
          ....
$PROC$ LANGUAGE plpgsql;
</programlisting>
    Within this, you might use quote marks for simple literal strings in
    SQL commands and <literal>$$</> to delimit fragments of SQL commands
    that you are assembling as strings.  If you need to quote text that
    includes <literal>$$</>, you could use <literal>$Q$</>, and so on.
   </para>

   <para>
    The following chart shows what you have to do when writing quote
    marks without dollar quoting.  It might be useful when translating
    pre-dollar quoting code into something more comprehensible.
  </para>

  <variablelist>
   <varlistentry>
    <term>1 quotation mark</term>
    <listitem>
     <para>
      To begin and end the function body, for example:
<programlisting>
CREATE FUNCTION foo() RETURNS integer AS '
          ....
' LANGUAGE plpgsql;
</programlisting>
      Anywhere within a single-quoted function body, quote marks
      <emphasis>must</> appear in pairs.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>2 quotation marks</term>
    <listitem>
     <para>
      For string literals inside the function body, for example:
<programlisting>
a_output := ''Blah'';
SELECT * FROM users WHERE f_name=''foobar'';
</programlisting>
      In the dollar-quoting approach, you'd just write:
<programlisting>
a_output := 'Blah';
SELECT * FROM users WHERE f_name='foobar';
</programlisting>
      which is exactly what the <application>PL/pgSQL</> parser would see
      in either case.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>4 quotation marks</term>
    <listitem>
     <para>
      When you need a single quotation mark in a string constant inside the
      function body, for example:
<programlisting>
a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''
</programlisting>
      The value actually appended to <literal>a_output</literal> would be:
      <literal> AND name LIKE 'foobar' AND xyz</literal>.
     </para>
     <para>
      In the dollar-quoting approach, you'd write:
<programlisting>
a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$
</programlisting>
      being careful that any dollar-quote delimiters around this are not
      just <literal>$$</>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>6 quotation marks</term>
    <listitem>
     <para>
      When a single quotation mark in a string inside the function body is
      adjacent to the end of that string constant, for example:
<programlisting>
a_output := a_output || '' AND name LIKE ''''foobar''''''
</programlisting>
      The value appended to <literal>a_output</literal> would then be:
      <literal> AND name LIKE 'foobar'</literal>.
     </para>
     <para>
      In the dollar-quoting approach, this becomes:
<programlisting>
a_output := a_output || $$ AND name LIKE 'foobar'$$
</programlisting>
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>10 quotation marks</term>
    <listitem>
     <para>
      When you want two single quotation marks in a string constant (which
      accounts for 8 quotation marks) and this is adjacent to the end of that
      string constant (2 more).  You will probably only need that if
      you are writing a function that generates other functions, as in
      <xref linkend="plpgsql-porting-ex2">.
      For example:
<programlisting>
3768 3769 3770 3771 3772
a_output := a_output || '' if v_'' ||
    referrer_keys.kind || '' like ''''''''''
    || referrer_keys.key_string || ''''''''''
    then return ''''''  || referrer_keys.referrer_type
    || ''''''; end if;'';
3773 3774 3775 3776 3777 3778 3779 3780 3781 3782 3783
</programlisting>
      The value of <literal>a_output</literal> would then be:
<programlisting>
if v_... like ''...'' then return ''...''; end if;
</programlisting>
     </para>
     <para>
      In the dollar-quoting approach, this becomes:
<programlisting>
a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
    || referrer_keys.key_string || $$'
3784 3785
    then return '$$  || referrer_keys.referrer_type
    || $$'; end if;$$;
3786 3787 3788 3789 3790 3791 3792 3793 3794 3795 3796
</programlisting>
      where we assume we only need to put single quote marks into
      <literal>a_output</literal>, because it will be re-quoted before use.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>

  </sect2>
 </sect1>

3797 3798
  <!-- **** Porting from Oracle PL/SQL **** -->

3799
 <sect1 id="plpgsql-porting">
3800
  <title>Porting from <productname>Oracle</productname> PL/SQL</title>
3801

Peter Eisentraut's avatar
Peter Eisentraut committed
3802
  <indexterm zone="plpgsql-porting">
3803
   <primary>Oracle</primary>
Peter Eisentraut's avatar
Peter Eisentraut committed
3804
   <secondary>porting from PL/SQL to PL/pgSQL</secondary>
3805 3806
  </indexterm>

Peter Eisentraut's avatar
Peter Eisentraut committed
3807
  <indexterm zone="plpgsql-porting">
Peter Eisentraut's avatar
Peter Eisentraut committed
3808 3809
   <primary>PL/SQL (Oracle)</primary>
   <secondary>porting to PL/pgSQL</secondary>
3810 3811
  </indexterm>

3812
  <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
3813 3814 3815
   This section explains differences between
   <productname>PostgreSQL</>'s <application>PL/pgSQL</application>
   language and Oracle's <application>PL/SQL</application> language,
3816 3817
   to help developers who port applications from
   <trademark class=registered>Oracle</> to <productname>PostgreSQL</>.
3818 3819 3820
  </para>

  <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
3821 3822 3823 3824 3825 3826
   <application>PL/pgSQL</application> is similar to PL/SQL in many
   aspects. It is a block-structured, imperative language, and all
   variables have to be declared.  Assignments, loops, conditionals
   are similar.  The main differences you should keep in mind when
   porting from <application>PL/SQL</> to
   <application>PL/pgSQL</application> are:
3827 3828 3829 3830

    <itemizedlist>
     <listitem>
      <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
3831
       There are no default values for parameters in <productname>PostgreSQL</>.
3832 3833 3834
      </para>
     </listitem>

3835 3836
     <listitem>
      <para>
3837 3838
       You can overload function names in <productname>PostgreSQL</>. This is
       often used to work around the lack of default parameters.
3839 3840 3841
      </para>
     </listitem>

3842 3843
     <listitem>
      <para>
3844 3845 3846 3847 3848 3849
       If a name used in a SQL command could be either a column name of a
       table or a reference to a variable of the function,
       <application>PL/SQL</> treats it as a column name, while
       <application>PL/pgSQL</> treats it as a variable name.  It's best
       to avoid such ambiguities in the first place, but if necessary you
       can fix them by properly qualifying the ambiguous name.
3850
       (See <xref linkend="plpgsql-var-subst">.)
3851 3852 3853 3854 3855
      </para>
     </listitem>

     <listitem>
      <para>
3856
       In <productname>PostgreSQL</> the function body must be written as
3857
       a string literal.  Therefore you need to use dollar quoting or escape
3858 3859
       single quotes in the function body. (See <xref
       linkend="plpgsql-quote-tips">.)
3860 3861 3862 3863 3864
      </para>
     </listitem>

     <listitem>
      <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
3865 3866
       Instead of packages, use schemas to organize your functions
       into groups.
3867 3868
      </para>
     </listitem>
3869 3870 3871 3872

     <listitem>
      <para>
       Since there are no packages, there are no package-level variables
3873
       either. This is somewhat annoying.  You can keep per-session state
3874
       in temporary tables instead.
3875 3876
      </para>
     </listitem>
3877

3878 3879 3880 3881 3882 3883 3884 3885 3886 3887 3888 3889
     <listitem>
      <para>
       Integer <command>FOR</> loops with <literal>REVERSE</> work
       differently: <application>PL/SQL</> counts down from the second
       number to the first, while <application>PL/pgSQL</> counts down
       from the first number to the second, requiring the loop bounds
       to be swapped when porting.  This incompatibility is unfortunate
       but is unlikely to be changed. (See <xref
       linkend="plpgsql-integer-for">.)
      </para>
     </listitem>

3890 3891 3892 3893 3894 3895 3896 3897 3898 3899 3900 3901 3902 3903 3904 3905 3906
     <listitem>
      <para>
       <command>FOR</> loops over queries (other than cursors) also work
       differently: the target variable(s) must have been declared,
       whereas <application>PL/SQL</> always declares them implicitly.
       An advantage of this is that the variable values are still accessible
       after the loop exits.
      </para>
     </listitem>

     <listitem>
      <para>
       There are various notational differences for the use of cursor
       variables.
      </para>
     </listitem>

3907 3908 3909
    </itemizedlist>
   </para>

Peter Eisentraut's avatar
Peter Eisentraut committed
3910 3911
  <sect2>
   <title>Porting Examples</title>
3912

Peter Eisentraut's avatar
Peter Eisentraut committed
3913 3914 3915 3916
   <para>
    <xref linkend="pgsql-porting-ex1"> shows how to port a simple
    function from <application>PL/SQL</> to <application>PL/pgSQL</>.
   </para>
3917

Peter Eisentraut's avatar
Peter Eisentraut committed
3918 3919
   <example id="pgsql-porting-ex1">
    <title>Porting a Simple Function from <application>PL/SQL</> to <application>PL/pgSQL</></title>
3920 3921

    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
3922
     Here is an <productname>Oracle</productname> <application>PL/SQL</> function:
3923
<programlisting>
3924 3925
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
                                                  v_version varchar)
3926 3927 3928 3929 3930 3931 3932 3933
RETURN varchar IS
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
/
Peter Eisentraut's avatar
Peter Eisentraut committed
3934
show errors;
3935 3936 3937 3938
</programlisting>
    </para>

    <para>
3939 3940
     Let's go through this function and see the differences compared to
     <application>PL/pgSQL</>:
3941 3942 3943 3944 3945 3946

     <itemizedlist>
      <listitem>
       <para>
        The <literal>RETURN</literal> key word in the function
        prototype (not the function body) becomes
3947 3948
        <literal>RETURNS</literal> in
        <productname>PostgreSQL</productname>.
3949 3950 3951
        Also, <literal>IS</> becomes <literal>AS</>, and you need to
        add a <literal>LANGUAGE</> clause because <application>PL/pgSQL</>
        is not the only possible function language.
3952 3953 3954 3955 3956
       </para>
      </listitem>

      <listitem>
       <para>
3957 3958 3959 3960
        In <productname>PostgreSQL</>, the function body is considered
        to be a string literal, so you need to use quote marks or dollar
        quotes around it.  This substitutes for the terminating <literal>/</>
        in the Oracle approach.
3961 3962 3963 3964 3965
       </para>
      </listitem>

      <listitem>
       <para>
3966 3967
        The <literal>show errors</literal> command does not exist in
        <productname>PostgreSQL</>, and is not needed since errors are
3968
        reported automatically.
3969 3970 3971 3972 3973 3974
       </para>
      </listitem>
     </itemizedlist>
    </para>

    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
3975
     This is how this function would look when ported to
3976
     <productname>PostgreSQL</>:
3977 3978

<programlisting>
3979 3980
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
                                                  v_version varchar)
3981
RETURNS varchar AS $$
3982 3983
BEGIN
    IF v_version IS NULL THEN
3984
        RETURN v_name;
3985
    END IF;
3986
    RETURN v_name || '/' || v_version;
3987
END;
3988
$$ LANGUAGE plpgsql;
3989 3990 3991
</programlisting>
    </para>
   </example>
Peter Eisentraut's avatar
Peter Eisentraut committed
3992 3993 3994

   <para>
    <xref linkend="plpgsql-porting-ex2"> shows how to port a
3995
    function that creates another function and how to handle the
Peter Eisentraut's avatar
Peter Eisentraut committed
3996 3997
    ensuing quoting problems.
   </para>
3998

Peter Eisentraut's avatar
Peter Eisentraut committed
3999 4000
   <example id="plpgsql-porting-ex2">
    <title>Porting a Function that Creates Another Function from <application>PL/SQL</> to <application>PL/pgSQL</></title>
4001 4002 4003

    <para>
     The following procedure grabs rows from a
4004
     <command>SELECT</command> statement and builds a large function
4005
     with the results in <literal>IF</literal> statements, for the
4006
     sake of efficiency.
Peter Eisentraut's avatar
Peter Eisentraut committed
4007
    </para>
4008

Peter Eisentraut's avatar
Peter Eisentraut committed
4009 4010
    <para>
     This is the Oracle version:
4011
<programlisting>
4012
CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
4013 4014
    CURSOR referrer_keys IS
        SELECT * FROM cs_referrer_keys
4015
        ORDER BY try_order;
4016 4017
    func_cmd VARCHAR(4000);
BEGIN
4018
    func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR,
4019
                 v_domain IN VARCHAR, v_url IN VARCHAR) RETURN VARCHAR IS BEGIN';
4020

4021
    FOR referrer_key IN referrer_keys LOOP
4022 4023 4024 4025
        func_cmd := func_cmd ||
          ' IF v_' || referrer_key.kind
          || ' LIKE ''' || referrer_key.key_string
          || ''' THEN RETURN ''' || referrer_key.referrer_type
4026 4027
          || '''; END IF;';
    END LOOP;
4028

4029
    func_cmd := func_cmd || ' RETURN NULL; END;';
4030

4031 4032 4033
    EXECUTE IMMEDIATE func_cmd;
END;
/
Peter Eisentraut's avatar
Peter Eisentraut committed
4034
show errors;
4035 4036 4037 4038
</programlisting>
    </para>

    <para>
4039
     Here is how this function would end up in <productname>PostgreSQL</>:
4040
<programlisting>
4041
CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$
4042
DECLARE
4043 4044 4045
    CURSOR referrer_keys IS
        SELECT * FROM cs_referrer_keys
        ORDER BY try_order;
4046 4047
    func_body text;
    func_cmd text;
4048
BEGIN
4049
    func_body := 'BEGIN';
4050

4051
    FOR referrer_key IN referrer_keys LOOP
4052 4053 4054 4055 4056
        func_body := func_body ||
          ' IF v_' || referrer_key.kind
          || ' LIKE ' || quote_literal(referrer_key.key_string)
          || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
          || '; END IF;' ;
4057
    END LOOP;
4058 4059 4060 4061 4062 4063

    func_body := func_body || ' RETURN NULL; END;';

    func_cmd :=
      'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
                                                        v_domain varchar,
4064
                                                        v_url varchar)
4065 4066 4067 4068 4069 4070
        RETURNS varchar AS '
      || quote_literal(func_body)
      || ' LANGUAGE plpgsql;' ;

    EXECUTE func_cmd;
END;
4071
$func$ LANGUAGE plpgsql;
4072
</programlisting>
4073 4074 4075 4076 4077 4078 4079 4080 4081 4082 4083 4084
     Notice how the body of the function is built separately and passed
     through <literal>quote_literal</> to double any quote marks in it.  This
     technique is needed because we cannot safely use dollar quoting for
     defining the new function: we do not know for sure what strings will
     be interpolated from the <structfield>referrer_key.key_string</> field.
     (We are assuming here that <structfield>referrer_key.kind</> can be
     trusted to always be <literal>host</>, <literal>domain</>, or
     <literal>url</>, but <structfield>referrer_key.key_string</> might be
     anything, in particular it might contain dollar signs.) This function
     is actually an improvement on the Oracle original, because it will
     not generate broken code when <structfield>referrer_key.key_string</> or
     <structfield>referrer_key.referrer_type</> contain quote marks.
4085 4086
    </para>
   </example>
4087

Peter Eisentraut's avatar
Peter Eisentraut committed
4088 4089 4090
   <para>
    <xref linkend="plpgsql-porting-ex3"> shows how to port a function
    with <literal>OUT</> parameters and string manipulation.
4091 4092
    <productname>PostgreSQL</> does not have a built-in
    <function>instr</function> function, but you can create one
Peter Eisentraut's avatar
Peter Eisentraut committed
4093 4094
    using a combination of other
    functions.<indexterm><primary>instr</></indexterm> In <xref
Peter Eisentraut's avatar
Peter Eisentraut committed
4095 4096 4097 4098 4099 4100 4101 4102 4103 4104
    linkend="plpgsql-porting-appendix"> there is a
    <application>PL/pgSQL</application> implementation of
    <function>instr</function> that you can use to make your porting
    easier.
   </para>

   <example id="plpgsql-porting-ex3">
    <title>Porting a Procedure With String Manipulation and
    <literal>OUT</> Parameters from <application>PL/SQL</> to
    <application>PL/pgSQL</></title>
4105 4106

    <para>
4107 4108
     The following <productname>Oracle</productname> PL/SQL procedure is used
     to parse a URL and return several elements (host, path, and query).
4109 4110
    </para>

Peter Eisentraut's avatar
Peter Eisentraut committed
4111 4112
    <para>
     This is the Oracle version:
4113
<programlisting>
4114 4115 4116 4117 4118
CREATE OR REPLACE PROCEDURE cs_parse_url(
    v_url IN VARCHAR,
    v_host OUT VARCHAR,  -- This will be passed back
    v_path OUT VARCHAR,  -- This one too
    v_query OUT VARCHAR) -- And this one
Peter Eisentraut's avatar
Peter Eisentraut committed
4119
IS
4120 4121
    a_pos1 INTEGER;
    a_pos2 INTEGER;
Peter Eisentraut's avatar
Peter Eisentraut committed
4122
BEGIN
4123 4124 4125
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
Peter Eisentraut's avatar
Peter Eisentraut committed
4126
    a_pos1 := instr(v_url, '//');
4127

4128 4129 4130
    IF a_pos1 = 0 THEN
        RETURN;
    END IF;
4131
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
4132
    IF a_pos2 = 0 THEN
4133 4134
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
4135 4136
        RETURN;
    END IF;
4137 4138 4139 4140

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

4141
    IF a_pos1 = 0 THEN
4142
        v_path := substr(v_url, a_pos2);
4143 4144
        RETURN;
    END IF;
4145 4146 4147

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
4148
END;
4149 4150 4151
/
show errors;
</programlisting>
Peter Eisentraut's avatar
Peter Eisentraut committed
4152
    </para>
4153 4154

    <para>
4155
     Here is a possible translation into <application>PL/pgSQL</>:
4156
<programlisting>
4157 4158 4159 4160 4161 4162
CREATE OR REPLACE FUNCTION cs_parse_url(
    v_url IN VARCHAR,
    v_host OUT VARCHAR,  -- This will be passed back
    v_path OUT VARCHAR,  -- This one too
    v_query OUT VARCHAR) -- And this one
AS $$
Peter Eisentraut's avatar
Peter Eisentraut committed
4163
DECLARE
4164 4165 4166
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEGIN
4167 4168 4169
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
4170
    a_pos1 := instr(v_url, '//');
Peter Eisentraut's avatar
Peter Eisentraut committed
4171

4172
    IF a_pos1 = 0 THEN
4173
        RETURN;
4174 4175 4176
    END IF;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    IF a_pos2 = 0 THEN
4177 4178 4179
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        RETURN;
4180
    END IF;
Peter Eisentraut's avatar
Peter Eisentraut committed
4181

4182
    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
4183
    a_pos1 := instr(v_url, '?', a_pos2 + 1);
Peter Eisentraut's avatar
Peter Eisentraut committed
4184

4185
    IF a_pos1 = 0 THEN
4186 4187
        v_path := substr(v_url, a_pos2);
        RETURN;
4188 4189
    END IF;

4190 4191
    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
4192
END;
4193
$$ LANGUAGE plpgsql;
4194 4195 4196 4197 4198
</programlisting>

     This function could be used like this:
<programlisting>
SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
4199 4200 4201 4202 4203
</programlisting>
    </para>
   </example>

   <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
4204 4205
    <xref linkend="plpgsql-porting-ex4"> shows how to port a procedure
    that uses numerous features that are specific to Oracle.
4206 4207
   </para>

Peter Eisentraut's avatar
Peter Eisentraut committed
4208 4209 4210 4211 4212
   <example id="plpgsql-porting-ex4">
    <title>Porting a Procedure from <application>PL/SQL</> to <application>PL/pgSQL</></title>

    <para>
     The Oracle version:
4213 4214

<programlisting>
4215 4216 4217 4218 4219
CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
    a_running_job_count INTEGER;
    PRAGMA AUTONOMOUS_TRANSACTION;<co id="co.plpgsql-porting-pragma">
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;<co id="co.plpgsql-porting-locktable">
4220

Peter Eisentraut's avatar
Peter Eisentraut committed
4221
    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
4222

4223
    IF a_running_job_count &gt; 0 THEN
4224
        COMMIT; -- free lock<co id="co.plpgsql-porting-commit">
4225
        raise_application_error(-20000, 'Unable to create a new job: a job is currently running.');
4226
    END IF;
4227

4228 4229
    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
4230

4231 4232
    BEGIN
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, sysdate);
4233 4234
    EXCEPTION
        WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
4235 4236 4237
    END;
    COMMIT;
END;
4238 4239 4240 4241 4242 4243
/
show errors
</programlisting>
   </para>

   <para>
4244 4245
    Procedures like this can easily be converted into <productname>PostgreSQL</>
    functions returning <type>void</type>. This procedure in
4246 4247 4248 4249 4250
    particular is interesting because it can teach us some things:

    <calloutlist>
     <callout arearefs="co.plpgsql-porting-pragma">
      <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
4251
       There is no <literal>PRAGMA</literal> statement in <productname>PostgreSQL</>.
4252 4253 4254 4255 4256
      </para>
     </callout>

     <callout arearefs="co.plpgsql-porting-locktable">
      <para>
4257 4258 4259
       If you do a <command>LOCK TABLE</command> in <application>PL/pgSQL</>,
       the lock will not be released until the calling transaction is
       finished.
4260 4261 4262 4263 4264
      </para>
     </callout>

     <callout arearefs="co.plpgsql-porting-commit">
      <para>
4265 4266 4267 4268 4269 4270 4271
       You cannot issue <command>COMMIT</> in a
       <application>PL/pgSQL</application> function.  The function is
       running within some outer transaction and so <command>COMMIT</>
       would imply terminating the function's execution.  However, in
       this particular case it is not necessary anyway, because the lock
       obtained by the <command>LOCK TABLE</command> will be released when
       we raise an error.
4272 4273 4274 4275 4276 4277
      </para>
     </callout>
    </calloutlist>
   </para>

   <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
4278
    This is how we could port this procedure to <application>PL/pgSQL</>:
4279 4280

<programlisting>
4281
CREATE OR REPLACE FUNCTION cs_create_job(v_job_id integer) RETURNS void AS $$
4282
DECLARE
Peter Eisentraut's avatar
Peter Eisentraut committed
4283
    a_running_job_count integer;
4284 4285
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
4286

Peter Eisentraut's avatar
Peter Eisentraut committed
4287
    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
4288

4289
    IF a_running_job_count &gt; 0 THEN
4290
        RAISE EXCEPTION 'Unable to create a new job: a job is currently running';<co id="co.plpgsql-porting-raise">
4291
    END IF;
4292

4293 4294
    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
4295

4296 4297 4298 4299 4300 4301
    BEGIN
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
    EXCEPTION
        WHEN unique_violation THEN <co id="co.plpgsql-porting-exception">
            -- don't worry if it already exists
    END;
4302
END;
4303
$$ LANGUAGE plpgsql;
4304 4305 4306 4307 4308
</programlisting>

    <calloutlist>
     <callout arearefs="co.plpgsql-porting-raise">
      <para>
4309 4310 4311 4312 4313 4314 4315 4316
       The syntax of <literal>RAISE</> is considerably different from
       Oracle's similar statement.
      </para>
     </callout>
     <callout arearefs="co.plpgsql-porting-exception">
      <para>
       The exception names supported by <application>PL/pgSQL</> are
       different from Oracle's.  The set of built-in exception names
4317 4318
       is much larger (see <xref linkend="errcodes-appendix">).  There
       is not currently a way to declare user-defined exception names.
4319 4320 4321
      </para>
     </callout>
    </calloutlist>
4322 4323 4324 4325 4326 4327

    The main functional difference between this procedure and the
    Oracle equivalent is that the exclusive lock on the <literal>cs_jobs</>
    table will be held until the calling transaction completes.  Also, if
    the caller later aborts (for example due to an error), the effects of
    this procedure will be rolled back.
4328
   </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
4329
   </example>
4330 4331
  </sect2>

Peter Eisentraut's avatar
Peter Eisentraut committed
4332 4333
  <sect2 id="plpgsql-porting-other">
   <title>Other Things to Watch For</title>
4334 4335

   <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
4336
    This section explains a few other things to watch for when porting
4337 4338
    Oracle <application>PL/SQL</> functions to
    <productname>PostgreSQL</productname>.
4339 4340
   </para>

4341 4342 4343 4344 4345 4346 4347
   <sect3 id="plpgsql-porting-exceptions">
    <title>Implicit Rollback after Exceptions</title>

    <para>
     In <application>PL/pgSQL</>, when an exception is caught by an
     <literal>EXCEPTION</> clause, all database changes since the block's
     <literal>BEGIN</> are automatically rolled back.  That is, the behavior
4348
     is equivalent to what you'd get in Oracle with:
4349 4350 4351 4352 4353 4354 4355 4356 4357 4358 4359 4360 4361 4362 4363 4364 4365 4366 4367 4368 4369 4370 4371 4372

<programlisting>
    BEGIN
        SAVEPOINT s1;
        ... code here ...
    EXCEPTION
        WHEN ... THEN
            ROLLBACK TO s1;
            ... code here ...
        WHEN ... THEN
            ROLLBACK TO s1;
            ... code here ...
    END;
</programlisting>

     If you are translating an Oracle procedure that uses
     <command>SAVEPOINT</> and <command>ROLLBACK TO</> in this style,
     your task is easy: just omit the <command>SAVEPOINT</> and
     <command>ROLLBACK TO</>.  If you have a procedure that uses
     <command>SAVEPOINT</> and <command>ROLLBACK TO</> in a different way
     then some actual thought will be required.
    </para>
   </sect3>

4373
   <sect3>
Peter Eisentraut's avatar
Peter Eisentraut committed
4374
    <title><command>EXECUTE</command></title>
4375 4376

    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
4377
     The <application>PL/pgSQL</> version of
4378
     <command>EXECUTE</command> works similarly to the
Peter Eisentraut's avatar
Peter Eisentraut committed
4379
     <application>PL/SQL</> version, but you have to remember to use
4380 4381
     <function>quote_literal</function> and
     <function>quote_ident</function> as described in <xref
Peter Eisentraut's avatar
Peter Eisentraut committed
4382
     linkend="plpgsql-statements-executing-dyn">.  Constructs of the
4383
     type <literal>EXECUTE 'SELECT * FROM $1';</literal> will not work
4384
     reliably unless you use these functions.
4385 4386 4387 4388
    </para>
   </sect3>

   <sect3 id="plpgsql-porting-optimization">
4389
    <title>Optimizing <application>PL/pgSQL</application> Functions</title>
4390 4391

    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
4392
     <productname>PostgreSQL</> gives you two function creation
Neil Conway's avatar
Neil Conway committed
4393 4394 4395 4396 4397 4398
     modifiers to optimize execution: <quote>volatility</> (whether
     the function always returns the same result when given the same
     arguments) and <quote>strictness</quote> (whether the function
     returns null if any argument is null).  Consult the <xref
     linkend="sql-createfunction" endterm="sql-createfunction-title">
     reference page for details.
4399 4400 4401
    </para>

    <para>
4402 4403
     When making use of these optimization attributes, your
     <command>CREATE FUNCTION</command> statement might look something
Peter Eisentraut's avatar
Peter Eisentraut committed
4404
     like this:
4405 4406

<programlisting>
4407
CREATE FUNCTION foo(...) RETURNS integer AS $$
4408
...
4409
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
4410 4411 4412 4413 4414 4415
</programlisting>
    </para>
   </sect3>
  </sect2>

  <sect2 id="plpgsql-porting-appendix">
Peter Eisentraut's avatar
Peter Eisentraut committed
4416
   <title>Appendix</title>
4417

Peter Eisentraut's avatar
Peter Eisentraut committed
4418
   <para>
4419 4420
    This section contains the code for a set of Oracle-compatible
    <function>instr</function> functions that you can use to simplify
Peter Eisentraut's avatar
Peter Eisentraut committed
4421 4422
    your porting efforts.
   </para>
4423 4424 4425 4426

<programlisting>
--
-- instr functions that mimic Oracle's counterpart
Peter Eisentraut's avatar
Peter Eisentraut committed
4427
-- Syntax: instr(string1, string2, [n], [m]) where [] denotes optional parameters.
4428
--
Peter Eisentraut's avatar
Peter Eisentraut committed
4429 4430 4431
-- Searches string1 beginning at the nth character for the mth occurrence
-- of string2.  If n is negative, search backwards.  If m is not passed,
-- assume 1 (search starts at first character).
4432 4433
--

4434
CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
4435 4436 4437
DECLARE
    pos integer;
BEGIN
Peter Eisentraut's avatar
Peter Eisentraut committed
4438
    pos:= instr($1, $2, 1);
4439 4440
    RETURN pos;
END;
4441
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
4442 4443


4444 4445
CREATE FUNCTION instr(string varchar, string_to_search varchar, beg_index integer)
RETURNS integer AS $$
4446 4447
DECLARE
    pos integer NOT NULL DEFAULT 0;
Peter Eisentraut's avatar
Peter Eisentraut committed
4448 4449 4450 4451
    temp_str varchar;
    beg integer;
    length integer;
    ss_length integer;
4452
BEGIN
4453
    IF beg_index &gt; 0 THEN
Peter Eisentraut's avatar
Peter Eisentraut committed
4454 4455
        temp_str := substring(string FROM beg_index);
        pos := position(string_to_search IN temp_str);
4456

Peter Eisentraut's avatar
Peter Eisentraut committed
4457 4458 4459 4460 4461
        IF pos = 0 THEN
            RETURN 0;
        ELSE
            RETURN pos + beg_index - 1;
        END IF;
4462
    ELSE
Peter Eisentraut's avatar
Peter Eisentraut committed
4463 4464 4465
        ss_length := char_length(string_to_search);
        length := char_length(string);
        beg := length + beg_index - ss_length + 2;
4466

4467
        WHILE beg &gt; 0 LOOP
Peter Eisentraut's avatar
Peter Eisentraut committed
4468 4469 4470
            temp_str := substring(string FROM beg FOR ss_length);
            pos := position(string_to_search IN temp_str);

4471
            IF pos &gt; 0 THEN
Peter Eisentraut's avatar
Peter Eisentraut committed
4472 4473
                RETURN beg;
            END IF;
4474

Peter Eisentraut's avatar
Peter Eisentraut committed
4475 4476
            beg := beg - 1;
        END LOOP;
4477

Peter Eisentraut's avatar
Peter Eisentraut committed
4478
        RETURN 0;
4479 4480
    END IF;
END;
4481
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
4482

Peter Eisentraut's avatar
Peter Eisentraut committed
4483

4484 4485 4486
CREATE FUNCTION instr(string varchar, string_to_search varchar,
                      beg_index integer, occur_index integer)
RETURNS integer AS $$
4487 4488
DECLARE
    pos integer NOT NULL DEFAULT 0;
Peter Eisentraut's avatar
Peter Eisentraut committed
4489 4490 4491 4492 4493 4494
    occur_number integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    i integer;
    length integer;
    ss_length integer;
4495
BEGIN
4496
    IF beg_index &gt; 0 THEN
4497 4498
        beg := beg_index;
        temp_str := substring(string FROM beg_index);
4499

4500 4501 4502 4503 4504 4505 4506 4507 4508 4509 4510
        FOR i IN 1..occur_index LOOP
            pos := position(string_to_search IN temp_str);

            IF i = 1 THEN
                beg := beg + pos - 1;
            ELSE
                beg := beg + pos;
            END IF;

            temp_str := substring(string FROM beg + 1);
        END LOOP;
4511 4512 4513 4514

        IF pos = 0 THEN
            RETURN 0;
        ELSE
4515
            RETURN beg;
4516 4517
        END IF;
    ELSE
4518
        ss_length := char_length(string_to_search);
4519
        length := char_length(string);
4520
        beg := length + beg_index - ss_length + 2;
4521

4522
        WHILE beg &gt; 0 LOOP
4523
            temp_str := substring(string FROM beg FOR ss_length);
4524 4525
            pos := position(string_to_search IN temp_str);

4526
            IF pos &gt; 0 THEN
4527 4528 4529 4530 4531 4532
                occur_number := occur_number + 1;

                IF occur_number = occur_index THEN
                    RETURN beg;
                END IF;
            END IF;
4533 4534 4535 4536 4537 4538 4539

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    END IF;
END;
4540
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
Peter Eisentraut's avatar
Peter Eisentraut committed
4541
</programlisting>
4542
  </sect2>
4543

4544 4545 4546
 </sect1>

</chapter>