Postgres Plus Advanced Server Oracle Compatibility Developer's Guide :

Previous PageTable Of ContentsNext Page

3.3.13 CREATE FUNCTION

Name

CREATE FUNCTION -- define a new function

Synopsis

CREATE [ OR REPLACE ] FUNCTION name
  [ (argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ]
    [, ...]) ]
  RETURN rettype [DETERMINISTIC]
[ AUTHID { DEFINER | CURRENT_USER } ]
{ IS | AS }
    [ declaration; ] [, ...]
  BEGIN
    statement; [...]
[ EXCEPTION
  { WHEN exception [ OR exception ] [...] THEN
      statement; [, ...] } [, ...]
]
  END [ name ]

Description

CREATE FUNCTION defines a new function. CREATE OR REPLACE FUNCTION will either create a new function, or replace an existing definition.

If a schema name is included, then the function is created in the specified schema. Otherwise it is created in the current schema. The name of the new function must not match any existing function with the same argument types in the same schema. However, functions of different input argument types may share a name (this is called overloading). (Overloading of functions is a Postgres Plus Advanced Server feature - overloading of stored functions is not Oracle compatible.)

To update the definition of an existing function, use CREATE OR REPLACE FUNCTION. It is not possible to change the name or argument types of a function this way (if you tried, you would actually be creating a new, distinct function). Also, CREATE OR REPLACE FUNCTION will not let you change the return type of an existing function. To do that, you must drop and recreate the function.

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

See Section 4.2.4 for more information on functions.

Parameters

name

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

argname

The name of an argument. The argument is referenced by this name within the function body.

IN | IN OUT | OUT

The argument mode. IN declares the argument for input only. This is the default. IN OUT allows the argument to receive a value as well as return a value. OUT specifies the argument is for output only.

argtype

The data type(s) of the function’s arguments. The argument types may be a base data type, a copy of the type of an existing column using %TYPE, or a user-defined type such as a nested table or an object type. A length must not be specified for any base type – for example, specify VARCHAR2, not VARCHAR2(10).

The type of a column is referenced by writing tablename.columnname%TYPE; using this can sometimes help make a function independent from changes to the definition of a table.

DEFAULT value

Supplies a default value for an input argument if one is not supplied in the function call. DEFAULT may not be specified for arguments with modes IN OUT or OUT.

rettype

The return data type, which may be any of the types listed for argtype. As for argtype, a length must not be specified for rettype.

DETERMINISTIC

Include DETERMINISTIC to specify that the function will always return the same result when given the same argument values.

DEFINER | CURRENT_USER

Specifies whether the privileges of the function owner (DEFINER) or the privileges of the current user executing the function (CURRENT_USER) are to be used to determine whether or not access is allowed to database objects referenced in the function. Also, under DEFINER, the search path of the function owner is used to resolve references to unqualified database objects while under CURRENT_USER, the search path of the current user executing the function is used to resolve references to unqualified database objects. DEFINER is the default.

declaration

A variable, type, or REF CURSOR declaration.

statement

An SPL program statement. Note that a DECLARE - BEGIN - END block is considered an SPL statement unto itself. Thus, the function body may contain nested blocks.

exception

An exception condition name such as NO_DATA_FOUND, OTHERS, etc.

Notes

Postgres Plus Advanced Server allows function overloading; that is, the same name can be used for several different functions so long as they have distinct 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 illustrates 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)

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 arguments’ 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

Pragmas

PRAGMA RESTRICT_REFERENCE

      Advanced Server accepts but ignores syntax referencing PRAGMA RESTRICT_REFERENCE.

See Also

DROP FUNCTION

Previous PageTable Of ContentsNext Page