Description

CREATE FUNCTION defines a new
function. CREATE OR REPLACE FUNCTION
will either create a new function, or replace an existing
definition. To be able to define a function, the user must have
the USAGE privilege on the language.

If a schema name is included, then the function is created in
the specified schema. Otherwise it is created in the current
schema. The name of the new function must not match any existing
function with the same input argument types in the same schema.
However, functions of different argument types can share a name
(this is called overloading).

To replace the current definition of an existing function, use
CREATE OR REPLACE FUNCTION. It is not
possible to change the name or argument types of a function this
way (if you tried, you would actually be creating a new, distinct
function). Also, CREATE OR REPLACE
FUNCTION will not let you change the return type of an
existing function. To do that, you must drop and recreate the
function. (When using OUT parameters,
that means you cannot change the types of any OUT parameters except by dropping the
function.)

When CREATE OR REPLACE FUNCTION is
used to replace an existing function, the ownership and
permissions of the function do not change. All other function
properties are assigned the values specified or implied in the
command. You must own the function to replace it (this includes
being a member of the owning role).

If you drop and then recreate a function, the new function is
not the same entity as the old; you will have to drop existing
rules, views, triggers, etc. that refer to the old function. Use
CREATE OR REPLACE FUNCTION to change a
function definition without breaking objects that refer to the
function. Also, ALTER FUNCTION can be
used to change most of the auxiliary properties of an existing
function.

The user that creates the function becomes the owner of the
function.

To be able to create a function, you must have USAGE privilege on the argument types and the
return type.

Parameters

name

The name (optionally schema-qualified) of the function
to create.

argmode

The mode of an argument: IN,
OUT, INOUT, or VARIADIC.
If omitted, the default is IN.
Only OUT arguments can follow a
VARIADIC one. Also, OUT and INOUT
arguments cannot be used together with the RETURNS TABLE notation.

argname

The name of an argument. Some languages (including SQL
and PL/pgSQL) let you use the name in the function body.
For other languages the name of an input argument is just
extra documentation, so far as the function itself is
concerned; but you can use input argument names when
calling a function to improve readability (see Section 4.3). In any
case, the name of an output argument is significant,
because it defines the column name in the result row type.
(If you omit the name for an output argument, the system
will choose a default column name.)

argtype

The data type(s) of the function's arguments (optionally
schema-qualified), if any. The argument types can be base,
composite, or domain types, or can reference the type of a
table column.

Depending on the implementation language it might also
be allowed to specify "pseudotypes" such as cstring. Pseudotypes indicate that the actual
argument type is either incompletely specified, or outside
the set of ordinary SQL data types.

The type of a column is referenced by writing table_name.column_name%TYPE. Using this
feature can sometimes help make a function independent of
changes to the definition of a table.

default_expr

An expression to be used as default value if the
parameter is not specified. The expression has to be
coercible to the argument type of the parameter. Only input
(including INOUT) parameters can
have a default value. All input parameters following a
parameter with a default value must have default values as
well.

rettype

The return data type (optionally schema-qualified). The
return type can be a base, composite, or domain type, or
can reference the type of a table column. Depending on the
implementation language it might also be allowed to specify
"pseudotypes" such as cstring. If the function is not supposed to
return a value, specify void as the
return type.

When there are OUT or
INOUT parameters, the RETURNS clause can be omitted. If present,
it must agree with the result type implied by the output
parameters: RECORD if there are
multiple output parameters, or the same type as the single
output parameter.

The SETOF modifier indicates
that the function will return a set of items, rather than a
single item.

The type of a column is referenced by writing table_name.column_name%TYPE.

column_name

The name of an output column in the RETURNS TABLE syntax. This is effectively
another way of declaring a named OUT parameter, except that RETURNS TABLE also implies RETURNS SETOF.

column_type

The data type of an output column in the RETURNS TABLE syntax.

lang_name

The name of the language that the function is
implemented in. It can be sql,
c, internal, or the name of a user-defined
procedural language, e.g. plpgsql.
Enclosing the name in single quotes is deprecated and
requires matching case.

TRANSFORM { FOR TYPE type_name } [, ... ] }

Lists which transforms a call to the function should
apply. Transforms convert between SQL types and
language-specific data types; see CREATE TRANSFORM. Procedural
language implementations usually have hardcoded knowledge
of the built-in types, so those don't need to be listed
here. If a procedural language implementation does not know
how to handle a type and no transform is supplied, it will
fall back to a default behavior for converting data types,
but this depends on the implementation.

WINDOW

WINDOW indicates that the
function is a window function
rather than a plain function. This is currently only useful
for functions written in C. The WINDOW attribute cannot be changed when
replacing an existing function definition.

IMMUTABLESTABLEVOLATILE

These attributes inform the query optimizer about the
behavior of the function. At most one choice can be
specified. If none of these appear, VOLATILE is the default assumption.

IMMUTABLE indicates that the
function cannot modify the database and always returns the
same result when given the same argument values; that is,
it does not do database lookups or otherwise use
information not directly present in its argument list. If
this option is given, any call of the function with
all-constant arguments can be immediately replaced with the
function value.

STABLE indicates that the
function cannot modify the database, and that within a
single table scan it will consistently return the same
result for the same argument values, but that its result
could change across SQL statements. This is the appropriate
selection for functions whose results depend on database
lookups, parameter variables (such as the current time
zone), etc. (It is inappropriate for AFTER triggers that wish to query rows
modified by the current command.) Also note that the
current_timestamp family of
functions qualify as stable, since their values do not
change within a transaction.

VOLATILE indicates that the
function value can change even within a single table scan,
so no optimizations can be made. Relatively few database
functions are volatile in this sense; some examples are
random(), currval(), timeofday(). But note that any function that
has side-effects must be classified volatile, even if its
result is quite predictable, to prevent calls from being
optimized away; an example is setval().

LEAKPROOF indicates that the
function has no side effects. It reveals no information
about its arguments other than by its return value. For
example, a function which throws an error message for some
argument values but not others, or which includes the
argument values in any error message, is not leakproof.
This affects how the system executes queries against views
created with the security_barrier
option or tables with row level security enabled. The
system will enforce conditions from security policies and
security barrier views before any user-supplied conditions
from the query itself that contain non-leakproof functions,
in order to prevent the inadvertent exposure of data.
Functions and operators marked as leakproof are assumed to
be trustworthy, and may be executed before conditions from
security policies and security barrier views. In addition,
functions which do not take arguments or which are not
passed any arguments from the security barrier view or
table do not have to be marked as leakproof to be executed
before security conditions. See CREATE VIEW and Section 39.5. This option can
only be set by the superuser.

CALLED ON NULL INPUTRETURNS NULL ON NULL INPUTSTRICT

CALLED ON NULL INPUT (the
default) indicates that the function will be called
normally when some of its arguments are null. It is then
the function author's responsibility to check for null
values if necessary and respond appropriately.

RETURNS NULL ON NULL INPUT or
STRICT indicates that the function
always returns null whenever any of its arguments are null.
If this parameter is specified, the function is not
executed when there are null arguments; instead a null
result is assumed automatically.

[EXTERNAL] SECURITY INVOKER[EXTERNAL]
SECURITY DEFINER

SECURITY INVOKER indicates that
the function is to be executed with the privileges of the
user that calls it. That is the default. SECURITY DEFINER specifies that the function
is to be executed with the privileges of the user that
created it.

The key word EXTERNAL is
allowed for SQL conformance, but it is optional since,
unlike in SQL, this feature applies to all functions not
only external ones.

PARALLEL

PARALLEL UNSAFE indicates that
the function can't be executed in parallel mode and the
presence of such a function in an SQL statement forces a
serial execution plan. This is the default. PARALLEL RESTRICTED indicates that the
function can be executed in parallel mode, but the
execution is restricted to parallel group leader.
PARALLEL SAFE indicates that the
function is safe to run in parallel mode without
restriction.

Functions should be labeled parallel unsafe if they
modify any database state, or if they make changes to the
transaction such as using sub-transactions, or if they
access sequences or attempt to make persistent changes to
settings (e.g. setval). They
should be labeled as parallel restricted if they access
temporary tables, client connection state, cursors,
prepared statements, or miscellaneous backend-local state
which the system cannot synchronize in parallel mode (e.g.
setseed cannot be executed other
than by the group leader because a change made by another
process would not be reflected in the leader). In general,
if a function is labeled as being safe when it is
restricted or unsafe, or if it is labeled as being
restricted when it is in fact unsafe, it may throw errors
or produce wrong answers when used in a parallel query.
C-language functions could in theory exhibit totally
undefined behavior if mislabeled, since there is no way for
the system to protect itself against arbitrary C code, but
in most likely cases the result will be no worse than for
any other function. If in doubt, functions should be
labeled as UNSAFE, which is the
default.

execution_cost

A positive number giving the estimated execution cost
for the function, in units of cpu_operator_cost.
If the function returns a set, this is the cost per
returned row. If the cost is not specified, 1 unit is
assumed for C-language and internal functions, and 100
units for functions in all other languages. Larger values
cause the planner to try to avoid evaluating the function
more often than necessary.

result_rows

A positive number giving the estimated number of rows
that the planner should expect the function to return. This
is only allowed when the function is declared to return a
set. The default assumption is 1000 rows.

configuration_parametervalue

The SET clause causes the
specified configuration parameter to be set to the
specified value when the function is entered, and then
restored to its prior value when the function exits.
SET FROM CURRENT saves the value
of the parameter that is current when CREATE FUNCTION is executed as the value to
be applied when the function is entered.

If a SET clause is attached to
a function, then the effects of a SET
LOCAL command executed inside the function for the
same variable are restricted to the function: the
configuration parameter's prior value is still restored at
function exit. However, an ordinary SET command (without LOCAL) overrides the SET clause, much as it would do for a
previous SET LOCAL command: the
effects of such a command will persist after function exit,
unless the current transaction is rolled back.

See SET and Chapter 19 for more information
about allowed parameter names and values.

definition

A string constant defining the function; the meaning
depends on the language. It can be an internal function
name, the path to an object file, an SQL command, or text
in a procedural language.

It is often helpful to use dollar quoting (see Section
4.1.2.4) to write the function definition string,
rather than the normal single quote syntax. Without dollar
quoting, any single quotes or backslashes in the function
definition must be escaped by doubling them.

obj_file, link_symbol

This form of the AS clause is
used for dynamically loadable C language functions when the
function name in the C language source code is not the same
as the name of the SQL function. The string obj_file is the name of the file
containing the dynamically loadable object, and link_symbol is the function's link
symbol, that is, the name of the function in the C language
source code. If the link symbol is omitted, it is assumed
to be the same as the name of the SQL function being
defined.

When repeated CREATE FUNCTION
calls refer to the same object file, the file is only
loaded once per session. To unload and reload the file
(perhaps during development), start a new session.

attribute

The historical way to specify optional pieces of
information about the function. The following attributes
can appear here:

isStrict

Equivalent to STRICT or
RETURNS NULL ON NULL
INPUT.

isCachable

isCachable is an obsolete
equivalent of IMMUTABLE;
it's still accepted for backwards-compatibility
reasons.

Overloading

PostgreSQL allows function
overloading; that is, the same name can
be used for several different functions so long as they have
distinct input argument types. However, the C names of all
functions must be different, so you must give overloaded C
functions different C names (for example, use the argument types
as part of the C names).

Two functions are considered the same if they have the same
names and input
argument types, ignoring any OUT
parameters. Thus for example these declarations conflict:

CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, out text) ...

Functions that have different argument type lists will not be
considered to conflict at creation time, but if defaults are
provided they might conflict in use. For example, consider

A call foo(10) will fail due to the
ambiguity about which function should be called.

Notes

The full SQL type syntax is
allowed for declaring a function's arguments and return value.
However, parenthesized type modifiers (e.g., the precision field
for type numeric) are discarded by
CREATE FUNCTION. Thus for example
CREATE FUNCTION foo (varchar(10)) ... is
exactly the same as CREATE FUNCTION foo
(varchar) ....

When replacing an existing function with CREATE OR REPLACE FUNCTION, there are restrictions
on changing parameter names. You cannot change the name already
assigned to any input parameter (although you can add names to
parameters that had none before). If there is more than one
output parameter, you cannot change the names of the output
parameters, because that would change the column names of the
anonymous composite type that describes the function's result.
These restrictions are made to ensure that existing calls of the
function do not stop working when it is replaced.

If a function is declared STRICT with
a VARIADIC argument, the strictness
check tests that the variadic array as a whole is non-null. The
function will still be called if the array has null elements.

Examples

Here are some trivial examples to help you get started. For
more information and examples, see Section
36.3.

However, a TABLE function is
different from the preceding examples, because it actually
returns a set of
records, not just one record.

Writing SECURITY DEFINER Functions
Safely

Because a SECURITY DEFINER function
is executed with the privileges of the user that created it, care
is needed to ensure that the function cannot be misused. For
security, search_path
should be set to exclude any schemas writable by untrusted users.
This prevents malicious users from creating objects (e.g.,
tables, functions, and operators) that mask objects intended to
be used by the function. Particularly important in this regard is
the temporary-table schema, which is searched first by default,
and is normally writable by anyone. A secure arrangement can be
obtained by forcing the temporary schema to be searched last. To
do this, write pg_temp as the last entry
in search_path. This function
illustrates safe usage:

This function's intention is to access a table admin.pwds. But without the SET clause, or with a SET
clause mentioning only admin, the
function could be subverted by creating a temporary table named
pwds.

Before PostgreSQL version
8.3, the SET clause was not available,
and so older functions may contain rather complicated logic to
save, set, and restore search_path. The
SET clause is far easier to use for this
purpose.

Another point to keep in mind is that by default, execute
privilege is granted to PUBLIC for newly
created functions (see GRANT for
more information). Frequently you will wish to restrict use of a
security definer function to only some users. To do that, you
must revoke the default PUBLIC
privileges and then grant execute privilege selectively. To avoid
having a window where the new function is accessible to all,
create it and set the privileges within a single transaction. For
example:

See Also

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.