<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML
><HEAD
><TITLE
>PL/pgSQL - SQL Procedural Language</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 7.4.1 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Server Programming"
HREF="server-programming.html"><LINK
REL="PREVIOUS"
TITLE="Procedural Languages"
HREF="xplang.html"><LINK
REL="NEXT"
TITLE="Tips for Developing in PL/pgSQL"
HREF="plpgsql-development-tips.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
NAME="creation"
CONTENT="2003-12-22T03:48:47"></HEAD
><BODY
CLASS="CHAPTER"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 7.4.1 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="xplang.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="xplang.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="pltcl.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="plpgsql-development-tips.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="CHAPTER"
><H1
><A
NAME="PLPGSQL"
></A
>Chapter 37. <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> - <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> Procedural Language</H1
><DIV
CLASS="TOC"
><DL
><DT
><B
>Table of Contents</B
></DT
><DT
>37.1. <A
HREF="plpgsql.html#PLPGSQL-OVERVIEW"
>Overview</A
></DT
><DD
><DL
><DT
>37.1.1. <A
HREF="plpgsql.html#PLPGSQL-ADVANTAGES"
>Advantages of Using <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
></A
></DT
><DT
>37.1.2. <A
HREF="plpgsql.html#PLPGSQL-ARGS-RESULTS"
>Supported Argument and Result Data Types</A
></DT
></DL
></DD
><DT
>37.2. <A
HREF="plpgsql-development-tips.html"
>Tips for Developing in <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
></A
></DT
><DT
>37.3. <A
HREF="plpgsql-structure.html"
>Structure of <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
></A
></DT
><DT
>37.4. <A
HREF="plpgsql-declarations.html"
>Declarations</A
></DT
><DD
><DL
><DT
>37.4.1. <A
HREF="plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES"
>Aliases for Function Parameters</A
></DT
><DT
>37.4.2. <A
HREF="plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE"
>Copying Types</A
></DT
><DT
>37.4.3. <A
HREF="plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES"
>Row Types</A
></DT
><DT
>37.4.4. <A
HREF="plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS"
>Record Types</A
></DT
><DT
>37.4.5. <A
HREF="plpgsql-declarations.html#PLPGSQL-DECLARATION-RENAMING-VARS"
><TT
CLASS="LITERAL"
>RENAME</TT
></A
></DT
></DL
></DD
><DT
>37.5. <A
HREF="plpgsql-expressions.html"
>Expressions</A
></DT
><DT
>37.6. <A
HREF="plpgsql-statements.html"
>Basic Statements</A
></DT
><DD
><DL
><DT
>37.6.1. <A
HREF="plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT"
>Assignment</A
></DT
><DT
>37.6.2. <A
HREF="plpgsql-statements.html#PLPGSQL-SELECT-INTO"
><TT
CLASS="COMMAND"
>SELECT INTO</TT
></A
></DT
><DT
>37.6.3. <A
HREF="plpgsql-statements.html#PLPGSQL-STATEMENTS-PERFORM"
>Executing an Expression or Query With No Result</A
></DT
><DT
>37.6.4. <A
HREF="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN"
>Executing Dynamic Commands</A
></DT
><DT
>37.6.5. <A
HREF="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS"
>Obtaining the Result Status</A
></DT
></DL
></DD
><DT
>37.7. <A
HREF="plpgsql-control-structures.html"
>Control Structures</A
></DT
><DD
><DL
><DT
>37.7.1. <A
HREF="plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING"
>Returning From a Function</A
></DT
><DT
>37.7.2. <A
HREF="plpgsql-control-structures.html#PLPGSQL-CONDITIONALS"
>Conditionals</A
></DT
><DT
>37.7.3. <A
HREF="plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS"
>Simple Loops</A
></DT
><DT
>37.7.4. <A
HREF="plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING"
>Looping Through Query Results</A
></DT
></DL
></DD
><DT
>37.8. <A
HREF="plpgsql-cursors.html"
>Cursors</A
></DT
><DD
><DL
><DT
>37.8.1. <A
HREF="plpgsql-cursors.html#PLPGSQL-CURSOR-DECLARATIONS"
>Declaring Cursor Variables</A
></DT
><DT
>37.8.2. <A
HREF="plpgsql-cursors.html#PLPGSQL-CURSOR-OPENING"
>Opening Cursors</A
></DT
><DT
>37.8.3. <A
HREF="plpgsql-cursors.html#PLPGSQL-CURSOR-USING"
>Using Cursors</A
></DT
></DL
></DD
><DT
>37.9. <A
HREF="plpgsql-errors-and-messages.html"
>Errors and Messages</A
></DT
><DT
>37.10. <A
HREF="plpgsql-trigger.html"
>Trigger Procedures</A
></DT
><DT
>37.11. <A
HREF="plpgsql-porting.html"
>Porting from <SPAN
CLASS="PRODUCTNAME"
>Oracle</SPAN
> PL/SQL</A
></DT
><DD
><DL
><DT
>37.11.1. <A
HREF="plpgsql-porting.html#AEN32376"
>Porting Examples</A
></DT
><DT
>37.11.2. <A
HREF="plpgsql-porting.html#PLPGSQL-PORTING-OTHER"
>Other Things to Watch For</A
></DT
><DT
>37.11.3. <A
HREF="plpgsql-porting.html#PLPGSQL-PORTING-APPENDIX"
>Appendix</A
></DT
></DL
></DD
></DL
></DIV
><A
NAME="AEN31291"
></A
><P
> <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> is a loadable procedural
language for the <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> database
system. The design goals of <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> were to create
a loadable procedural language that
<P
></P
></P><UL
><LI
><P
> can be used to create functions and trigger procedures,
</P
></LI
><LI
><P
> adds control structures to the <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> language,
</P
></LI
><LI
><P
> can perform complex computations,
</P
></LI
><LI
><P
> inherits all user-defined types, functions, and operators,
</P
></LI
><LI
><P
> can be defined to be trusted by the server,
</P
></LI
><LI
><P
> is easy to use.
</P
></LI
></UL
><P>
</P
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="PLPGSQL-OVERVIEW"
>37.1. Overview</A
></H1
><P
> The <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> call handler parses the function's source text and
produces an internal binary instruction tree the first time the
function is called (within each session). The instruction tree
fully translates the
<SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> statement structure, but individual
<ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> expressions and <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> commands
used in the function are not translated immediately.
</P
><P
> As each expression and <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> command is first
used in the function, the <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> interpreter
creates a prepared execution plan (using the
<ACRONYM
CLASS="ACRONYM"
>SPI</ACRONYM
> manager's <CODE
CLASS="FUNCTION"
>SPI_prepare</CODE
>
and <CODE
CLASS="FUNCTION"
>SPI_saveplan</CODE
>
functions).<A
NAME="AEN31324"
></A
> Subsequent visits to that expression or command
reuse the prepared plan. Thus, a function with conditional code
that contains many statements for which execution plans might be
required will only prepare and save those plans that are really
used during the lifetime of the database connection. This can
substantially reduce the total amount of time required to parse,
and generate execution plans for the statements in a
<SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> function. A disadvantage is that errors
in a specific expression or command may not be detected until that
part of the function is reached in execution.
</P
><P
> Once <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> has made an execution plan for a particular
command in a function, it will reuse that plan for the life of the
database connection. This is usually a win for performance, but it
can cause some problems if you dynamically
alter your database schema. For example:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE FUNCTION populate() RETURNS integer AS '
DECLARE
-- declarations
BEGIN
PERFORM my_function();
END;
' LANGUAGE plpgsql;</PRE
><P>
If you execute the above function, it will reference the OID for
<CODE
CLASS="FUNCTION"
>my_function()</CODE
> in the execution plan produced for
the <TT
CLASS="COMMAND"
>PERFORM</TT
> statement. Later, if you
drop and recreate <CODE
CLASS="FUNCTION"
>my_function()</CODE
>, then
<CODE
CLASS="FUNCTION"
>populate()</CODE
> will not be able to find
<CODE
CLASS="FUNCTION"
>my_function()</CODE
> anymore. You would then have to
recreate <CODE
CLASS="FUNCTION"
>populate()</CODE
>, or at least start a new
database session so that it will be compiled afresh. Another way
to avoid this problem is to use <TT
CLASS="COMMAND"
>CREATE OR REPLACE
FUNCTION</TT
> when updating the definition of
<CODE
CLASS="FUNCTION"
>my_function</CODE
> (when a function is
<SPAN
CLASS="QUOTE"
>"replaced"</SPAN
>, its OID is not changed).
</P
><P
> Because <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> saves execution plans
in this way, SQL commands that appear directly in a
<SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> function must refer to the
same tables and columns on every execution; that is, you cannot use
a parameter as the name of a table or column in an SQL command. To get
around this restriction, you can construct dynamic commands using
the <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> <TT
CLASS="COMMAND"
>EXECUTE</TT
>
statement --- at the price of constructing a new execution plan on
every execution.
</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> The <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
>
<TT
CLASS="COMMAND"
>EXECUTE</TT
> statement is not related to the
<TT
CLASS="COMMAND"
>EXECUTE</TT
> statement supported by the
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> server. The server's
<TT
CLASS="COMMAND"
>EXECUTE</TT
> statement cannot be used within
<SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> functions (and is not needed).
</P
></BLOCKQUOTE
></DIV
><P
> Except for input/output conversion and calculation functions
for user-defined types, anything that can be defined in C language
functions can also be done with <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
>.
For example, it is possible to
create complex conditional computation functions and later use
them to define operators or use them in index expressions.
</P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="PLPGSQL-ADVANTAGES"
>37.1.1. Advantages of Using <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
></A
></H2
><P
> <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> is the language <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
(and most other relational databases) use as query language. It's
portable and easy to learn. But every <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
>
statement must be executed individually by the database server.
</P
><P
> That means that your client application must send each query to
the database server, wait for it to be processed, receive the
results, do some computation, then send other queries to the
server. All this incurs interprocess communication and may also
incur network overhead if your client is on a different machine
than the database server.
</P
><P
> With <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> you can group a block of computation and a
series of queries <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>inside</I
></SPAN
> 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. This can make for a
considerable performance increase.
</P
><P
> Also, with <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> you can use all
the data types, operators and functions of SQL.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="PLPGSQL-ARGS-RESULTS"
>37.1.2. Supported Argument and Result Data Types</A
></H2
><P
> Functions written in <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> can accept
as arguments any scalar or array data type supported by the server,
and they can return a result of any of these types. They can also
accept or return any composite type (row type) specified by name.
It is also possible to declare a <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
>
function as returning <TT
CLASS="TYPE"
>record</TT
>, which means that the result
is a row type whose columns are determined by specification in the
calling query, as discussed in <A
HREF="queries-table-expressions.html#QUERIES-TABLEFUNCTIONS"
>Section 7.2.1.4</A
>.
</P
><P
> <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> functions may also be declared to accept
and return the polymorphic types
<TT
CLASS="TYPE"
>anyelement</TT
> and <TT
CLASS="TYPE"
>anyarray</TT
>. The actual
data types handled by a polymorphic function can vary from call to
call, as discussed in <A
HREF="extend-type-system.html#EXTEND-TYPES-POLYMORPHIC"
>Section 33.2.5</A
>.
An example is shown in <A
HREF="plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES"
>Section 37.4.1</A
>.
</P
><P
> <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> functions can also be declared to return
a <SPAN
CLASS="QUOTE"
>"set"</SPAN
>, or table, of any data type they can return a single
instance of. Such a function generates its output by executing
<TT
CLASS="LITERAL"
>RETURN NEXT</TT
> for each desired element of the result set.
</P
><P
> Finally, a <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> function may be declared to return
<TT
CLASS="TYPE"
>void</TT
> if it has no useful return value.
</P
></DIV
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="xplang.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="plpgsql-development-tips.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Procedural Languages</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="server-programming.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Tips for Developing in <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
></TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
