CREATE FUNCTION
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'
} ...
Description
CREATE FUNCTION defines a new function. CREATE OR REPLACE FUNCTION will create a new function or replace an existing function. To define a function, the user must have USAGE privilege on the language.
If a schema name is included, the function will be created in the specified schema. Otherwise, it will be created in the current schema. The name of the new function cannot match any existing function or procedure in the same schema that has the same input parameter types.
However, functions and procedures with different parameter types can share the same name (this is called overloading).
To replace the current definition of an existing function, you can use CREATE OR REPLACE FUNCTION. However, you cannot change the name or parameter types of the function this way (attempting to do so will actually create a new, different function). Also, CREATE OR REPLACE FUNCTION will not allow you to change the return type of an existing function. To do so, you must drop and recreate the function (when using OUT parameters, this 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 attributes are assigned the values specified or implied by the command. You must own the function (including being a member of the owning role) to replace it.
If you drop and recreate a function, the new function will not be the same as the old one, and you will have to drop any existing rules, views, triggers, etc. that reference the old function. Using CREATE OR REPLACE FUNCTION to change a function definition does not destroy objects that reference the function. Additionally, ALTER FUNCTION can be used to change most auxiliary attributes of an existing function.
The user who creates the function becomes its owner.
To create a function, you must have USAGE privilege on the parameter types and return type.
Parameters
name
The name of the function to be created (can be schema-qualified).
argmode
The mode of an argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN. Only OUT arguments can follow a VARIADIC argument. 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 merely extra documentation (as far as the function itself is concerned). In any case, the name of an output argument is significant, because it defines the column name in the result row type (if an output argument's name is omitted, the system will choose a default column name).
argtype
The data type(s) of the function's arguments, if any (can be schema-qualified). The argument types can be base types, composite types, or domain types, or can reference the type of a table column.
Depending on the implementation language, it may also be possible to specify "pseudo-types" such as cstring. A pseudo-type indicates that the actual argument type is either not fully specified or is outside the set of ordinary SQL data types.
You can write table_name.column_name%TYPE to reference the type of a column. Using this feature can sometimes help create a function that is unaffected by changes to table definitions.
default_expr
An expression to be used as the default value if the argument is not specified. The expression must be coercible to the argument's type. Only input (including INOUT) arguments can have default values. All input arguments following an argument with a default value must also have default values.
rettype
The return data type (can be schema-qualified). The return type can be a base type, composite type, or domain type, or can reference the type of a table column. Depending on the implementation language, it may also be possible to specify "pseudo-types" such as cstring. If the function does not return a value, the return type can be specified as void.
When there are OUT or INOUT arguments, the RETURNS clause can be omitted. If present, it must be consistent with the result type implied by the output parameters: if there are multiple output parameters, it must be RECORD; otherwise, it must match the type of the single output parameter.
The SETOF modifier indicates that the function will return a set of items rather than a single item.
You can write table_name.column_name%TYPE to reference the type of a column.
column_name
The name of an output column in the RETURNS TABLE syntax. This is actually another way to declare OUT parameters, but 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 used to implement the function. It can be sql, c, internal, or a user-defined procedural language such as plpgsql. Wrapping the name in single quotes is deprecated and requires case-sensitive matching.
TRANSFORM { FOR TYPE type_name } [, ... ] }
A list of transforms that apply to calls of this function. Transforms convert between SQL types and language-specific data types; see CREATE TRANSFORM. Procedural language implementations typically hard-code knowledge of built-in types, so those do not need to be listed here. If a procedural language implementation does not know how to handle a type and no transform is provided, it will fall back to a default behavior for converting data types, but this is implementation-dependent.
WINDOW
WINDOW indicates that the function is a window function rather than a plain function. Currently, this is only used for functions written in C.
When replacing an existing function definition, you cannot change the WINDOW attribute.
IMMUTABLE
STABLE
VOLATILE
These attributes inform the query optimizer about the behavior of the function. At most one can be specified. If none of these appear, VOLATILE is the default.
IMMUTABLE indicates that the function cannot modify the database and always returns the same result for the same argument values; that is, it does not do database lookups or 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 for a single table scan it will consistently return the same result for the same argument values. However, its results may change across SQL statements. This is appropriate for functions whose results depend on database lookups, parameter variables (such as the current timezone), etc. (This is not suitable for AFTER triggers that wish to see rows changed by the current command.) Also note that the current_timestamp family of functions are appropriately marked as stable, since their values do not change within a transaction.
VOLATILE indicates that the function's 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(), and timeofday(). Note that any function with side effects must be classified as volatile, even if its result is predictable, to prevent calls from being optimized away. An example is setval().
LEAKPROOF
LEAKPROOF indicates that the function has no side effects. It does not reveal information about its arguments (except through the return value).
For example, a function that throws an error for some argument values but not others is not leakproof, and a function that includes argument values in any error messages is also not leakproof. This affects how the system executes queries on views created with the security_barrier option or tables with row-level security enabled. For queries containing non-leakproof functions, the system will enforce conditions from security policies or security barriers before any user-supplied conditions from the query itself, preventing inadvertent data exposure. Functions and operators marked as leakproof are assumed to be trusted and can be evaluated before conditions from security policies and security barrier views. Additionally, functions with no parameters or functions that do not receive any parameters from security barrier views or tables do not necessarily need to be marked as leakproof. This option can only be set by superusers.
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
STRICT
CALLED ON NULL INPUT (the default) indicates that the function should be called normally when some of its arguments are null. The function's author is responsible for checking for null values and responding appropriately if necessary.
RETURNS NULL ON NULL INPUT or STRICT indicates that the function will return null whenever any of its arguments are null. If this parameter is specified, the function will not be executed when there are null arguments; instead, a null result will be returned 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. This is the default. SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that owns it.
The keyword EXTERNAL is allowed for SQL conformance. However, it is optional because, unlike in SQL, this feature applies to all functions, not just external functions.
PARALLEL
PARALLEL UNSAFE indicates that the function cannot be run in parallel mode and that the presence of such a function in an SQL statement forces a sequential execution plan. This is the default. PARALLEL RESTRICTED indicates that the function can be run in parallel mode, but its execution is restricted to the parallel group leader. PARALLEL SAFE indicates that the function is safe to run in parallel mode without restriction. Functions that modify database state, change transactions using subtransactions or similar, access sequences, or make persistent changes to settings (such as setval) should be labeled as parallel unsafe. Functions that access temporary tables, client connection state, cursors, prepared statements, or local backend state that the system cannot synchronize in parallel mode (for example, setseed can only be executed in the group leader because changes made by another process are not reflected in the leader) should be labeled as parallel restricted. Typically, if a function is restricted or unsafe but is labeled as safe, or is actually unsafe but is labeled as restricted, it may throw errors or produce incorrect answers when executed in a parallel query. If incorrectly labeled, C language functions could theoretically exhibit completely undefined behavior, because the system has no way to protect itself from arbitrary C code, but in most cases the result will not be worse than for any other function. When in doubt, functions should be labeled as UNSAFE, which is the default.
COST execution_cost
A positive number giving the estimated execution cost of the function, in units of cpu_operator_cost. If the function returns a set, this is the cost per returned row. If no cost is 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 unnecessary excessive evaluation of the function.
ROWS result_rows
A positive number giving the estimated number of rows 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.
You must be a 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 restored to its previous value when the function exits. SET FROM CURRENT saves the current value of the parameter at the time CREATE FUNCTION is executed as the value to be applied when entering the function.
If a SET clause is attached to a function, then a SET LOCAL command executed within the function for the same variable is limited to the function: the configuration parameter's previous value will still be restored when the function exits. However, a regular SET command (without LOCAL) overrides the SET clause, much like a prior SET LOCAL command: the effect of such a command will persist after the function exits, unless the current transaction is rolled back.
definition
A string constant defining the function; its meaning depends on the language. It can be an internal function name, a path to an object file, a SQL command, or text in a procedural language.
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 differs from the SQL function name. The string obj_file is the name of the shared library file containing the compiled C function, and it is resolved by 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 will be assumed to be the same as the name of the SQL function being defined. All functions' C names must be distinct, so overloaded C functions must be given different C names (for example, by incorporating the argument types as part of the C name).
When CREATE FUNCTION is called repeatedly referencing the same object file, the file will only be loaded once per session. To unload and reload the file (perhaps during development), you must start a new session.
Overloading
Halo allows function overloading; that is, the same name can be used for multiple different functions as long as they have distinguishable input argument types. Regardless of whether this is used, this compatibility requires security precautions when calling functions in databases where some users do not trust others.
Two functions are considered the same if they have the same names and input argument types (ignoring any OUT parameters).
Therefore, these declarations would conflict:
CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, out text) ...
Functions with different argument type lists will not be considered conflicting at creation time, but if default values are provided, they may conflict at use time.
For example, consider:
CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, int default 42) ...
Calling foo(10) would fail because there is ambiguity when deciding which function should be called.
Notes
The full SQL type syntax is allowed for declaring a function's arguments and return values. However, CREATE FUNCTION discards parenthesized type modifiers (such as the precision field for type numeric). For example, CREATE FUNCTION foo (varchar(10)) ... and CREATE FUNCTION foo (varchar) ... are exactly the same.
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 argument (however, you can add names to previously unnamed parameters). If there is more than one output parameter, you cannot change the names of output parameters because this might change the column names of the anonymous composite type describing the function's result. These restrictions exist to ensure that existing calls to the function will not break when the function is replaced.
If a function declared as STRICT has a VARIADIC argument, the strictness check verifies that the variadic array as a whole is non-null. If the array has null elements, the function will still be called.
Examples
Here are some small examples to help you understand function creation.
CREATE FUNCTION add(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
In PL/pgSQL, using a parameter name to increment an integer:
CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
BEGIN
RETURN i + 1;
END;
$$ LANGUAGE plpgsql;
Return a record with 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 in a more elaborate way (using 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, the TABLE function differs from the previous example because it actually returns a set of records rather than just one record.
Writing SECURITY DEFINER Functions Safely
Because a SECURITY DEFINER function is executed with the privileges of the user who created it, care must be taken to ensure that the function cannot be misused. For safety, search_path should be set to exclude any schemas writable by untrusted users. This prevents malicious users from creating objects (such as tables, functions, and operators) that shadow the objects the function needs to use. Particularly important in this regard is the temporary table schema, which by default is searched first and is typically writable by any user. A safe layout can be achieved by forcing the temporary schema to be searched last. To do this, write pg_temp as the last item in search_path. This function demonstrates 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 safe search_path: trusted schema, then 'pg_temp'.
SET search_path = admin, pg_temp;
The purpose of this function is to access the table admin.pwds. But without the SET clause, or with a SET clause that only mentions admin, the function could create a temporary table named pwds.
Another thing to remember is that by default, execute privilege is granted to PUBLIC for newly created functions. You will often want to restrict the use of security definer functions to certain users. To do this, you must revoke the default PUBLIC privilege and then selectively grant execute privilege. To avoid a time window where the new function is accessible to everyone, you should create it and set 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;