Home » PL/pgSQL » PL/pgSQL Blocks

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: