CREATE FUNCTION(7) PostgreSQL 15.7 Documentation CREATE FUNCTION(7)
NAME
CREATE_FUNCTION - define a new function
SYNOPSIS
CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
[ RETURNS rettype
| RETURNS TABLE ( column_name column_type [, ...] ) ]
{ LANGUAGE lang_name
| TRANSFORM { FOR TYPE type_name } [, ... ]
| WINDOW
| { IMMUTABLE | STABLE | VOLATILE }
| [ NOT ] LEAKPROOF
| { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
| { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
| PARALLEL { UNSAFE | RESTRICTED | SAFE }
| COST execution_cost
| ROWS result_rows
| SUPPORT support_function
| SET configuration_parameter { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'obj_file', 'link_symbol'
| sql_body
} ...
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 or
procedure with the same input argument types in the same schema.
However, functions and procedures 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.
Refer to Section 38.3 for further information on writing functions.
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 “pseudo-types” such as cstring. Pseudo-types 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 “pseudo-types” 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. The default is sql if sql_body is
specified. 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 (CREATE_TRANSFORM(7)). 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.
IMMUTABLE
STABLE
VOLATILE
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().
For additional details see Section 38.7.
LEAKPROOF
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 (CREATE_VIEW(7)) and
Section 41.5. This option can only be set by the superuser.
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
STRICT
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 owns 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.
COST 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.
ROWS 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.
SUPPORT support_function
The name (optionally schema-qualified) of a planner support
function to use for this function. See Section 38.11 for details.
You must be superuser to use this option.
configuration_parameter
value
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(7) and Chapter 20 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 shared library file containing the
compiled C function, and is interpreted as for the LOAD command.
The string 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. 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).
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.
sql_body
The body of a LANGUAGE SQL function. This can either be a single
statement
RETURN expression
or a block
BEGIN ATOMIC
statement;
statement;
...
statement;
END
This is similar to writing the text of the function body as a
string constant (see definition above), but there are some
differences: This form only works for LANGUAGE SQL, the string
constant form works for all languages. This form is parsed at
function definition time, the string constant form is parsed at
execution time; therefore this form cannot support polymorphic
argument types and other constructs that are not resolvable at
function definition time. This form tracks dependencies between the
function and objects used in the function body, so DROP ... CASCADE
will work correctly, whereas the form using string literals may
leave dangling functions. Finally, this form is more compatible
with the SQL standard and other SQL implementations.
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. Whether or not you use it, this capability
entails security precautions when calling functions in databases where
some users mistrust other users; see Section 10.3.
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
CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, int default 42) ...
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
Add two integers using an SQL function:
CREATE FUNCTION add(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
The same function written in a more SQL-conforming style, using
argument names and an unquoted body:
CREATE FUNCTION add(a integer, b integer) RETURNS integer
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT
RETURN a + b;
Increment an integer, making use of an argument name, in PL/pgSQL:
CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
BEGIN
RETURN i + 1;
END;
$$ LANGUAGE plpgsql;
Return a record containing multiple output parameters:
CREATE FUNCTION dup(in int, out f1 int, out f2 text)
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL;
SELECT * FROM dup(42);
You can do the same thing more verbosely with an explicitly named
composite type:
CREATE TYPE dup_result AS (f1 int, f2 text);
CREATE FUNCTION dup(int) RETURNS dup_result
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL;
SELECT * FROM dup(42);
Another way to return multiple columns is to use a TABLE function:
CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL;
SELECT * FROM dup(42);
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 owns 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:
CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
SELECT (pwd = $2) INTO passed
FROM pwds
WHERE username = $1;
RETURN passed;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER
-- Set a secure search_path: trusted schema(s), then 'pg_temp'.
SET search_path = admin, pg_temp;
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 Section 5.7 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:
BEGIN;
CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER;
REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
COMMIT;
COMPATIBILITY
A CREATE FUNCTION command is defined in the SQL standard. The
PostgreSQL implementation can be used in a compatible way but has many
extensions. Conversely, the SQL standard specifies a number of optional
features that are not implemented in PostgreSQL.
The following are important compatibility issues:
• OR REPLACE is a PostgreSQL extension.
• For compatibility with some other database systems, argmode can be
written either before or after argname. But only the first way is
standard-compliant.
• For parameter defaults, the SQL standard specifies only the syntax
with the DEFAULT key word. The syntax with = is used in T-SQL and
Firebird.
• The SETOF modifier is a PostgreSQL extension.
• Only SQL is standardized as a language.
• All other attributes except CALLED ON NULL INPUT and RETURNS NULL
ON NULL INPUT are not standardized.
• For the body of LANGUAGE SQL functions, the SQL standard only
specifies the sql_body form.
Simple LANGUAGE SQL functions can be written in a way that is both
standard-conforming and portable to other implementations. More complex
functions using advanced features, optimization attributes, or other
languages will necessarily be specific to PostgreSQL in a significant
way.
SEE ALSO
ALTER FUNCTION (ALTER_FUNCTION(7)), DROP FUNCTION (DROP_FUNCTION(7)),
GRANT(7), LOAD(7), REVOKE(7)
PostgreSQL 15.7 2024 CREATE FUNCTION(7)
Generated by dwww version 1.15 on Sat Jun 29 02:14:03 CEST 2024.