DO blocks in SQL

An anonymous PL/pgSQL block: declare, query, and report, all inline.

-- DO runs an anonymous PL/pgSQL block once, right here.
DO $$
DECLARE
    open_orders INTEGER;
BEGIN
    SELECT COUNT(*)
    INTO open_orders
    FROM orders
    WHERE status = 'pending';
    RAISE NOTICE 'orders still open: %', open_orders;
END;
$$;

How it works

1. DO $$ ... $$; runs a one-off block with no function to clean up afterward.
2. DECLARE introduces open_orders before the block's BEGIN.
3. SELECT ... INTO fills the variable and RAISE NOTICE reports it.

Keywords and builtins used here

• BEGIN
• COUNT
• DECLARE
• DO
• END
• FROM
• INTEGER
• INTO
• SELECT
• WHERE

The run, in numbers

Lines

12

Characters to type

227

Tokens

34

Three-star pace

70 tpm

At the three-star pace of 70 tokens a minute, this run takes about 29 seconds.

Type this snippet

Step 1 of 4 in PL/pgSQL blocks, step 5 of 23 in PostgreSQL Stored Procedures.

← Previous Next →