|The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 478 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
9.5.3 Executing a Query with a Single-Row Result
The result of a SQL command yielding a single row (possibly of multiple
columns) can be assigned to a record variable, row-type variable, or list
of scalar variables. This is done by writing the base SQL command and
INTO clause. For example,
SELECT select_expressions INTO [STRICT] target FROM ...; INSERT ... RETURNING expressions INTO [STRICT] target; UPDATE ... RETURNING expressions INTO [STRICT] target; DELETE ... RETURNING expressions INTO [STRICT] target;
where target can be a record variable, a row
variable, or a comma-separated list of simple variables and
PL/pgSQL variables will be
substituted into the rest of the query, and the plan is cached,
just as described above for commands that do not return rows.
This works for
RETURNING, and utility commands that return row-set
results (such as
Except for the
INTO clause, the SQL command is the same
as it would be written outside PL/pgSQL.
Tip: Note that this interpretation of
INTOis quite different from PostgreSQL's regular
SELECT INTOcommand, wherein the
INTOtarget is a newly created table. If you want to create a table from a
SELECTresult inside a PL/pgSQL function, use the syntax
CREATE TABLE ... AS SELECT.
If a row or a variable list is used as target, the query's result columns must exactly match the structure of the target as to number and data types, or else 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.
INTO clause can appear almost anywhere in the SQL
command. Customarily it is written either just before or just after
the list of select_expressions in a
SELECT command, or at the end of the command for other
command types. It is recommended that you follow this convention
in case the PL/pgSQL parser becomes
stricter in future versions.
STRICT is not specified in the
clause, then target will be set to the first
row returned by the query, or to nulls if the query returned no rows.
(Note that “the first row” is not
well-defined unless you've used
ORDER BY.) Any result rows
after the first row are discarded.
You can check the special
FOUND variable (see
section 9.5.5 Obtaining the Result Status) to
determine whether a row was returned:
SELECT * INTO myrec FROM emp WHERE empname = myname; IF NOT FOUND THEN RAISE EXCEPTION 'employee % not found', myname; END IF;
STRICT option is specified, the query must
return exactly one row or a run-time error will be reported, either
NO_DATA_FOUND (no rows) or
(more than one row). You can use an exception block if you wish
to catch the error, for example:
BEGIN SELECT * INTO STRICT myrec FROM emp WHERE empname = myname; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'employee % not found', myname; WHEN TOO_MANY_ROWS THEN RAISE EXCEPTION 'employee % not unique', myname; END;
Successful execution of a command with
FOUND to true.
RETURNING, PL/pgSQL reports
an error for more than one returned row, even when
STRICT is not specified. This is because there
is no option such as
ORDER BY with which to determine
which affected row should be returned.
STRICToption matches the behavior of Oracle PL/SQL's
SELECT INTOand related statements.
To handle cases where you need to process multiple result rows from a SQL query, see section 9.6.4 Looping Through Query Results.
|ISBN 9781906966065||The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide||See the print edition|