Help Center/ GaussDB/ Developer Guide(Distributed_2.x)/ SQL Reference/ SQL Syntax/ CREATE FUNCTION
Updated on 2024-10-14 GMT+08:00
View PDF
Share

CREATE FUNCTION

Function

CREATE FUNCTION 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 SELECT statement uses the default values of the output parameters when calling the function. When the CALL statement calls the function, it requires that the output parameters must be specified. When the CALL statement calls an overloaded PACKAGE function, it can use the default values of the output parameters. For details, see examples in CALL.
  • 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 can use the SELECT statement to specify different parameters using identical functions, but cannot use the CALL statement to call 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.
  • In non-logical 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.
  • In logical cluster mode, if return values and parameters of the function are user tables, all the tables must be in the same logical cluster. If the function body involves operations on multiple logical cluster tables, the function cannot be set to IMMUTABLE or SHIPPABLE, preventing the function from being pushed down to a DN. (The current feature is a lab feature. Contact Huawei technical support before using it.)
  • In logical cluster mode, the parameters and return values of the function cannot use %type to reference a table column type. Otherwise, the function will fail to be created. (The current feature is a lab feature. Contact Huawei technical support before using it.)
  • By default, the permissions to execute new functions are granted to PUBLIC. For details, see GRANT. By default, a user inherits the permissions of the PUBLIC role. Therefore, other users also have the permission to execute a function and view the definition of the function. In addition, to execute a function, other users must have the USAGE permission on the schema of the function. When creating a function, the user can revoke the default execution permissions from PUBLIC and grant them to other 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 the permission to execute. For details about how to enable the attribute, see Security Hardening Guide.
  • If a function is defined as an IMMUTABLE or SHIPPABLE function, avoid INSERT, UPDATE, DELETE, MERGE, and DDL operations on 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 plpgsql 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 calling functions without parameters inside another function, you can omit brackets and call functions using their names directly.
  • When functions with output parameters are called inside another function which is an assignment expression, you can omit the output parameters of the called functions.
  • 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. If you want to change the default permission to SECURITY DEFINER, you need to set the GUC parameter behavior_compat_options to 'plsql_security_definer'. For details about the SECURITY DEFINER permission, see sections "Database Configuration > Permission Management" in Security Hardening Guide.

Syntax

  • Syntax (compatible with PostgreSQL) for creating a customized function: 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
    		 
    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'
        | AS 'obj_file', 'link_symbol'
    }
  • Oracle syntax of creating a customized function: 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
    		 
    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
/

Parameter Description

  • function_name

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

    Value range: a string. It must comply with the identifier naming convention.

  • argname

    Specifies the parameter name of the function.

    Value range: a string. It must comply with the identifier naming convention.

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

  • expression

    Specifies the default expression of a parameter.

  • 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 IN OUT parameter, the RETURNS clause can be omitted. If the clause exists, 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.

  • 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, a 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, 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 O-style database is created, the language used is plpgsql.

  • 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 DNs.
    • Functions of the STABLE or VOLATILE type can be pushed down to DNs only if their attribute is SHIPPABLE.

    If SHIPPABLE/IMMUABLE is specified for a function or stored procedure, the function or stored procedure cannot contain EXCEPTION or invoke functions or stored procedures that contain EXCEPTION.

  • PACKAGE
    Specifies whether the function can be overloaded.
    • 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 invoked 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 keyword EXTERNAL is allowed for SQL conformance, but it is optional since, unlike in SQL, this feature applies to all functions not only external ones.

  • SECURITY INVOKER

    AUTHID CURRENT_USER

    Specifies that the function will be executed with the permissions of the user who invokes 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

    Indicates 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 C functions. (The current feature is a lab feature. Contact Huawei technical support before using it.) If this parameter is not specified, the default value FENCED is used.
    • Customize PL/Java function. If this parameter is not specified, the default value FENCED is used and the NOT FENCED execution mode is not supported. (The current feature is a lab feature. Contact Huawei technical support before using it.)
    • Customize PL/pgSQL 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: a positive integer

  • 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: a positive number. 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 closes 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.

  • obj_file, link_symbol

    (Used for C functions) Specifies the absolute path of the dynamic library using obj_file and the link symbol (function name in C programming language) of the function using link_symbol. (The current feature is a lab feature. Contact Huawei technical support before using it.)

  • plsql_body

    Specifies the PL/SQL stored procedure body.

    When a user is created in the function body, the plaintext password is recorded in the log. You are not advised to do it.

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

-- Return the RECORD type.
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.
openGauss=# CREATE FUNCTION func_dup_sql(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

openGauss=# SELECT * FROM func_dup_sql(42);

-- Compute the sum of two integers and return the result (if the input is null, the returned result is null).
openGauss=# CREATE FUNCTION func_add_sql2(num1 integer, num2 integer) RETURN integer
AS
BEGIN 
RETURN num1 + num2;
END;
/
-- Create an overloaded function with the PACKAGE attribute:
openGauss=# create or replace function package_func_overload(col int, col2  int)
return integer package
as
declare
    col_type text;
begin
     col := 122;
         dbe_output.print_line('two int parameters ' || col2);
         return 0;
end;
/

openGauss=# create or replace function package_func_overload(col int, col2 smallint)
return integer package
as
declare
    col_type text;
begin
     col := 122;
         dbe_output.print_line('two smallint parameters ' || col2);
         return 0;
end;
/

-- Alter the execution rule of function add to IMMUTABLE (that is, the same result is returned if the parameter remains unchanged).
openGauss=# ALTER FUNCTION func_add_sql2(INTEGER, INTEGER) IMMUTABLE;

-- Alter the name of function add to add_two_number.
openGauss=# ALTER FUNCTION func_add_sql2(INTEGER, INTEGER) RENAME TO add_two_number;

-- Change the owner of function add to omm.
openGauss=# ALTER FUNCTION add_two_number(INTEGER, INTEGER) OWNER TO omm;

-- Delete the function.
openGauss=# DROP FUNCTION add_two_number;
openGauss=# DROP FUNCTION func_increment_sql;
openGauss=# DROP FUNCTION func_dup_sql;
openGauss=# DROP FUNCTION func_increment_plsql;
openGauss=# DROP FUNCTION func_add_sql;

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.
Parent topic: SQL Syntax