CREATE FUNCTION

Creates a function.

important

Queries involving functions created by CREATE FUNCTION can only be run on Hybrid DPS clusters, not on Extreme DPS clusters.

Syntax

CREATE [OR REPLACE] FUNCTION <name>    
    ( [ [<arg_mode>] [<arg_name>] <arg_type> [ { DEFAULT | = } <default_expr> ] [, ...] ] )
      [ RETURNS <return_type> 
        | RETURNS TABLE ( <column_name> <column_type> [, ...] ) ]
  { LANGUAGE <lang_name>
    | TRANSFORM { FOR TYPE <type_name> } [, ... ]
    | WINDOW
    | { IMMUTABLE | STABLE | VOLATILE }
    | { 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>
    | SET <config_param> { TO <value> | = <value> | FROM CURRENT }
    | AS '<definition>'
  } ...

Description

To create a function, you must have the USAGE privilege on the argument types and the return type. You are the owner of the functions that you create, by default.

Relyt uses the function name and the input argument types to uniquely identify a function. Therefore, functions with the same name but different input argument types can exist in the same schema.

CREATE FUNCTION can only be used to create a function. If you want to replace an existing one, use CREATE OR REPLACE FUNCTION.

Only the owner of a function can run CREATE OR REPLACE FUNCTION to replace the function. After the command is run, the owner and the permissions of the function remain the same, while the other parameters are using the values specified in the command.

If you drop and then recreate a function, the new function is a different entity from the dropped one. You also need to drop objects that depend on the dropped function, including rules and views. If you run CREATE OR REPLACE FUNCTION to directly change the function definition, the object dependencies will not be affected.

For more information about the creating a function, see User Defined Functions in the PostgreSQL documentation.

info

If you want to update the name or argument types of a function, drop the function and recreate it. If you try to use CREATE OR REPLACE UFNCTION to achieve this, a new function will be created, instead of replacing the existing one.

CREATE OR REPLACE FUNCTION cannot be used to update the return type of a function.

Parameters

<name>

The name of the function. You can specify the name with the schema qualification to create the function in the specified schema. Otherwise, the function is created in the current schema.
<arg_mode>

The mode of the argument. Available values are IN, OUT, INOUT, and VARIADIC. If you omit this parameter, the default value IN is used. VARIADIC arguments can be followed only by OUT arguments.

OUT and INOUT arguments cannot be used with the RETURNS TABLE notation.
<arg_name>

The name of the argument. SQL and PL/pgSQL allow you to use the name in the function body. Other languages just use input argument names for documentation purposes. You can use input argument names to improve readability.

The name of an output argument is necessary, because the name defines the column name in the result row type. If you omit it, the system will choose a default column name.
<arg_type>

The data type of the argument. You can specify the data type with the schema qualification. Supported data types can be base, composite, and domain types, and can reference from the data type of a table column.

Pseudotypes are also allowed for certain implementation languages, so that you can 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.

When you reference the data type of a column, specify it in the table_name.column_name%TYPE format. By doing this, the function you create can be independent from the changes to the definition of a table.
<default_expr>

The expression to generate the default value if the parameter is not specified. The expression must be coercible to the argument type of the parameter. Note that only IN and INOUT parameters can have default values.

In the argument list, each IN or INOUT parameter that follows a parameter with a default value must have a default value.
<return_type>

The data type of the return value. You can specify the data type with the schema qualification. Supported data types can be base, composite, and domain types, and can reference from the data type of a table column.

Pseudotypes are also allowed for certain implementation languages, so that you can specify pseudotypes such as cstring.

If the function is supposed to return null, set <return_type> to void.

If the function contains OUT or INOUT parameters, you can omit the RETURNS clause. If present, you must set <return_type> to:
- RECORD, if multiple OUT or INOUT parameters exist.
- The same type as the OUT or INOUT parameter, if only one such parameter exists.
In addition, you can use the SETOF modifier to specify that the function will return a set of items, instead of a single one.

To reference the type of a column, specify the type in the table_name.column_name**%TYPE** format.
<column_name>

The name of the output column. This parameter is used in the RETURNS TABLE syntax. RETURNS TABLE is an alternative for declaring a named OUT parameter in a CREATE FUNCTION command specified with the SETOF modifier.
<column_type>

The data type of the output column. This parameter is used in the RETURNS TABLE syntax.
<lang_name>

The name of the language in which the function is implemented. The value can be SQL, C, internal, or the name of a user-defined procedural language.
TRANSFORM { FOR TYPE <type_name> } [, ... ]

The list of transforms to which a call to the function must apply. A transform is used to convert between an SQL type and a language-specific data type. In most cases, built-in types are hard-coded in procedural language implementations. Therefore, this parameter is unnecessary. Even if the specific procedural implementation does not know how to handle a type and no transform is provided, it will use a default behavior when converting data types. The default behavior varies with the implementation.
WINDOW

Specifies that the function is a window function. Only functions implemented in C can be window functions.

When you use CREATE OR REPLACE FUNCTION to change the definition of a function, you cannot change the WINDOW attribute if the function has.
IMMUTABLE, STABLE, or VOLATILE

Specifies the volatibility for the function. You can choose at most one of them for the function. If none is set, VOLATILE is used by default.

An IMMUTABLE function cannot modify the database and returns the same results when given the same argument values. It uses only the information that is directly provided in the argument list, without the need for scanning the database. When a query calls the function with constant arguments, the arguments are immediately replaced with the function value. If a function is IMMUTABLE, you must declare it as IMMUTABLE, since Relyt implements restrictions on the use of VOLATILE functions.

A STABLE function cannot modify the database and returns the same results when given the same argument values within a single table scan.

However, when used in different SQL statements, the same STABLE function with the same argument values can return different results. STABLE is suitable for functions of which the results depend on database lookups and parameter variables.

Functions in the current_timestamp() family are stable functions, because their function values do not change within a single transaction.

A VOLATILE function can return different results when given the same argument values within a single table scan, such as random() and timeofday(). VOLATILE functions are few in amount. No optimizations can be made on VOLATIBLE functions. Functions that have side effects, for example setval(), must also be classified as VOLATILE to prevent calls from being optimized, even when the results are predictable.
CALLED ON NULL INPUT, RETURNS NULL ON NULL INPUT, or STRICT

The behavior of the function when null input arguments exist. Possible values are:
- CALLED ON NULL INPUT: indicates the function will be normally called and is the default.
- RETURNS NULL ON NULL INPUT or STRICT: indicates that the function will not run, with a null value directly returned.
[EXTERNAL] SECURITY INVOKER or [EXTERNAL] SECURITY DEFINER

The user of which the privileges are required for running the function.
- SECURITY INVOKER: the user who calls the function.
- SECURITY DEFINER: the user who creates the function.
Keyword EXTERNAL is optional because this option is available for all functions in Relyt, instead of only external ones. You can specify it to conform to the SQL standard.
PARALLEL

Specifies whether the function can be executed in parallel mode. Supported modes include PARALLEL UNSAFE, PARALLEL RESTRICTED, and PARALLEL SAFE.
- PARALLEL UNSAFE: indicates that the function cannot be executed in parallel mode and is the default. The execution plan of an SQL statement that uses a PARALLEL UNSAFE can only be serial.
  
  Label the function PARALLEL UNSAFE if it will modify any database state, change the transaction, access sequences, or try to make persistent changes to settings.
- PARALLEL RESTRICTED: indicates that only the parallel group leader can execute the function in parallel mode.
  
  Lable the function PARALLEL RESTRICTED if it will access temporary tables, client connection state, cursors, prepared statements, or miscellaneous backend-local state which the system cannot synchronize in parallel mode.
- PARALLEL SAFE: indicates that the function can be executed in parallel mode without restrictions.
In most cases, a function that is defined PARALLEL SAFE but it is unsafe or restricted or that is defined PARALLEL STRICT but is unsafe will produce an error when the function is used in a parallel query. C-language functions can in theory exhibit totally undefined behavior if mislabeled, since the system has no way to protect itself against arbitrary C code, but in most likely cases, the result will be no worse than for any other function. If you are uncertain whether a function is parallel safe or not, label it UNSAFE, which is the default.
COST <execution_cost>

The estimated execution cost of the function, measured in cpu_operator_cost. The value must be a positive number. If the function returns a set, <execution_cost> indicates the cost for each returned row. If the cost is not specified, C-language and internal functions default to 1 unit, while functions in other languages default to 100 units. Larger values cause the planner to try to avoid evaluating the function more often than necessary.
ROWS <result_rows>

The estimated number of rows to return. The value must be a positive number. This parameter can be used only when the function is declared to return a set. The default is 1000.
<config_param> and <value>

You can use the SET clause to specify a value for a session configuration parameter for the function. The configuration parameter setting specified in this way is effective only for the current session. When the function exits, the value of the configuration parameter is restored to the original value.

SET FROM CURRENT saves the current value of the configuration parameter as the value to be applied when the function is entered.

If a function has a SET clause, any SET LOCAL command executed inside the function for the same variable only applies to the function. Once the function exits, the configuration parameter will be restored to its original value. However, an ordinary SET command (without LOCAL) overrides the SET clause, just as it will for a previous SET LOCAL command. The effects of this command will persist after the function exits, unless the current transaction is rolled back.

For more information, see SET.
<definition>

The definition of the function. The value must be a string constant that indicates the internal function name, the path to an object file, an SQL command, or text based on the language used to implement the function.

We recommend that you use dollar quoting to specify the value of <definition>, or you must escape single quotes (' ') or backslashes () in the function definition by doubling them. For details about dollar quoting, see Dollar-Quoted String Constraints in the PostgreSQL documentation.

Overloading

Function overloading indicates that functions with the same name but different input argument types can exist at the same time. This capability introduces security precautions in some cases. For more information, see Functions in the PostgreSQL documentation.

However, if two functions have the same name and input argument types but different OUT argument types, they are regarded as the same function. For example:

CREATE FUNCTION add_user(varchar)...
CREATE FUNCTION add_user(varchar, out text)...

The two declarations conflict.

If two functions have the same name and different argument type lists, they are not considered conflict during creation. However, if defaults are provided, they can conflict while they are in use. For example:

CREATE FUNCTION add_user(varchar) ...
CREATE FUNCTION add_user(varchar, varchar default 'fresh') ...

A call add_user('senior') will fail because the planner does not know which function to use.

Usage notes

CREATE FUNCTION allows using the complete SQL type syntax for input arguments and return value. However, it ignores any parenthesized type modifiers.

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 strict function has a VARIADIC argument, the strictness check regards the variadic array as a whole. This indicates that the function runs properly unless all elements in the array are null.

Writing SECURITY DEFINER functions safely

A SECURITY DEFINER function runs using the privileges of the user that creates the function. Therefore, you must ensure such functions are not misused. search_path is expected not to include any schemas that untrusted users can modify, for security purposes. This helps to prevent malicious activity such as creating objects to mask objects used by the function. The temporary-table schema is of particular concern in this case because it is always searched first and anyone can write in it. To further enhance security, you can change how the temporary schema is searched by setting pg_temp as the last entry in search_path. The following example is a function that ensures 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;

By default, new functions are given execute privilege to PUBLIC. For more details, see GRANT. For security purposes, you may want to limit the use of a security definer function to specific users. To do this, you need to revoke execute privilege for PUBLIC and then selectively grant it to the desired users. To prevent the function from being available to everyone, we recommend that you create 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;

Examples

Create a function to add two integers:

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

Create a function to 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;

Create a function to 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.

Use polymorphic types to return an ENUM array:

CREATE TYPE rainbow AS ENUM('red','orange','yellow','green','blue','indigo','violet');
CREATE FUNCTION return_enum_as_array( anyenum, anyelement, anyelement ) 
    RETURNS TABLE (ae anyenum, aa anyarray) AS $$
    SELECT $1, array[$2, $3] 
$$ LANGUAGE SQL STABLE;

SELECT * FROM return_enum_as_array('red'::rainbow, 'green'::rainbow, 'blue'::rainbow);

SQL standard compatibility

CREATE FUNCTION is defined in SQL:1999 and later. CREATE FUNCTION in Relyt is similar to CREATE FUNCTION in the SQL standard. Attributes and available languages in CREATE FUNCTION in Relyt are not portable.

CREATE FUNCTION

Syntax​

Description​

Parameters​

Overloading​

Usage notes​

Writing SECURITY DEFINER functions safely​

Examples​

SQL standard compatibility​