PL/pgSQL Block Structure in PostgreSQL: DECLARE, BEGIN, EXCEPTION, END

Every piece of PL/pgSQL you write, whether it’s a one-off DO script or a stored function, is a block. The PL/pgSQL block structure has four parts: an optional label, an optional DECLARE section, a required BEGIN … END body, and an optional EXCEPTION section.

Quick answer: DO $$ DECLARE n int := 1; BEGIN RAISE NOTICE 'n = %', n; EXCEPTION WHEN others THEN RAISE NOTICE '%', SQLERRM; END $$; runs a complete anonymous block. Every declaration and statement ends with a semicolon.

I tested every block on PostgreSQL 18.6 (Windows, psql), so the NOTICE lines below are my real output.

Sample table for the PL/pgSQL block examples

You’ll reuse one five-row customers table in a demo database called plpgsql_block_demo. If the DDL is new to you, here’s how to create a table in PostgreSQL.

CREATE TABLE customers (
  customer_id int PRIMARY KEY,
  full_name   text NOT NULL,
  state       char(2) NOT NULL,
  balance     numeric(10,2) NOT NULL
);
INSERT INTO customers VALUES
 (1, 'Hannah Brooks',  'TX', 1250.00),
 (2, 'Tyler Reed',     'CA',  640.50),
 (3, 'Grace Miller',   'NY',    0.00),
 (4, 'Marcus Bennett', 'OH', 2310.75),
 (5, 'Olivia Carter',  'FL',   95.20);

The parts of a PL/pgSQL block: label, DECLARE, BEGIN, EXCEPTION, END

Here’s the documented shape. Only BEGIN and END are required.

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
[ EXCEPTION
    handlers ]
END [ label ];

RAISE NOTICE is how you see what ran; each % takes the next argument.

DO $$
<<main>>
DECLARE
  total numeric(10,2);
BEGIN
  SELECT sum(balance) INTO total FROM customers;
  RAISE NOTICE 'Total balance: $%', total;
END main $$;

Output:

psql:block-anatomy.sql:8: NOTICE:  Total balance: $4296.45
DO

You should see Total balance: $4296.45 and then DO. The END label must match the top one, and a semicolon right after BEGIN is a syntax error.

DECLARE section: data types, %TYPE, %ROWTYPE, CONSTANT and NOT NULL

Declare variables here with any of the PostgreSQL data types. Without a default, a variable starts as NULL.

DO $$
DECLARE
  tax_rate CONSTANT numeric := 0.0825;
  c        customers%ROWTYPE;
  who      customers.full_name%TYPE;
  run_date date NOT NULL := '2026-11-02';
  note     text;  -- no default, so NULL
BEGIN
  SELECT * INTO c FROM customers WHERE customer_id = 1;
  who := c.full_name;
  RAISE NOTICE '% (%): tax $%', who, c.state, round(c.balance * tax_rate, 2);
  RAISE NOTICE 'Run %, note is null: %', to_char(run_date, 'MM/DD/YYYY'), note IS NULL;
END $$;

Output:

psql:declare-section.sql:13: NOTICE:  Hannah Brooks (TX): tax $103.13
psql:declare-section.sql:13: NOTICE:  Run 11/02/2026, note is null: t
DO

Hannah Brooks’s tax is $103.13. %ROWTYPE copies a whole row and %TYPE borrows a column’s type, so neither breaks when the table changes.

Note: a NOT NULL variable needs a non-null default, and a CONSTANT can’t be reassigned.

Nested PL/pgSQL blocks: which variable wins when names clash?

When a sub-block declares a variable with an outer variable’s name, the inner one hides it. The label is the way back.

DO $$
<<outer_blk>>
DECLARE
  balance numeric := 100;
BEGIN
  DECLARE
    balance numeric := 25;
  BEGIN
    RAISE NOTICE 'inner %, outer %', balance, outer_blk.balance;
  END;
  RAISE NOTICE 'after the sub-block: %', balance;
END outer_blk $$;

Output:

psql:nested-blocks.sql:12: NOTICE:  inner 25, outer 100
psql:nested-blocks.sql:12: NOTICE:  after the sub-block: 100
DO

The inner block prints 25 and 100, and the outer block still sees 100 afterwards. Labels also let PL/pgSQL loops target an outer loop with EXIT.

EXCEPTION section: what does PostgreSQL roll back?

A block with an EXCEPTION clause works like a savepoint. When the handler catches an error, every change made inside that block is undone, while work done before it stays.

DO $$
BEGIN
  INSERT INTO customers VALUES (6, 'Logan Hayes', 'GA', 300.00);
  BEGIN
    INSERT INTO customers VALUES (7, 'Chloe Ward', 'AZ', 410.00);
    PERFORM 1 / 0;
  EXCEPTION WHEN division_by_zero THEN
    RAISE NOTICE 'Caught: % (SQLSTATE %)', SQLERRM, SQLSTATE;
  END;
END $$;
SELECT customer_id, full_name FROM customers WHERE customer_id > 5;

Output:

psql:exception-rollback.sql:10: NOTICE:  Caught: division by zero (SQLSTATE 22012)
DO
 customer_id |  full_name  
-------------+-------------
           6 | Logan Hayes
(1 row)

Check your work in the SELECT: only Logan Hayes (row 6) is there. Chloe Ward’s insert rolled back with the sub-block. SQLERRM and SQLSTATE hold the message and code.

Important: a block with EXCEPTION is more expensive to enter, so don’t add one without need.

DO block vs CREATE FUNCTION: the same block, a different wrapper

An anonymous DO block takes no parameters, returns nothing and isn’t saved. Put the same block inside CREATE FUNCTION when you want to call it again, pass values in or return rows from a function.

CREATE OR REPLACE FUNCTION state_balance(state text)
RETURNS numeric
LANGUAGE plpgsql AS $body$
DECLARE
  total numeric;
BEGIN
  SELECT sum(c.balance) INTO total
  FROM customers c WHERE c.state = state_balance.state;
  RETURN coalesce(total, 0);
END
$body$;
SELECT state_balance('TX') AS tx, state_balance('ME') AS me;

Output:

CREATE FUNCTION
   tx    | me 
---------+----
 1250.00 |  0
(1 row)

Texas returns 1250.00 and Maine returns 0. $body$ is a named dollar-quote tag, handy when the body contains $$. The final END needs no semicolon.

A hidden outer block is labeled with the function name, so state_balance.state means the parameter, not the column.

Is BEGIN in a PL/pgSQL block a transaction?

No. PL/pgSQL’s BEGIN and END only group statements. A DO block can COMMIT, but only outside an explicit transaction block.

DO $$
BEGIN
  UPDATE customers SET balance = 50 WHERE customer_id = 3;
  COMMIT;
  RAISE NOTICE 'COMMIT worked at the top level';
END $$;
BEGIN;
DO $$
BEGIN
  COMMIT;
END $$;

Output and error:

psql:commit-in-do.sql:6: NOTICE:  COMMIT worked at the top level
DO
BEGIN
psql:commit-in-do.sql:11: ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function inline_code_block line 3 at COMMIT

The first DO commits Grace Miller’s new balance. After BEGIN;, the same COMMIT fails. The rule is the same for stored procedures called with CALL, while functions can never commit.

Common PL/pgSQL block syntax errors

Missing semicolon after a nested END

DO $$
BEGIN
  BEGIN
    RAISE NOTICE 'inner block';
  END
  RAISE NOTICE 'outer block';
END $$;

Error:

psql:error-missing-semicolon.sql:7: ERROR:  end label "raise" specified for unlabeled block
LINE 6:   RAISE NOTICE 'outer block';
          ^

I didn’t expect this one. PL/pgSQL reads the next word as an end label, hence the label raise. Add ; after the inner END.

Missing END IF

DO $$
BEGIN
  IF now() > '2026-01-01' THEN
    RAISE NOTICE 'It is 2026';
END $$;

Error:

psql:error-missing-end-if.sql:5: ERROR:  syntax error at end of input
LINE 5: END $$;
            ^

My first guess was wrong here: the message doesn’t mention IF. When you see syntax error at end of input, count your IF … END IF pairs and loops first.

Where to go next with PL/pgSQL blocks

Test in a DO block, keep EXCEPTION sections small, then move the block into a function. Next steps:

• Repeating work with a PostgreSQL WHILE loop
• Building dynamic SQL safely with format()
• Branching inside a query with CASE
• How SQL Server handles the same errors with TRY…CATCH

Frequently Asked Questions