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 ORDER BY.) If the last query happens
to return no rows at all, the null value will be returned.

Alternatively, an SQL function can be declared to return a
set, by specifying the function's return type as SETOF sometype, or
equivalently by declaring it as RETURNS
TABLE(columns). 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 SELECT, or an INSERT,
UPDATE, or DELETE that has a RETURNING clause.

Any collection of commands in the SQL language can be packaged together and
defined as a function. Besides SELECT
queries, the commands can include data modification queries
(INSERT, UPDATE, and DELETE), as
well as other SQL commands. (You cannot use transaction control
commands, e.g. COMMIT, SAVEPOINT, and some utility commands, e.g.
VACUUM, in SQL functions.) However, the final command
must be a SELECT or have a RETURNING clause 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 void. For example, this function removes rows with
negative salaries from the emp
table:

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 Section
4.1.2.4) 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 Section
4.1.2.1).

Arguments of a SQL function can be referenced in the
function body using either names or numbers. Examples of both
methods appear below.

To use a name, declare the function argument as having a
name, and then just write that name in the function body. If
the argument name is the same as any column name in the current
SQL command within the function, the column name will take
precedence. To override this, qualify the argument name with
the name of the function itself, that is function_name.argument_name. (If this would
conflict with a qualified column name, again the column name
wins. You can avoid the ambiguity by choosing a different alias
for the table within the SQL command.)

In the older numeric approach, arguments are referenced
using the syntax $n: $1
refers to the first input argument, $2
to the second, and so on. This will work whether or not the
particular argument was declared with a name.

If an argument is of a composite type, then the dot
notation, e.g., argname.fieldname or
$1.fieldname, can be used to access
attributes of the argument. Again, you might need to qualify
the argument's name with the function name to make the form
with an argument name unambiguous.

SQL function 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);

Note: The ability to use names to reference SQL
function arguments was added in PostgreSQL 9.2. Functions to be used
in older servers must use the $n
notation.

Notice that we defined a column alias within the function
body for the result of the function (with the name result), but this column alias is not visible
outside the function. Hence, the result is labeled one instead of result.

It is almost as easy to define SQL functions that take base types as
arguments:

A user could execute this function to debit account 17 by
$100.00 as follows:

SELECT tf1(17, 100.0);

In this example, we chose the name accountno for the first argument, but this is
the same as the name of a column in the bank table. Within the UPDATE command, accountno refers to the column bank.accountno, so tf1.accountno must be used to refer to the
argument. We could of course avoid this by using a different
name for the argument.

In practice one would probably like a more useful result
from the function than a constant 1, so a more likely
definition is:

When writing functions with arguments of composite types, we
must not only specify which argument we want but also the
desired attribute (field) of that argument. For example,
suppose that emp is a table containing
employee data, and therefore also the name of the composite
type of each row of the table. Here is a function double_salary that computes what someone's
salary would be if it were doubled:

Notice the use of the syntax $1.salary to select one field of the argument
row value. Also notice how the calling SELECT command uses *
to select the entire current row of a table as a composite
value. The table row can alternatively be referenced using just
the table name, like this:

SELECT name, double_salary(emp) AS dream
FROM emp
WHERE emp.cubicle ~= point '(2,1)';

but this usage is deprecated since it's easy to get
confused.

Sometimes it is handy to construct a composite argument
value on-the-fly. This can be done with the ROW construct. For example, we could adjust the
data being passed to the function:

In this example we have specified each of the attributes
with a constant value, but any computation could have been
substituted for these constants.

Note two important things about defining the function:

The select list order in the query must be exactly the
same as that in which the columns appear in the table
associated with the composite type. (Naming the columns, as
we did above, is irrelevant to the system.)

You must typecast the expressions to match the
definition of the composite type, or you will get errors
like this:

Here we wrote a SELECT that returns
just a single column of the correct composite type. This isn't
really better in this situation, but it is a handy alternative
in some cases — for example, if we need to compute the result
by calling another function that returns the desired composite
value.

Another option is to use functional notation for extracting
an attribute. The simple way to explain this is that we can use
the notations attribute(table) and
table.attribute interchangeably.

SELECT name(new_emp());
name
------
None

-- This is the same as:
-- SELECT emp.name AS youngster FROM emp WHERE emp.age < 30;
SELECT name(emp) AS youngster FROM emp WHERE age(emp) < 30;
youngster
-----------
Sam
Andy

Tip: The equivalence between functional notation
and attribute notation makes it possible to use functions
on composite types to emulate "computed
fields". For example, using the previous definition
for double_salary(emp), we can
write

SELECT emp.name, emp.double_salary FROM emp;

An application using this wouldn't need to be directly
aware that double_salary isn't a
real column of the table. (You can also emulate computed
fields with views.)

Because of this behavior, it's unwise to give a function
that takes a single composite-type argument the same name
as any of the fields of that composite type.

Another way to use a function returning a composite type is
to pass the result to another function that accepts the correct
row type as input:

This is not essentially different from the version of
add_em shown in Section 35.4.2.
The real value of output parameters is that they provide a
convenient way of defining functions that return several
columns. For example,

but not having to bother with the separate composite type
definition is often handy. Notice that the names attached to
the output parameters are not just decoration, but determine
the column names of the anonymous composite type. (If you omit
a name for an output parameter, the system will choose a name
on its own.)

Notice that output parameters are not included in the
calling argument list when invoking such a function from SQL.
This is because PostgreSQL
considers only the input parameters to define the function's
calling signature. That means also that only the input
parameters matter when referencing the function for purposes
such as dropping it. We could drop the above function with
either of

Parameters can be marked as IN (the
default), OUT, INOUT, or VARIADIC. An
INOUT parameter serves as both an
input parameter (part of the calling argument list) and an
output parameter (part of the result record type). VARIADIC parameters are input parameters, but
are treated specially as described next.

SQL functions can be
declared to accept variable numbers of arguments, so long as
all the "optional" arguments are of
the same data type. The optional arguments will be passed to
the function as an array. The function is declared by marking
the last parameter as VARIADIC; this
parameter must be declared as being of an array type. For
example:

Effectively, all the actual arguments at or beyond the
VARIADIC position are gathered up into
a one-dimensional array, as if you had written

SELECT mleast(ARRAY[10, -1, 5, 4.4]); -- doesn't work

You can't actually write that, though — or at least, it will
not match this function definition. A parameter marked
VARIADIC matches one or more
occurrences of its element type, not of its own type.

Sometimes it is useful to be able to pass an
already-constructed array to a variadic function; this is
particularly handy when one variadic function wants to pass on
its array parameter to another one. You can do that by
specifying VARIADIC in the call:

SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);

This prevents expansion of the function's variadic parameter
into its element type, thereby allowing the array argument
value to match normally. VARIADIC can
only be attached to the last actual argument of a function
call.

Specifying VARIADIC in the call is
also the only way to pass an empty array to a variadic
function, for example:

SELECT mleast(VARIADIC ARRAY[]::numeric[]);

Simply writing SELECT mleast() does
not work because a variadic parameter must match at least one
actual argument. (You could define a second function also named
mleast, with no parameters, if you
wanted to allow such calls.)

The array element parameters generated from a variadic
parameter are treated as not having any names of their own.
This means it is not possible to call a variadic function using
named arguments (Section 4.3), except when
you specify VARIADIC. For example,
this will work:

Functions can be declared with default values for some or
all input arguments. The default values are inserted whenever
the function is called with insufficiently many actual
arguments. Since arguments can only be omitted from the end of
the actual argument list, all parameters after a parameter with
a default value have to have default values as well. (Although
the use of named argument notation could allow this restriction
to be relaxed, it's still enforced so that positional argument
notation works sensibly.)

All SQL functions can be used in the FROM clause of a query, but it is particularly
useful for functions returning composite types. If the function
is defined to return a base type, the table function produces a
one-column table. If the function is defined to return a
composite type, the table function produces a column for each
attribute of the composite type.

When an SQL function is declared as returning SETOF sometype,
the function's final query is executed to completion, and each
row it outputs is returned as an element of the result set.

This feature is normally used when calling the function in
the FROM clause. In this case each row
returned by the function becomes a row of the table seen by the
query. For example, assume that table foo has the same contents as above, and we
say:

The key point here is that you must write RETURNS SETOF record to indicate that the
function returns multiple rows instead of just one. If there is
only one output parameter, write that parameter's type instead
of record.

Currently, functions returning sets can also be called in
the select list of a query. For each row that the query
generates by itself, the function returning set is invoked, and
an output row is generated for each element of the function's
result set. Note, however, that this capability is deprecated
and might be removed in future releases. The following is an
example function returning a set from the select list:

In the last SELECT, notice that no
output row appears for Child2,
Child3, etc. This happens because
listchildren returns an empty set
for those arguments, so no result rows are generated.

Note: If a function's last command is INSERT, UPDATE, or
DELETE with RETURNING, that command will always be
executed to completion, even if the function is not
declared with SETOF or the calling
query does not fetch all the result rows. Any extra rows
produced by the RETURNING clause
are silently dropped, but the commanded table modifications
still happen (and are all completed before returning from
the function).

There is another way to declare a function as returning a
set, which is to use the syntax RETURNS
TABLE(columns). This is
equivalent to using one or more OUT
parameters plus marking the function as returning SETOF record (or SETOF
a single output parameter's type, as appropriate). This
notation is specified in recent versions of the SQL standard,
and thus may be more portable than using SETOF.

For example, the preceding sum-and-product example could
also be done this way:

SQL functions can be
declared to accept and return the polymorphic types anyelement, anyarray,
anynonarray, anyenum, and anyrange. See
Section
35.2.5 for a more detailed explanation of polymorphic
functions. Here is a polymorphic function make_array that builds up an array from two
arbitrary data type elements:

Notice the use of the typecast 'a'::text to specify that the argument is of
type text. This is required if the
argument is just a string literal, since otherwise it would be
treated as type unknown, and array of
unknown is not a valid type. Without the
typecast, you will get errors like this:

ERROR: could not determine polymorphic type because input has type "unknown"

It is permitted to have polymorphic arguments with a fixed
return type, but the converse is not. For example:

When a SQL function has one or more parameters of collatable
data types, a collation is identified for each function call
depending on the collations assigned to the actual arguments,
as described in Section 22.2. If a
collation is successfully identified (i.e., there are no
conflicts of implicit collations among the arguments) then all
the collatable parameters are treated as having that collation
implicitly. This will affect the behavior of
collation-sensitive operations within the function. For
example, using the anyleast
function described above, the result of

SELECT anyleast('abc'::text, 'ABC');

will depend on the database's default collation. In
C locale the result will be ABC, but in many other locales it will be
abc. The collation to use can be
forced by adding a COLLATE clause to
any of the arguments, for example

SELECT anyleast('abc'::text, 'ABC' COLLATE "C");

Alternatively, if you wish a function to operate with a
particular collation regardless of what it is called with,
insert COLLATE clauses as needed in
the function definition. This version of anyleast would always use en_US locale to compare strings:

But note that this will throw an error if applied to a
non-collatable data type.

If no common collation can be identified among the actual
arguments, then a SQL function treats its parameters as having
their data types' default collation (which is usually the
database's default collation, but could be different for
parameters of domain types).

The behavior of collatable parameters can be thought of as a
limited form of polymorphism, applicable only to textual data
types.