CREATE FUNCTION - PolarDB - Alibaba Cloud Documentation Center

Creates a function.

Syntax

CREATE [ OR REPLACE ] FUNCTION name [ (parameters) ]
  RETURN data_type
   [
          IMMUTABLE
        | STABLE
        | VOLATILE
        | DETERMINISTIC
        | [ NOT ] LEAKPROOF
        | CALLED ON NULL INPUT
        | RETURNS NULL ON NULL INPUT
        | STRICT
        | [ EXTERNAL ] SECURITY INVOKER
        | [ EXTERNAL ] SECURITY DEFINER
        | AUTHID DEFINER
        | AUTHID CURRENT_USER
        | PARALLEL { UNSAFE | RESTRICTED | SAFE }
        | COST execution_cost
        | ROWS result_rows
        | SET configuration_parameter
          { TO value | = value | FROM CURRENT }
   ...]
{ IS | AS }
    [ PRAGMA AUTONOMOUS_TRANSACTION; ]
    [ declarations ]
  BEGIN
    statements
  END [ name ];

Description

CREATE FUNCTION creates a function. CREATE OR REPLACE FUNCTION either creates a new function or replaces an existing definition.

If you specify a schema name, the function is created in the specified schema. Otherwise, the function is created in the current schema. The name of the new function cannot be the same as an existing function that has the same input argument types in the same schema. However, functions with different input argument types can share a name. This is called overloading. Overloading of functions is a feature of PolarDB for PostgreSQL(Compatible with Oracle). Overloading of stored standalone functions is incompatible with Oracle databases.

To update the definition of an existing function, you can use the CREATE OR REPLACE FUNCTION statement. You cannot use the statement to change the name or argument types of a function. If you have tried, a new distinct function is created. In addition, you cannot use the CREATE OR REPLACE FUNCTION statement to change the return type of an existing function. To change the return type of an existing function, you must delete the function and create the function again. When using the OUT parameters, you cannot change the types of OUT parameters unless you delete the function.

The user that creates the function becomes the owner of the function.

PolarDB for PostgreSQL(Compatible with Oracle) support function overloading. The same name can be used for several different functions if they have distinct input (IN, IN OUT) argument data types.

Parameters

Parameter	Description


Parameter	Description
name	The identifier of the function.
parameters	A list of parameter values.
data_type	The data type of the value returned by the RETURN statement of the function.
declarations	Variable, cursor, type, or subprogram declarations. If subprogram declarations are included, they must be declared after all other variable, cursor, and type declarations.
statements	The SPL program statements. The BEGIN - END block can contain an EXCEPTION section.
IMMUTABLE \| STABLE \| VOLATILE	These attributes are used to inform the query optimizer about the behavior of the function. You can specify only one of them. VOLATILE is the default behavior. IMMUTABLE indicates that the function does not modify the database and always returns the same result if the same argument value is specified. The function does not perform database lookups or use information that is excluded from the argument list. If this clause is included, a call to the function with all-constant arguments can be immediately replaced with the function value. STABLE indicates that the function does not modify the database and that the function returns the same result for the same argument value within a single table scan. In this case, the result can change across SQL statements. This attribute is suitable for functions that depend on database lookups and parameter variables such as the current time zone. VOLATILE indicates that the function value changes within a single table scan. In this case, no optimizations can be made. Note that functions with negative effects must be classified as a volatile function, even if the results are predictable. This prevents calls from being removed due to optimization.
DETERMINISTIC	DETERMINISTIC is a synonym for IMMUTABLE. A DETERMINISTIC function does not modify the database and always returns the same result if the same argument value is specified. The function does not perform database lookups or use information that is excluded from the argument list. If this clause is included, a call to the function with all-constant arguments can be immediately replaced with the function value.
[ NOT ] LEAKPROOF	A LEAKPROOF function has no negative effects and reveals no information about the values used to call the function.
CALLED ON NULL INPUT \| RETURNS NULL ON NULL INPUT \| STRICT	CALLED ON NULL INPUT is the default value. It indicates that the stored procedure is called when some arguments are NULL. If necessary, the author is responsible for checking NULL values and making proper responses. RETURNS NULL ON NULL INPUT or STRICT indicates that the stored procedure returns NULL whenever some arguments are NULL. If these clauses are specified, the stored procedure is not executed when NULL arguments exist. A NULL result is returned automatically.
[ EXTERNAL ] SECURITY DEFINER	SECURITY DEFINER specifies that the function executes with the privileges of the user that created it. This is the default value. The EXTERNAL keyword is allowed for SQL conformance but it is optional.
[ EXTERNAL ] SECURITY INVOKER	The SECURITY INVOKER clause indicates that the function executes with the privileges of the user that calls it. The EXTERNAL keyword is allowed for SQL conformance but it is optional.
AUTHID DEFINER \| AUTHID CURRENT_USER	The AUTHID DEFINER clause is a synonym for [EXTERNAL] SECURITY DEFINER. If the AUTHID clause is omitted or AUTHID DEFINER is specified, the rights of the function owner are used to determine access privileges to database objects. The AUTHID CURRENT_USER clause is a synonym for [EXTERNAL] SECURITY INVOKER. If AUTHID CURRENT_USER is specified, the rights of the current user executing the function are used to determine access privileges.
PARALLEL { UNSAFE \| RESTRICTED \| SAFE }	The PARALLEL clause enables the use of parallel sequential scans (parallel mode). A parallel sequential scan uses multiple workers to scan a relation in parallel during a query in contrast to a serial sequential scan. If this parameter is set to UNSAFE, the function cannot be executed in the parallel mode. If such a function exists in an SQL statement, a serial execution plan is enforced. If the PARALLEL clause is omitted, this is the default setting. If this parameter is set to RESTRICTED, the function can be executed in the parallel mode, but the execution is restricted to the parallel group leader. If the qualification for a particular relation has content that is parallel restricted, the relation is not selected for parallel execution. If this parameter is set to SAFE, the function can be executed in the parallel mode without restrictions.
COST execution_cost	execution_cost is a positive value that indicates the estimated execution cost of the function. The unit is cpu_operator_cost. If the function returns a set, this is the cost of each returned row. Larger values cause the planner to try to avoid evaluating the function more often than necessary.
ROWS result_rows	result_rows is a positive value that indicates the estimated number of rows that the planner expects the function to return. This value can be used only when the function is declared to return a set. The default value is 1,000 rows.
SET configuration_parameter { TO value \| = value \| FROM CURRENT }	The SET clause causes the specified configuration parameter to be set to the specified value when the function is entered, and then restored to its prior value when the function exits. SET FROM CURRENT saves the current value of the parameter as the value to be applied when the function is entered. If a SET clause is attached to a function, the effects of a SET LOCAL command executed inside the function for the same variable are restricted to the function. The configuration parameter is restored to its prior value when the function exits. An ordinary SET command without LOCAL overrides the SET clause. This is similar to a previous SET LOCAL command. The effects of such a command persist after the function exits, unless the current transaction is rolled back.
PRAGMA AUTONOMOUS_TRANSACTION	PRAGMA AUTONOMOUS_TRANSACTION is the directive that sets the function as an autonomous transaction.

Note

The STRICT, LEAKPROOF, PARALLEL, COST, ROWS, and SET keywords provide extended functionality for PolarDB for PostgreSQL(Compatible with Oracle). However, these keywords are not supported by Oracle databases.

Examples

The emp_comp function accepts two numbers as inputs and returns a computed value. The SELECT command is used to describe how to use the function.

CREATE OR REPLACE FUNCTION emp_comp (
    p_sal           NUMBER,
    p_comm          NUMBER
) RETURN NUMBER
IS
BEGIN
    RETURN (p_sal + NVL(p_comm, 0)) * 24;
END;

SELECT ename "Name", sal "Salary", comm "Commission", emp_comp(sal, comm)
    "Total Compensation"  FROM emp;

  Name  | Salary  | Commission | Total Compensation
--------+---------+------------+--------------------
 SMITH  |  800.00 |            |           19200.00
 ALLEN  | 1600.00 |     300.00 |           45600.00
 WARD   | 1250.00 |     500.00 |           42000.00
 JONES  | 2975.00 |            |           71400.00
 MARTIN | 1250.00 |    1400.00 |           63600.00
 BLAKE  | 2850.00 |            |           68400.00
 CLARK  | 2450.00 |            |           58800.00
 SCOTT  | 3000.00 |            |           72000.00
 KING   | 5000.00 |            |          120000.00
 TURNER | 1500.00 |       0.00 |           36000.00
 ADAMS  | 1100.00 |            |           26400.00
 JAMES  |  950.00 |            |           22800.00
 FORD   | 3000.00 |            |           72000.00
 MILLER | 1300.00 |            |           31200.00
(14 rows)

The sal_range function returns the number of employees whose salary falls in the specified range. The following anonymous block calls the function multiple times and the default value of the arguments are used in the first two calls.

CREATE OR REPLACE FUNCTION sal_range (
    p_sal_min       NUMBER DEFAULT 0,
    p_sal_max       NUMBER DEFAULT 10000
) RETURN INTEGER
IS
    v_count         INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM emp
        WHERE sal BETWEEN p_sal_min AND p_sal_max;
    RETURN v_count;
END;

BEGIN
    DBMS_OUTPUT.PUT_LINE('Number of employees with a salary: ' ||
        sal_range);
    DBMS_OUTPUT.PUT_LINE('Number of employees with a salary of at least '
        || '$2000.00: ' || sal_range(2000.00));
    DBMS_OUTPUT.PUT_LINE('Number of employees with a salary between '
        || '$2000.00 and $3000.00: ' || sal_range(2000.00, 3000.00));

END;

Number of employees with a salary: 14
Number of employees with a salary of at least $ 2000.00: 6
Number of employees with a salary between $ 2000.00 and $ 3000.00: 5

The following example shows how to use the AUTHID CURRENT_USER clause and STRICT keyword in a function declaration:

CREATE OR REPLACE FUNCTION dept_salaries(dept_id int) RETURN NUMBER
  STRICT
  AUTHID CURRENT_USER
BEGIN
  RETURN QUERY (SELECT sum(salary) FROM emp WHERE deptno = id);
END;

The STRICT keyword is included to instruct the server to return NULL if an input parameter passed is NULL. If a NULL value is passed, the function is not executed.

The dept_salaries function executes with the privileges of the role that is calling the function. If the current user does not have sufficient privileges to execute the SELECT statement to query the emp table (to display employee salaries), the function reports an error. To instruct the server to use the privileges associated with the role that defined the function, replace the AUTHID CURRENT_USER clause with the AUTHID DEFINER clause.