PostgreSQL CREATE FUNCTION Statement

Summary: in this tutorial, you’ll learn how to use the PostgreSQL CREATE FUNCTION statement to create a user-defined function.

Getting Started with the PostgreSQL CREATE FUNCTION statement #

A function is a reusable piece of code that performs a specific task. For example, the CONCAT() function allows you to concatenate two or more strings into one string. The CONCAT() is a built-in function provided by PostgreSQL.

PostgreSQL allows you to create a new function using the CREATE FUNCTION statement. This function is called a user-defined function because it is created by you and other developers, not provided by PostgreSQL out of the box.

A user-defined function is a custom function that does a specific task like a built-in function.

This function takes input parameters, executes one or more SQL statements, and returns one or multiple values.

Typically, you write a user-defined function to encapsulate complex logic and make it reusable within the database.

The following shows the basic syntax of the CREATE FUNCTION statement:

CREATE [OR REPLACE] FUNCTION function_name (parameters)
RETURNS return_type 
AS 
$$
  -- function body
$$ 
LANGUAGE SQL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

CREATE [OR REPLACE] FUNCTION instructs PostgreSQL to create a new function. If the function name already exists, the OR REPLACE option replaces it.
function_name (parameters) is the function name followed by parameters.
RETURNS return_type specifies the data type of a value the function will return. If the function returns no value, the return_type is VOID.
AS $$ ... $$ indicates the function body enclosed within dollar-quoted strings ($$).
LANGUAGE SQL specifies that the function is using SQL. Besides SQL, you can use other procedure programming languages such as PL/pgSQL.

Dollar-Quoted String Literal ($$) #

PostgreSQL requires a function body as a string. If you use regular strings, you need to escape quotes and other special characters.

To make the function body more readable, PostgreSQL offers dollar-quoted string syntax:

$tag$<string_constant>$tag$Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

The tag is an optional identifier.
Between $tag$ s, you can place a string constant, which is the function body. And you don’t have to escape quotes or special characters.

Calling a user-defined function #

To call a user-defined function, you use the SELECT statement, followed by the function name and arguments:

SELECT function_name(arguments);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the arguments can be one or more arguments corresponding to parameters defined in the function_name.

PostgreSQL CREATE FUNCTION statement examples #

Let’s explore some examples of creating new user-defined functions.

Creating a function that returns no value #

The following example uses the CREATE FUNCTION statement to create a function that adds a new warehouse with name and location:

CREATE OR REPLACE FUNCTION add_warehouse (
  name VARCHAR, 
  location VARCHAR
) 
RETURNS VOID 
AS 
$$
    INSERT INTO warehouses(warehouse_name, address)
    VALUES(name,location);
$$ 
LANGUAGE SQL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The add_warehouse function takes two parameters:

namewith the type VARCHAR, which indicates the warehouse name.
location has the same VARCHAR data type, which indicates the warehouse’s address.

You cannot use the parameter names the same as the column names of tables used inside the function. If you do so, PostgreSQL will issue an error. The reason is that PostgreSQL will confuse the parameters and column names.

The RETURNS VOID indicates the add_warehouse function does not return any value.

Notice that the RETURNS keyword includes the letter S, not RETURN.

The AS keyword tells the start of the function’s body.

Between the $$ is a dollar-quoted string that represents the function body.

Inside the function body, we use an INSERT statement that inserts a new row into the warehouses table:

INSERT INTO warehouses (warehouse_name, address)
VALUES(name, location);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In the VALUES clause, we use the parameters name and location.

The LANGUAGE SQL clause indicates that the function uses SQL.

Note that you can write all the code in lowercase, including keywords like create or replace function.

Calling the add_warehouse function #

First, call the add_warehouse function to add a new warehouse to the warehouses table:

SELECT
  add_warehouse (
    'San Mateo',
    '2222 S Delaware St, San Mateo, CA 94403'
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, retrieve data from the warehouses table to verify the function call:

SELECT
  warehouse_name,
  address
FROM
  warehouses;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     warehouse_name      |                     address
-------------------------+-------------------------------------------------
 San Jose Warehouse      | 205 E Alma Ave, San Jose, CA 95112
 San Francisco Warehouse | 233 E Harris Ave, South San Francisco, CA 94080
 Los Angeles Warehouse   | 1919 Vineburn Avenue, Los Angeles, CA 90032
 San Mateo               | 2222 S Delaware St, San Mateo, CA 94403Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The output shows the new warehouse in the warehouses table.

Creating a function that returns a single value #

The following example uses the CREATE FUNCTION statement to create a get_inventory_amount() that returns the total inventory amount:

CREATE OR REPLACE FUNCTION get_inventory_amount () 
  RETURNS DEC 
AS 
$$
SELECT
  SUM(quantity * price)
FROM
  inventories
  JOIN products USING (product_id);
$$ 
LANGUAGE SQL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

In this example, the get_inventory_amount() function has no parameter and returns a number with the type DECIMAL or DEC in short.

The get_inventory_amount function executes an SQL statement that joins the inventories and products tables and calculates the total inventory amount using the SUM aggregate function.

The following statement calls the get_inventory_amount() function:

SELECT
  get_inventory_amount () AS inventory_amount;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 inventory_amount
------------------
       6843946.40Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Creating a function that returns a table #

The following statement uses the CREATE TABLE statement to create a function get_inventory_amount_by_warehouses that returns the inventory amounts by warehouses:

CREATE OR REPLACE FUNCTION get_inventory_amount_by_warehouses () 
  RETURNS TABLE (
       warehouse_name VARCHAR, 
       amount DEC
  ) 
AS 
$$
SELECT
  warehouse_name, 
  SUM(quantity * price) AS amount
FROM
  inventories
  JOIN products USING (product_id)
  JOIN warehouses USING(warehouse_id)
GROUP BY warehouse_name
ORDER BY warehouse_name;
$$ 
LANGUAGE SQL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The get_inventory_amount_by_warehouses function’s return type is a table with two columns:

warehouse_name
amount

The function executes a query that returns a result set with the columns corresponding to the table column in the RETURNS clause.

The following statement calls the get_inventory_amount_by_warehouses() function:

SELECT * FROM get_inventory_amount_by_warehouses();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     warehouse_name      |   amount
-------------------------+------------
 Los Angeles Warehouse   | 2379982.20
 San Francisco Warehouse | 2419483.10
 San Jose Warehouse      | 2044481.10Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Since the function returns a table, you can place it in the FROM clause.

If you call the function in the SELECT clause, it’ll return a table of three rows. Each row contains a value of a composite type that consists of two fields warehouse_name and amount:

SELECT
  get_inventory_amount_by_warehouses ();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   get_inventory_amount_by_warehouses
----------------------------------------
 ("Los Angeles Warehouse",2379982.20)
 ("San Francisco Warehouse",2419483.10)
 ("San Jose Warehouse",2044481.10)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Creating a function that returns a value of a composite type. #

In PostgreSQL, you can define a composite type that groups multiple fields into a single unit. The PostgreSQL composite type works like a record or struct in other programming languages.

First, define a composite type called product_info that consists of the product name and price:

CREATE TYPE product_info AS (
    product_name VARCHAR, 
    price DECIMAL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, create a function that returns a value of the product_info composite type:

CREATE OR REPLACE FUNCTION get_product_info (id INT) 
  RETURNS product_info 
AS 
$$
SELECT product_name, price
FROM products
WHERE product_id = id;
$$ 
LANGUAGE SQL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

In this example, the function get_product_info returns a value of the composite type product_info, which includes product_name and price.

Third, call the get_product_info to retrieve the information of the product id 1:

SELECT get_product_info(1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       get_product_info
-------------------------------
 ("Samsung Galaxy S24",999.99)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The statement returns a value of the product_info composite type.

To select fields from this composite value, you can place the function call in the FROM clause:

SELECT * FROM get_product_info(1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    product_name    | price
--------------------+--------
 Samsung Galaxy S24 | 999.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

Use the CREATE FUNCTION statement to create a new user-defined function.

Quiz #

Was this tutorial helpful ?