CREATE FUNCTION

Description

Creates a function.

This function is for internal use only and not available to users.

Precautions

• If the parameters or return values of a function have precision, the precision is not checked.
• When creating a function, you are advised to explicitly specify the schemas of tables in the function definition. Otherwise, the function may fail to be executed.
• If a function has output parameters, the GUC parameter set behavior_compat_options must be set to 'proc_outparam_override' for the output parameters to take effect. When the function is called using SELECT or CALL, an actual parameter must be provided in the position of the output parameter. Otherwise, the function fails to be called.
• Only the functions compatible with PostgreSQL can be overloaded.
• You cannot create overloaded functions with different formal parameter names (the function name and parameter list type are the same).
• Formal parameters cannot be overloaded if only the custom ref cursor type is different from the sys_refcursor type.
• Function overloading is not supported if only the returned data types are different.
• Function overloading is not supported if only the default values are different.
• When an overloaded function is called, the variable type must be specified.
• If an undeclared variable is used in a function, an error is reported when the function is called.
• You can use the SELECT statement to specify different parameters using identical functions.
• When you create a function, you cannot insert other agg functions out of the avg function or other functions.
• By default, the permissions to execute new functions are granted to PUBLIC users. By default, a user inherits the permissions of the PUBLIC role. Therefore, the user has the permission to execute a function and view the definition of the function. In addition, to execute the function, the user must have the USAGE permission on the schema to which the function locates. You can revoke the default execution permissions from the PUBLIC role when creating a function and grant the function execute permission to users as needed. To avoid the time window during which new functions can be accessed by all users, create functions and set function execution permissions in a transaction. After the database object isolation attribute is enabled, common users can view only the definitions of functions that they have permission to execute.
• When functions without parameters are called inside another function, you can omit brackets and call functions using their names directly.
• When other functions with output parameters are called in a function and an assignment expression, set the GUC parameter set behavior_compat_options to 'proc_outparam_override', define variables of the same type as the output parameters in advance, and use the variables as output parameters to call other functions with output parameters for the output parameters to take effect. Otherwise, the output parameters of the called functions will be ignored.
• Users granted with the CREATE ANY FUNCTION permission can create or replace functions in the user schemas.
• The default permission on a function is AUTHID CURRENT_USER. To change the permission to AUTHID DEFINER, set the GUC parameter behavior_compat_options to 'plsql_security_definer'.
• For PL/SQL functions, after behavior_compat_options is set to 'proc_outparam_override', the behaviors of out and inout change. In the functions, return, out, and inout can be returned at the same time. Before the parameter is set to 'proc_outparam_override', only return is returned. For details, see Examples.
• For PL/SQL functions, after behavior_compat_options is set to 'proc_outparam_override', the restrictions are as follows:
  • If a function with the out/inout parameter already exists in the same schema or package, you cannot create another function with the same name with the out/inout parameter.
  • In some scenarios, functions cannot be used in expressions (compared with those before the parameter is enabled), for example, call function.
  • Functions without return cannot be called. The perform function can be used to call functions.
  • After the GUC parameter is set to proc_outparam_override, if the return type of the function is setof, the output parameter will not take effect.

Syntax

CREATE OR REPLACE FUNCTION function_name 
    ( [ { argname [ argmode ] argtype [ { DEFAULT | := | = } expression ]} [, ...] ] )
    [ RETURNS rettype | RETURNS TABLE ( { column_name column_type } [, ...] ) ]
    LANGUAGE lang_name 
    [ 
       {IMMUTABLE | STABLE | VOLATILE}
        | {SHIPPABLE | NOT SHIPPABLE}
        | [ NOT ] LEAKPROOF  
        | STRICT
        | {AUTHID DEFINER | AUTHID CURRENT_USER} 
        | not fenced
        | COST execution_cost
        | ROWS result_rows
     ][...]
    {
        AS 'definition'
    };

Parameters

• function_name

  Specifies the name of the function to be created (optionally schema-qualified).

  Value range: a string complying with Identifier Description. The value contains a maximum of 63 characters. If the value contains more than 63 characters, the database truncates it and retains the first 63 characters as the function name.

  

  You are advised not to create a function with the same name as a system function. Otherwise, you need to specify the schema of the function when calling the function.

• argname

  Specifies the parameter name of the function.

  Value range: a string complying with Identifier Description. The value contains a maximum of 63 characters. If the value contains more than 63 characters, the database truncates it and retains the first 63 characters as the function parameter name.

• argmode

  Specifies the parameter mode of the function.

  Value range: IN, OUT, INOUT, and VARIADIC. The default value is IN. Only the parameter of the OUT mode can be followed by VARIADIC. The parameters of OUT and INOUT cannot be used in the function definition of RETURNS TABLE.

  

  VARIADIC specifies parameters of the array type.

• argtype

  Specifies the data type of a function parameter. %TYPE or %ROWTYPE can be used to indirectly reference a variable or table type.

• expression

  Specifies the default expression of a parameter.

  

  • If a_format_version is set to 10c and a_format_dev_version is set to s2, the default expression is not supported when the parameter is in INOUT mode.
  • It is recommended that you define all default parameters after all non-default parameters.
  • If a function with default parameters is called, input parameters are added to the function from left to right. If inputs of non-default parameters are missing, an error is reported.
  • If proc_uncheck_default_param is enabled and a function with default parameters is called, input parameters are added to the function from left to right. The number of defaulted inputs depends on the number of default parameters. If an input of a non-default parameter is missing, the previous default value is used to fill this parameter.
  • When a_format_version is set to 10c, a_format_dev_version is set to s1, proc_outparam_override is disabled, and the function parameters include the output parameter out and default, the default value cannot be used.
• rettype

  Specifies the return data type. Same as argtype, %TYPE or %ROWTYPE can also be used to indirectly reference types.

  When there is OUT or INOUT parameter, the RETURNS clause can be omitted. If the clause is not omitted, the result type of the clause must be the same as that of the output parameter. If there are multiple output parameters, the result type of the clause is RECORD. Otherwise, the result type of the clause is the same as that of a single output parameter.

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

  

  In FUNCTION argtype and rettype outside PACKAGE, %TYPE cannot reference the type of the PACKAGE variable.

• column_name

  Specifies the column name.

• column_type

  Specifies the column type.

• definition

  Specifies a string constant defining a function. Its meaning depends on the language. It can be an internal function name, a path pointing to a target file, an SQL query, or text in a procedural language.

• LANGUAGE lang_name

  Specifies the name of the language that is used to implement the function. It can be SQL, internal, or the name of a customized process language. To ensure downward compatibility, the name can use single quotation marks. Contents in single quotation marks must be capitalized.

  

  • When an internal function is defined, if AS specifies the function as an internal system function, the parameter type, number of parameters, and return value type of the new function must be the same as those of the internal system function, and the user who creates the internal function must have the permission to execute the internal system function.
  • Only users with the SYSADMIN permission can create internal functions.
• IMMUTABLE

  Specifies that the function always returns the same result if the parameter values are the same.

• STABLE

  Specifies that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same parameter value, but its result varies by SQL statements.

• VOLATILE

  Specifies that the function value can change in a single table scan and no optimization is performed.

• SHIPPABLE|NOT SHIPPABLE

  Specifies whether the function can be pushed down for execution. This port is reserved and is not recommended.

• NOT FENCED

  Specifies whether the user-defined C function is executed in not-fenced mode. This port is reserved and is not recommended.

• LEAKPROOF

  Specifies that the function has no side effects. LEAKPROOF can be set only by the system administrator.

• STRICT

  Specifies that the function always returns null whenever any of its parameters is null. If this parameter is specified, the function is not executed when there are null parameters; instead a null result is returned automatically.

• AUTHID CURRENT_USER

  Specifies that the function will be executed with the permissions of the user who calls it. This parameter can be omitted.

• AUTHID DEFINER

  Specifies that the function will be executed with the permissions of the user who created it.

• COST execution_cost

  Estimates the execution cost of a function.

  The unit of execution_cost is cpu_operator_cost.

  Value range: ≥ 0

• ROWS result_rows

  Estimates the number of rows returned by the function. This is only allowed when the function is declared to return a set.

  Value range: ≥ 0. The default value is 1000.

  

  When you perform password-related operations, such as user creation, password change, and encryption/decryption, in a function body, the password will be recorded in the system catalogs and logs in plaintext. To prevent sensitive information leakage, you are advised not to perform operations on the function body related to sensitive information, such as passwords or keys.

Examples

-- Define a function as SQL query.
m_db=# CREATE OR REPLACE FUNCTION func_add_sql(integer, integer) RETURNS bigint
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    SHIPPABLE
    STRICT
    AUTHID DEFINER
    not fenced
    COST 10000;

-- Add an integer by parameter name using PL/pgSQL.
m_db=# CREATE OR REPLACE FUNCTION func_increment_plsql(i integer) RETURNS integer AS $$
    BEGIN
        RETURN i + 1;
    END;
    $$ LANGUAGE plpgsql;

-- Create an internal function.
m_db=# CREATE OR REPLACE FUNCTION func_test(name) RETURNS text LANGUAGE INTERNAL AS 'series_internal';

-- Create a function with the same name.
m_db=# CREATE OR REPLACE FUNCTION func_add(int) RETURNS int AS $$
    BEGIN
        RETURN $1+10;
    END;
    $$ LANGUAGE PLPGSQL;

m_db=# CREATE OR REPLACE FUNCTION func_add(int,int) RETURNS int AS $$
    BEGIN
        RETURN $1+$2;
    END;
    $$ LANGUAGE PLPGSQL;

-- Call a function.
m_db=# SELECT func_add_sql(3, 7);
 func_add_sql 
--------------
           10
(1 row)

m_db=# SELECT func_increment_plsql(5);
 func_increment_plsql 
----------------------
                    6
(1 row)

m_db=# SELECT func_add(4);
 func_add 
----------
       14
(1 row)

m_db=# SELECT func_add(2,7);
 func_add 
----------
        9
(1 row)

-- Delete the function.
m_db=# DROP FUNCTION func_add_sql;
m_db=# DROP FUNCTION IF EXISTS func_increment_plsql;
m_db=# DROP FUNCTION func_test() CASCADE;
m_db=# DROP FUNCTION func_add(int);
m_db=# DROP FUNCTION IF EXISTS func_add(int,int) RESTRICT;

Helpful Links

DROP FUNCTION