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.
• 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 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.
• ORA-compatible functions are created for ORA-compatible databases and PG-compatible functions are created for PG-compatible databases. Hybrid creation is not recommended.
• 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.
• In common cluster mode, return values, parameters, and variables cannot be set to the tables of the node groups that are not installed in the system by default. The internal statements of SQL functions cannot be executed on such tables.
• 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.
• If a function is defined as IMMUTABLE or SHIPPABLE, avoid INSERT, UPDATE, DELETE, MERGE, and DDL operations in the function because the CN needs to determine the execution node for these operations. Otherwise, an error may occur. If DDL operations are performed on a function of the IMMUTABLE or SHIPPABLE type, database objects on each node may be inconsistent. To resolve this problem, create the VOLATILE PL/SQL function on the CN, run the EXECUTE statement in the function definition to dynamically execute the DDL operation for repairing system objects, and then use the EXECUTE DIRECT ON syntax to call the repair function on the specified DN.
• When functions without parameters are called inside another function, you can omit brackets and call functions using their names directly.
• If the parameter set behavior_compat_options is not set to proc_outparam_override, the return value of the function directly called by an anonymous block or stored procedure 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 the example.
• 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.
• 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.
• 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'.

Syntax

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

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

  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} | COST execution_cost | ROWS result_rows | SET configuration_parameter { {TO | =} value | FROM CURRENT }} ][...] { AS 'definition' | AS 'obj_file', 'link_symbol' };

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

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

  CREATE [ OR REPLACE ] FUNCTION function_name ( [ { argname [ argmode ] argtype [ { DEFAULT | := | = } expression ] } [, ...] ] ) RETURN rettype [ DETERMINISTIC ] [ {IMMUTABLE | STABLE | VOLATILE } | {SHIPPABLE | NOT SHIPPABLE} | {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 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. It must comply with the naming convention. 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. You can use %ROWTYPE to indirectly reference the type of a table, or %TYPE to indirectly reference the type of a column in a table or composite 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.

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

• 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, C, 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 ORA-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

  Specifies that the function is a window function. This is currently only useful for functions written in C. 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 to DNs for execution.

  • Functions of the IMMUTABLE type can always be pushed down to DN.
  • Functions of the STABLE or VOLATILE type can be pushed down to DN only if their attribute is SHIPPABLE.
  

  The function or stored procedure of the SHIPPABLE/IMMUTABLE type cannot contain EXCEPTION or call functions or stored procedures that contain EXCEPTION.

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

• FENCED

  NOT FENCED

  Specifies whether the function is executed in fenced mode or not fenced mode. In NOT FENCED mode, a function is executed in a CN or DN process. In FENCED mode, a function is executed in a new fork process, which does not affect CN or DN processes.

  Application scenarios:

  • Develop or debug a function in FENCED mode and execute it in NOT FENCED mode. This reduces the overhead of the fork process and communication.
  • Perform complex OS operations, such as opening a file, and processing signals and threads in FENCED mode; otherwise, the GaussDB database execution may be affected.
  • Customize PL/SQL functions. If this parameter is not specified, the default value NOT FENCED is used and the FENCED execution mode is not supported.
• 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 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

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140

-- Create an ORA-compatible database.
gaussdb=# CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'ORA';
CREATE DATABASE

-- Switch to another database.
gaussdb=# \c ora_compatible_db

-- Define a function as SQL query.
ora_compatible_db=# 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.
ora_compatible_db=# CREATE OR REPLACE FUNCTION func_increment_plsql(i integer) RETURNS integer AS $$
        BEGIN
            RETURN i + 1;
        END;
    $$ LANGUAGE plpgsql;

-- Return the RECORD type.
ora_compatible_db=# CREATE OR REPLACE FUNCTION compute(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.
ora_compatible_db=# 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.
ora_compatible_db=# 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):
ora_compatible_db=# CREATE FUNCTION func_add_sql2(num1 integer, num2 integer) RETURN integer
    AS
    BEGIN 
        RETURN num1 + num2;
    END;
    /

-- Delete the function.
ora_compatible_db=# DROP FUNCTION func_add_sql;
ora_compatible_db=# DROP FUNCTION func_increment_plsql;
ora_compatible_db=# DROP FUNCTION compute;
ora_compatible_db=# DROP FUNCTION func_dup_sql;
ora_compatible_db=# DROP FUNCTION func_add_sql2;



-- If the parameter set behavior_compat_options is not set to proc_outparam_override, the return value of the function directly called by an anonymous block or stored procedure is used as the first value of the OUT output parameter. As a result, the calling fails.
ora_compatible_db=# CREATE TYPE rec AS(c1 int, c2 int);
ora_compatible_db=# 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;
    /
ora_compatible_db=# 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
ora_compatible_db=# DROP FUNCTION func;
ora_compatible_db=# DROP TYPE rec;

-- The following examples can be executed only in an ORA-compatible database.

-- Create a function containing IN/OUT parameters.
ora_compatible_db=# 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;
    /
ora_compatible_db=# 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.
ora_compatible_db=# 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;
    /
ora_compatible_db=# 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.
ora_compatible_db=# DROP FUNCTION func_001;


-- Switch back to the initial database and delete the test database. Replace postgres with the actual database name.
ora_compatible_db=# \c postgees
gaussdb=# DROP DATABASE ora_compatible_db;

Helpful Links

ALTER FUNCTION and DROP FUNCTION

Suggestions

• analyse | analyze
  • Do not run ANALYZE in a transaction or anonymous block.
  • Do not run ANALYZE in a function or stored procedure.