CREATE FUNCTION v15

Name

CREATE FUNCTION — Define a new function.

Synopsis

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 defines a new function. CREATE OR REPLACE FUNCTION either creates a new function or replaces an existing definition.

If you include a schema name, then the function is created in the specified schema. Otherwise it's created in the current schema. The name of the new function can't match any existing function with the same input argument types in the same schema. However, functions of different input argument types can share a name. This is called overloading.

To update the definition of an existing function, use CREATE OR REPLACE FUNCTION. You can't change the name or argument types of a function this way. (That syntax instead creates a new function.) CREATE OR REPLACE FUNCTION also doesn't let you change the return type of an existing function. To do that, you must drop the function and create it again. Also, you can change the types of any OUT parameters only by dropping the function first.

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

Parameters

name

The identifier of the function.

parameters

A list of formal parameters.

data_type

The data type of the value returned by the function’s RETURN statement.

declarations

Variable, cursor, type, or subprogram declarations. To include subprogram declarations, declare them after all other variable, cursor, and type declarations.

statements

SPL program statements. The BEGIN - END block can contain an EXCEPTION section.

IMMUTABLE

STABLE

VOLATILE

These attributes inform the query optimizer about the behavior of the function. You can specify only one of these attributes.

• Use IMMUTABLE to indicate that the function can't modify the database and must always reach the same result when given the same argument values. This attribute doesn't perform database lookups and uses only information present in its argument list. If you include this clause, you can immediately replace any call of the function with all-constant arguments with the function value.

• Use STABLE to indicate that the function can't modify the database and that, in a single table scan, it consistently returns the same result for the same argument values. However, its result might change across SQL statements. Use this selection for functions that depend on database lookups, parameter variables (such as the current time zone), and so on.

• Use VOLATILE (the default) to indicate that the function value can change even in a single table scan, so no optimizations can be made. Classify any function that has side effects as volatile, even if its result is predictable. This setting prevent calls from being optimized away.

DETERMINISTIC

DETERMINISTIC is a synonym for IMMUTABLE. A DETERMINISTIC function can't modify the database and always reaches the same result when given the same argument values. It doesn't perform database lookups and uses only information directly present in its argument list. If you include this clause, you can immediately replace any call of the function with all-constant arguments with the function value.

[ NOT ] LEAKPROOF

A LEAKPROOF function has no side effects and reveals no information about the values used to call the function.

CALLED ON NULL INPUT

RETURNS NULL ON NULL INPUT

STRICT

• Use CALLED ON NULL INPUT (the default) to call the procedure normally when some of its arguments are NULL. Check for NULL values, if necessary, and respond appropriately.

• Use RETURNS NULL ON NULL INPUT or STRICT to indicate that the procedure always returns NULL when any of its arguments are NULL. If you use these clauses, the procedure doesn't execute when there are NULL arguments. Instead, a NULL result is assumed.

[ EXTERNAL ] SECURITY DEFINER

SECURITY DEFINER (the default) specifies for the function to execute with the privileges of the user that created it. The optional keyword EXTERNAL is allowed for SQL conformance.

[ EXTERNAL ] SECURITY INVOKER

Use the SECURITY INVOKER clause to execute the function with the privileges of the user that calls it. The optional keyword EXTERNAL is allowed for SQL conformance.

AUTHID DEFINER

AUTHID CURRENT_USER

• The AUTHID DEFINER clause is a synonym for [EXTERNAL] SECURITY DEFINER. If you omit the AUTHID clause or specify AUTHID DEFINER, the rights of the function owner determine access privileges to database objects.

• The AUTHID CURRENT_USER clause is a synonym for [EXTERNAL] SECURITY INVOKER. If you specify AUTHID CURRENT_USER, the rights of the current user executing the function 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.

• When set to UNSAFE (the default), the function can't execute in parallel mode. The presence of such a function in a SQL statement forces a serial execution plan.

• When set to RESTRICTED, the function can execute in parallel mode, but the execution is restricted to the parallel group leader. If the qualification for any particular relation has anything that is parallel restricted, that relation isn't chosen for parallelism.

• When set to SAFE, the function can execute in parallel mode with no restriction.

COST execution_cost

execution_cost is a positive number giving the estimated execution cost for the function, in units of cpu_operator_cost. If the function returns a set, this is the cost per returned row. Larger values cause the planner to try to avoid evaluating the function more often than needed.

ROWS result_rows

result_rows is a positive number giving the estimated number of rows for the planner to expect the function to return. This option is allowed only when the function is declared to return a set. The default assumption is 1000 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 session's current value of the parameter as the value to apply when the function is entered.

If a SET clause is attached to a function, then the effects of a SET LOCAL command executed inside the function for the same variable are restricted to the function. The configuration parameter's prior value is restored at function exit. An ordinary SET command (without LOCAL) overrides the SET clause, similar to a previous SET LOCAL command. The effects of such a command persist after procedure exit unless the current transaction is rolled back.

PRAGMA AUTONOMOUS_TRANSACTION

PRAGMA AUTONOMOUS_TRANSACTION is the directive that sets the function as an autonomous transaction.

Notes

EDB Postgres Advanced Server allows function overloading. That is, you can use the same name for several different functions. However, they must have discrete input (IN, IN OUT) argument data types.

Examples

The function emp_comp takes two numbers as input and returns a computed value. The SELECT command shows the use of 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 function sal_range returns a count of the number of employees whose salary falls in the specified range. The following anonymous block calls the function a number of times using the argument's default values for 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

This example uses 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;

Include the STRICT keyword to return NULL if any input parameter passed is NULL. If a NULL value is passed, the function doesn't execute.

The dept_salaries function executes with the privileges of the role that's calling the function. If the current user doesn't have privileges to perform the SELECT statement querying the emp table (to display employee salaries), the function reports an error. To use the privileges associated with the role that defined the function, replace the AUTHID CURRENT_USER clause with the AUTHID DEFINER clause.

Other Pragmas (declared in a package specification)

PRAGMA RESTRICT_REFERENCES

EDB Postgres Advanced Server accepts but ignores syntax referencing PRAGMA RESTRICT_REFERENCES.

See also

DROP FUNCTION