PLpgSQL Functions

Summary: In this tutorial, you’ll learn how to create a user-defined function using PL/pgSQL.

Creating PL/pgSQL functions #

A user-defined function is a reusable piece of code stored in the PostgreSQL Server that performs a specific task, extending the database’s functionality.

PostgreSQL allows you to write user-defined functions in various languages, including SQL and PL/pgSQL.

To define a user-defined function, you use the CREATE FUNCTION statement.

Here’s the syntax for creating a function using PL/pgSQL:

CREATE OR REPLACE function_name(parameters)
RETURNS return_type
AS
'function body'
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• The 'function body' is a literal string. Typically, you use a dollar-quoted string to define the function body.
• The LANGUAGE plpgsql option instructs PostgreSQL that the function uses PL/pgSQL as the language.

The following shows how to create a user-defined function whose body is a dollar-quoted string:

CREATE OR REPLACE function_name(parameters)
RETURNS return_type
AS
$$
DECLARE
    -- declaration
BEGIN
    -- function body
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, we use the dollar-quoted string literal syntax. You can place the PL/pgSQL code between the $$:

DECLARE
   -- declaration
BEGIN
   -- function body
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PL/pgSQL Function Example #

We’ll use the products table for the example:

The following example defines a function called get_price that returns the price of a product with the DECIMAL type based on a product id:

CREATE OR REPLACE FUNCTION get_price(id INT)
RETURNS DEC
AS
$$
DECLARE
    v_price DEC;
BEGIN
    SELECT price
    INTO v_price
    FROM products
    WHERE product_id = id;

    RETURN v_price;
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How it works:

First, declare a variable v_price in the declaration section:

DECLARE v_price DEC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, retrieve the price of the product based on the id from the products table and assign it to the v_price variable:

SELECT price
INTO v_price
FROM products
WHERE product_id = id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, return the product price. If the product does not exist, the v_price will be NULL:

RETURN v_price;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following statement calls the get_price function to return the price of the product with id 1:

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

Try it

Output:

 get_price
-----------
    999.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the CREATE FUNCTION with the option LANGUAGE plpgsql to define a function using PL/pgSQL.

Quiz #