CREATE FUNCTION

Description

Creates a function.

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.
• When a stored procedure is created, a write lock is added only to the CREATE stored procedure or package, and a read lock is added only to the functions and packages on which the functions depend during compilation and execution.
• current_schema and search_path specified by SET during function creation are invalid. search_path and current_schema before and after function execution should be the same.
• 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 or those with the PACKAGE attribute can be overloaded. After REPLACE is specified, a new function is created instead of replacing a function if the number of parameters, parameter type, or return value is different.
• You cannot create overloaded functions with different formal parameter names (the function name and parameter list type are the same).
• You cannot create a function that has the same name and parameter list as a stored procedure.
• 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.
• A-compatible functions are created for A-compatible databases and PG-compatible functions are created for PG-compatible databases. Hybrid creation is not recommended.
• If a function supports overloading, you need to add the keyword PACKAGE.
• 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. The syntax does not support calling identical functions without the PACKAGE attribute.
• 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. For details, see GRANT. 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 execution 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.
• Oracle-compatible functions support viewing, exporting, and importing parameter comments.
• Oracle-compatible functions support viewing, exporting, and importing comments between IS/AS and plsql_body.
• Users granted with the CREATE ANY FUNCTION permission can create or replace functions in the user schemas.
• The default permission on a function is SECURITY INVOKER. To change the permission to SECURITY DEFINER, set the GUC parameter behavior_compat_options to 'plsql_security_definer'.
• If the parameter set behavior_compat_options is not set to proc_outparam_override, the OUT and IN OUT output parameters of the function directly called by an anonymous block or stored procedure cannot be of the composite type, and the return value is used as the first value of the OUT output parameter. As a result, the calling fails. To correctly use the OUT and IN OUT output parameters, set the parameter set behavior_compat_options to proc_outparam_override. For details, see Examples.
• 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:
  1. 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.
  2. The out parameter must be added no matter whether the SELECT or CALL statement is used to call a stored procedure.
  3. In some scenarios, functions cannot be used in expressions (compared with those before the parameter is enabled), for example, left assignment in a stored procedure and the call function. For details, see Examples.
  4. Functions without return cannot be called. The perform function can be used to call functions.
  5. When a function is called in a stored procedure, out/inout cannot be set to a constant. For details, see Examples.
  6. After the GUC parameter behavior_compat_options is set to 'proc_outparam_override', if the return type of the function is setof, the output parameter will not take effect.

Syntax

• Syntax (compatible with PostgreSQL) for creating a user-defined function:

  CREATE [ OR REPLACE  ] FUNCTION function_name 
      [ ( [  { argname [ argmode  ] argtype [  { DEFAULT  | :=  | =  } expression  ]}  [, ...]  ] ) ]
      [ RETURNS rettype [ DETERMINISTIC  ]  | RETURNS TABLE (  { column_name column_type  }  [, ...] )]
      LANGUAGE lang_name 
      [ 
         {IMMUTABLE  | STABLE  | VOLATILE }
          | {SHIPPABLE | NOT SHIPPABLE}
          | WINDOW
          | [ NOT  ] LEAKPROOF  
          | {CALLED ON NULL INPUT  | RETURNS NULL ON NULL INPUT | STRICT } 
          | {[ EXTERNAL  ] SECURITY INVOKER | [ EXTERNAL  ] SECURITY DEFINER | AUTHID DEFINER  | AUTHID CURRENT_USER} 
          | {fenced | not fenced}
          | {PACKAGE}
          | COST execution_cost
          | ROWS result_rows
          | SET configuration_parameter { {TO | =} value | FROM CURRENT }}
       ][...]
      {
          AS 'definition'
      };

• O-compatible syntax for creating a user-defined function:

  CREATE [ OR REPLACE  ] FUNCTION function_name 
      ( [  { argname [ argmode  ] argtype [  { DEFAULT | := | =  } expression  ] }  [, ...]  ] )
      RETURN rettype [ DETERMINISTIC  ]
      [ 
          {IMMUTABLE  | STABLE  | VOLATILE } 
          | {SHIPPABLE | NOT SHIPPABLE}
          | {PACKAGE}
          | {FENCED | NOT FENCED}
          | [ NOT  ] LEAKPROOF  
          | {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT } 
          | {[ EXTERNAL  ] SECURITY INVOKER  | [ EXTERNAL  ] SECURITY DEFINER | AUTHID DEFINER | AUTHID CURRENT_USER} 
          | COST execution_cost  
          | ROWS result_rows  
          | SET configuration_parameter { {TO | =} value  | FROM CURRENT }
          | LANGUAGE lang_name
      ][...] 
      { 
        IS  | AS
      } plsql_body
  /

Parameters

• function_name

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

  Value range: a string. It must comply with the naming convention. The value can contain 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. It must comply with the naming convention. The value can contain 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 parameters of OUT 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. For details, see Variable Definition Statements.

• 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.

• DETERMINISTIC

  Specifies an API compatible with the SQL syntax. You are advised not to use it.

• 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.

  Due to compatibility issues, no matter which language is specified when an A-style database is created, the language used is plpgsql.

  

  • 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.
• WINDOW

  Indicates that this function is a window function. The WINDOW attribute cannot be changed when replacing an existing function definition.

  

  For a customized window function, the value of LANGUAGE can only be internal, and the referenced internal function must be a window function.

• 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.

• FENCED|NOT FENCED

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

• PACKAGE
  Specifies whether the function can be overloaded. PostgreSQL-style functions can be overloaded, and this parameter is designed for functions of other styles.

  • All PACKAGE and non-PACKAGE functions cannot be overloaded or replaced.
  • PACKAGE functions do not support parameters of the VARIADIC type.
  • The PACKAGE attribute of functions cannot be modified.
• LEAKPROOF

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

• CALLED ON NULL INPUT

  Declares that some parameters of the function can be called in normal mode if the parameter values are null. This parameter can be omitted.

• RETURNS NULL ON NULL INPUT

  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.

  RETURNS NULL ON NULL INPUT and STRICT have the same functions.

• EXTERNAL

  The purpose is to be compatible with SQL statements and it is optional. This feature applies to all functions, not only external functions.

• SECURITY INVOKER

  AUTHID CURRENT_USER

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

  SECURITY INVOKER and AUTHID CURRENT_USER have the same functions.

• SECURITY DEFINER

  AUTHID DEFINER

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

  AUTHID DEFINER and SECURITY DEFINER have the same functions.

• 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.

• configuration_parameter
  • value

    Sets a specified database session parameter to a specified value. If the value is DEFAULT or RESET, the default setting is used in the new session. OFF disables the setting.

    Value range: a string

    • DEFAULT
    • OFF
    • RESET

    Specifies the default value.

  • FROM CURRENT

    Uses the value of configuration_parameter of the current session.

• plsql_body

  Specifies the PL/SQL stored procedure body.

  

  When you are performing operations in the function body related to password or key, such as creating a user, changing a password, encrypting or decrypting, the system catalog and log record plaintext information about the password or key. 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.
gaussdb=# CREATE FUNCTION func_add_sql(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

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

-- Return the RECORD type.
gaussdb=# CREATE OR REPLACE FUNCTION func_increment_sql(i int, out result_1 bigint, out result_2 bigint)
    RETURNS SETOF RECORD
    AS $$
    BEGIN
        result_1 = i + 1;
        result_2 = i * 10;
        RETURN next;
    END;
    $$LANGUAGE plpgsql;

-- Return a record containing multiple output parameters.
gaussdb=# CREATE FUNCTION func_dup_sql(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

-- Call the func_dup_sql function.
gaussdb=# SELECT * FROM func_dup_sql(42);
 f1 |     f2     
----+------------
 42 | 42 is text
(1 row)

-- Compute the sum of two integers and returning the result (if the input is null, the returned result is null):
gaussdb=# CREATE FUNCTION func_add_sql2(num1 integer, num2 integer) RETURN integer
    AS
    BEGIN
        RETURN num1 + num2;
    END;
    /


-- Delete the function.
gaussdb=# DROP FUNCTION func_add_sql;
gaussdb=# DROP FUNCTION func_increment_plsql;
gaussdb=# DROP FUNCTION func_increment_sql;
gaussdb=# DROP FUNCTION func_dup_sql;
gaussdb=# DROP FUNCTION func_add_sql2;


-- Set parameters.
gaussdb=# SET behavior_compat_options='proc_outparam_override';

-- Create a function.
gaussdb=# CREATE OR REPLACE FUNCTION func1(in a integer, out b integer)
    RETURNS int
    AS $$
    DECLARE
        c int;
        BEGIN
            c := 1;
            b := a + c;
            RETURN c;
        END; $$
    LANGUAGE 'plpgsql' NOT FENCED;

-- Return return and output parameters at the same time.
gaussdb=# DECLARE
        result integer;
        a integer := 2;
        b integer := NULL;
    BEGIN
        result := func1(a => a, b => b);
        raise info 'b is: %', b;
        raise info 'result is: %', result;
    END;
    /
INFO:  b is: 3
INFO:  result is: 1
ANONYMOUS BLOCK EXECUTE

-- Left assignment expressions are not supported.
gaussdb=# DECLARE
        result integer;
        a integer := 2;
        b integer := NULL;
    BEGIN
        result := func1(a => a, b => b) + 1;
        raise info 'b is: %', b;
        raise info 'result is: %', result;
    END;
    /
ERROR:  when invoking function func1, maybe input something superfluous.
CONTEXT:  compilation of PL/SQL function "inline_code_block" near line 3

-- out/inout in a stored procedure cannot be set to a constant.
gaussdb=# DECLARE
        result integer;
        a integer := 2;
        b integer := NULL;
    BEGIN
        result := func1(a => a, b => 10);
        raise info 'b is: %', b;
        raise info 'result is: %', result;
    END;
    /
ERROR:  when invoking function func1, no destination for argments "b"
CONTEXT:  compilation of PL/SQL function "inline_code_block" near line 3

-- out/inout in stored procedures can be set to a variable.
gaussdb=# DECLARE
        result integer;
        a integer := 2;
        b integer := NULL;
    BEGIN
        result := func1(a,b);
        raise info 'b is: %', b;
        raise info 'result is: %', result;
    END;
    /
INFO:  b is: 3
INFO:  result is: 1
ANONYMOUS BLOCK EXECUTE

-- Delete the func1 function.
gaussdb=# DROP FUNCTION func1;

-- If the parameter set behavior_compat_options is not set to 'proc_outparam_override', the OUT and IN OUT output parameters of the function directly called by an anonymous block or stored procedure cannot be of the composite type, and the return value is used as the first value of the OUT output parameter. As a result, the call fails.
gaussdb=# CREATE TYPE rec as(c1 int, c2 int);
gaussdb=# CREATE OR REPLACE FUNCTION func(a in out rec, b in out int) RETURN int
    AS
    BEGIN
        a.c1:=100;
        a.c2:=200;
        b:=300;
        return 1;
    END;
    /
gaussdb=# DECLARE
        r rec;
        b int;
    BEGIN
  func(r,b);		-- Not supported
    END;
    /
ERROR:  cannot assign non-composite value to a row variable
CONTEXT:  PL/SQL function inline_code_block line 4 at SQL statement
gaussdb=# DROP FUNCTION func;
gaussdb=# DROP TYPE rec;

-- The following examples can be executed only in an A-compatible database:
gaussdb=# CREATE OR REPLACE PACKAGE pkg_type AS
type table_of_index_int is table of integer index by integer; -- Create an integer type.
type table_of_index_int01 is table of table_of_index_int index by integer; -- Create a nested integer type.
type table_of_index_var is table of integer index by varchar(5); -- Create a varchar type.
type table_of_index_var01 is table of table_of_index_var index by varchar(5); -- Create a nested varchar type.
    END pkg_type;
    /

-- Create a function that returns results of the table of integer index by integer type.
gaussdb=# CREATE OR REPLACE FUNCTION func_001(a in out pkg_type.table_of_index_int, b in out pkg_type.table_of_index_var) --#add in & inout #defult value
    RETURN pkg_type.table_of_index_int
    AS
        table_of_index_int_val pkg_type.table_of_index_int;
        table_of_index_var_val pkg_type.table_of_index_var;
    BEGIN
        for i in 1..2 loop
            table_of_index_int_val(i) := i;
            a(i) := i;
            table_of_index_var_val(i) := i;
            b(i) := i;
        end loop;
        raise info '%',table_of_index_int_val;
        raise info '%',table_of_index_var_val;
        raise info '%',a;
        raise info '%',b;
        RETURN table_of_index_int_val;
    END;
    /
gaussdb=# DECLARE
        table_of_index_int_val pkg_type.table_of_index_int;
        table_of_index_var_val pkg_type.table_of_index_var;
    begin
        func_001(table_of_index_int_val,table_of_index_var_val);
    end;
    /
INFO:  {indexbyType:int,1=>1,2=>2}
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement
INFO:  {indexbyType:varchar,"1"=>1,"2"=>2}
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement
INFO:  {indexbyType:int,1=>1,2=>2}
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement
INFO:  {indexbyType:varchar,"1"=>1,"2"=>2}
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement
ERROR:  expression is of wrong type
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement

-- Create a function containing IN/OUT parameters.
gaussdb=# CREATE OR REPLACE FUNCTION func_001(a in out date, b in out date) --#add in & inout #defult value
    RETURN integer
    AS
    BEGIN
        raise info '%', a;
        raise info '%', b;
        RETURN 1;
    END;
    /
gaussdb=# DECLARE
        date1 date := '2022-02-02';
        date2 date := '2022-02-02';
    BEGIN
        func_001(date1, date2);
    END;
    /
INFO:  2022-02-02 00:00:00
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement
INFO:  2022-02-02 00:00:00
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement
ERROR:  invalid input syntax for type timestamp: "1"
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement

-- Create a function containing IN/OUT parameters.
gaussdb=# CREATE OR REPLACE FUNCTION func_001(a in out INT, b in out date) --#add in & inout #defult value
    RETURN INT
    AS
    BEGIN
        raise info '%', a;
        raise info '%', b;
        RETURN a;
    END;
    /
gaussdb=# DECLARE
        date1 int := 1;
        date2 date := '2022-02-02';
    BEGIN
        func_001(date1, date2);
    END;
    /
INFO:  1
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement
INFO:  2022-02-02 00:00:00
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement
ANONYMOUS BLOCK EXECUTE

-- Delete the function.
gaussdb=# DROP FUNCTION func_001;

-- Delete the package.
gaussdb=# DROP PACKAGE pkg_type;

Helpful Links

ALTER FUNCTION and DROP FUNCTION