CREATE FUNCTION

Synopsis

Use the CREATE FUNCTION statement to define a new function in a database.

Syntax

• Grammar
• Diagram

create_function ::= CREATE [ OR REPLACE ] FUNCTION name ( 
                    [ arg_decl [ , ... ] ] )  
                    [ RETURNS type_name
                      | RETURNS TABLE ( { column_name type_name } 
                        [ , ... ] ) ]  
                    { LANGUAGE lang_name
                      | TRANSFORM { FOR TYPE type_name } [ , ... ]
                      | WINDOW
                      | IMMUTABLE
                      | STABLE
                      | VOLATILE
                      | [ NOT ] LEAKPROOF
                      | CALLED ON NULL INPUT
                      | RETURNS NULL ON NULL INPUT
                      | STRICT
                      | [ EXTERNAL ] SECURITY INVOKER
                      | [ EXTERNAL ] SECURITY DEFINER
                      | PARALLEL { UNSAFE | RESTRICTED | SAFE }
                      | COST int_literal
                      | ROWS int_literal
                      | SET configuration_parameter 
                        { TO value | = value | FROM CURRENT }
                      | AS 'definition'
                      | AS 'obj_file' 'link_symbol' } [ ... ]
arg_decl ::= [ argmode ] [ argname ] argtype 
             [ { DEFAULT | = } expression ]

create_function

arg_decl

Semantics

• If a function with the given name and argument types already exists then CREATE FUNCTION will throw an error unless the CREATE OR REPLACE FUNCTION version is used.In that case it will replace any existing definition instead.

• The languages supported by default are sql, plpgsql and C.

• VOLATILE, STABLE and IMMUTABLE inform the query optimizer about the behavior the function.

  • VOLATILE is the default and indicates that the function result could be different for every call. For instance random() or now().
  • STABLE indicates that the function cannot modify the database so that within a single scan it will return the same result given the same arguments.
  • IMMUTABLE indicates that the function cannot modify the database and always returns the same results given the same arguments.

• CALLED ON NULL INPUT, RETURNS NULL ON NULL INPUT and STRICT define the function’s behavior with respect to ‘null’s.

  • CALLED ON NULL INPUT indicates that input arguments may be null.
  • RETURNS NULL ON NULL INPUT or STRICT indicate that the function always returns null if any of its arguments are null.

Examples

Define a function using the SQL language.

CREATE FUNCTION mul(integer, integer) RETURNS integer
    AS 'SELECT $1 * $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;
SELECT mul(2,3), mul(10, 12);

 mul | mul
-----+-----
   6 | 120
(1 row)

Define a function using the PL/pgSQL language.

CREATE OR REPLACE FUNCTION inc(i integer) RETURNS integer AS $$
        BEGIN
                RAISE NOTICE 'Incrementing %', i ;
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;
SELECT inc(2), inc(5), inc(10);

NOTICE:  Incrementing 2
NOTICE:  Incrementing 5
NOTICE:  Incrementing 10
 inc | inc | inc
-----+-----+-----
   3 |   6 |  11
(1 row)

See also

• CREATE PROCEDURE
• CREATE TRIGGER
• DROP FUNCTION