CREATE FUNCTION

CREATE FUNCTION defines a new function.

Supported syntax

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE sql
    | { IMMUTABLE | STABLE | VOLATILE }
    | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
    | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | RETURN expression
    | BEGIN ATOMIC statement; [ statement; ... ] END
  } ...

where LANGUAGE must be sql

Description

CREATE FUNCTION defines a new function. CREATE OR REPLACE FUNCTION will either create a new function, or replace an existing definition. To be able to define a function, you must have the USAGE privilege on the language.

If a schema name is included, then the function is created in the specified schema. Otherwise, the function is created in the current schema. The name of the new function must not match any existing function with the same input argument types in the same schema. However, functions of different argument types can share a name (this is called overloading).

To replace the current definition of an existing function, use CREATE OR REPLACE FUNCTION. You can't change the name or argument types of a function this way (this would actually create a new, distinct function). Also, you can't use CREATE OR REPLACE FUNCTION to change the return type of an existing function. To do that, you must drop and recreate the function.

When CREATE OR REPLACE FUNCTION is used to replace an existing function, the ownership and permissions of the function don't change. All other function properties are assigned the values specified or implied in the command. You must own the function to replace it (this includes being a member of the owning role).

If you drop and then recreate a function, the new function isn't the same entity as the old; you will have to drop existing rules, views, and so on, that refer to the old function. Use CREATE OR REPLACE FUNCTION to change a function definition without breaking objects that refer to the function.

You become the owner of a function that you created.

To be able to create a function, you must have USAGE privilege on the argument types and the return type.

A SQL function wraps one or more SQL statements into a reusable, callable unit. Use functions to centralize common expressions or calculations, apply consistent logic across multiple queries, and simplify application code by calling a single function instead of repeating the same SQL.

Parameters

Notes

Use the DROP FUNCTION statement to remove a function. You can also grant and revoke privileges on a function with GRANT and REVOKE (for example, REVOKE ALL ON FUNCTION).

Any function that reads or modifies data in tables is subject to the same transaction and concurrency behavior as the equivalent SQL statements run directly. Functions declared IMMUTABLE or STABLE must not modify the database.

To update the definition of an existing function, use CREATE OR REPLACE FUNCTION. You can't change the argument types or return type of a function this way; instead, drop and recreate the function.

To rename a function, change its owner, or move it to another schema, use ALTER FUNCTION. To change any other attributes, use CREATE OR REPLACE FUNCTION with the desired changes. For more information, see ALTER FUNCTION.

Function overloading is supported: you can define several functions with the same name as long as their input argument types differ. A user-defined function can also call other functions, and can shadow a built-in function of the same name and argument types.

For a function that returns a set (SETOF or RETURNS TABLE) whose body contains more than one statement, only the result of the final statement is returned. The function still executes the earlier statements, but discards their results. This is the standard behavior for SQL-language functions. Structure the function so that its final statement produces the complete result set.

A set-returning function doesn't guarantee any particular row order. To control row order, include an ORDER BY clause in the query.

Considerations

The following CREATE FUNCTION capabilities return the error shown.

Aurora DSQL doesn't support CREATE PROCEDURE, which fails with the error PROCEDURE is not supported.

The WINDOW attribute is accepted syntactically but isn't useful for SQL-language functions. Avoid using this attribute.

Examples

Create a simple SQL function that adds two integers, using a string-constant body:

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'SELECT $1 + $2;'
    LANGUAGE sql
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

Create the same function using the SQL-standard RETURN body and named arguments:

CREATE FUNCTION add(a integer, b integer) RETURNS integer
    LANGUAGE sql
    IMMUTABLE
    RETURN a + b;

Create a function that returns a set of rows using RETURNS TABLE and a BEGIN ATOMIC body:

CREATE FUNCTION employees_in_dept(dept_id integer)
    RETURNS TABLE (id integer, name text)
    LANGUAGE sql
    STABLE
    BEGIN ATOMIC
        SELECT id, name FROM employees WHERE department_id = dept_id;
    END;

Create a function that runs with the privileges of its owner, and sets a configuration parameter for the duration of the call:

CREATE FUNCTION current_department() RETURNS text
    LANGUAGE sql
    STABLE
    SECURITY DEFINER
    SET search_path = app
    AS 'SELECT current_setting(''app.department'');';

Compatibility

A CREATE FUNCTION command is defined in the SQL standard. The PostgreSQL implementation can be used in a compatible way but has many extensions. Conversely, the SQL standard specifies a number of optional features that are not implemented in PostgreSQL.

The following are important compatibility issues:

Simple LANGUAGE sql functions can be written in a way that is both standard-conforming and portable to other implementations. More complex functions using advanced features, optimization attributes, or other languages will necessarily be specific to PostgreSQL in a significant way.