Block Structure and DO Statements

Every piece of PL/pgSQL code is organised into blocks. Understanding the block structure is essential before writing functions or triggers.

The Block Structure

[ DECLARE
    declarations ]
BEGIN
    statements
[ EXCEPTION
    WHEN condition THEN
        handler_statements ]
END;

Section	Required?	Purpose
DECLARE	No	Declare local variables and cursors
BEGIN…END	Yes	The executable statements
EXCEPTION	No	Handle runtime errors

Running Code with DO

The DO statement executes an anonymous (unnamed) block — perfect for one-off scripts:

DO $$
BEGIN
  RAISE NOTICE 'Hello, PL/pgSQL!';
END;
$$;

RAISE NOTICE prints a message to the client. It is the PL/pgSQL equivalent of DBMS_OUTPUT.PUT_LINE in Oracle.

A Block with Variables

DO $$
DECLARE
  v_message TEXT    := 'Hello!';
  v_count   INTEGER := 0;
BEGIN
  v_count := v_count + 1;
  RAISE NOTICE 'Message: %, Count: %', v_message, v_count;
END;
$$;

The % in RAISE NOTICE is a placeholder substituted by the following arguments (like printf in C).

RAISE Levels

RAISE DEBUG   'debug info';
RAISE INFO    'informational';
RAISE NOTICE  'notice to client';   -- most commonly used
RAISE WARNING 'warning message';
RAISE EXCEPTION 'this is an error'; -- raises an exception, aborts the block

Each level maps to a PostgreSQL message severity. RAISE EXCEPTION actually throws a runtime error.

Nested Blocks

Blocks can be nested. The inner block can read outer variables but the outer block cannot read inner variables:

DO $$
DECLARE
  v_outer TEXT := 'outer';
BEGIN
  DECLARE
    v_inner TEXT := 'inner';
  BEGIN
    RAISE NOTICE '% and %', v_outer, v_inner;
  END;
  -- v_inner is not in scope here
  RAISE NOTICE '%', v_outer;
END;
$$;

Comments

-- Single-line comment

/*
   Multi-line
   comment
*/

The Assignment Operator

PL/pgSQL uses := or = for assignment (both are accepted):

v_count := 10;
v_name  = 'Alice';   -- also valid

String Concatenation

Use || or the concat() function:

RAISE NOTICE 'Hello, %!', first_name || ' ' || last_name;

Common Beginner Mistakes