plsql.sgml 78.9 KB
Newer Older
1
<!--
2
$Header: /cvsroot/pgsql/doc/src/sgml/Attic/plsql.sgml,v 2.39 2001/09/13 15:55:23 petere Exp $
3 4
-->

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

8 9 10 11
 <indexterm zone="plpgsql">
  <primary>PL/pgSQL</primary>
 </indexterm>

12
 <para>
13
  <application>PL/pgSQL</application> is a loadable procedural language for the
14 15 16 17 18
  <productname>Postgres</productname> database system.
 </para>
   
 <para>
  This package was originally written by Jan Wieck. This
Peter Eisentraut's avatar
Peter Eisentraut committed
19 20
  documentation was in part written 
  by Roberto Mello (<email>rmello@fslc.usu.edu</email>).
21
 </para>
22

23
  <sect1 id="plpgsql-overview">
24 25 26
   <title>Overview</title>

   <para>
27
    The design goals of <application>PL/pgSQL</> were to create a loadable procedural
28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
    language that
    <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>
       inherits all user defined types, functions and operators,
      </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>
   <para>
63
    The <application>PL/pgSQL</> call handler parses the function's source text and
64
    produces an internal binary instruction tree the first time the
65
    function is called. The produced bytecode is identified
66
    in the call handler by the object ID of the function. This ensures
67 68 69 70 71
    that changing a function by a DROP/CREATE sequence will take effect
    without establishing a new database connection. 
   </para>
   <para>
    For all expressions and <acronym>SQL</acronym> statements used in
72
    the function, the <application>PL/pgSQL</> bytecode interpreter creates a
73 74 75 76
    prepared execution plan using the <acronym>SPI</acronym> manager's 
    <function>SPI_prepare()</function> and
    <function>SPI_saveplan()</function> functions. This is done the 
    first time the individual
77
    statement is processed in the <application>PL/pgSQL</> function. Thus, a function with
78 79
    conditional code that contains many statements for which execution
    plans would be required, will only prepare and save those plans
80
    that are really used during the lifetime of the database
81 82
    connection.
   </para>
83 84 85 86
  <para>
  This means that you have to be careful about your user-defined
  functions. For example:

Peter Eisentraut's avatar
Peter Eisentraut committed
87
<programlisting>
88 89 90 91 92 93
CREATE FUNCTION populate() RETURNS INTEGER AS '
DECLARE
    -- Declarations
BEGIN
    PERFORM my_function();
END;
Peter Eisentraut's avatar
Peter Eisentraut committed
94 95 96 97 98 99 100 101
' LANGUAGE 'plpgsql';
</programlisting>
    If you create the above function, it will reference the OID for
    <function>my_function()</function> in its bytecode. Later, if you
    drop and re-create <function>my_function()</function>, then
    <function>populate()</function> will not be able to find
    <function>my_function()</function> anymore. You would then have to
    re-create <function>populate()</function>.
102
   </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
103

104
   <para>
105 106
    Because <application>PL/pgSQL</application> saves execution plans in this way, queries that appear
    directly in a <application>PL/pgSQL</application> function must refer to the same tables and fields
107 108
    on every execution; that is, you cannot use a parameter as the name of
    a table or field in a query.  To get around
109
    this restriction, you can construct dynamic queries using the <application>PL/pgSQL</application>
110 111 112 113 114
    EXECUTE statement --- at the price of constructing a new query plan
    on every execution.
   </para>
   <para>
    Except for input/output conversion and calculation functions
115
    for user defined types, anything that can be defined in C language
116
    functions can also be done with <application>PL/pgSQL</application>. It is possible to
117
    create complex conditional computation functions and later use
118
    them to define operators or use them in functional indexes.
119
   </para>
120 121 122 123 124 125
  <sect2 id="plpgsql-advantages">
   <title>Advantages of Using PL/pgSQL</title>

   <itemizedlist>
    <listitem>
     <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
126
      Better performance (see <xref linkend="plpgsql-advantages-performance">)
127 128
     </para>
    </listitem>
Peter Eisentraut's avatar
Peter Eisentraut committed
129

130 131
    <listitem>
     <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
132
      SQL support (see <xref linkend="plpgsql-advantages-sqlsupport">)
133 134
     </para>
    </listitem> 
Peter Eisentraut's avatar
Peter Eisentraut committed
135

136 137
    <listitem>
     <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
138
      Portability (see <xref linkend="plpgsql-advantages-portability">)
139 140 141 142 143 144
     </para>
    </listitem>
   </itemizedlist>

   <sect3 id="plpgsql-advantages-performance">
    <title>Better Performance</title>
Peter Eisentraut's avatar
Peter Eisentraut committed
145

146 147 148 149 150 151 152
    <para>
     <acronym>SQL</acronym> is the language PostgreSQL (and
     most other Relational Databases) use as query
     language. It's portable and easy to learn. But every
     <acronym>SQL</acronym> statement must be executed
     individually by the database server.
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
153

154 155 156 157 158 159 160 161 162
    <para>
     That means that your client application must send each
     query to the database server, wait for it to process it,
     receive the results, do some computation, then send
     other queries to the server. All this incurs inter
     process communication and may also incur network
     overhead if your client is on a different machine than
     the database server.
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
163

164
    <para>
165
     With <application>PL/pgSQL</application> you can group a block of computation and a
166 167 168 169 170
     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 saving lots of
     time because you don't have the whole client/server
     communication overhead. Your application will enjoy a
171
     considerable performance increase by using <application>PL/pgSQL</application>. 
172 173
    </para>
   </sect3>
Peter Eisentraut's avatar
Peter Eisentraut committed
174

175 176
   <sect3 id="plpgsql-advantages-sqlsupport">
    <title>SQL Support</title>
Peter Eisentraut's avatar
Peter Eisentraut committed
177

178
    <para>
179
     <application>PL/pgSQL</application> adds the power of a procedural language to the
180
     flexibility and ease of <acronym>SQL</acronym>. With
181
     <application>PL/pgSQL</application> you can use all the data types, columns, operators
182 183 184
     and functions of SQL.    
    </para>
   </sect3>
Peter Eisentraut's avatar
Peter Eisentraut committed
185

186 187
   <sect3 id="plpgsql-advantages-portability">
    <title>Portability</title>
Peter Eisentraut's avatar
Peter Eisentraut committed
188

189
    <para>
190
     Because <application>PL/pgSQL</application> functions run inside PostgreSQL, these
191 192 193 194 195 196 197
     functions will run on any platform where PostgreSQL
     runs. Thus you can reuse code and have less development costs.
    </para>
   </sect3>
  </sect2>

  <sect2 id="plpgsql-overview-developing-in-plpgsql">
198
   <title>Developing in <application>PL/pgSQL</application></title>
199 200

   <para>
201
    Developing in <application>PL/pgSQL</application> is pretty straight forward, especially
202
    if you have developed in other database procedural languages,
203 204
    such as Oracle's <application>PL/SQL</application>. Two good ways of developing in
    <application>PL/pgSQL</application> are:
Peter Eisentraut's avatar
Peter Eisentraut committed
205

206 207 208 209 210 211
    <itemizedlist>
     <listitem>
      <para>
       Using a text editor and reloading the file with <command>psql</command>
      </para>
     </listitem>
Peter Eisentraut's avatar
Peter Eisentraut committed
212

213 214
     <listitem>
      <para>
215
       Using PostgreSQL's GUI Tool: <application>PgAccess</>
216 217 218 219
      </para>
     </listitem>
    </itemizedlist>
   </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
220

221
   <para>
222
    One good way to develop in <application>PL/pgSQL</> is to simply use the text
223 224 225
    editor of your choice to create your functions, and in another
    console, use <command>psql</command> (PostgreSQL's interactive monitor) to load
    those functions. If you are doing it this way (and if you are
226
    a <application>PL/pgSQL</> novice or in debugging stage), it is a good idea to 
227 228 229
    always <command>DROP</command> your function before creating it. That way
    when you reload the file, it'll drop your functions and then
    re-create them. For example:
Peter Eisentraut's avatar
Peter Eisentraut committed
230
<programlisting>
231 232 233 234 235
drop function testfunc(integer);
create function testfunc(integer) return integer as '
    ....
end;
' language 'plpgsql';
Peter Eisentraut's avatar
Peter Eisentraut committed
236 237
</programlisting>
   </para>
238

Peter Eisentraut's avatar
Peter Eisentraut committed
239
   <para>
240 241 242
    When you load the file for the first time,
    <productname>PostgreSQL</> will raise a warning saying this
    function doesn't exist and go on to create it. To load an SQL
243
    file (e.g., <filename>filename.sql</filename>) into a database named <literal>dbname</literal>, use the command:
Peter Eisentraut's avatar
Peter Eisentraut committed
244
<programlisting>
245
psql -f filename.sql dbname
Peter Eisentraut's avatar
Peter Eisentraut committed
246 247
</programlisting>
   </para>
248

Peter Eisentraut's avatar
Peter Eisentraut committed
249
   <para>
250 251
    Another good way to develop in <application>PL/pgSQL</> is using
    <productname>PostgreSQL</>'s GUI tool: <application>PgAccess</>. It does some
252 253 254 255
    nice things for you, like escaping single-quotes, and making
    it easy to recreate and debug functions.
   </para>
  </sect2>
Peter Eisentraut's avatar
Peter Eisentraut committed
256
 </sect1>
257 258 259

  <!-- **** PL/pgSQL Description **** -->

260
  <sect1 id="plpgsql-description">
261 262 263 264 265
   <title>Description</title>

   <!-- **** PL/pgSQL structure **** -->

   <sect2>
266
    <title>Structure of <application>PL/pgSQL</application></title>
267 268

    <para>
269
     <application>PL/pgSQL</application> is a <emphasis>block structured</emphasis> language. All
Peter Eisentraut's avatar
Peter Eisentraut committed
270 271
     keywords and identifiers can be used in mixed upper and
     lower-case. A block is defined as:
272

Peter Eisentraut's avatar
Peter Eisentraut committed
273
<synopsis>
274 275 276
<optional>&lt;&lt;label&gt;&gt;</optional>
<optional>DECLARE
    <replaceable>declarations</replaceable></optional>
277 278 279
BEGIN
    <replaceable>statements</replaceable>
END;
Peter Eisentraut's avatar
Peter Eisentraut committed
280 281
</synopsis>
    </para>
282 283

    <para>
284 285
     There can be any number of sub-blocks in the statement section
     of a block. Sub-blocks can be used to hide variables from outside a
286 287
     block of statements.
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
288

289
    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
290 291 292 293
     The variables declared in the declarations section preceding a
     block are initialized to their default values every time the
     block is entered, not only once per function call. For example:
<programlisting>
294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309
CREATE FUNCTION somefunc() RETURNS INTEGER AS '
DECLARE
   quantity INTEGER := 30;
BEGIN
   RAISE NOTICE ''Quantity here is %'',quantity;  -- Quantity here is 30
   quantity := 50;
   --
   -- Create a sub-block
   --
   DECLARE
      quantity INTEGER := 80;
   BEGIN
      RAISE NOTICE ''Quantity here is %'',quantity;  -- Quantity here is 80
   END;

   RAISE NOTICE ''Quantity here is %'',quantity;  -- Quantity here is 50
Tatsuo Ishii's avatar
Tatsuo Ishii committed
310 311

   RETURN quantity;
312
END;
Peter Eisentraut's avatar
Peter Eisentraut committed
313 314 315
' LANGUAGE 'plpgsql';
</programlisting>
    </para>
316 317

    <para>
318
     It is important not to confuse the use of BEGIN/END for
319 320
     grouping statements in <application>PL/pgSQL</> with the database commands for
     transaction control.  <application>PL/pgSQL</>'s BEGIN/END are only for grouping;
321 322 323 324
     they do not start or end a transaction.  Functions and trigger procedures
     are always executed within a transaction established by an outer query
     --- they cannot start or commit transactions, since
     <productname>Postgres</productname> does not have nested transactions.
325 326 327 328 329 330 331
    </para>
   </sect2>

   <sect2>
    <title>Comments</title>

    <para>
332
     There are two types of comments in <application>PL/pgSQL</>. A double dash <literal>--</literal>
333 334
     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>.
335 336
     Block comments cannot be nested, but double dash comments can be
     enclosed into a block comment and a double dash can hide
337
     the block comment delimiters <literal>/*</literal> and <literal>*/</literal>.
338 339 340
    </para>
   </sect2>

341
   <!-- **** PL/pgSQL Variables and Constants **** -->
342
   <sect2>
343
    <title>Variables and Constants</title>
344 345

    <para>
346
     All variables, rows and records used in a block or its
347 348 349
     sub-blocks must be declared in the declarations section of a block.
     The exception being the loop variable of a FOR loop iterating over a range
     of integer values. 
Peter Eisentraut's avatar
Peter Eisentraut committed
350 351 352
    </para>

    <para>
353
     <application>PL/pgSQL</> variables can have any SQL data type, such as
354 355 356
     <type>INTEGER</type>, <type>VARCHAR</type> and
     <type>CHAR</type>. All variables have as default value the
     <acronym>SQL</acronym> NULL value. 
357
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
358 359

    <para>
360
     Here are some examples of variable declarations:
Peter Eisentraut's avatar
Peter Eisentraut committed
361
<programlisting>
362 363 364
user_id INTEGER;
quantity NUMBER(5);
url VARCHAR;
Peter Eisentraut's avatar
Peter Eisentraut committed
365 366
</programlisting>
    </para>
367 368 369 370 371 372

    <sect3 id="plpgsql-description-default-vars">
     <title>Constants and Variables With Default Values</title>

     <para>
      The declarations have the following syntax:
Peter Eisentraut's avatar
Peter Eisentraut committed
373
<synopsis>
374
<replaceable>name</replaceable> <optional> CONSTANT </optional> <replaceable>type</replaceable> <optional> NOT NULL </optional> <optional> { DEFAULT | := } <replaceable>value</replaceable> </optional>;
Peter Eisentraut's avatar
Peter Eisentraut committed
375 376 377
</synopsis>
     </para>

378 379 380 381 382 383 384
     <para>
      The value of variables declared as CONSTANT cannot be changed. If NOT NULL
      is specified, an assignment of a NULL value results in a runtime
      error. Since the default value of all variables is the
      <acronym>SQL</acronym> NULL value, all variables declared as NOT NULL
      must also have a default value specified.
     </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
385

386 387 388 389 390 391 392
     <para>
      The default value is evaluated every time the function is called. So
      assigning '<literal>now</literal>' to a variable of type
      <type>timestamp</type> causes the variable to have the
      time of the actual function call, not when the function was
      precompiled into its bytecode.
     </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
393

394 395
     <para>
      Examples:
Peter Eisentraut's avatar
Peter Eisentraut committed
396
<programlisting>
397 398 399
quantity INTEGER := 32;
url varchar := ''http://mysite.com'';
user_id CONSTANT INTEGER := 10;
Peter Eisentraut's avatar
Peter Eisentraut committed
400 401
</programlisting>
     </para>
402 403 404
    </sect3>

    <sect3 id="plpgsql-description-passed-vars">
405
     <title>Parameters Passed to Functions</title>
406 407

     <para>
408
      Parameters passed to functions are named with the identifiers
409
      <literal>$1</literal>, <literal>$2</literal>,
410 411
      etc.  Optionally, aliases can be declared for the <literal>$n</literal>
      parameter names for increased readability.  Some examples:
Peter Eisentraut's avatar
Peter Eisentraut committed
412
<programlisting>
413 414 415 416 417 418
CREATE FUNCTION sales_tax(REAL) RETURNS REAL AS '
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    return subtotal * 0.06;
END;
Peter Eisentraut's avatar
Peter Eisentraut committed
419
' LANGUAGE 'plpgsql';
420 421


422 423 424 425 426 427 428
CREATE FUNCTION instr(VARCHAR,INTEGER) RETURNS INTEGER AS '
DECLARE
    v_string ALIAS FOR $1;
    index ALIAS FOR $2;
BEGIN
    -- Some computations here
END;
Peter Eisentraut's avatar
Peter Eisentraut committed
429 430 431
' LANGUAGE 'plpgsql';
</programlisting>
     </para>
432 433 434 435 436 437 438 439
    </sect3>

    <sect3 id="plpgsql-description-attributes">
     <title>Attributes</title>

     <para>
      Using the <type>%TYPE</type> and <type>%ROWTYPE</type>
      attributes, you can declare variables with the same
440
      data type or structure of another database item (e.g: a
441 442 443 444 445 446
      table field).
     </para>

     <variablelist>
      <varlistentry>
       <term>
447
        <replaceable>variable</replaceable>%TYPE
448 449 450
       </term>
       <listitem>
        <para>
451
         <type>%TYPE</type> provides the data type of a
452 453 454 455 456
         variable or database column. You can use this to
         declare variables that will hold database
         values. For example, let's say you have a column
         named <type>user_id</type> in your
         <type>users</type> table. To declare a variable with
457
         the same data type as <structname>users</>.<structfield>user_id</> you write:
Peter Eisentraut's avatar
Peter Eisentraut committed
458
<programlisting>
459
user_id   users.user_id%TYPE;
Peter Eisentraut's avatar
Peter Eisentraut committed
460 461 462
</programlisting>
        </para>

463 464
        <para>
         By using <type>%TYPE</type> you don't need to know
465 466
         the data type of the structure you are referencing,
         and most important, if the data type of the
467 468 469 470 471 472 473 474 475 476
         referenced item changes in the future (e.g: you
         change your table definition of user_id to become a
         REAL), you won't need to change your function
         definition.
        </para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>
477
        <literal><replaceable>table</replaceable>%ROWTYPE</literal>
478 479 480
       </term>
       <listitem>
        <para>
481
	 <type>%ROWTYPE</type> provides the composite data type corresponding
482 483
	 to a whole row of the specified table.
	 <replaceable>table</replaceable> must be an existing
Peter Eisentraut's avatar
Peter Eisentraut committed
484 485 486
	 table or view name of the database. The fields of the row are
	 accessed in the dot notation. Parameters to a function can be
	 composite types (complete table rows). In that case, the
487 488
	 corresponding identifier $n will be a rowtype, and fields can
	 be selected from it, for example <literal>$1.user_id</literal>.
489 490 491
        </para>

        <para>
492 493 494
         Only the user-defined attributes of a table row are accessible in a
	 rowtype variable, not OID or other system attributes (because the
	 row could be from a view).  The fields of the rowtype inherit the
Peter Eisentraut's avatar
Peter Eisentraut committed
495 496
	 table's field sizes or precision for <type>char()</type>
	 etc. data types.
497
        </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
498
<programlisting>
499 500
DECLARE
    users_rec users%ROWTYPE;
501
    user_id users.user_id%TYPE;
502 503 504
BEGIN
    user_id := users_rec.user_id;
    ...
505

506 507 508 509 510 511 512 513 514 515 516 517
create function cs_refresh_one_mv(integer) returns integer as '
   DECLARE
        key ALIAS FOR $1;
        table_data cs_materialized_views%ROWTYPE;
   BEGIN
        SELECT INTO table_data * FROM cs_materialized_views
               WHERE sort_key=key;

        IF NOT FOUND THEN
           RAISE EXCEPTION ''View '' || key || '' not found'';
           RETURN 0;
        END IF;
518

519 520 521 522 523
        -- The mv_name column of cs_materialized_views stores view
        -- names.
 
        TRUNCATE TABLE table_data.mv_name;
        INSERT INTO table_data.mv_name || '' '' || table_data.mv_query;
524

525 526
        return 1;
end;
Peter Eisentraut's avatar
Peter Eisentraut committed
527 528
' LANGUAGE 'plpgsql';
</programlisting>
529 530 531 532 533 534 535 536 537
       </listitem>
      </varlistentry>
     </variablelist>
    </sect3>

    <sect3 id="plpgsql-description-remaning-vars">
     <title>
      RENAME
     </title>
Peter Eisentraut's avatar
Peter Eisentraut committed
538

539 540 541 542 543
     <para>
      Using RENAME you can change the name of a variable, record
      or row. This is useful if NEW or OLD should be referenced
      by another name inside a trigger procedure.
     </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
544

545 546
     <para>
      Syntax and examples:
Peter Eisentraut's avatar
Peter Eisentraut committed
547
<programlisting>
548 549
RENAME <replaceable>oldname</replaceable> TO <replaceable>newname</replaceable>;

550 551
RENAME id TO user_id;
RENAME this_var TO that_var;
Peter Eisentraut's avatar
Peter Eisentraut committed
552 553
</programlisting>
     </para>
554
    </sect3>
555 556 557 558 559 560 561 562
   </sect2>

   <!-- **** PL/pgSQL expressions **** -->

   <sect2>
    <title>Expressions</title>

    <para>
563 564
     All expressions used in <application>PL/pgSQL</application> statements are processed using
     the executor of the server. Expressions that appear to contain
565 566
     constants may in fact require run-time evaluation
     (e.g. <literal>'now'</literal>  for the 
567
     <type>timestamp</type> type) so
568
     it is impossible for the <application>PL/pgSQL</application> parser
569 570
     to identify real constant values other than the NULL keyword. All
     expressions are evaluated internally by executing a query
Peter Eisentraut's avatar
Peter Eisentraut committed
571
<synopsis>
572
SELECT <replaceable>expression</replaceable>
Peter Eisentraut's avatar
Peter Eisentraut committed
573
</synopsis>
574
     using the <acronym>SPI</acronym> manager. In the expression, occurrences of variable
575 576
     identifiers are substituted by parameters and the actual values from
     the variables are passed to the executor in the parameter array. All
577
     expressions used in a <application>PL/pgSQL</application> function are only prepared and
578 579
     saved once.  The only exception to this rule is an EXECUTE statement
     if parsing of a query is needed each time it is encountered.
580
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
581

582 583 584 585
    <para>
     The type checking done by the <productname>Postgres</productname>
     main parser has some side
     effects to the interpretation of constant values. In detail there
586
     is a difference between what these two functions do:
587

Peter Eisentraut's avatar
Peter Eisentraut committed
588
<programlisting>
589
CREATE FUNCTION logfunc1 (text) RETURNS timestamp AS '
590 591 592 593 594 595 596
    DECLARE
        logtxt ALIAS FOR $1;
    BEGIN
        INSERT INTO logtable VALUES (logtxt, ''now'');
        RETURN ''now'';
    END;
' LANGUAGE 'plpgsql';
Peter Eisentraut's avatar
Peter Eisentraut committed
597
</programlisting>
598 599 600

     and

Peter Eisentraut's avatar
Peter Eisentraut committed
601
<programlisting>
602
CREATE FUNCTION logfunc2 (text) RETURNS timestamp AS '
603 604
    DECLARE
        logtxt ALIAS FOR $1;
605
        curtime timestamp;
606 607 608 609 610 611
    BEGIN
        curtime := ''now'';
        INSERT INTO logtable VALUES (logtxt, curtime);
        RETURN curtime;
    END;
' LANGUAGE 'plpgsql';
Peter Eisentraut's avatar
Peter Eisentraut committed
612
</programlisting>
613 614 615 616 617

     In the case of <function>logfunc1()</function>, the 
     <productname>Postgres</productname> main parser knows when 
     preparing the plan for the INSERT, that the string 
     <literal>'now'</literal> should be interpreted as 
618
     <type>timestamp</type> because the target field of <classname>logtable</classname>
619 620
     is of that type. Thus, it will make a constant from it at this
     time and this constant value is then used in all invocations of 
621
     <function>logfunc1()</function> during the lifetime of the
622 623
     backend. Needless to say that this isn't what the
     programmer wanted.
624
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
625

626
    <para>
627 628 629 630 631
     In the case of <function>logfunc2()</function>, the 
     <productname>Postgres</productname> main parser does not know
     what type <literal>'now'</literal> should become and therefore 
     it returns a data type of <type>text</type> containing the string 
     <literal>'now'</literal>. During the assignment
632 633
     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
634
     <function>text_out()</function> and <function>timestamp_in()</function>
635 636
     functions for the conversion.
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
637

638 639
    <para>
     This type checking done by the <productname>Postgres</productname> main
640
     parser got implemented after <application>PL/pgSQL</application> was nearly done.
641
     It is a difference between 6.3 and 6.4 and affects all functions
642
     using the prepared plan feature of the <acronym>SPI</acronym> manager.
643
     Using a local
644
     variable in the above manner is currently the only way in <application>PL/pgSQL</application> to get
645 646
     those values interpreted correctly.
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
647

648 649 650 651 652 653 654 655 656 657
    <para>
     If record fields are used in expressions or statements, the data types of
     fields should not change between calls of one and the same expression.
     Keep this in mind when writing trigger procedures that handle events
     for more than one table.
    </para>
   </sect2>

   <!-- **** PL/pgSQL statements **** -->

658 659
  <sect2>
   <title>Statements</title>
660

661
   <para>
662
    Anything not understood by the <application>PL/pgSQL</application> parser as specified below
663 664 665 666 667 668
    will be put into a query and sent down to the database engine
    to execute. The resulting query should not return any data.
   </para>
   
   <sect3 id="plpgsql-statements-assignment">
    <title>Assignment</title>
669
    <para>
670 671
     An assignment of a value to a variable or row/record field is
     written as:
Peter Eisentraut's avatar
Peter Eisentraut committed
672
<synopsis>
673
<replaceable>identifier</replaceable> := <replaceable>expression</replaceable>;
Peter Eisentraut's avatar
Peter Eisentraut committed
674
</synopsis>
675 676 677

     If the expressions result data type doesn't match the variables
     data type, or the variable has a size/precision that is known
678 679
     (as for <type>char(20)</type>), the result value will be implicitly cast by
     the <application>PL/pgSQL</application> bytecode interpreter using the result types output- and
680 681 682
     the variables type input-functions. Note that this could potentially
     result in runtime errors generated by the types input functions.
    </para>
683

Peter Eisentraut's avatar
Peter Eisentraut committed
684
<programlisting>
685 686
user_id := 20;
tax := subtotal * 0.06;
Peter Eisentraut's avatar
Peter Eisentraut committed
687
</programlisting>
688
   </sect3>
689

690 691
   <sect3 id="plpgsql-statements-calling-other-funcs">
    <title>Calling another function</title>
692

693 694 695 696
    <para>
     All functions defined in a <productname>Postgres</productname>
     database return a value. Thus, the normal way to call a function
     is to execute a SELECT query or doing an assignment (resulting
697
     in a <application>PL/pgSQL</application> internal SELECT). 
698
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
699

700 701 702 703
    <para>
     But there are cases where someone is not interested in the 
     function's result. In these cases, use the PERFORM
     statement.
Peter Eisentraut's avatar
Peter Eisentraut committed
704
<synopsis>
705
PERFORM <replaceable>query</replaceable>
Peter Eisentraut's avatar
Peter Eisentraut committed
706 707
</synopsis>
     This executes a <literal>SELECT <replaceable>query</replaceable></literal> over the
708 709 710
     <acronym>SPI manager</acronym> and discards the result. Identifiers like local
     variables are still substituted into parameters.
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
711
<programlisting>
712 713 714 715 716
PERFORM create_mv(''cs_session_page_requests_mv'',''
     select   session_id, page_id, count(*) as n_hits,
              sum(dwell_time) as dwell_time, count(dwell_time) as dwell_count
     from     cs_fact_table
     group by session_id, page_id '');
Peter Eisentraut's avatar
Peter Eisentraut committed
717
</programlisting>
718 719 720 721 722 723 724
   </sect3>
   
   <sect3 id="plpgsql-statements-executing-dyn-queries">
    <title>Executing dynamic queries</title>
    
    <para>
     Often times you will want to generate dynamic queries inside
725 726
     your <application>PL/pgSQL</application> functions. Or you have functions that will
     generate other functions. <application>PL/pgSQL</application> provides the EXECUTE
727 728
     statement for these occasions.
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
729

730
    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
731
<synopsis>
732
EXECUTE <replaceable class="command">query-string</replaceable>
Peter Eisentraut's avatar
Peter Eisentraut committed
733 734 735 736 737 738
</synopsis>
     where <replaceable>query-string</replaceable> is a string of type
     <type>text</type> containing the <replaceable>query</replaceable>
     to be executed.
    </para>

739 740
    <para>
    When working with dynamic queries you will have to face
741
    escaping of single quotes in <application>PL/pgSQL</>. Please refer to the
742
    table available at the <xref linkend="plpgsql-porting">
743 744 745
    for a detailed explanation that will save you some effort.
    </para>
     
Peter Eisentraut's avatar
Peter Eisentraut committed
746
    <para>
747
     Unlike all other queries in <application>PL/pgSQL</>, a
Peter Eisentraut's avatar
Peter Eisentraut committed
748 749 750 751 752 753 754 755
     <replaceable>query</replaceable> run by an EXECUTE statement is
     not prepared and saved just once during the life of the server.
     Instead, the <replaceable>query</replaceable> is prepared each
     time the statement is run. The
     <replaceable>query-string</replaceable> can be dynamically
     created within the procedure to perform actions on variable
     tables and fields.
    </para>
756
  
Peter Eisentraut's avatar
Peter Eisentraut committed
757 758 759 760 761 762
    <para>
     The results from SELECT queries are discarded by EXECUTE, and
     SELECT INTO is not currently supported within EXECUTE.  So, the
     only way to extract a result from a dynamically-created SELECT is
     to use the FOR ... EXECUTE form described later.
    </para>
763

Peter Eisentraut's avatar
Peter Eisentraut committed
764 765 766
    <para>
     An example:
<synopsis>
767 768 769 770 771
EXECUTE ''UPDATE tbl SET ''
        || quote_ident(fieldname)
        || '' = ''
        || quote_literal(newvalue)
        || '' WHERE ...'';
Peter Eisentraut's avatar
Peter Eisentraut committed
772
</synopsis>
773
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
774

775 776 777 778 779 780 781 782 783 784 785
    <para>
     This example shows use of the functions
     <function>quote_ident</function>(<type>TEXT</type>) and
     <function>quote_literal</function>(<type>TEXT</type>).
     Variables containing field and table identifiers should be
     passed to function <function>quote_ident()</function>.
     Variables containing literal elements of the dynamic query
     string should be passed to
     <function>quote_literal()</function>.  Both take the
     appropriate steps to return the input text enclosed in single
     or double quotes and with any embedded special characters.
Peter Eisentraut's avatar
Peter Eisentraut committed
786 787
    </para>

788
    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
789 790
     Here is a much larger example of a dynamic query and EXECUTE:
<programlisting>
791 792 793 794 795 796 797 798 799 800 801
CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS '
DECLARE
    referrer_keys RECORD;  -- Declare a generic record to be used in a FOR
    a_output varchar(4000);
BEGIN 
    a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar) 
                  RETURNS varchar AS '''' 
                     DECLARE 
                         v_host ALIAS FOR $1; 
                         v_domain ALIAS FOR $2; 
                         v_url ALIAS FOR $3; ''; 
802

803 804 805 806
    -- 
    -- Notice how we scan through the results of a query in a FOR loop
    -- using the FOR &lt;record&gt; construct.
    --
807

808 809 810 811 812 813 814 815 816 817 818 819 820
    FOR referrer_keys IN select * from cs_referrer_keys order by try_order LOOP
        a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '''''''''' 
                 || referrer_keys.key_string || '''''''''' then return '''''' 
                 || referrer_keys.referrer_type || ''''''; end if;''; 
    END LOOP; 
  
    a_output := a_output || '' return null; end; '''' language ''''plpgsql'''';''; 
 
    -- This works because we are not substituting any variables
    -- Otherwise it would fail. Look at PERFORM for another way to run functions
    
    EXECUTE a_output; 
end; 
Peter Eisentraut's avatar
Peter Eisentraut committed
821 822
' LANGUAGE 'plpgsql';
</programlisting>
823 824
    </para>
   </sect3>
825

Peter Eisentraut's avatar
Peter Eisentraut committed
826 827 828 829 830
   <sect3 id="plpgsql-statements-diagnostics">
    <title>Obtaining other results status</title>

    <para>
<synopsis>
831
GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>
Peter Eisentraut's avatar
Peter Eisentraut committed
832 833 834 835 836
</synopsis>

     This command allows retrieval of system status indicators.  Each
     <replaceable>item</replaceable> is a keyword identifying a state
     value to be assigned to the specified variable (which should be
837
     of the right data type to receive it).  The currently available
Peter Eisentraut's avatar
Peter Eisentraut committed
838 839 840 841 842 843 844 845
     status items are <varname>ROW_COUNT</>, the number of rows
     processed by the last <acronym>SQL</acronym> query sent down to
     the <acronym>SQL</acronym> engine; and <varname>RESULT_OID</>,
     the Oid of the last row inserted by the most recent
     <acronym>SQL</acronym> query.  Note that <varname>RESULT_OID</>
     is only useful after an INSERT query.
    </para>
   </sect3>
846 847 848
   
   <sect3 id="plpgsql-statements-returning">
    <title>Returning from a function</title>
849

850
    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
851
<synopsis>
852
RETURN <replaceable>expression</replaceable>
Peter Eisentraut's avatar
Peter Eisentraut committed
853 854 855 856 857 858 859 860 861 862 863 864 865
</synopsis>
     The function terminates and the value of
     <replaceable>expression</replaceable> will be returned to the
     upper executor. The return value of a function cannot be
     undefined. If control reaches the end of the top-level block of
     the function without hitting a RETURN statement, a runtime error
     will occur.
    </para>

    <para>
     The expressions result will be automatically casted into the
     function's return type as described for assignments.
    </para>
866 867
   </sect3>
  </sect2>
868

869
   <!-- **** PL/pgSQL Control Structures **** -->
870

871
  <sect2 id="plpgsql-description-control-structures">
872

873 874 875
   <title>Control Structures</title>
   <para>
    Control structures are probably the most useful (and
876
    important) part of PL/SQL. With <application>PL/pgSQL</>'s control structures,
877 878 879 880
    you can manipulate <productname>PostgreSQL</> data in a very
    flexible and powerful way. 
   </para>
    
Peter Eisentraut's avatar
Peter Eisentraut committed
881
   <sect3 id="plpgsql-description-conditionals">
882
    <title>Conditional Control: IF statements</title>
883 884

    <para>
Bruce Momjian's avatar
Bruce Momjian committed
885 886 887 888 889
	<function>IF</function> statements let you execute commands based on
      certain conditions. PL/PgSQL has four forms of IF: IF-THEN, IF-THEN-ELSE,
      IF-THEN-ELSE IF, IF-THEN-ELSIF-THEN-ELSE. NOTE: All PL/PgSQL IF statements need
	a corresponding <function>END IF</function> clause. With ELSE-IF statements,
      you need two: one for the first IF and one for the second (ELSE IF).
890 891 892 893
    </para>

    <variablelist>
     <varlistentry>
894 895 896
      <term>
       IF-THEN
      </term>
Peter Eisentraut's avatar
Peter Eisentraut committed
897

898 899
      <listitem>
       <para>
900 901 902 903
        IF-THEN statements is the simplest form of an IF. The
        statements between THEN and END IF will be executed if
        the condition is true. Otherwise, the statements
        following END IF will be executed.
Peter Eisentraut's avatar
Peter Eisentraut committed
904
<programlisting>
905 906 907
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
908
</programlisting>
909 910 911 912 913
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
914 915 916
      <term>
       IF-THEN-ELSE
      </term>
Peter Eisentraut's avatar
Peter Eisentraut committed
917

918 919
      <listitem>
       <para>
920 921 922
        IF-THEN-ELSE statements adds to IF-THEN by letting you
        specify the statements that should be executed if the
        condition evaluates to FALSE.
Peter Eisentraut's avatar
Peter Eisentraut committed
923
<programlisting>
924 925 926 927 928 929
IF parentid IS NULL or parentid = ''''
THEN 
    return fullname;
ELSE
    return hp_true_filename(parentid) || ''/'' || fullname;
END IF;
930

931 932 933 934 935 936 937

IF v_count > 0 THEN 
    INSERT INTO users_count(count) VALUES(v_count);
    return ''t'';
ELSE 
    return ''f'';
END IF;
Peter Eisentraut's avatar
Peter Eisentraut committed
938
</programlisting>
939
       </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
940

941
       <para>
942 943
        IF statements can be nested and in the following
        example:
Peter Eisentraut's avatar
Peter Eisentraut committed
944
<programlisting>
945 946 947 948 949 950 951
IF demo_row.sex = ''m'' THEN
  pretty_sex := ''man'';
ELSE
  IF demo_row.sex = ''f'' THEN
    pretty_sex := ''woman'';
  END IF;
END IF;
Peter Eisentraut's avatar
Peter Eisentraut committed
952
</programlisting>
953 954 955 956 957
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
958 959 960
      <term>
       IF-THEN-ELSE IF
      </term>
Peter Eisentraut's avatar
Peter Eisentraut committed
961

962 963
      <listitem>
       <para>
964
        When you use the <literal>ELSE IF</> statement, you are actually
965 966 967
        nesting an IF statement inside the ELSE
        statement. Thus you need one END IF statement for each
        nested IF and one for the parent IF-ELSE.
968
       </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
969

970 971
       <para>
        For example:
Peter Eisentraut's avatar
Peter Eisentraut committed
972
<programlisting>
973 974 975 976 977 978
IF demo_row.sex = ''m'' THEN
   pretty_sex := ''man'';
ELSE IF demo_row.sex = ''f'' THEN
        pretty_sex := ''woman'';
     END IF;
END IF;
Peter Eisentraut's avatar
Peter Eisentraut committed
979 980
</programlisting>
       </para>
981 982
      </listitem>
     </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016

     <varlistentry>
      <term>
       IF-THEN-ELSIF-ELSE
      </term>

      <listitem>
       <para>
        IF-THEN-ELSIF-ELSE allows you test multiple conditions
        in one statement. Internally it is handled as nested 
        IF-THEN-ELSE-IF-THEN commands. The optional ELSE
        branch is executed when none of the conditions are met.
       </para>

       <para>
        Here is an example:

<programlisting>
IF number = 0 THEN
    result := ''zero'';
ELSIF number &lt; 0 THEN
    result := ''negative'';
ELSIF number &gt; 0 THEN 
    result := ''negative'';
ELSE
    -- now it seems to be NULL
    result := ''NULL'';
END IF;
</programlisting>
       </para>
      </listitem>
     </varlistentry>


1017 1018 1019 1020 1021
    </variablelist>
   </sect3>

   <sect3 id="plpgsql-description-control-structures-loops">
    <title>Iterative Control: LOOP, WHILE, FOR and EXIT</title>
1022

1023 1024
    <para>
     With the LOOP, WHILE, FOR and EXIT statements, you can
1025 1026
     control the flow of execution of your <application>PL/pgSQL</application> program
     iteractively.
1027
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1028

1029
    <variablelist>
1030
     <varlistentry>
1031 1032 1033
      <term>
       LOOP
      </term>
Peter Eisentraut's avatar
Peter Eisentraut committed
1034

1035 1036
      <listitem>
       <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1037
<synopsis>
1038 1039 1040 1041
<optional>&lt;&lt;label&gt;&gt;</optional>
LOOP
    <replaceable>statements</replaceable>
END LOOP;
Peter Eisentraut's avatar
Peter Eisentraut committed
1042
</synopsis>
1043 1044 1045 1046
        An unconditional loop that must be terminated explicitly
        by an EXIT statement. The optional label can be used by
        EXIT statements of nested loops to specify which level of
        nesting should be terminated.
1047 1048 1049 1050 1051
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
1052 1053 1054
      <term>
       EXIT
      </term>
Peter Eisentraut's avatar
Peter Eisentraut committed
1055

1056 1057
      <listitem>
       <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1058
<synopsis>
1059
EXIT <optional> <replaceable>label</replaceable> </optional> <optional> WHEN <replaceable>expression</replaceable> </optional>;
Peter Eisentraut's avatar
Peter Eisentraut committed
1060
</synopsis>
1061 1062 1063 1064 1065 1066 1067 1068
        If no <replaceable>label</replaceable> is given,
        the innermost loop is terminated and the
        statement following END LOOP is executed next.
        If <replaceable>label</replaceable> is given, it
        must be the label of the current or an upper level of nested loop
        blocks. Then the named loop or block is terminated and control
        continues with the statement after the loops/blocks corresponding
        END.
1069
       </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1070

1071
       <para>
1072
        Examples:
Peter Eisentraut's avatar
Peter Eisentraut committed
1073
<programlisting>
1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091
LOOP
    -- some computations
    IF count > 0 THEN
        EXIT;  -- exit loop
    END IF;
END LOOP;

LOOP
    -- some computations
    EXIT WHEN count > 0;
END LOOP;

BEGIN
    -- some computations
    IF stocks > 100000 THEN
        EXIT;  -- illegal. Can't use EXIT outside of a LOOP
    END IF;
END;
Peter Eisentraut's avatar
Peter Eisentraut committed
1092
</programlisting>
1093 1094 1095 1096 1097
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
1098 1099 1100
      <term>
       WHILE
      </term>
Peter Eisentraut's avatar
Peter Eisentraut committed
1101

1102 1103
      <listitem>
       <para>
1104 1105 1106
        With the WHILE statement, you can loop through a
        sequence of statements as long as the evaluation of
        the condition expression is true.
Peter Eisentraut's avatar
Peter Eisentraut committed
1107
<synopsis>
1108 1109 1110 1111
<optional>&lt;&lt;label&gt;&gt;</optional>
WHILE <replaceable>expression</replaceable> LOOP
    <replaceable>statements</replaceable>
END LOOP;
Peter Eisentraut's avatar
Peter Eisentraut committed
1112
</synopsis>
1113
        For example:
Peter Eisentraut's avatar
Peter Eisentraut committed
1114
<programlisting>
1115 1116 1117 1118 1119 1120 1121
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- some computations here
END LOOP;

WHILE NOT boolean_expression LOOP
    -- some computations here
END LOOP;
Peter Eisentraut's avatar
Peter Eisentraut committed
1122
</programlisting>
1123 1124 1125 1126 1127
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
1128 1129 1130
      <term>
       FOR
      </term>
Peter Eisentraut's avatar
Peter Eisentraut committed
1131

1132 1133
      <listitem>
       <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1134
<synopsis>
1135 1136 1137 1138
<optional>&lt;&lt;label&gt;&gt;</optional>
FOR <replaceable>name</replaceable> IN <optional> REVERSE </optional> <replaceable>expression</replaceable> .. <replaceable>expression</replaceable> LOOP
    <replaceable>statements</replaceable>
END LOOP;
Peter Eisentraut's avatar
Peter Eisentraut committed
1139
</synopsis>
1140 1141 1142 1143 1144 1145
        A loop that iterates over a range of integer values. The variable
        <replaceable>name</replaceable> is automatically created as type
        integer and exists only inside the loop. The two expressions giving
        the lower and upper bound of the range are evaluated only when entering
        the loop. The iteration step is always 1.
       </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1146

1147
       <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1148 1149 1150 1151
        Some examples of FOR loops (see <xref
        linkend="plpgsql-description-records"> for iterating over
        records in FOR loops):
<programlisting>
1152 1153 1154 1155 1156 1157 1158 1159 1160
FOR i IN 1..10 LOOP
  -- some expressions here

    RAISE NOTICE 'i is %',i;
END LOOP;

FOR i IN REVERSE 1..10 LOOP
    -- some expressions here
END LOOP;
Peter Eisentraut's avatar
Peter Eisentraut committed
1161
</programlisting>
1162 1163 1164 1165
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
1166 1167 1168 1169
      </sect3>
   </sect2>

   <!-- **** PL/pgSQL records **** -->
1170

1171 1172
  <sect2 id="plpgsql-description-records">
   <title>Working with RECORDs</title>
Peter Eisentraut's avatar
Peter Eisentraut committed
1173

1174 1175 1176 1177 1178 1179 1180 1181
   <para>
    Records are similar to rowtypes, but they have no predefined structure.
    They are used in selections and FOR loops to hold one actual
    database row from a SELECT operation.
   </para>

   <sect3 id="plpgsql-description-records-declaration">
    <title>Declaration</title>
Peter Eisentraut's avatar
Peter Eisentraut committed
1182

1183
    <para>
1184 1185 1186 1187
     One variables of type RECORD can be used for different
     selections. Accessing a record or an attempt to assign
     a value to a record field when there is no actual row in it results
     in a runtime error. They can be declared like this:
1188
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1189

1190
    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1191
<synopsis>
1192
<replaceable>name</replaceable> RECORD;
Peter Eisentraut's avatar
Peter Eisentraut committed
1193
</synopsis>
1194 1195
    </para>
   </sect3>
Peter Eisentraut's avatar
Peter Eisentraut committed
1196

1197 1198
   <sect3 id="plpgsql-description-records-assignment">
    <title>Assignments</title>
Peter Eisentraut's avatar
Peter Eisentraut committed
1199

1200 1201 1202
    <para>
     An assignment of a complete selection into a record or row can
     be done by:
Peter Eisentraut's avatar
Peter Eisentraut committed
1203
<synopsis>
1204
SELECT  INTO <replaceable>target</replaceable> <replaceable>expressions</replaceable> FROM ...;
Peter Eisentraut's avatar
Peter Eisentraut committed
1205 1206 1207 1208 1209 1210
</synopsis>
     <replaceable>target</replaceable> can be a record, a row variable
     or a comma separated list of variables and
     record-/row-fields. Note that this is quite different from
     Postgres' normal interpretation of SELECT INTO, which is that the
     INTO target is a newly created table.  (If you want to create a
1211
     table from a SELECT result inside a <application>PL/pgSQL</application> function, use the
Peter Eisentraut's avatar
Peter Eisentraut committed
1212
     equivalent syntax <command>CREATE TABLE AS SELECT</command>.)
1213
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1214

1215 1216 1217 1218 1219 1220
    <para>
     If a row or a variable list is used as target, the selected values
     must exactly match the structure of the target(s) or a runtime error
     occurs. The FROM keyword can be followed by any valid qualification,
     grouping, sorting etc. that can be given for a SELECT statement.
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1221

1222 1223
    <para>
     Once a record or row has been assigned to a RECORD variable,
1224
     you can use the <literal>.</> (dot) notation to access fields in that
1225
     record:
Peter Eisentraut's avatar
Peter Eisentraut committed
1226
<programlisting>
1227 1228 1229 1230 1231 1232 1233
DECLARE
    users_rec RECORD;
    full_name varchar;
BEGIN
    SELECT INTO users_rec * FROM users WHERE user_id=3;

  full_name := users_rec.first_name || '' '' || users_rec.last_name;
Peter Eisentraut's avatar
Peter Eisentraut committed
1234
</programlisting>
1235
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1236

1237
    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1238 1239 1240
     There is a special variable named FOUND of type
     <type>boolean</type> that can be used immediately after a SELECT
     INTO to check if an assignment had success.
1241
  
Peter Eisentraut's avatar
Peter Eisentraut committed
1242
<programlisting>
1243 1244 1245 1246
SELECT INTO myrec * FROM EMP WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION ''employee % not found'', myname;
END IF;
Peter Eisentraut's avatar
Peter Eisentraut committed
1247
</programlisting>
1248 1249 1250 1251 1252 1253

     You can also use the IS NULL (or ISNULL) conditionals to
     test for NULLity of a RECORD/ROW. If the selection returns
     multiple rows, only the first is moved into the target
     fields. All others are silently discarded.
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1254

1255
    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1256
<programlisting>
1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268
DECLARE
    users_rec RECORD;
    full_name varchar;
BEGIN
    SELECT INTO users_rec * FROM users WHERE user_id=3;

    IF users_rec.homepage IS NULL THEN
        -- user entered no homepage, return "http://"

        return ''http://'';
    END IF;
END;
Peter Eisentraut's avatar
Peter Eisentraut committed
1269
</programlisting>
1270 1271
    </para>
   </sect3>
Peter Eisentraut's avatar
Peter Eisentraut committed
1272

1273 1274 1275 1276 1277 1278 1279
   <sect3 id="plpgsql-description-records-iterating">
    <title>Iterating Through Records</title>

    <para>
     Using a special type of FOR loop, you can iterate through
     the results of a query and manipulate that data
     accordingly. The syntax is as follow:
Peter Eisentraut's avatar
Peter Eisentraut committed
1280
<synopsis>
1281 1282 1283 1284
<optional>&lt;&lt;label&gt;&gt;</optional>
FOR <replaceable>record | row</replaceable> IN <replaceable>select_clause</replaceable> LOOP
    <replaceable>statements</replaceable>
END LOOP;
Peter Eisentraut's avatar
Peter Eisentraut committed
1285
</synopsis>
1286 1287 1288 1289
     The record or row is assigned all the rows 
     resulting from the select clause and the loop body executed 
     for each. Here is an example:
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1290

1291
    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1292
<programlisting>
1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316
create function cs_refresh_mviews () returns integer as '
DECLARE
     mviews RECORD;

     -- Instead, if you did:
     -- mviews  cs_materialized_views%ROWTYPE;
     -- this record would ONLY be usable for the cs_materialized_views table

BEGIN
     PERFORM cs_log(''Refreshing materialized views...'');

     FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP

         -- Now "mviews" has one record from cs_materialized_views

         PERFORM cs_log(''Refreshing materialized view '' || mview.mv_name || ''...'');
         TRUNCATE TABLE mview.mv_name;
         INSERT INTO mview.mv_name || '' '' || mview.mv_query;
     END LOOP;

     PERFORM cs_log(''Done refreshing materialized views.'');
     return 1;
end;
' language 'plpgsql';
Peter Eisentraut's avatar
Peter Eisentraut committed
1317
</programlisting>
1318

Peter Eisentraut's avatar
Peter Eisentraut committed
1319 1320
     If the loop is terminated with an EXIT statement, the last
     assigned row is still accessible after the loop.
1321
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1322 1323 1324 1325 1326

    <para>
     The FOR-IN EXECUTE statement is another way to iterate over
     records:
<synopsis>
1327 1328 1329 1330
<optional>&lt;&lt;label&gt;&gt;</optional>
FOR <replaceable>record | row</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> LOOP 
    <replaceable>statements</replaceable>
END LOOP;
Peter Eisentraut's avatar
Peter Eisentraut committed
1331 1332 1333 1334 1335 1336 1337 1338
</synopsis>
     This is like the previous form, except that the source SELECT
     statement is specified as a string expression, which is evaluated
     and re-planned on each entry to the FOR loop.  This allows the
     programmer to choose the speed of a pre-planned query or the
     flexibility of a dynamic query, just as with a plain EXECUTE
     statement.
    </para>
1339 1340 1341 1342
   </sect3>
  </sect2>

  <sect2 id="plpgsql-description-aborting-and-messages">
Peter Eisentraut's avatar
Peter Eisentraut committed
1343
   <title>Aborting and Messages</title>
1344 1345 1346

   <para>
    Use the RAISE statement to throw messages into the 
1347
    <productname>Postgres</productname> <function>elog</function> mechanism.
1348

Peter Eisentraut's avatar
Peter Eisentraut committed
1349
<synopsis>
1350
RAISE <replaceable class="parameter">level</replaceable> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">variable</replaceable> <optional>...</optional></optional>;
Peter Eisentraut's avatar
Peter Eisentraut committed
1351 1352
</synopsis>

1353 1354 1355
    Possible levels are DEBUG (write the message into the postmaster log),
    NOTICE (write the message into the postmaster log and forward it to
    the client application) and EXCEPTION (raise an error,
1356
    aborting the transaction).
1357 1358 1359 1360 1361 1362
    Inside the format string, <literal>%</literal> is replaced by the next
    optional argument's external representation.
    Write <literal>%%</literal> to emit a literal <literal>%</literal>.
    Note that the optional arguments must presently
    be simple variables, not expressions, and the format must be a simple
    string literal.
1363
   </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1364

1365 1366 1367 1368 1369 1370
   <!--
   This example should work, but does not:
   	RAISE NOTICE ''Id number '' || key || '' not found!'';
   Put it back when we allow non-string-literal formats.
    -->

1371
   <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1372
<programlisting>
1373
RAISE NOTICE ''Calling cs_create_job(%)'',v_job_id;
Peter Eisentraut's avatar
Peter Eisentraut committed
1374
</programlisting>
1375
    In this example, the value of v_job_id will replace the % in the
1376 1377
    string.
   </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1378

1379
   <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1380
<programlisting>
1381
RAISE EXCEPTION ''Inexistent ID --> %'',user_id;
Peter Eisentraut's avatar
Peter Eisentraut committed
1382
</programlisting>
1383
    This will abort the transaction with the given error message.
1384 1385
   </para>
  </sect2>   
1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396

   <!-- **** PL/pgSQL exceptions **** -->

   <sect2>
    <title>Exceptions</title>

    <para>
     <productname>Postgres</productname> does not have a very smart
     exception handling model. Whenever the parser, planner/optimizer
     or executor decide that a statement cannot be processed any longer,
     the whole transaction gets aborted and the system jumps back
1397
     into the main loop to get the next query from the client application.
1398
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1399

1400 1401
    <para>
     It is possible to hook into the error mechanism to notice that this
1402
     happens. But currently it is impossible to tell what really
1403 1404 1405 1406 1407
     caused the abort (input/output conversion error, floating point
     error, parse error). And it is possible that the database backend
     is in an inconsistent state at this point so returning to the upper
     executor or issuing more commands might corrupt the whole database.
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1408

1409
    <para>
1410
     Thus, the only thing <application>PL/pgSQL</application> currently does when it encounters
1411
     an abort during execution of a function or trigger
1412
     procedure is to write some additional NOTICE level log messages
1413 1414 1415 1416
     telling in which function and where (line number and type of
     statement) this happened.
    </para>
   </sect2>
1417
 </sect1>
1418 1419


1420
   <!-- **** PL/pgSQL trigger procedures **** -->
1421

1422
 <sect1 id="plpgsql-trigger">
Peter Eisentraut's avatar
Peter Eisentraut committed
1423
  <title>Trigger Procedures</title>
1424

Peter Eisentraut's avatar
Peter Eisentraut committed
1425
  <para>
1426
   <application>PL/pgSQL</application> can be used to define trigger procedures. They are created
Peter Eisentraut's avatar
Peter Eisentraut committed
1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438
   with the usual <command>CREATE FUNCTION</command> command as a function with no
   arguments and a return type of <type>OPAQUE</type>.
  </para>

  <para>
   There are some <productname>Postgres</productname> specific details
   in functions used as trigger procedures.
  </para>

  <para>
   First they have some special variables created automatically in the 
   top-level blocks declaration section. They are
1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449
   
   <variablelist>
    <varlistentry>
     <term><varname>NEW</varname></term>
     <listitem>
      <para>
       Data type <type>RECORD</type>; variable holding the new database row on INSERT/UPDATE
       operations on ROW level triggers.
      </para>
     </listitem>
    </varlistentry>
1450

1451 1452 1453 1454 1455 1456 1457 1458 1459
    <varlistentry>
     <term><varname>OLD</varname></term>
     <listitem>
      <para>
       Data type <type>RECORD</type>; variable holding the old database row on UPDATE/DELETE
       operations on ROW level triggers.
      </para>
     </listitem>
    </varlistentry>
1460

1461 1462 1463 1464 1465 1466 1467 1468 1469
    <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>
1470

1471 1472 1473 1474 1475 1476 1477 1478 1479 1480
    <varlistentry>
     <term><varname>TG_WHEN</varname></term>
     <listitem>
      <para>
       Data type <type>text</type>; a string of either 
              <literal>BEFORE</literal> or <literal>AFTER</literal> 
              depending on the triggers definition.
      </para>
     </listitem>
    </varlistentry>
1481

1482 1483 1484 1485 1486 1487 1488 1489 1490 1491
    <varlistentry>
     <term><varname>TG_LEVEL</varname></term>
     <listitem>
      <para>
       Data type <type>text</type>; a string of either 
              <literal>ROW</literal> or <literal>STATEMENT</literal> depending on the
       triggers definition.
      </para>
     </listitem>
    </varlistentry>
1492

1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503
    <varlistentry>
     <term><varname>TG_OP</varname></term>
     <listitem>
      <para>
       Data type <type>text</type>; a string of 
              <literal>INSERT</literal>, <literal>UPDATE</literal> 
              or <literal>DELETE</literal> telling
       for which operation the trigger is actually fired.
      </para>
     </listitem>
    </varlistentry>
1504

1505 1506 1507 1508 1509 1510 1511 1512 1513
    <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>
1514

1515 1516 1517 1518 1519 1520 1521 1522 1523
    <varlistentry>
     <term><varname>TG_RELNAME</varname></term>
     <listitem>
      <para>
       Data type <type>name</type>; the name of the table that caused the trigger
       invocation.
      </para>
     </listitem>
    </varlistentry>
1524

1525 1526 1527 1528 1529 1530 1531 1532 1533
    <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>
1534

1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546
    <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.
       The index counts from 0 and can be given as an expression. Invalid
       indices (&lt; 0 or &gt;= tg_nargs) result in a NULL value.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
Peter Eisentraut's avatar
Peter Eisentraut committed
1547
  </para>
1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559

   <para>
    Second they must return either NULL or a record/row containing
    exactly the structure of the table the trigger was fired for.
    Triggers fired AFTER might always return a NULL value with no
    effect. Triggers fired BEFORE signal the trigger manager
    to skip the operation for this actual row when returning NULL.
    Otherwise, the returned record/row replaces the inserted/updated
    row in the operation. It is possible to replace single values directly
    in NEW and return that or to build a complete new record/row to
    return.
   </para>
1560

1561
   <example>
1562
    <title>A <application>PL/pgSQL</application> Trigger Procedure Example</title>
1563 1564

    <para>
1565
     This trigger ensures, that any time a row is inserted or updated
1566
     in the table, the current user name and time are stamped into the
1567 1568 1569
     row. And it ensures that an employees name is given and that the
     salary is a positive value.

Peter Eisentraut's avatar
Peter Eisentraut committed
1570
<programlisting>
1571 1572
CREATE TABLE emp (
    empname text,
1573 1574
    salary integer,
    last_date timestamp,
Peter Eisentraut's avatar
Peter Eisentraut committed
1575 1576
    last_user text
);
1577

Bruce Momjian's avatar
Bruce Momjian committed
1578
CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS '
1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname ISNULL THEN
            RAISE EXCEPTION ''empname cannot be NULL value'';
        END IF;
        IF NEW.salary ISNULL THEN
            RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
        END IF;

        -- Who works for us when she must pay for?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := ''now'';
1595
        NEW.last_user := current_user;
1596 1597 1598 1599 1600 1601
        RETURN NEW;
    END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
Peter Eisentraut's avatar
Peter Eisentraut committed
1602
</programlisting>
1603
    </para>
1604
   </example>
Peter Eisentraut's avatar
Peter Eisentraut committed
1605
  </sect1>
1606 1607 1608 1609 1610 1611 1612 1613

  <!-- **** PL/pgSQL Examples **** -->

  <sect1 id="plpgsql-examples">
   <title>Examples</title>

   <para>
    Here are only a few functions to demonstrate how easy it is to
1614
    write <application>PL/pgSQL</>
1615
    functions. For more complex examples the programmer
1616
    might look at the regression test for <application>PL/pgSQL</>.
1617 1618 1619
   </para>

   <para>
1620
    One painful detail in writing functions in <application>PL/pgSQL</application> is the handling
1621 1622 1623 1624 1625 1626 1627 1628
    of single quotes. The function's source text on <command>CREATE FUNCTION</command> must
    be a literal string. Single quotes inside of literal strings must be
    either doubled or quoted with a backslash. We are still looking for
    an elegant alternative. In the meantime, doubling the single quotes
    as in the examples below should be used. Any solution for this
    in future versions of <productname>Postgres</productname> will be
    forward compatible.
   </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1629

1630 1631
   <para>
    For a detailed explanation and examples of how to escape single
Peter Eisentraut's avatar
Peter Eisentraut committed
1632
    quotes in different situations, please see <xref linkend="plpgsql-quote">.
1633 1634 1635
   </para>

   <example>
1636
    <title>A Simple <application>PL/pgSQL</application> Function to Increment an Integer</title>
1637 1638

    <para>
1639
     The following two <application>PL/pgSQL</application> functions are identical to their
1640 1641 1642 1643 1644
     counterparts from the C language function discussion. This
     function receives an <type>integer</type> and increments it by
     one, returning the incremented value.
    </para>

Peter Eisentraut's avatar
Peter Eisentraut committed
1645
<programlisting>
1646 1647 1648 1649 1650
CREATE FUNCTION add_one (integer) RETURNS integer AS '
    BEGIN
        RETURN $1 + 1;
    END;
' LANGUAGE 'plpgsql';
Peter Eisentraut's avatar
Peter Eisentraut committed
1651
</programlisting>
1652 1653 1654
   </example>

   <example>
1655
    <title>A Simple <application>PL/pgSQL</application> Function to Concatenate Text</title>
1656 1657 1658 1659 1660 1661

    <para>
     This function receives two <type>text</type> parameters and
     returns the result of concatenating them.
    </para>

Peter Eisentraut's avatar
Peter Eisentraut committed
1662
<programlisting>
1663 1664 1665 1666 1667
CREATE FUNCTION concat_text (text, text) RETURNS text AS '
    BEGIN
        RETURN $1 || $2;
    END;
' LANGUAGE 'plpgsql';
Peter Eisentraut's avatar
Peter Eisentraut committed
1668
</programlisting>
1669 1670 1671
   </example>

   <example>
1672
    <title>A <application>PL/pgSQL</application> Function on Composite Type</title>
1673 1674

    <para>
1675
     In this example, we take <literal>EMP</> (a table) and an
Peter Eisentraut's avatar
Peter Eisentraut committed
1676
     <type>integer</type> as arguments to our function, which returns
1677 1678
     a <type>boolean</type>. If the <structfield>salary</> field of the <structname>EMP</> table is
     <literal>NULL</literal>, we return <literal>f</>. Otherwise we compare with
Peter Eisentraut's avatar
Peter Eisentraut committed
1679 1680
     that field with the <type>integer</type> passed to the function
     and return the <type>boolean</type> result of the comparison (t
1681
     or f). This is the <application>PL/pgSQL</application> equivalent to the example from the C
Peter Eisentraut's avatar
Peter Eisentraut committed
1682
     functions.
1683
    </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1684 1685

<programlisting>
1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696
CREATE FUNCTION c_overpaid (EMP, integer) RETURNS boolean AS '
    DECLARE
        emprec ALIAS FOR $1;
        sallim ALIAS FOR $2;
    BEGIN
        IF emprec.salary ISNULL THEN
            RETURN ''f'';
        END IF;
        RETURN emprec.salary > sallim;
    END;
' LANGUAGE 'plpgsql';
Peter Eisentraut's avatar
Peter Eisentraut committed
1697 1698
</programlisting>
   </example>
1699
  </sect1>
1700

1701 1702
  <!-- **** Porting from Oracle PL/SQL **** -->

1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728
 <sect1 id="plpgsql-porting">

  <sect1info>
   <date>
    February 2001
   </date>
   <author>
    <firstname>Roberto</firstname>
    <surname>Mello</surname>
    <affiliation>
     <address>
      <email>rmello@fslc.usu.edu</email>
     </address>
    </affiliation>
   </author>

   <legalnotice>
    <para>
     Except for portions of this document quoted from other sources,
     this document is licensed under the BSD License.
    </para>
   </legalnotice>
  </sect1info> 

  <title>Porting from Oracle PL/SQL</title>

1729 1730 1731 1732 1733 1734 1735 1736
  <indexterm>
   <primary>Oracle</primary>
  </indexterm>

  <indexterm>
   <primary>PL/SQL</primary>
  </indexterm>

1737 1738 1739 1740 1741 1742 1743 1744 1745
  <note>
   <title>Author</title>
   <para>
    Roberto Mello (<email>rmello@fslc.usu.edu</email>)
   </para>
  </note>

  <para>
   This section explains differences between Oracle's PL/SQL and
1746
   PostgreSQL's <application>PL/pgSQL</application> languages in the hopes of helping developers
1747 1748 1749 1750 1751 1752 1753 1754 1755
   port applications from Oracle to PostgreSQL.  Most of the code here
   is from the <ulink url="http://www.arsdigita.com">ArsDigita</ulink>
   <ulink url="http://www.arsdigita.com/asj/clickstream">Clickstream
   module</ulink> that I ported to PostgreSQL when I took an
   internship with <ulink url="http://www.openforce.net">OpenForce
   Inc.</ulink> in the Summer of 2000.
  </para>

  <para>
1756
   <application>PL/pgSQL</application> is similar to PL/SQL in many aspects. It is a block
1757 1758
   structured, imperative language (all variables have to be
   declared). PL/SQL has many more features than its PostgreSQL
1759
   counterpart, but <application>PL/pgSQL</application> allows for a great deal of functionality
1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798
   and it is being improved constantly.
  </para>

  <sect2>
   <title>Main Differences</title>

   <para>
    Some things you should keep in mind when porting from Oracle to PostgreSQL:

    <itemizedlist>
     <listitem>
      <para>
       No default parameters in PostgreSQL.
      </para>
     </listitem>

     <listitem>
      <para>
       You can overload functions in PostgreSQL. This is often used to work 
       around the lack of default parameters.
      </para>
     </listitem>

     <listitem>
      <para>
       Assignments, loops and conditionals are similar. 
      </para>
     </listitem>

     <listitem>
      <para>
       No need for cursors in PostgreSQL, just put the query in the FOR 
       statement (see example below)
      </para>
     </listitem>

     <listitem>
      <para>
       In PostgreSQL you <emphasis>need</emphasis> to escape single
Peter Eisentraut's avatar
Peter Eisentraut committed
1799
       quotes. See <xref linkend="plpgsql-quote">.
1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811
      </para>
     </listitem>
    </itemizedlist>
   </para>

   <sect3 id="plpgsql-quote">
    <title>Quote Me on That: Escaping Single Quotes</title>

    <para>
     In PostgreSQL you need to escape single quotes inside your
     function definition. This can lead to quite amusing code at
     times, especially if you are creating a function that generates
1812
     other function(s), as in 
Peter Eisentraut's avatar
Peter Eisentraut committed
1813
     <xref linkend="plpgsql-porting-nastyquote">.  
1814
     One thing to keep in mind
1815 1816 1817 1818 1819 1820
     when escaping lots of single quotes is that, except for the
     beginning/ending quotes, all the others will come in even
     quantity.
    </para>

    <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1821
     <xref linkend="plpgsql-quoting-table"> gives the scoop.  (You'll
1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893
     love this little chart.)
    </para>

    <table id="plpgsql-quoting-table">
     <title>Single Quotes Escaping Chart</title>

     <tgroup cols="4">
      <thead>
       <row>
        <entry>No. of Quotes</entry>
        <entry>Usage</entry>
        <entry>Example</entry>
        <entry>Result</entry>
       </row>
      </thead>

      <tbody>
       <row>
        <entry>1</entry>
        <entry>To begin/terminate function bodies</entry>
        <entry><programlisting>
CREATE FUNCTION foo() RETURNS INTEGER AS '...'
LANGUAGE 'plpgsql';
</programlisting></entry>
        <entry>as is</entry>
       </row>

       <row>
        <entry>2</entry>
        <entry>In assignments, SELECTs, to delimit strings, etc.</entry>
        <entry><programlisting>
a_output := ''Blah'';
SELECT * FROM users WHERE f_name=''foobar'';
</programlisting></entry>
        <entry><literal>SELECT * FROM users WHERE f_name='foobar';</literal></entry>
       </row>

       <row>
        <entry>4</entry>
        <entry>
         When you need two single quotes in your resulting string
         without terminating that string.
        </entry>
        <entry><programlisting>
a_output := a_output || '' AND name 
    LIKE ''''foobar'''' AND ...''
</programlisting></entry>
        <entry><literal>AND name LIKE 'foobar' AND ...</literal></entry>
       </row>

       <row>
        <entry>6</entry>
        <entry>
         When you want double quotes in your resulting string
         <emphasis>and</emphasis> terminate that string.
        </entry>
        <entry><programlisting>
a_output := a_output || '' AND name 
    LIKE ''''foobar''''''
</programlisting></entry>
        <entry>
         <literal>AND name LIKE 'foobar'</literal>
        </entry>
       </row>

       <row>
        <entry>10</entry>
        <entry>
         When you want two single quotes in the resulting string
         (which accounts for 8 quotes) <emphasis>and</emphasis>
         terminate that string (2 more).  You will probably only need
         that if you were using a function to generate other functions
Peter Eisentraut's avatar
Peter Eisentraut committed
1894
         (like in <xref linkend="plpgsql-porting-nastyquote">).
1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939
        </entry>
        <entry><programlisting>
a_output := a_output || '' if v_'' || 
    referrer_keys.kind || '' like '''''''''' 
    || referrer_keys.key_string || '''''''''' 
    then return ''''''  || referrer_keys.referrer_type 
    || ''''''; end if;''; 
</programlisting></entry>
        <entry>
         <literal>if v_<...> like ''<...>'' then return ''<...>''; end if;</literal>
        </entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </sect3>
  </sect2>  

  <sect2 id="plpgsql-porting-functions">
   <title>
    Porting Functions
   </title>

   <example>
    <title>
     A Simple Function
    </title>

    <para>
     Here is an Oracle function:
<programlisting>
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name IN varchar, v_version IN varchar)
RETURN varchar IS
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
/
SHOW ERRORS;
</programlisting>
    </para>

    <para>
1940
     Let's go through this function and see the differences to <application>PL/pgSQL</>:
1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982

     <itemizedlist>
      <listitem>
       <para>
        The <literal>OR REPLACE</literal> clause is not allowed. You
        will have to explicitly drop the function before creating it
        to achieve similar results.
       </para>
      </listitem>

      <listitem>
       <para>
        <productname>PostgreSQL</productname> does not have named
        parameters. You have to explicitly alias them inside your
        function.
       </para>
      </listitem>

      <listitem>
       <para>
        Oracle can have <literal>IN</literal>, <literal>OUT</literal>,
        and <literal>INOUT</literal> parameters passed to functions.
        The <literal>INOUT</literal>, for example, means that the
        parameter will receive a value and return another. PostgreSQL
        only has <quote>IN</quote> parameters and functions can return
        only a single value.
       </para>
      </listitem>

      <listitem>
       <para>
        The <literal>RETURN</literal> key word in the function
        prototype (not the function body) becomes
        <literal>RETURNS</literal> in PostgreSQL.
       </para>
      </listitem>

      <listitem>
       <para>
        On PostgreSQL functions are created using single quotes as
        delimiters, so you have to escape single quotes inside your
        functions (which can be quite annoying at times; see <xref
Peter Eisentraut's avatar
Peter Eisentraut committed
1983
        linkend="plpgsql-quote">).
1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002
       </para>
      </listitem>

      <listitem>
       <para>
        The <literal>/show errors</literal> command does not exist in
        PostgreSQL.
       </para>
      </listitem>
     </itemizedlist>
    </para>

    <para>
     So let's see how this function would be look like ported to
     PostgreSQL:

<programlisting>
DROP FUNCTION cs_fmt_browser_version(varchar, varchar);
CREATE FUNCTION cs_fmt_browser_version(varchar, varchar)
Tatsuo Ishii's avatar
Tatsuo Ishii committed
2003
RETURNS varchar AS '
2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 2061 2062 2063 2064 2065 2066 2067 2068 2069 2070 2071 2072 2073 2074 2075 2076 2077 2078 2079 2080 2081 2082 2083 2084 2085 2086 2087 2088 2089 2090 2091 2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 2102
DECLARE
    v_name ALIAS FOR $1;
    v_version ALIAS FOR $2;
BEGIN
    IF v_version IS NULL THEN
        return v_name;
    END IF;
    RETURN v_name || ''/'' || v_version;
END;
' LANGUAGE 'plpgsql';
</programlisting>
    </para>
   </example>
 
   <example id="plpgsql-porting-nastyquote">
    <title>
     A Function that Creates Another Function
    </title>

    <para>
     The following procedure grabs rows from a
     <literal>SELECT</literal> statement and builds a large function
     with the results in <literal>IF</literal> statements, for the
     sake of efficiency. Notice particularly the differences in
     cursors, <literal>FOR</literal> loops, and the need to escape
     single quotes in PostgreSQL.

<programlisting>
create or replace procedure cs_update_referrer_type_proc is 
    cursor referrer_keys is 
        select * from cs_referrer_keys 
        order by try_order;

    a_output varchar(4000); 
begin 
    a_output := 'create or replace function cs_find_referrer_type(v_host IN varchar, v_domain IN varchar, 
v_url IN varchar) return varchar is begin'; 

    for referrer_key in referrer_keys loop 
        a_output := a_output || ' if v_' || referrer_key.kind || ' like ''' || 
referrer_key.key_string || ''' then return ''' || referrer_key.referrer_type || 
'''; end if;'; 
    end loop; 

    a_output := a_output || ' return null; end;'; 
    execute immediate a_output; 
end; 
/ 
show errors
</programlisting>
    </para>

    <para>
     Here is how this function would end up in PostgreSQL:

<programlisting>
CREATE FUNCTION cs_update_referrer_type_proc() RETURNS integer AS '
DECLARE
    referrer_keys RECORD;  -- Declare a generic record to be used in a FOR
    a_output varchar(4000);
BEGIN 
    a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar) 
                  RETURNS varchar AS '''' 
                     DECLARE 
                         v_host ALIAS FOR $1; 
                         v_domain ALIAS FOR $2; 
                         v_url ALIAS FOR $3; ''; 

    -- 
    -- Notice how we scan through the results of a query in a FOR loop
    -- using the FOR &lt;record&gt; construct.
    --

    FOR referrer_keys IN select * from cs_referrer_keys order by try_order LOOP
        a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '''''''''' 
                 || referrer_keys.key_string || '''''''''' then return '''''' 
                 || referrer_keys.referrer_type || ''''''; end if;''; 
    END LOOP; 
  
    a_output := a_output || '' return null; end; '''' language ''''plpgsql'''';''; 
 
    -- This works because we are not substituting any variables
    -- Otherwise it would fail. Look at PERFORM for another way to run functions
    
    EXECUTE a_output; 
end; 
' LANGUAGE 'plpgsql';
</programlisting>
    </para>
   </example>
 
   <example>
    <title>
     A Procedure with a lot of String Manipulation and OUT Parameters
    </title>

    <para>
     The following Oracle PL/SQL procedure is used to parse a URL and
     return several elements (host, path and query). It is an
2103
     procedure because in <application>PL/pgSQL</application> functions only one value can be returned
Peter Eisentraut's avatar
Peter Eisentraut committed
2104
     (see <xref linkend="plpgsql-porting-procedures">).  In
2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115 2116 2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 2127 2128 2129 2130 2131 2132 2133 2134 2135 2136 2137 2138 2139 2140 2141 2142 2143 2144 2145 2146 2147 2148 2149 2150 2151 2152 2153
     PostgreSQL, one way to work around this is to split the procedure
     in three different functions: one to return the host, another for
     the path and another for the query.
    </para>

<programlisting>
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
is
    a_pos1 integer;
    a_pos2 integer;
begin
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//'); -- PostgreSQL doesn't have an instr function

    if a_pos1 = 0 then
        return;
    end if;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    if a_pos2 = 0 then
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        return;
    end if;

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

    if a_pos1 = 0 then
        v_path := substr(v_url, a_pos2);
        return;
    end if;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
end;
/
show errors;
</programlisting>

    <para>
     Here is how this procedure could be translated for PostgreSQL:

<programlisting>
Peter Eisentraut's avatar
Peter Eisentraut committed
2154 2155 2156 2157 2158 2159 2160 2161 2162 2163 2164 2165 2166 2167 2168 2169 2170 2171 2172 2173 2174 2175 2176 2177 2178 2179 2180 2181
drop function cs_parse_url_host(varchar); 
create function cs_parse_url_host(varchar) returns varchar as ' 
declare 
    v_url ALIAS FOR $1; 
    v_host varchar; 
    v_path varchar; 
    a_pos1 integer; 
    a_pos2 integer; 
    a_pos3 integer; 
begin 
    v_host := NULL; 
    a_pos1 := instr(v_url,''//''); 

    if a_pos1 = 0 then 
        return '''';  -- Return a blank
    end if; 

    a_pos2 := instr(v_url,''/'',a_pos1 + 2); 
    if a_pos2 = 0 then 
        v_host := substr(v_url, a_pos1 + 2); 
        v_path := ''/''; 
        return v_host; 
    end if; 

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2 ); 
    return v_host; 
end; 
' language 'plpgsql';
2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192
</programlisting>
    </para>
   </example>

   <note>
    <para>
     PostgreSQL does not have an <function>instr</function> function,
     so you can work around it using a combination of other functions.
     I got tired of doing this and created my own
     <function>instr</function> functions that behave exactly like
     Oracle's (it makes life easier). See the <xref
Peter Eisentraut's avatar
Peter Eisentraut committed
2193
     linkend="plpgsql-porting-appendix"> for the code.
2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 2204 2205
    </para>
   </note>
  </sect2>

  <sect2 id="plpgsql-porting-procedures">
   <title>
    Procedures
   </title>

   <para>
    Oracle procedures give a little more flexibility to the developer
    because nothing needs to be explicitly returned, but it can be
2206
    through the use of <literal>INOUT</> or <literal>OUT</> parameters.
2207 2208 2209 2210 2211 2212 2213 2214 2215
   </para>

   <para>
    An example:

<programlisting>
create or replace procedure cs_create_job(v_job_id in integer)
is
    a_running_job_count integer;
Peter Eisentraut's avatar
Peter Eisentraut committed
2216
    pragma autonomous_transaction;<co id="co.plpgsql-porting-pragma">
2217
begin
Peter Eisentraut's avatar
Peter Eisentraut committed
2218
    lock table cs_jobs in exclusive mode;<co id="co.plpgsql-porting-locktable">
2219 2220 2221 2222 2223

    select count(*) into a_running_job_count from cs_jobs
    where end_stamp is null;

    if a_running_job_count > 0 then
Peter Eisentraut's avatar
Peter Eisentraut committed
2224
        commit; -- free lock<co id="co.plpgsql-porting-commit">
2225 2226 2227 2228 2229 2230 2231 2232
        raise_application_error(-20000, 'Unable to create a new job: a job is currently running.');
    end if;

    delete from cs_active_job;
    insert into cs_active_job(job_id) values(v_job_id);

    begin
        insert into cs_jobs(job_id, start_stamp) values(v_job_id, sysdate);
Peter Eisentraut's avatar
Peter Eisentraut committed
2233
        exception when dup_val_on_index then null; -- don't worry if it already exists<co id="co.plpgsql-porting-exception">
2234 2235 2236 2237 2238 2239 2240 2241 2242 2243 2244 2245 2246 2247 2248 2249 2250 2251 2252 2253 2254 2255
    end;
    commit;
end;
/
show errors
</programlisting>
   </para>

   <para>
    Procedures like this can be easily converted into PostgreSQL
    functions returning an <type>INTEGER</type>. This procedure in
    particular is interesting because it can teach us some things:

    <calloutlist>
     <callout arearefs="co.plpgsql-porting-pragma">
      <para>
       There is no <literal>pragma</literal> statement in PostgreSQL.
      </para>
     </callout>

     <callout arearefs="co.plpgsql-porting-locktable">
      <para>
2256
       If you do a <literal>LOCK TABLE</literal> in <application>PL/pgSQL</>, the lock
2257 2258 2259 2260 2261 2262
       will not be released until the calling transaction is finished.
      </para>
     </callout>

     <callout arearefs="co.plpgsql-porting-commit">
      <para>
2263
       You also cannot have transactions in <application>PL/pgSQL</application> procedures. The
2264 2265 2266 2267 2268 2269 2270 2271 2272 2273 2274 2275 2276 2277 2278 2279 2280
       entire function (and other functions called from therein) is
       executed in a transaction and PostgreSQL rolls back the results if
       something goes wrong. Therefore only one
       <literal>BEGIN</literal> statement is allowed.
      </para>
     </callout>

     <callout arearefs="co.plpgsql-porting-exception">
      <para>
       The exception when would have to be replaced by an
       <literal>IF</literal> statement.
      </para>
     </callout>
    </calloutlist>
   </para>

   <para>
2281
    So let's see one of the ways we could port this procedure to <application>PL/pgSQL</>:
2282 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292 2293 2294 2295 2296 2297 2298 2299 2300 2301 2302 2303 2304 2305 2306 2307

<programlisting>
drop function cs_create_job(integer);
create function cs_create_job(integer) returns integer as ' declare
    v_job_id alias for $1;
    a_running_job_count integer;
    a_num integer;
    -- pragma autonomous_transaction;
begin
    lock table cs_jobs in exclusive mode;
    select count(*) into a_running_job_count from cs_jobs where end_stamp is null;

    if a_running_job_count > 0 then
        -- commit; -- free lock
        raise exception ''Unable to create a new job: a job is currently running.'';
    end if;

    delete from cs_active_job;
    insert into cs_active_job(job_id) values(v_job_id);

    SELECT count(*) into a_num FROM cs_jobs WHERE job_id=v_job_id;
    IF NOT FOUND THEN  -- If nothing was returned in the last query
        -- This job is not in the table so lets insert it.
        insert into cs_jobs(job_id, start_stamp) values(v_job_id, sysdate());
        return 1;
    ELSE
Peter Eisentraut's avatar
Peter Eisentraut committed
2308
        raise NOTICE ''Job already running.'';<co id="co.plpgsql-porting-raise">
2309 2310 2311 2312 2313 2314 2315 2316 2317 2318
    END IF;

    return 0;
end;
' language 'plpgsql';
</programlisting>

    <calloutlist>
     <callout arearefs="co.plpgsql-porting-raise">
      <para>
2319
       Notice how you can raise notices (or errors) in <application>PL/pgSQL</>.
2320 2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 2346 2347 2348 2349 2350 2351 2352 2353 2354 2355 2356 2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 2399 2400 2401 2402 2403 2404 2405 2406 2407 2408 2409 2410 2411 2412 2413 2414 2415 2416 2417 2418
      </para>
     </callout>
    </calloutlist>
   </para>
  </sect2>

  <sect2 id="plpgsql-porting-packages">
   <title>
    Packages
   </title>

   <note>
    <para>
     I haven't done much with packages myself, so if there are
     mistakes here, please let me know.
    </para>
   </note>

   <para>
    Packages are a way Oracle gives you to encapsulate PL/SQL
    statements and functions into one entity, like Java classes, where
    you define methods and objects. You can access these
    objects/methods with a <quote><literal>.</literal></quote>
    (dot). Here is an example of an Oracle package from ACS 4 (the
    <ulink url="http://www.arsdigita.com/doc/">ArsDigita Community
    System</ulink>):

<programlisting>
create or replace package body acs
as
  function add_user (
    user_id     in users.user_id%TYPE default null,
    object_type     in acs_objects.object_type%TYPE
               default 'user',
    creation_date   in acs_objects.creation_date%TYPE
               default sysdate,
    creation_user   in acs_objects.creation_user%TYPE
               default null,
    creation_ip     in acs_objects.creation_ip%TYPE default null,
  ...
  ) return users.user_id%TYPE
  is
    v_user_id       users.user_id%TYPE;
    v_rel_id        membership_rels.rel_id%TYPE;
  begin
    v_user_id := acs_user.new (user_id, object_type, creation_date,
                creation_user, creation_ip, email,
    ...
    return v_user_id;
  end;
end acs;
/
show errors
</programlisting>
   </para>

   <para>
    We port this to PostgreSQL by creating the different objects of
    the Oracle package as functions with a standard naming
    convention. We have to pay attention to some other details, like
    the lack of default parameters in PostgreSQL functions. The above
    package would become something like this:
 
<programlisting>
CREATE FUNCTION acs__add_user(integer,integer,varchar,datetime,integer,integer,...)
RETURNS integer AS '
DECLARE
    user_id ALIAS FOR $1;
    object_type ALIAS FOR $2;
    creation_date ALIAS FOR $3;
    creation_user ALIAS FOR $4;
    creation_ip ALIAS FOR $5;
    ...
    v_user_id users.user_id%TYPE;
    v_rel_id membership_rels.rel_id%TYPE;
BEGIN
    v_user_id := acs_user__new(user_id,object_type,creation_date,creation_user,creation_ip, ...);
    ...

    return v_user_id;
END;
' LANGUAGE 'plpgsql';
</programlisting>
   </para>
  </sect2>

  <sect2 id="plpgsql-porting-other">
   <title>
    Other Things to Watch For
   </title>

   <sect3>
    <title>EXECUTE</title>

    <para>
     The PostgreSQL version of <literal>EXECUTE</literal> works
     nicely, but you have to remember to use
     <function>quote_literal(TEXT)</function> and
     <function>quote_string(TEXT)</function> as described in <xref
Peter Eisentraut's avatar
Peter Eisentraut committed
2419
     linkend="plpgsql-statements-executing-dyn-queries">.  Constructs of the type
2420 2421 2422 2423 2424 2425
     <literal>EXECUTE ''SELECT * from $1'';</literal> will not work
     unless you use these functions.
    </para>
   </sect3>

   <sect3 id="plpgsql-porting-optimization">
2426
    <title>Optimizing <application>PL/pgSQL</application> Functions</title>
2427 2428 2429 2430 2431 2432

    <para>
     PostgreSQL gives you two function creation modifiers to optimize
     execution: <literal>iscachable</literal> (function always returns
     the same result when given the same arguments) and
     <literal>isstrict</literal> (function returns NULL if any
2433 2434
     argument is NULL).  Consult the <command>CREATE
     FUNCTION</command> reference for details.
2435 2436 2437 2438
    </para>

    <para>
     To make use of these optimization attributes, you have to use the
2439 2440
     <literal>WITH</literal> modifier in your <command>CREATE
     FUNCTION</command> statement.  Something like:
2441 2442 2443 2444 2445 2446 2447 2448 2449 2450 2451 2452 2453 2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475

<programlisting>
CREATE FUNCTION foo(...) RETURNS integer AS '
...
' LANGUAGE 'plpgsql'
WITH (isstrict, iscachable);
</programlisting>
    </para>
   </sect3>
  </sect2>

  <sect2 id="plpgsql-porting-appendix">
   <title>
    Appendix
   </title>

   <sect3>
    <title>
     Code for my <function>instr</function> functions
    </title>

    <comment>
     This function should probably be integrated into the core.
    </comment>

<programlisting>
--
-- instr functions that mimic Oracle's counterpart
-- Syntax: instr(string1,string2,[n],[m]) where [] denotes optional params.
-- 
-- 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).
--
-- by Roberto Mello (rmello@fslc.usu.edu)
2476
-- modified by Robert Gaszewski (graszew@poland.com)
2477 2478 2479
-- Licensed under the GPL v2 or later.
--

2480 2481
DROP FUNCTION instr(varchar,varchar);
CREATE FUNCTION instr(varchar,varchar) RETURNS integer AS '
2482 2483 2484
DECLARE
    pos integer;
BEGIN
2485
    pos:= instr($1,$2,1);
2486 2487
    RETURN pos;
END;
2488 2489 2490 2491 2492 2493 2494 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 2521 2522 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532
' language 'plpgsql';


DROP FUNCTION instr(varchar,varchar,integer);
CREATE FUNCTION instr(varchar,varchar,integer) RETURNS integer AS '
DECLARE
    string ALIAS FOR $1;
    string_to_search ALIAS FOR $2;
    beg_index ALIAS FOR $3;
    pos integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    length integer;
    ss_length integer;
BEGIN
    IF beg_index > 0 THEN

       temp_str := substring(string FROM beg_index);
       pos := position(string_to_search IN temp_str);

       IF pos = 0 THEN
	         RETURN 0;
	     ELSE
	         RETURN pos + beg_index - 1;
	     END IF;
    ELSE
       ss_length := char_length(string_to_search);
       length := char_length(string);
       beg := length + beg_index - ss_length + 2;

       WHILE beg > 0 LOOP

           temp_str := substring(string FROM beg FOR ss_length);
	         pos := position(string_to_search IN temp_str);

	         IF pos > 0 THEN
		           RETURN beg;
	         END IF;

	         beg := beg - 1;
       END LOOP;
       RETURN 0;
    END IF;
END;
' language 'plpgsql';
2533

2534 2535 2536 2537 2538 2539
--
-- Written by Robert Gaszewski (graszew@poland.com)
-- Licensed under the GPL v2 or later.
--
DROP FUNCTION instr(varchar,varchar,integer,integer);
CREATE FUNCTION instr(varchar,varchar,integer,integer) RETURNS integer AS '
2540 2541 2542 2543
DECLARE
    string ALIAS FOR $1;
    string_to_search ALIAS FOR $2;
    beg_index ALIAS FOR $3;
2544
    occur_index ALIAS FOR $4;
2545
    pos integer NOT NULL DEFAULT 0;
2546
    occur_number integer NOT NULL DEFAULT 0;
2547 2548
    temp_str varchar;
    beg integer;
2549
    i integer;
2550
    length integer;
2551
    ss_length integer;
2552 2553
BEGIN
    IF beg_index > 0 THEN
2554 2555
        beg := beg_index;
        temp_str := substring(string FROM beg_index);
2556

2557 2558 2559 2560 2561 2562 2563 2564 2565 2566 2567
        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;
2568 2569 2570 2571

        IF pos = 0 THEN
            RETURN 0;
        ELSE
2572
            RETURN beg;
2573 2574
        END IF;
    ELSE
2575
        ss_length := char_length(string_to_search);
2576
        length := char_length(string);
2577
        beg := length + beg_index - ss_length + 2;
2578

2579 2580
        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
2581 2582
            pos := position(string_to_search IN temp_str);

2583 2584 2585 2586 2587 2588 2589
            IF pos > 0 THEN
                occur_number := occur_number + 1;

                IF occur_number = occur_index THEN
                    RETURN beg;
                END IF;
            END IF;
2590 2591 2592 2593 2594 2595 2596

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    END IF;
END;
2597
' language 'plpgsql';
Peter Eisentraut's avatar
Peter Eisentraut committed
2598
</programlisting>
2599 2600 2601 2602 2603 2604
   </sect3>
  </sect2>
  
 </sect1>

</chapter>
2605 2606 2607 2608 2609 2610 2611 2612 2613 2614 2615 2616 2617

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
2618
sgml-local-catalogs:("/usr/lib/sgml/catalog")
2619 2620 2621
sgml-local-ecat-files:nil
End:
-->