CREATE FUNCTION

Synopsis

Use the CREATE FUNCTION statement to create a function in a database.

Syntax

create_function ::= CREATE [ OR REPLACE ] FUNCTION function_name ( 
                    [ arg_decl [ , ... ] ] )  
                    [ RETURNS data_type
                      | RETURNS TABLE ( { column_name data_type } 
                        [ , ... ] ) ]  function_attribute [ ... ]

arg_decl ::= [ argmode ] [ argname ] argtype 
             [ { DEFAULT | = } expression ]

function_attribute ::= WINDOW
                       | IMMUTABLE
                       | STABLE
                       | VOLATILE
                       | [ NOT ] LEAKPROOF
                       | CALLED ON NULL INPUT
                       | RETURNS NULL ON NULL INPUT
                       | STRICT
                       | PARALLEL { UNSAFE | RESTRICTED | SAFE }
                       | COST int_literal
                       | ROWS int_literal
                       | TRANSFORM { FOR TYPE type_name } [ , ... ]
                       | SET configuration_parameter 
                         { TO value | = value | FROM CURRENT }
                       | [ EXTERNAL ] SECURITY security_kind
                       | LANGUAGE lang_name
                       | AS implementation_definition

security_kind ::= INVOKER | DEFINER

lang_name ::= SQL | PLPGSQL | C

implementation_definition ::= ' sql_stmt_list '
                              | ' plpgsql_block_stmt '
                              | ' obj_file ' [ , ' link_symbol ' ]

sql_stmt_list ::= sql_stmt ; [ sql_stmt ... ]

create_function

CREATEORREPLACEFUNCTIONfunction_name(,arg_decl)RETURNSdata_typeRETURNSTABLE(,column_namedata_type)function_attribute

arg_decl

argmodeargnameargtypeDEFAULT=expression

function_attribute

WINDOWIMMUTABLESTABLEVOLATILENOTLEAKPROOFCALLEDONNULLINPUTRETURNSNULLONNULLINPUTSTRICTPARALLELUNSAFERESTRICTEDSAFECOSTint_literalROWSint_literalTRANSFORM,FORTYPEtype_nameSETconfiguration_parameterTOvalue=valueFROMCURRENTEXTERNALSECURITYsecurity_kindLANGUAGElang_nameASimplementation_definition

security_kind

INVOKERDEFINER

lang_name

SQLPLPGSQLC

implementation_definition

'sql_stmt_list''plpgsql_block_stmt''obj_file','link_symbol'

sql_stmt_list

sql_stmt;sql_stmt;

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 the 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