PL/pgSQL

About

It is a powerful SQL scripting language optimized for performance of data intensive tasks, available by default in PostgreSQL, providing:

• Control structure;

• Variable declaration;

• Expressions;

• Loops;

• Cursors and more.

With it you can create:

• Complex functions;

• New data types;

• Stored procedures and more.

User defined functions CANNOT execute Transactions.

Creating

The structure of a PL/pgSQL function is very similar to a PL/SQL functions seen in Functions.

CREATE FUNCTION function_name(p1 type, p2 type, ...) RETURNS return_type AS
$$
    BEGIN
        -- statements
    END;
$$ LANGUAGE plpgsql

Example

Differently from PL/SQL, you return results with RETURN instead of calling a SELECT.

But using SELECT will work.

CREATE OR REPLACE FUNCTION fn_max_col() RETURNS bigint AS
$$
    BEGIN
        RETURN MAX(col_name) FROM table_name;
    END;
$$ LANGUAGE plpgsql

Calling

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

SELECT fn_max_col();

Running without Creating

You can execute blocks without declaring a function with DO.

This way you can test logic without creating a function to later have to delete it.

DO
$$
    BEGIN
        -- statements
    END;
$$ LANGUAGE plpgsql

Usage

Declaring variables

A variable holds a value that can be changed through the block.

Empty variables are NULL.

Use INTO in queries (right after FROM) to pipe the query results to variables.

When assigning query results to variables, make sure that the return data will fit the variable type being used.

E.g.:

• Simple variable can hold 1 col x 1 row.

• record variables can hold n cols x 1 row.

CREATE OR REPLACE FUNCTION fn_max_col(int) RETURNS bigint AS
$$
    DECLARE
        var_name   int := 1;
        created_at timestamp := NOW();
        empty_var  int;
        empty_row  record;
    BEGIN
        -- The SELECT MUST return only 1 value
        SELECT MAX(col_name) + var_name FROM table_name INTO empty_var LIMIT 1;
        
        -- Get all cols from a row
        SELECT * FROM table_name INTO empty_row LIMIT 1;
        
        RETURN empty_var;
    END;
$$ LANGUAGE plpgsql

Overwrite variables names

Use ALIAS FOR to declare a new name for an existing variable.

CREATE OR REPLACE FUNCTION fn_ex(int) RETURNS void AS
$$
    DECLARE
        x ALIAS FOR $1; -- naming unnamed parameter
    BEGIN
        ...
    END;
$$ LANGUAGE plpgsql

Copying data types from table columns

CREATE OR REPLACE FUNCTION fn_ex(int) RETURNS void AS
$$
    DECLARE
        -- Will make a var_name with the same type as column_name from table_name
        var_name table_name.column_name%TYPE;
    BEGIN
        ...
    END;
$$ LANGUAGE plpgsql

Print variable values inside block

Use RAISE NOTICE to print values during execution.

CREATE OR REPLACE FUNCTION fn_ex() RETURNS void AS
$$
    DECLARE
        var_name   int := 1;
        empty_var  varchar;
        created_at timestamp := NOW();
    BEGIN
        RAISE NOTICE 'My variables % % %',
            var_name,
            empty_var,
            created_at;
    END;
$$ LANGUAGE plpgsql

Using IN/OUT without RETURNS

Instead of usign RETURNS to define the return variable you can use IN and OUT to define the parameters that enter and the parameters that returns.

CREATE OR REPLACE FUNCTION fn_ex(IN p1 int, IN p2 int, OUT ret int) AS
$$
    BEGIN
        -- There is no need to RETURN `ret`, at the end of the execution
        -- the value in `ret` will be returned.
        ret := p1 + p2;
    END;
$$ LANGUAGE plpgsql

Returning multiple values

Results are returned as columns. (Column ret1 and column ret2)

CREATE OR REPLACE FUNCTION fn_ex(IN p1 int, IN p2 int, OUT ret1 int, OUT ret2 int) AS
$$
    BEGIN
        ret1 := p1 + p2;
        ret2 := p1 * p2;
    END;
$$ LANGUAGE plpgsql

Subblocks

You can have multiple sub-blocks inside blocks.

Blocks have scopes and can have its own variables.

For this you must label the outer blocks so that the END keyword can know which block is it ending.

-- Define block labels like this
<<PARENT>>
DECLARE
    ...
BEGIN
    DECLARE
    
    BEGIN
    
    END;
END PARENT;

Access outer blocks variables

Access other scope variables by referencing their labels before.

CREATE OR REPLACE FUNCTION fn_ex() RETURNS void AS
$$
    <<PARENT>>
    DECLARE
        counter int := 0;
    BEGIN
        DECLARE
            -- Will have a different scope
            counter int := 0;
        BEGIN
            -- Access the parent node by referencing its label
            RAISE NOTICE 'Parent variable %', PARENT.counter;
            RAISE NOTICE 'Local variable %', counter;
        END;
    END PARENT;
$$ LANGUAGE plpgsql

Retuning query results

When returning multiple rows from a function, use SETOF or TABLE in the function definition, and RETURN QUERY in the block.

Must return all the table columns. Use RETURN TABLE to choose columns.

CREATE OR REPLACE FUNCTION fn_ex() RETURNS [ SETOF | TABLE ] table_name AS
$$
    BEGIN
        -- SETOF can only return *
        RETURN QUERY SELECT * FROM table_name;
        
        -- TABLE can return any columns you choose
        RETURN QUERY SELECT col_1, col_2 FROM table_name;
    END;
$$ LANGUAGE plpgsql

Control structure

if else

BEGIN
    /*
        IF expression THEN
            statement...
        [ ELSIF expression THEN
            statement... ]
        [ ELSE
            statement... ]
        END IF;
    */
END;

case

BEGIN
    /*
        CASE search-expression
            WHEN expression [, expression] THEN
                statement...
            [ WHEN expression [, expression] THEN
                statement... ]
            [ ELSE
                statement... ]
        END CASE;
    */
END;

loop

BEGIN
    /*
        LOOP
            statement...
            EXIT THEN condition;
        END LOOP;
    */
END;

for loops

BEGIN
    /*
        FOR [counter name] IN [REVERSE] [START value] .. [END value] [BY stepping]
        LOOP
            statement...
        END LOOP;
    */
    
    FOR counter IN 1..10
    LOOP
        ...
    END LOOP;
END;

Looping from select queries.

DECLARE
    rec record;
BEGIN
    FOR rec IN
        SELECT col_name FROM table_name;
    LOOP
        ...
    END LOOP;
END;

foreach

BEGIN
    /*
        FOREACH var_name IN ARRAY array_name
        LOOP
            statement...
        END LOOP;
    */
END;

while

BEGIN
    /*
        WHILE expression
        LOOP
            statement...
        END LOOP;
    */
END;

continue

BEGIN
    /*
        CONTINUE [loop_label] [WHEN condition]
    */
    
    DECLARE
        counter int
    LOOP
        counter := counter + 1;
        EXIT WHEN counter > 20;
        -- When counter is 5 it will not run the statements inside the loop
        CONTINUE WHEN counter = 5;
    END LOOP;
END;

Executing other sql commands inside

Use EXECUTE to run some types of SQL queries inside.

BEGIN
    EXECUTE format('
        CREATE TABLE IF NOT EXISTS table_name (id serial NOT NULL)
    ');
END;