• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. CREATE FUNCTION

CREATE FUNCTION

Function

CREATE FUNCTION creates a function.

Precautions

  • The precision values (if any) of the parameters or return values of a function are 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 parameter values are adapted to Oracle. For details, see examples in CALL.
  • The function can be reloaded. You can use the SELECT statement to specify different parameters using identical functions, but cannot use the CALL statement to do so.
  • When you create a function, you cannot insert other agg functions out of the avg function or other functions.
  • Currently, 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 run on such tables.

Syntax

  • Syntax (compatible with PostgreSQL) of creating a customized 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 }
    
            | 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'
        }
    
    
  • Oracle syntax of creating a customized function:
    CREATE [ OR REPLACE  ] FUNCTION function_name 
        ( [  { argname [ argmode  ] argtype [  { DEFAULT | := | =  } expression  ] }  [, ...]  ] )
        RETURN rettype [ DETERMINISTIC  ]
        [ 
            {IMMUTABLE  | STABLE  | VOLATILE } 
    
            | [ 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

  • function_name

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

    Value range: A string. It must comply with the naming convention rule.

  • argname

    Indicates the name of a function parameter.

    Value range: A string. It must comply with the naming convention rule.

  • argmode

    Indicates the mode of a parameter.

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

    NOTE:

    VARIADIC specifies parameters of array types.

  • argtype

    Indicates the data types of the function's parameters.

  • 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. If the clause exists, it must be the same as the result type indicated by the output parameter. If there are multiple output parameters, the value is RECORD. Otherwise, the value is the same as the type of a single output parameter.

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

  • DETERMINISTIC

    The adaptation 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, C, 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. This is currently only useful for functions written in C. The WINDOW attribute cannot be changed when replacing an existing function definition.

  • IMMUTABLE

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

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

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

    Value range: A positive number. The default is 1000 rows.

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

  • plsql_body

    Indicates the PL/SQL stored procedure body.

    When the function is creating users, the log will record unencrypted passwords. You are not advised to do it.

      

Examples

-- Define the function as SQL query:
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:
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:
CREATE FUNCTION func_dup_sql(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM func_dup_sql(42);

-- Self-increase by an integer:
CREATE OR REPLACE FUNCTION func_increment_sql( i integer) RETURN integer 
AS
BEGIN
        RETURN i + 1;
END;
/

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

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

-- Alter the name of function add to add_two_number:
ALTER FUNCTION func_add_sql2(INTEGER, INTEGER) RENAME TO add_two_number;

-- Change the owner of the function to dbadmin:
ALTER FUNCTION add_two_number(INTEGER, INTEGER) OWNER TO dbadmin;

-- Delete the function:
DROP FUNCTION add_two_number;
DROP FUNCTION func_increment_sql;
DROP FUNCTION func_dup_sql;
DROP FUNCTION func_increment_plsql;
DROP FUNCTION func_add_sql;

Helpful Links

ALTER FUNCTION, DROP FUNCTION