PL/pgSQL Blocks

Summary: in this tutorial, you’ll learn about PL/pgSQL blocks, how to create and execute some blocks.

Overview of a PL/pgSQL Block #

PL/pgSQL is a blocked structure programming language. PL/pgSQL organizes code into blocks.

Here’s the syntax of a block:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

A block has two main sections:

• Declaration: The declaration section is optional. It is where you declare variables, constants, and cursors.
• Body: The body section is required. It is where you put the logic of the block, such as SQL statements.

A block may include an optional label appearing at the beginning and end. The label is necessary only when you use statements like the EXIT statement or to qualify the variable names declared in the block.

PL/pgSQL Block Example #

The following example shows how to create a block that displays a message "Hello, World":

DO
$$
BEGIN
    RAISE NOTICE '%', 'Hello, World';
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output

NOTICE:  Hello, WorldCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How it works:

1. The DO statement executes the PL/pgSQL block.
2. We use the PL/pgSQL block as a dollar-quoted string constant, which starts with $$ and ends with $$. This helps avoid escaping quotations and special characters in the PL/pgSQL code.
3. A block starts with the BEGIN keyword and ends with the keyword END. Note that BEGIN doesn’t mean start a transaction.

The RAISE NOTICE statement displays a notice when you execute the block:

RAISE NOTICE '%', 'Hello, World';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The RAISE NOTICE statement starts with a placeholder %, followed by a comma, and the value for that placeholder. The block does not include a declaration section in this example.

Executing the Block in psql #

First, connect to PostgreSQL using the psql tool:

psql -U postgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The psql tool will prompt you to enter the password for the postgres user:

Password for user postgres:Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You must enter a valid password and press the Enter (or Return) key.

Second, copy the PL/pgSQL code above and paste it into the psql tool:

postgres=# DO
postgres-# $$
postgres$# BEGIN
postgres$#     RAISE NOTICE '%', 'Hello, World';
postgres$# END;
postgres$# $$;
NOTICE:  Hello, World
DOCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output

NOTICE:  Hello, World
DOCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Executing the PL/pgSQL Block Using pgAdmin 4 #

First, launch the pgAdmin4 tool and open the inventory database.

Second, open the query tool by following the menu path Tools > Query tool:

Third, copy and paste the PL/pgSQL block into the query tool:

Finally, click the Execute Script button (or press the F5 keyboard shortcut).

PL/pgSQL Block with Declaration Example #

The following example shows how to define a block with a declaration section.

DO
$$
DECLARE
    name VARCHAR = 'Joe';
BEGIN
    RAISE NOTICE 'Hello %', name;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output

NOTICE:  Hello JoeCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, we add the declaration section and declare a variable name with VARCHAR type and value 'Joe':

DECLARE
    name VARCHAR = 'Joe';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To declare a variable, you provide the name, data type, and initial value:

name VARCHAR = 'Joe';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

We substitute the name variable with the placeholder % in the 'Hello %' string in the RAISE NOTICE statement:

RAISE NOTICE 'Hello %', name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The output will be like this:

NOTICE:  Hello JoeCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PL/pgSQL Subblocks #

In PL/pgSQL, blocks can be nested within the body of other blocks. Blocks nested inside other blocks are called subblocks. Blocks that contain nested blocks are called outer blocks:

Block nesting allows you to organize the blocks into smaller, more manageable trunks. For example:

DO
$$
DECLARE
    total_quantity INT = 0;
BEGIN
     DECLARE
          safety_stock INT = 10;
          on_hand_qty INT = 100;
     BEGIN
          total_quantity = safety_stock + on_hand_qty;
     END;
     RAISE NOTICE 'The total quantity is %', total_quantity;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output

NOTICE:  The total quantity is 110Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How it works:

In the main block, we declare and initialize total_quantity to 0:

DECLARE
    total_quantity INT = 0;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In the subblock, we declare and initialize safety_stock to 10 and on_hand_qty to 100:

DECLARE
    safety_stock INT = 10;
    on_hand_qty INT = 100;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Additionally, we calculate the total quantity by adding safety_stock and on_hand_qty and assigning the result to total_quantity:

total_quantity = safety_stock + on_hand_qty;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

After the subblock, we display the total quantity in the main block:

RAISE NOTICE 'The total quantity is %', total_quantity;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• PL/pgSQL is a blocked structure programming language.
• A block includes two main sections: declaration and body.
• The declaration section is optional, while the body section is required.
• Blocks can be nested within the body section of other blocks.

Quiz #