CREATE FUNCTION_DDL Syntax_SQL Syntax Reference (9.1.0.x)_SQL Syntax Reference

Function

A function is a reusable code block that accepts input parameters, performs specific operations, and returns a value or result set. CREATE FUNCTION is used to create a user-defined function (UDF) in a database, enabling other computing functions that are not supported by the built-in system functions of GaussDB(DWS) and covering complex computing scenarios specific to users.

The functions have the following features:

They support input parameters and return results depending on specific conditions.
You must declare the return value's data type. It outputs either a single value (scalar function) or a table (table-valued function).
By wrapping complex logic into one function, you can call it like a built-in SQL function, enhancing code reuse.

Three GaussDB(DWS) Function Types Based on Behaviors

SQL functions fall into three types based on their behaviors: immutable, stable, and volatile. Table 1 shows their relationships.

**Table 1** Function types
Function Type	Definition	Typical Use Case
immutable	An immutable function's output depends solely on its input parameters, ignoring external factors like database state, time, or transaction context. It consistently produces the same result when given identical inputs, regardless of when or where it is called. Additionally, it does not alter database data.	Mathematical calculations, hashing, and string operations—examples include ABS(x), POWER(a, b), and MD5(str).
stable	A stable function produces consistent results within a single query if the inputs remain unchanged. Its output depends on the database's current state, like table data or session variables, so results may vary across separate queries or transactions.	Tasks like filtering or aggregating live data, such as using MAX(col) or MIN(col), which stay constant during one scan.
volatile	Volatile functions are highly unpredictable. Their results can differ with every call or alter the database state by performing actions like writing data. Since their behavior cannot be predicted, the optimizer must recompute them on each execution.	Tasks like generating random numbers, retrieving timestamps, or executing DML operations. For instance, calling the RANDOM() function produces a new value every time.

You can view the provolatile column in the PG_PROC system catalog to check the function type. (i indicates immutable, s indicates stable, and v indicates volatile.)

Pushing Functions Down to DNs (SHIPPABLE and NOT SHIPPABLE)

The CREATE FUNCTION syntax uses SHIPPABLE and NOT SHIPPABLE to determine if the function runs on the data node (DN).

Functions of the IMMUTABLE type can always be pushed down to the DNs.
Functions of the STABLE or VOLATILE type can be pushed down to DNs only if their attribute is SHIPPABLE.
Exercise caution when defining the SHIPPABLE attribute for a function. SHIPPABLE means that the entire function will be pushed down to DNs for execution. If the attribute is incorrectly set, serious problems such as result errors may occur.

The SHIPPABLE attribute, like IMMUTABLE, comes with usage limits. It requires functions to exclude elements that prevent pushdown. Once pushed down to a DN, the function's calculations must rely solely on the DN's dataset.

Functions cannot be marked as IMMUTABLE if they:

Reference tables or views because their results may change when the data updates.
Call other STABLE or VOLATILE functions.
Include non-pushdown components, which clash with the IMMUTABLE rule. This often involves unsupported pushdown functions or syntax.
Perform aggregations needing STREAM plans (such as the listagg function), as these require computations on both DNs and CNs.

Functions cannot be marked as SHIPPABLE if they:

Reference a hash-distributed table.
Include non-pushdown components, functions, or syntax. For details, see Optimizing Statement Pushdown
Involve calculations with data spread across DNs, such as aggregations.

To optimize performance in GaussDB(DWS), always define function attributes accurately. Incorrect settings can reduce query speed and produce inconsistent results.

Precautions

The precision values (if any) of the parameters or return values of a function are not checked.
Always define a clear schema for table operations in your functions. For instance, use hr.table1 to point to a table in the hr schema. Without this, the function might not run correctly.
current_schema and search_path specified by SET during function creation are invalid. The values of search_path and current_schema after function execution are the same as those before function execution.
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 parameter values are adapted to Oracle. 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 because CALL aligns with Oracle syntax.
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.
In logical cluster mode, the parameters and return values of the function cannot use the %type to reference a table column type. Otherwise, the function will fail to be created.
By default, the permissions to execute new functions are granted to PUBLIC. For details, see GRANT. You 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 in transactions and set function execution permissions.
In a cluster with multiple CNs, the input or output parameters of a function cannot be set to the temporary table type. This is because the correct temporary schema cannot be obtained based on the table name when the function is created on a non-current CN. As a result, the accurate table type cannot be obtained.

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'
        | AS 'obj_file', 'link_symbol'
    }

           

         

       
      

Oracle syntax of creating a customized 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

    ][...] 

    { 
      IS  | AS
} plsql_body
/

           

         

       
      

Parameter Description

**Table 2** CREATE FUNCTION parameters
Parameter	Description	Value Range
function_name	Indicates the name of the function to create (optionally schema-qualified).	Value range: a string. It must comply with the naming convention. For details, see Identifier Naming Conventions. CAUTION: If the name of the function to be created is the same as that of a system function, you are advised to specify a schema. When invoking a user-defined function, you need to specify a schema. Otherwise, the system preferentially invokes the system function.
argname	Indicates the name of a function parameter.	Value range: a string. It must comply with the naming convention.
argmode	Specifies the mode of a function parameter.	Value range: IN, OUT, IN OUT, INOUT, and VARIADIC. IN: input parameter. The default value is IN. OUT: output parameter. Only this type can include VARIADIC. INOUT: both input and output parameters. OUT VARIADIC: output parameter, which is defined as an array type. CAUTION: The parameters of OUT and INOUT cannot be used in function definition of RETURNS TABLE.
argtype	Data types of function parameters.	For details about common data types, see Data Types.
expression	Indicates the default expression of a parameter.	-
rettype	Indicates the return data type.	When there is OUT or IN OUT parameter, the RETURNS clause can be omitted. When this clause is present, the result type matches the specified output parameter. For multiple outputs, the result type becomes RECORD. With only one output, the result type aligns with that single parameter. The SETOF modifier indicates that the function will return a set of items, rather than a single item.
DETERMINISTIC	Adapted to Oracle SQL syntax. You are not advised to use it.	-
column_name	Specifies the column name.	-
column_type	Specifies the column type.	-
definition	Specifies a string constant defining the function; the 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	Indicates the name of the language that is used to implement the function. It can be SQL, internal, or the name of user-defined process language. To ensure downward compatibility, the name can use single quotation marks. Contents in single quotation marks must be capitalized.	-
WINDOW	Indicates that the function is a window function. The WINDOW attribute cannot be changed when the function definition is replaced. CAUTION: For a user-defined window function, the value of LANGUAGE can only be internal, and the referenced internal function must be a window function.	-
IMMUTABLE	Indicates that the function always returns the same result if the parameter values are the same.	If the input argument of the function is a constant, the function value is calculated at the optimizer stage. The advantage is that the expression value can be obtained as early as possible, so the cost estimation is more accurate and the execution plan generated is better. A user-defined IMMUTABLE function is automatically pushed down to DNs for execution, which may cause potential risks. If a function is defined as IMMUTABLE but the function execution process is in fact not IMMUTABLE, serious problems such as result errors may occur. Therefore, exercise caution when defining the IMMUTABLE attribute for a function. Examples: If a user-defined function references objects such as tables and views, the function cannot be defined as IMMUTABLE, because the function may return different results when the data in a referenced table changes. If a user-defined function references a STABLE or VOLATILE function, the function cannot be defined as IMMUTABLE. If a user-defined function contains factors that cannot be pushed down, the function cannot be defined as IMMUTABLE, because the IMMUTABLE attribute conflicts with factors that cannot be pushed down. Typical scenarios include functions and syntax that cannot be pushed down. If a user-defined function contains an aggregation operation that will generate STREAM plans to complete the operation (meaning that DNs and CNs are involved for results calculation, such as the LISTAGG function), the function cannot be defined as IMMUTABLE. To prevent possible problems, you can set behavior_compat_options to check_function_conflicts in the database to check definition conflicts. This method can identify the 1 and 2 scenarios described above.
STABLE	Indicates 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 values, but that its result varies by SQL statements.	-
VOLATILE	Indicates that the function value can change even within a single table scan, so no optimizations can be made.	-
SHIPPABLE NOT SHIPPABLE	Indicates whether the function can be pushed down to DNs for execution.	Functions of the IMMUTABLE type can always be pushed down to the DNs. Functions of the STABLE or VOLATILE type can be pushed down to DNs only if their attribute is SHIPPABLE. Exercise caution when defining the SHIPPABLE attribute for a function. SHIPPABLE means that the entire function will be pushed down to DNs for execution. If the attribute is incorrectly set, serious problems such as result errors may occur. Similar to the IMMUTABLE attribute, the SHIPPABLE attribute has use restrictions. The function cannot contain factors that do not allow the function to be pushed down for execution. If a function is pushed down to a single DN for execution, the function's calculation logic will depend only on the data set of the DN. Examples: If a function references a hash table, you cannot define the function as SHIPPABLE. If a function contains factors, functions, or syntax that cannot be pushed down, the function cannot be defined as SHIPPABLE. For details, see Optimizing Statement Pushdown. If a function's calculation process involves data across DNs, the function cannot be defined as SHIPPABLE. For example, some aggregation operations involve data across DNs.
PACKAGE	Indicates whether the function can be overloaded. PostgreSQL-style functions can be overloaded, and this parameter is designed for Oracle-style functions.	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	Indicates 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	Indicates that the function always returns NULL whenever any of its parameters are NULL. If this parameter is specified, the function is not executed when there are NULL parameters; instead a NULL result is returned automatically. The usage of RETURNS NULL ON NULL INPUT is the same as that of STRICT.	-
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	Indicates that the function is to be executed with the permissions of the user that 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 is to be executed with the permissions of the user that created it. The usage of AUTHID DEFINER is the same as that of SECURITY DEFINER.	-
FENCED NOT FENCED	(Effective only for C functions) Specifies whether functions are executed in 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 cost of the fork process and communication. Perform complex OS operations, such as open a file, process signals and threads, in FENCED mode so that GaussDB(DWS) running is not affected. The default value is FENCED.
COST execution_cost	A positive number giving the estimated execution cost for the function. The unit of execution_cost is cpu_operator_cost.	Value range: A positive number.
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.	Its value is a positive number. The default value is 1000.
configuration_parameter	Specifies the parameter value configured for the function.	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.
plsql_body	Indicates the PL/SQL stored procedure body.	NOTICE: When the function is creating users, the log will record unencrypted passwords. You are not advised to do it.

Example: Calculate the Sum of Two Integers Using an SQL Function

Syntax:

     
          CREATE  OR REPLACE FUNCTION func_add_sql(
integer,                  --The first integer parameter is not named and is referenced by the location parameter $1.
integer                  --The second integer parameter is not named and is referenced by the location parameter $2.
) 
RETURNS integer              --The return type of the function is integer.
    AS 'select $1 + $2;'     --The function body adds the location parameters $1 and $2.
    LANGUAGE SQL             --The SQL language is used to implement this function.
    IMMUTABLE                --The function type is IMMUTABLE, which indicates that the same result is returned for the same input.
    RETURNS NULL ON NULL INPUT;       --It is same as STRICT. If any parameter is NULL, the function returns NULL.

Return the sum of the two input parameters.

     
          SELECT func_add_sql(8,3);

Return null because the input parameter is null.

     
          SELECT func_add_sql(Null,3);

Example: Increment an Integer, in PL/pgSQL

Syntax:

     
          CREATE OR REPLACE FUNCTION func_increment_plsql(
i integer                                 -The input parameter is of the integer type.
) RETURNS integer                       -The return value of the function is of the integer type.
AS $$
        BEGIN                             --The function body begins.       
                RETURN i + 1;             --The return result is the sum of 1 and the input parameter.
        END;
$$ LANGUAGE plpgsql;                     --The function is written in PL/pgSQL.

Return the integer auto-increment result.

     
          SELECT func_increment_plsql(5);

The input parameter is specified by the first column of the numbers table. After the function is used for calculation, the calculation result is returned to the second column.

     
          DROP TABLE IF EXISTS numbers;
CREATE TABLE numbers (value integer);
INSERT INTO numbers VALUES (1), (2), (3), (4), (5);
SELECT value, func_increment_plsql(value) AS incremented_value FROM numbers ORDER BY 1;

Example: Return a Record in PL/pgSQL

     
          CREATE OR REPLACE FUNCTION compute(
i int,                             ---The input parameter is of the integer type.
out result_1 bigint,               --The output result is 1 and the data type is large integer.
out result_2 bigint               --The output result is 2 and the data type is large integer.
)
RETURNS SETOF RECORD               --A record containing more than one output parameter is returned.
AS $$
BEGIN
    result_1 = i + 1;              -- It is used to calculate the first result value. The input value is increased by 1.
    result_2 = i * 10;              -- It is used to calculate the second result value. The input value is multiplied by 10.
RETURN next;                       --It returns the current calculation result and then continue to execution (multiple rows can be returned).
END;
$$LANGUAGE plpgsql;                --The function is written in PL/pgSQL.

Return the following information when the function is called once.

     
          SELECT * FROM compute(5);

Generate a sequence of 1, 2, and 3, and call the compute function for each value in the sequence.

     
          SELECT i, (compute(i)).*        --Call the compute function first, and then expand all columns returned by the function.
FROM generate_series(1, 3)      --Generate a temporary sequence containing 1, 2, and 3 to assign values to i.
AS t(i);                         --Assign the result to temporary table t, in which i is the column name.

The data in table test_data is used. The value column in the table is used as the input parameter of the compute function. The calculation result is as follows:

     
          DROP TABLE IF EXISTS test_data;
CREATE TABLE test_data (id serial, value int);
INSERT INTO test_data (value) VALUES (2), (4), (6);

SELECT id, value, (compute(value)).* 
FROM test_data ORDER BY 1;

Example: Create an SQL Function that Returns Multiple Output Parameters

Syntax:

     
          CREATE OR REPLACE FUNCTION func_dup_sql(
in int,                              --The input parameter is of the integer type.
out f1 int,                          --The first output parameter is of the integer type.
out f2 text)                         --The second output parameter is of the text type.
 AS $$ 
SELECT $1, CAST($1 AS text) || 's text' -- Function body. The query returns two columns. The first column is the input parameter $1, and the second column is the parameter converted to text and concatenated with is text.
 $$   LANGUAGE SQL;

Execute a function.

     
          SELECT * FROM func_dup_sql(42);

Example: Create an Overloaded Function with the PACKAGE Attribute

Define two overloaded functions named package_func_overload in package. The two functions have different meanings depending on the parameter type.

     
      
        
        
          CREATE OR REPLACE FUNCTION package_func_overload(
col int,                                     --The first input parameter is of the integer type.
col2  int                                    --The second input parameter is of the integer type.
)
RETURN integer PACKAGE
AS
DECLARE
    col_type text;                   -- Declare a local variable.
BEGIN
     col := 122;                     --Set the first parameter to 122.
     dbms_output.put_line('two int parameters ' || col2);
     RETURN 0;                       --Return the integer 0.
END;
/

CREATE OR REPLACE FUNCTION package_func_overload(
col int, 
col2 smallint
)
RETURN integer PACKAGE
AS
DECLARE
    col_type text;
BEGIN
     col := 122;
     dbms_output.put_line('two smallint parameters ' || col2);
     RETURN 0;
END;
/

         

       

     
    

Function Pushdown Logic and Example

Pushdown logic for functions has two main aspects:

The system checks if the entire function can be pushed down based on its SHIPPABLE attribute.
If the full function is not pushed down, the optimizer automatically determines which individual SQL statements within the function can be executed separately.

Function-invoking Statements Without User Tables

When a function is called without referencing the user table, the statement runs on the CN, and the function body is not pushed down. Here is how it works:

First, execute the function. Since the statement does not include the user table, it always runs on the CN.
Next, execute the statements inside the function body. These can be pushed down if nothing prevents it (using FQS or STREAM plan frameworks).

Calling a function alone (without user tables) with SQL join queries still produces accurate results.
Pushdown only matters if the function call includes user tables directly.
Execution plans within the function are not visible by default. Setting enable_track_record_subsql to on to log them.

Example: The function body is NOT SHIPPABLE, and the calling statement lacks the user table.

       
        
          
          
            CREATE TABLE student (               
        id integer,                   
        name character varying(50),   
        age integer                   
)                                     
WITH (orientation=row, compression=no)
DISTRIBUTE BY HASH(id);

CREATE TABLE stuscore(               
        id integer,                   
        score integer
)                                     
WITH (orientation=row, compression=no)
DISTRIBUTE BY HASH(id);

INSERT INTO student(id,name,age) VALUES(1,'bob',17),(3,'peter',18),(4,'david',15),(8,'alice','17');
INSERT INTO stuscore(id,score) VALUES(1,88),(3,68),(8,100),(4,90);

--Create a NOT SHIPPABLE function.
CREATE OR REPLACE FUNCTION get_student_id(name character varying)
    RETURNS TABLE(id integer)
    LANGUAGE plpgsql
    VOLATILE NOT FENCED NOT SHIPPABLE
AS $function$
DECLARE
BEGIN
    RETURN QUERY (SELECT id FROM mytest.student WHERE name=$1);
END $function$;

           

         

       
      

Check the execution plan for this function call.

       
            EXPLAIN VERBOSE SELECT * FROM get_student_id('alice');

Since the user table is missing, the results do not indicate if the function was pushed down, as pushdown only matters when the user table is present.

Function-invoking Statements with User Tables

When a function-calling statement includes a user table, its pushdown capability depends on the function's SHIPPABLE attribute.

Example: The function attribute is NOT SHIPPABLE and the calling statement contains a user table.

Check the execution plan to confirm that the function cannot be pushed down.

       
            EXPLAIN VERBOSE SELECT get_student_id('alice') FROM stuscore;

Click to enlarge

Example: The function attribute is SHIPPABLE and the calling statement contains a user table.

       
            --Change the function attribute to SHIPPABLE.
CREATE OR REPLACE FUNCTION get_student_id(name character varying)
    RETURNS TABLE(id integer)
    LANGUAGE plpgsql
    VOLATILE NOT FENCED SHIPPABLE
AS $function$
DECLARE
BEGIN
    RETURN QUERY (SELECT id FROM mytest.student WHERE name=$1);
END $function$;

Check the execution plan to confirm that the function can be pushed down.

       
            EXPLAIN VERBOSE SELECT get_student_id('alice') FROM stuscore;

Click to enlarge

Helpful Links

ALTER FUNCTION, DROP FUNCTION

CREATE FUNCTION

Function

Three GaussDB(DWS) Function Types Based on Behaviors

Pushing Functions Down to DNs (SHIPPABLE and NOT SHIPPABLE)

Precautions

Syntax

Parameter Description

Example: Calculate the Sum of Two Integers Using an SQL Function

Example: Increment an Integer, in PL/pgSQL

Example: Return a Record in PL/pgSQL

Example: Create an SQL Function that Returns Multiple Output Parameters

Example: Create an Overloaded Function with the PACKAGE Attribute

Function Pushdown Logic and Example

Function-invoking Statements Without User Tables

Function-invoking Statements with User Tables

Helpful Links

Feedback

Was this page helpful?