Creating Functions

A function is a program that does some processing and then returns a result. They are called using SELECT SQL statement. A procedure can be defined in SQL or PGSQL standards.

CREATE FUNCTION creates a new function. CREATE OR REPLACE FUNCTION creates new or overwrites an existing function. The name of the new function should not match with the name of any existing function or procedure with the same input argument types in the same schema. However, functions and procedures of different argument types can share a name (this is called overloading).

The full syntax is here.

Creating a simple function – Multiply two integers using a SQL function

# CREATE FUNCTION multi(integer, integer) RETURNS integer
    AS 'select $1 * $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

# SELECT multi(3, 6);

# \df

# \df multi

# \x

# \df+ multi

appdb=> CREATE FUNCTION multi(integer, integer) RETURNS integer
appdb->     AS 'select $1 * $2;'
appdb->     LANGUAGE SQL
appdb->     IMMUTABLE
appdb->     RETURNS NULL ON NULL INPUT;
CREATE FUNCTION
appdb=> SELECT multi(3, 6);
 multi
-------
    18
(1 row)

appdb=>
appdb=> \df
                       List of functions
 Schema | Name  | Result data type | Argument data types | Type
--------+-------+------------------+---------------------+------
 public | multi | integer          | integer, integer    | func
(1 row)

appdb=> \df multi
                       List of functions
 Schema | Name  | Result data type | Argument data types | Type
--------+-------+------------------+---------------------+------
 public | multi | integer          | integer, integer    | func
(1 row)

appdb=> \x
Expanded display is on.
appdb=>
appdb=> \df+ multi
List of functions
-[ RECORD 1 ]-------+-----------------
Schema              | public
Name                | multi
Result data type    | integer
Argument data types | integer, integer
Type                | func
Volatility          | immutable
Parallel            | unsafe
Owner               | manoj
Security            | invoker
Access privileges   |
Language            | sql
Source code         | select $1 * $2;
Description         |

appdb=>

Creating a simple function – Multiply two integers using arguments, PL/pgSQL

# CREATE FUNCTION multipg(a integer, b integer) RETURNS integer
    AS $$
    DECLARE
    c integer;
    BEGIN
     c = a * b;
     RETURN c;
    END;
    $$ LANGUAGE plpgsql;

# SELECT multipg(4, 5);

# \df

# \df multipg

# \x

# \df+ multipg

appdb=> CREATE FUNCTION multipg(a integer, b integer) RETURNS integer
appdb->     AS $$
appdb$>     DECLARE
appdb$>     c integer;
appdb$>     BEGIN
appdb$>      c = a * b;
appdb$>      RETURN c;
appdb$>     END;
appdb$>     $$ LANGUAGE plpgsql;
CREATE FUNCTION
appdb=> SELECT multipg(4, 5);
-[ RECORD 1 ]
multipg | 20

appdb=> \df
List of functions
-[ RECORD 1 ]-------+---------------------
Schema              | public
Name                | multi
Result data type    | integer
Argument data types | integer, integer
Type                | func
-[ RECORD 2 ]-------+---------------------
Schema              | public
Name                | multipg
Result data type    | integer
Argument data types | a integer, b integer
Type                | func

appdb=> \df multipg
List of functions
-[ RECORD 1 ]-------+---------------------
Schema              | public
Name                | multipg
Result data type    | integer
Argument data types | a integer, b integer
Type                | func


appdb=> \x
Expanded display is on.
appdb=> \df+ multipg
List of functions
-[ RECORD 1 ]-------+---------------------
Schema              | public
Name                | multipg
Result data type    | integer
Argument data types | a integer, b integer
Type                | func
Volatility          | volatile
Parallel            | unsafe
Owner               | manoj
Security            | invoker
Access privileges   |
Language            | plpgsql
Source code         |                     +
                    |     DECLARE         +
                    |     c integer;      +
                    |     BEGIN           +
                    |      c = a * b;     +
                    |      RETURN c;      +
                    |     END;            +
                    |
Description         |

appdb=>

Creating a function returning a table

# CREATE OR REPLACE FUNCTION maths(int, int) RETURNS TABLE(f1 text, f2 int)
    AS $$
	SELECT 'ADD', ($1 + $2)
	UNION
	SELECT 'SUBTRACT', ($1 - $2)
	UNION
	SELECT 'MULTIPLY', ($1 * $2)
	UNION
	SELECT 'DIVIDE', ($1 / $2)
	$$
    LANGUAGE SQL;

# \x off

# SELECT * FROM maths(2, 5);

# \x on

# \df+ maths

appdb=> CREATE OR REPLACE FUNCTION maths(int, int) RETURNS TABLE(f1 text, f2 int)
appdb->     AS $$
appdb$> SELECT 'ADD', ($1 + $2)
appdb$> UNION
appdb$> SELECT 'SUBTRACT', ($1 - $2)
appdb$> UNION
appdb$> SELECT 'MULTIPLY', ($1 * $2)
appdb$> UNION
appdb$> SELECT 'DIVIDE', ($1 / $2)
appdb$> $$
appdb->     LANGUAGE SQL;
CREATE FUNCTION
appdb=> \x
Expanded display is off.
appdb=> SELECT * FROM maths(2, 5);
    f1    | f2
----------+----
 ADD      |  7
 DIVIDE   |  0
 MULTIPLY | 10
 SUBTRACT | -3
(4 rows)

appdb=> \x on
Expanded display is on.
appdb=> \df+ maths
List of functions
-[ RECORD 1 ]-------+-----------------------------
Schema              | public
Name                | maths
Result data type    | TABLE(f1 text, f2 integer)
Argument data types | integer, integer
Type                | func
Volatility          | volatile
Parallel            | unsafe
Owner               | manoj
Security            | invoker
Access privileges   |
Language            | sql
Source code         |                             +
                    | SELECT 'ADD', ($1 + $2)     +
                    | UNION                       +
                    | SELECT 'SUBTRACT', ($1 - $2)+
                    | UNION                       +
                    | SELECT 'MULTIPLY', ($1 * $2)+
                    | UNION                       +
                    | SELECT 'DIVIDE', ($1 / $2)  +
                    |
Description         |

appdb=>