<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Basic Statements</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.0.11 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="PL/pgSQL - SQL Procedural Language"
HREF="plpgsql.html"><LINK
REL="PREVIOUS"
TITLE="Expressions"
HREF="plpgsql-expressions.html"><LINK
REL="NEXT"
TITLE="Control Structures"
HREF="plpgsql-control-structures.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2007-02-02T03:57:22"></HEAD
><BODY
CLASS="SECT1"
><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 8.0.11 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="plpgsql-expressions.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="plpgsql.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 35. <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> - <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> Procedural Language</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="plpgsql.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="plpgsql-control-structures.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="PLPGSQL-STATEMENTS"
>35.6. Basic Statements</A
></H1
><P
> In this section and the following ones, we describe all the statement
types that are explicitly understood by
<SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
>.
Anything not recognized as one of these statement types is presumed
to be an SQL command and is sent to the main database engine to execute
(after substitution of any <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> variables
used in the statement). Thus,
for example, the SQL commands <TT
CLASS="COMMAND"
>INSERT</TT
>, <TT
CLASS="COMMAND"
>UPDATE</TT
>, and
<TT
CLASS="COMMAND"
>DELETE</TT
> may be considered to be statements of
<SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
>, but they are not specifically
listed here.
</P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="PLPGSQL-STATEMENTS-ASSIGNMENT"
>35.6.1. Assignment</A
></H2
><P
> An assignment of a value to a variable or row/record field is
written as:
</P><PRE
CLASS="SYNOPSIS"
><TT
CLASS="REPLACEABLE"
><I
>identifier</I
></TT
> := <TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
>;</PRE
><P>
As explained above, the expression in such a statement is evaluated
by means of an SQL <TT
CLASS="COMMAND"
>SELECT</TT
> command sent to the main
database engine. The expression must yield a single value.
</P
><P
> If the expression's result data type doesn't match the variable's
data type, or the variable has a specific size/precision
(like <TT
CLASS="TYPE"
>char(20)</TT
>), the result value will be implicitly
converted by the <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> interpreter using
the result type's output-function and
the variable type's input-function. Note that this could potentially
result in run-time errors generated by the input function, if the
string form of the result value is not acceptable to the input function.
</P
><P
> Examples:
</P><PRE
CLASS="PROGRAMLISTING"
>user_id := 20;
tax := subtotal * 0.06;</PRE
><P>
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="PLPGSQL-SELECT-INTO"
>35.6.2. <TT
CLASS="COMMAND"
>SELECT INTO</TT
></A
></H2
><A
NAME="AEN32754"
></A
><P
> The result of a <TT
CLASS="COMMAND"
>SELECT</TT
> command yielding multiple columns (but
only one row) can be assigned to a record variable, row-type
variable, or list of scalar variables. This is done by:
</P><PRE
CLASS="SYNOPSIS"
>SELECT INTO <TT
CLASS="REPLACEABLE"
><I
>target</I
></TT
> <TT
CLASS="REPLACEABLE"
><I
>select_expressions</I
></TT
> FROM ...;</PRE
><P>
where <TT
CLASS="REPLACEABLE"
><I
>target</I
></TT
> can be a record variable, a row
variable, or a comma-separated list of simple variables and
record/row fields. The <TT
CLASS="REPLACEABLE"
><I
>select_expressions</I
></TT
>
and the remainder of the command are the same as in regular SQL.
</P
><P
> Note that this is quite different from
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>'s normal interpretation of
<TT
CLASS="COMMAND"
>SELECT INTO</TT
>, where the <TT
CLASS="LITERAL"
>INTO</TT
> target
is a newly created table. If you want to create a table from a
<TT
CLASS="COMMAND"
>SELECT</TT
> result inside a
<SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> function, use the syntax
<TT
CLASS="COMMAND"
>CREATE TABLE ... AS SELECT</TT
>.
</P
><P
> If a row or a variable list is used as target, the selected values
must exactly match the structure of the target, or a run-time error
occurs. When a record variable is the target, it automatically
configures itself to the row type of the query result columns.
</P
><P
> Except for the <TT
CLASS="LITERAL"
>INTO</TT
> clause, the <TT
CLASS="COMMAND"
>SELECT</TT
>
statement is the same as a normal SQL <TT
CLASS="COMMAND"
>SELECT</TT
> command
and can use its full power.
</P
><P
> The <TT
CLASS="LITERAL"
>INTO</TT
> clause can appear almost anywhere in the
<TT
CLASS="COMMAND"
>SELECT</TT
> statement. Customarily it is written
either just after <TT
CLASS="LITERAL"
>SELECT</TT
> as shown above, or
just before <TT
CLASS="LITERAL"
>FROM</TT
> — that is, either just before
or just after the list of <TT
CLASS="REPLACEABLE"
><I
>select_expressions</I
></TT
>.
</P
><P
> If the query returns zero rows, null values are assigned to the
target(s). If the query returns multiple rows, the first
row is assigned to the target(s) and the rest are discarded.
(Note that <SPAN
CLASS="QUOTE"
>"the first row"</SPAN
> is not well-defined unless you've
used <TT
CLASS="LITERAL"
>ORDER BY</TT
>.)
</P
><P
> You can check the special <TT
CLASS="LITERAL"
>FOUND</TT
> variable (see
<A
HREF="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS"
>Section 35.6.6</A
>) after a
<TT
CLASS="COMMAND"
>SELECT INTO</TT
> statement to determine whether the
assignment was successful, that is, at least one row was was returned by
the query. For example:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT INTO myrec * FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
END IF;</PRE
><P>
</P
><P
> To test for whether a record/row result is null, you can use the
<TT
CLASS="LITERAL"
>IS NULL</TT
> conditional. There is, however, no
way to tell whether any additional rows might have been
discarded. Here is an example that handles the case where no
rows have been returned:
</P><PRE
CLASS="PROGRAMLISTING"
>DECLARE
users_rec RECORD;
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;</PRE
><P>
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="PLPGSQL-STATEMENTS-PERFORM"
>35.6.3. Executing an Expression or Query With No Result</A
></H2
><P
> Sometimes one wishes to evaluate an expression or query but
discard the result (typically because one is calling a function
that has useful side-effects but no useful result value). To do
this in <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
>, use the
<TT
CLASS="COMMAND"
>PERFORM</TT
> statement:
</P><PRE
CLASS="SYNOPSIS"
>PERFORM <TT
CLASS="REPLACEABLE"
><I
>query</I
></TT
>;</PRE
><P>
This executes <TT
CLASS="REPLACEABLE"
><I
>query</I
></TT
> and discards the
result. Write the <TT
CLASS="REPLACEABLE"
><I
>query</I
></TT
> the same
way as you would in an SQL <TT
CLASS="COMMAND"
>SELECT</TT
> command, but replace the
initial keyword <TT
CLASS="COMMAND"
>SELECT</TT
> with <TT
CLASS="COMMAND"
>PERFORM</TT
>.
<SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> variables will be
substituted into the query as usual. Also, the special variable
<TT
CLASS="LITERAL"
>FOUND</TT
> is set to true if the query produced at
least one row or false if it produced no rows.
</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> One might expect that <TT
CLASS="COMMAND"
>SELECT</TT
> with no
<TT
CLASS="LITERAL"
>INTO</TT
> clause would accomplish this result, but at
present the only accepted way to do it is
<TT
CLASS="COMMAND"
>PERFORM</TT
>.
</P
></BLOCKQUOTE
></DIV
><P
> An example:
</P><PRE
CLASS="PROGRAMLISTING"
>PERFORM create_mv('cs_session_page_requests_mv', my_query);</PRE
><P>
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="PLPGSQL-STATEMENTS-NULL"
>35.6.4. Doing Nothing At All</A
></H2
><P
> Sometimes a placeholder statement that does nothing is useful.
For example, it can indicate that one arm of an if/then/else
chain is deliberately empty. For this purpose, use the
<TT
CLASS="COMMAND"
>NULL</TT
> statement:
</P><PRE
CLASS="SYNOPSIS"
>NULL;</PRE
><P>
</P
><P
> For example, the following two fragments of code are equivalent:
</P><PRE
CLASS="PROGRAMLISTING"
> BEGIN
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN
NULL; -- ignore the error
END;</PRE
><P>
</P><PRE
CLASS="PROGRAMLISTING"
> BEGIN
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN -- ignore the error
END;</PRE
><P>
Which is preferable is a matter of taste.
</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> In Oracle's PL/SQL, empty statement lists are not allowed, and so
<TT
CLASS="COMMAND"
>NULL</TT
> statements are <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>required</I
></SPAN
> for situations
such as this. <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> allows you to
just write nothing, instead.
</P
></BLOCKQUOTE
></DIV
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="PLPGSQL-STATEMENTS-EXECUTING-DYN"
>35.6.5. Executing Dynamic Commands</A
></H2
><P
> Oftentimes you will want to generate dynamic commands inside your
<SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> functions, that is, commands
that will involve different tables or different data types each
time they are executed. <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
>'s
normal attempts to cache plans for commands will not work in such
scenarios. To handle this sort of problem, the
<TT
CLASS="COMMAND"
>EXECUTE</TT
> statement is provided:
</P><PRE
CLASS="SYNOPSIS"
>EXECUTE <TT
CLASS="REPLACEABLE"
><I
>command-string</I
></TT
>;</PRE
><P>
where <TT
CLASS="REPLACEABLE"
><I
>command-string</I
></TT
> is an expression
yielding a string (of type
<TT
CLASS="TYPE"
>text</TT
>) containing the command
to be executed. This string is fed literally to the SQL engine.
</P
><P
> Note in particular that no substitution of <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
>
variables is done on the command string. The values of variables must
be inserted in the command string as it is constructed.
</P
><P
> Unlike all other commands in <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
>, a command
run by an <TT
CLASS="COMMAND"
>EXECUTE</TT
> statement is not prepared
and saved just once during the life of the session. Instead, the
command is prepared each time the statement is run. The command
string can be dynamically created within the function to perform
actions on different tables and columns.
</P
><P
> The results from <TT
CLASS="COMMAND"
>SELECT</TT
> commands are discarded
by <TT
CLASS="COMMAND"
>EXECUTE</TT
>, and <TT
CLASS="COMMAND"
>SELECT INTO</TT
>
is not currently supported within <TT
CLASS="COMMAND"
>EXECUTE</TT
>.
So there is no way to extract a result from a dynamically-created
<TT
CLASS="COMMAND"
>SELECT</TT
> using the plain <TT
CLASS="COMMAND"
>EXECUTE</TT
>
command. There are two other ways to do it, however: one is to use the
<TT
CLASS="COMMAND"
>FOR-IN-EXECUTE</TT
>
loop form described in <A
HREF="plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING"
>Section 35.7.4</A
>,
and the other is to use a cursor with <TT
CLASS="COMMAND"
>OPEN-FOR-EXECUTE</TT
>, as
described in <A
HREF="plpgsql-cursors.html#PLPGSQL-CURSOR-OPENING"
>Section 35.8.2</A
>.
</P
><P
> When working with dynamic commands you will often have to handle escaping
of single quotes. The recommended method for quoting fixed text in your
function body is dollar quoting. (If you have legacy code that does
not use dollar quoting, please refer to the
overview in <A
HREF="plpgsql-development-tips.html#PLPGSQL-QUOTE-TIPS"
>Section 35.2.1</A
>, which can save you
some effort when translating said code to a more reasonable scheme.)
</P
><P
> Dynamic values that are to be inserted into the constructed
query require special handling since they might themselves contain
quote characters.
An example (this assumes that you are using dollar quoting for the
function as a whole, so the quote marks need not be doubled):
</P><PRE
CLASS="PROGRAMLISTING"
>EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = '
|| quote_literal(newvalue)
|| ' WHERE key = '
|| quote_literal(keyvalue);</PRE
><P>
</P
><P
> This example shows use of the functions
<CODE
CLASS="FUNCTION"
>quote_ident(<TT
CLASS="TYPE"
>text</TT
>)</CODE
> and
<CODE
CLASS="FUNCTION"
>quote_literal(<TT
CLASS="TYPE"
>text</TT
>)</CODE
>.<A
NAME="AEN32862"
></A
><A
NAME="AEN32865"
></A
> For safety, variables containing column and
table identifiers should be passed to function
<CODE
CLASS="FUNCTION"
>quote_ident</CODE
>. Variables containing values
that should be literal strings in the constructed command should
be passed to <CODE
CLASS="FUNCTION"
>quote_literal</CODE
>. Both take the
appropriate steps to return the input text enclosed in double or
single quotes respectively, with any embedded special characters
properly escaped.
</P
><P
> Note that dollar quoting is only useful for quoting fixed text.
It would be a very bad idea to try to do the above example as
</P><PRE
CLASS="PROGRAMLISTING"
>EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = $$'
|| newvalue
|| '$$ WHERE key = '
|| quote_literal(keyvalue);</PRE
><P>
because it would break if the contents of <TT
CLASS="LITERAL"
>newvalue</TT
>
happened to contain <TT
CLASS="LITERAL"
>$$</TT
>. The same objection would
apply to any other dollar-quoting delimiter you might pick.
So, to safely quote text that is not known in advance, you
<SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>must</I
></SPAN
> use <CODE
CLASS="FUNCTION"
>quote_literal</CODE
>.
</P
><P
> A much larger example of a dynamic command and
<TT
CLASS="COMMAND"
>EXECUTE</TT
> can be seen in <A
HREF="plpgsql-porting.html#PLPGSQL-PORTING-EX2"
>Example 35-6</A
>, which builds and executes a
<TT
CLASS="COMMAND"
>CREATE FUNCTION</TT
> command to define a new function.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="PLPGSQL-STATEMENTS-DIAGNOSTICS"
>35.6.6. Obtaining the Result Status</A
></H2
><P
> There are several ways to determine the effect of a command. The
first method is to use the <TT
CLASS="COMMAND"
>GET DIAGNOSTICS</TT
>
command, which has the form:
</P><PRE
CLASS="SYNOPSIS"
>GET DIAGNOSTICS <TT
CLASS="REPLACEABLE"
><I
>variable</I
></TT
> = <TT
CLASS="REPLACEABLE"
><I
>item</I
></TT
> [<SPAN
CLASS="OPTIONAL"
> , ... </SPAN
>] ;</PRE
><P>
This command allows retrieval of system status indicators. Each
<TT
CLASS="REPLACEABLE"
><I
>item</I
></TT
> is a key word identifying a state
value to be assigned to the specified variable (which should be
of the right data type to receive it). The currently available
status items are <TT
CLASS="VARNAME"
>ROW_COUNT</TT
>, the number of rows
processed by the last <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> command sent down to
the <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> engine, and <TT
CLASS="VARNAME"
>RESULT_OID</TT
>,
the OID of the last row inserted by the most recent
<ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> command. Note that <TT
CLASS="VARNAME"
>RESULT_OID</TT
>
is only useful after an <TT
CLASS="COMMAND"
>INSERT</TT
> command.
</P
><P
> An example:
</P><PRE
CLASS="PROGRAMLISTING"
>GET DIAGNOSTICS integer_var = ROW_COUNT;</PRE
><P>
</P
><P
> The second method to determine the effects of a command is to check the
special variable named <TT
CLASS="LITERAL"
>FOUND</TT
>, which is of
type <TT
CLASS="TYPE"
>boolean</TT
>. <TT
CLASS="LITERAL"
>FOUND</TT
> starts out
false within each <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> function call.
It is set by each of the following types of statements:
<P
></P
></P><UL
><LI
><P
> A <TT
CLASS="COMMAND"
>SELECT INTO</TT
> statement sets
<TT
CLASS="LITERAL"
>FOUND</TT
> true if it returns a row, false if no
row is returned.
</P
></LI
><LI
><P
> A <TT
CLASS="COMMAND"
>PERFORM</TT
> statement sets <TT
CLASS="LITERAL"
>FOUND</TT
>
true if it produces (and discards) a row, false if no row is
produced.
</P
></LI
><LI
><P
> <TT
CLASS="COMMAND"
>UPDATE</TT
>, <TT
CLASS="COMMAND"
>INSERT</TT
>, and <TT
CLASS="COMMAND"
>DELETE</TT
>
statements set <TT
CLASS="LITERAL"
>FOUND</TT
> true if at least one
row is affected, false if no row is affected.
</P
></LI
><LI
><P
> A <TT
CLASS="COMMAND"
>FETCH</TT
> statement sets <TT
CLASS="LITERAL"
>FOUND</TT
>
true if it returns a row, false if no row is returned.
</P
></LI
><LI
><P
> A <TT
CLASS="COMMAND"
>FOR</TT
> statement sets <TT
CLASS="LITERAL"
>FOUND</TT
> true
if it iterates one or more times, else false. This applies to
all three variants of the <TT
CLASS="COMMAND"
>FOR</TT
> statement (integer
<TT
CLASS="COMMAND"
>FOR</TT
> loops, record-set <TT
CLASS="COMMAND"
>FOR</TT
> loops, and
dynamic record-set <TT
CLASS="COMMAND"
>FOR</TT
>
loops). <TT
CLASS="LITERAL"
>FOUND</TT
> is set this way when the
<TT
CLASS="COMMAND"
>FOR</TT
> loop exits; inside the execution of the loop,
<TT
CLASS="LITERAL"
>FOUND</TT
> is not modified by the
<TT
CLASS="COMMAND"
>FOR</TT
> statement, although it may be changed by the
execution of other statements within the loop body.
</P
></LI
></UL
><P>
<TT
CLASS="LITERAL"
>FOUND</TT
> is a local variable within each
<SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> function; so any changes
to it affect only the current function.
</P
></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="plpgsql-expressions.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-control-structures.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Expressions</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="plpgsql.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Control Structures</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
