Functions

About

Postgres function can be written in many different programming languages.

They are a way to extend the Postgres functionality.

Functions created will have the same privileges and ability that the built-in function possess.

User defined functions CANNOT execute Transactions.

Creating

Functions can be overloaded (created with the same name, but different arguments).

/*
    p1 type..      Make a list of parameters separated by commas
    RETURNS        Specify the return data type
    LANGUAGE       Define the language in which the function was written
*/
CREATE OR REPLACE FUNCTION function_name (p1 type, p2 type, ...)
RETURNS return_type AS
BEGIN
    -- function logic
END;
LANGUAGE language_name

It is a good practice to name functions with fn prefix, since your created functions will stay along side all the builtin Postgres function. (Easier to differentiate)

Examples

Postgres engine accepts the function logic to be wrapped with ' instead of the BEGIN and END keywords.

CREATE OR REPLACE FUNCTION fn_mysum(int, int)
RETURNS int AS
'
    SELECT $1 + $2;
' LANGUAGE SQL

Also use $$ to wrap the logic, if you need to use ' inside. This way you don't need to escape it.

CREATE OR REPLACE FUNCTION fn_mysum(int, int)
RETURNS int AS
$$
    SELECT $1 + $2;
$$ LANGUAGE SQL

Calling

You can call your functions, by just calling them in queries.

SELECT fn_mysum(1, 1);

Deleting

-- CASCADE     Will drop its dependent objects
DROP FUNCTION [IF EXISTS] function_name(argument_list) [CASCADE | RESTRICT]

Since functions can be overloaded, Postgres need to know which function you want to remove by checking the argument_list.

Usage

Returning void

CREATE OR REPLACE FUNCTION fn_ex(int, int)
RETURNS void AS
$$
    UPDATE table_name
    SET col_name = 'N/A'
    WHERE col_name IS NUL
    ;
$$ LANGUAGE SQL

Returning composite

Use RETURN table_name to return all columns of a table.

CREATE OR REPLACE FUNCTION fn_ex(cid int)
RETURNS table_name AS
$$
    SELECT * FROM table_name WHERE col_id = cid;
$$ LANGUAGE SQL

When accessing the returned data, wrap it with ().* to get the result spread the data into columns, instead of all the data as a single column.

SELECT (fn_ex(1)).*

Returning multiple rows

Use RETURNS SETOF to be able to return multiple rows.

CREATE OR REPLACE FUNCTION fn_ex()
RETURNS SETOF table_name AS
$$
    SELECT * FROM table_name;
$$ LANGUAGE SQL

Access parameters

You can access the parameters with $1, $2, and etc without specifying names.

CREATE OR REPLACE FUNCTION fn_mysum(int, int)
RETURNS int AS
$$
    SELECT $1 + $2;
$$ LANGUAGE SQL

Or if specifying parameter names.

Avoid using parameter's names the same as table column's names, or it may conflict.

CREATE OR REPLACE FUNCTION fn_mysum(cid int)
RETURNS int AS
$$
    SELECT col_2 FROM table_name WHERE col_id = cid;
$$ LANGUAGE SQL

Default values

CREATE OR REPLACE FUNCTION fn_mysum(cid int DEFAULT 0)
RETURNS int AS
$$
    SELECT col_2 FROM table_name WHERE col_id = cid;
$$ LANGUAGE SQL

Using as source

You can use functions as table sources.

SELECT * FROM fn_ex;