|PostgreSQL Reference Manual - Volume 2 - Programming Guide|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 408 pages
RRP £19.95 ($34.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
5.4 Query Language (SQL) Functions
SQL functions execute an arbitrary list of SQL statements, returning
the result of the last query in the list.
In the simple (non-set)
case, the first row of the last query's result will be returned.
(Bear in mind that “the first row” of a multirow
result is not well-defined unless you use
If the last query happens
to return no rows at all, the null value will be returned.
an SQL function may be declared to return a set, by specifying the
function's return type as
sometype. In this case all rows of the
last query's result are returned. Further details appear below.
The body of an SQL function must be a list of SQL
statements separated by semicolons. A semicolon after the last
statement is optional. Unless the function is declared to return
void, the last statement must be a
Any collection of commands in the SQL
language can be packaged together and defined as a function.
SELECT queries, the commands can include data
modification queries (
DELETE), as well as
other SQL commands. (The only exception is that you can't put
SAVEPOINT commands into a SQL function.)
However, the final command
must be a
SELECT that returns whatever is
specified as the function's return type. Alternatively, if you
want to define a SQL function that performs actions but has no
useful value to return, you can define it as returning
In that case, the function body must not end with a
For example, this function removes rows with negative salaries from
CREATE FUNCTION clean_emp() RETURNS void AS ' DELETE FROM emp WHERE salary < 0; ' LANGUAGE SQL; SELECT clean_emp(); clean_emp ----------- (1 row)
The syntax of the
CREATE FUNCTION command requires
the function body to be written as a string constant. It is usually
most convenient to use dollar quoting (see Volume 1: Dollar-Quoted String Constants) for the string constant.
If you choose to use regular single-quoted string constant syntax,
you must double single quote marks (
') and backslashes
\) (assuming escape string syntax) in the body of
the function (see Volume 1: String Constants).
Arguments to the SQL function are referenced in the function
body using the syntax
refers to the first argument,
$2 to the second, and so on.
If an argument is of a composite type, then the dot notation,
$1.name, may be used to access attributes
of the argument. The arguments can only be used as data values,
not as identifiers. Thus for example this is reasonable:
INSERT INTO mytable VALUES ($1);
but this will not work:
INSERT INTO $1 VALUES (42);
|ISBN 0954612035||PostgreSQL Reference Manual - Volume 2 - Programming Guide||See the print edition|