How to Use DO Block in PostgreSQL: Syntax and Examples

In PostgreSQL, the DO block lets you run anonymous procedural code without creating a stored function. You write the code inside DO $$ ... $$ with a specified language like plpgsql, allowing you to execute procedural commands directly.

Syntax

The DO block syntax runs anonymous code in a procedural language. It starts with DO, followed by $$ delimiters enclosing the code, and ends with the language declaration.

• DO: keyword to start the block
• $$ ... $$: dollar-quoted string containing the code
• LANGUAGE: specifies the procedural language, usually plpgsql

sql

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

Example

This example shows how to use a DO block to print a message and perform a simple calculation in PostgreSQL.

sql

DO $$
DECLARE
  my_var integer := 10;
BEGIN
  RAISE NOTICE 'The value of my_var is %', my_var;
  my_var := my_var + 5;
  RAISE NOTICE 'After adding 5, my_var is %', my_var;
END;
$$ LANGUAGE plpgsql;

Output

NOTICE:  The value of my_var is 10
NOTICE:  After adding 5, my_var is 15

Common Pitfalls

Common mistakes when using DO blocks include:

• Forgetting to specify the LANGUAGE at the end, which causes a syntax error.
• Using single quotes inside the block without proper escaping or dollar quoting.
• Trying to return results directly from a DO block, which is not allowed since it does not return data.

Always use RAISE NOTICE or other logging commands to output messages inside DO blocks.

sql

/* Wrong: Missing LANGUAGE clause */
DO $$
BEGIN
  RAISE NOTICE 'Hello';
END
$$;

/* Right: Include LANGUAGE plpgsql */
DO $$
BEGIN
  RAISE NOTICE 'Hello';
END;
$$ LANGUAGE plpgsql;

Quick Reference

Part	Description
DO	Starts the anonymous code block
$$ ... $$	Dollar-quoted string containing the procedural code
LANGUAGE plpgsql	Specifies the procedural language used
BEGIN ... END	Block where procedural statements are written
RAISE NOTICE	Command to output messages during execution

Key Takeaways

Use the DO block to run anonymous procedural code without creating a function.

Always specify the LANGUAGE (usually plpgsql) at the end of the DO block.

Use RAISE NOTICE to print messages inside the DO block.

DO blocks cannot return query results directly.

Dollar quoting ($$) helps avoid escaping single quotes inside the block.