SQL DO

What is the PostgreSQL DO statement?

Executes an anonymous procedural code block immediately, without creating a stored function.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DO: Supported: PostgreSQL 9.0+. Not available in MySQL, SQL Server, Oracle, SQLite, or standard SQL.

SQL DO Full Explanation

DO runs an inline code block written in any trusted procedural language installed in the PostgreSQL instance (PL/pgSQL by default). Because the block is anonymous, PostgreSQL compiles and discards it after execution, making DO ideal for one-off tasks that do not justify a permanent function. The block executes in the current transaction; if the surrounding transaction is rolled back, changes made inside the DO block are also rolled back. A DO block can contain complex control-of-flow, variable declarations, cursors, and exception handling, but it cannot return a result set or be used in a SELECT statement. Optional SECURITY DEFINER allows the block to run with the privileges of its creator; SECURITY INVOKER (default) uses the caller’s rights. You may specify a different language such as plpythonu or plv8 if the language is trusted or the superuser flag allows it. The code must be delimited by a pair of dollar-quoting tags, commonly $$, and terminated with a semicolon after the closing delimiter. Large blocks should be indented for readability. Because the block is parsed each time it runs, heavy repetition inside application code is discouraged in favor of permanent functions.

SQL DO Syntax

DO [ [ SECURITY { DEFINER | INVOKER } ] ]
[ LANGUAGE lang_name ]
$$
    -- procedural statements
$$;

SQL DO Parameters

LANGUAGE lang_name (text) - Procedural language used to execute the block; default is plpgsql.
SECURITY DEFINER | INVOKER (keyword) - Defines whether the block runs with the privileges of its creator (DEFINER) or caller (INVOKER); default is INVOKER.

Example Queries Using SQL DO

-- Simple ad-hoc notice
DO $$
BEGIN
    RAISE NOTICE 'Hello from DO!';
END;
$$;

-- Data fix inside a transaction
BEGIN;
DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT id FROM users WHERE is_active IS NULL LOOP
        UPDATE users SET is_active = FALSE WHERE id = rec.id;
    END LOOP;
END;
$$;
COMMIT;

-- Using a different language (plpython3u)
DO LANGUAGE plpython3u $$
import datetime
plpy.execute("INSERT INTO audit_log(event, created_at) VALUES ('maintenance', '%s')" % datetime.datetime.utcnow())
$$;

Expected Output Using SQL DO

PostgreSQL returns the message DO and the time taken
Any RAISE NOTICE statements display their text
Data modifications inside the block take effect when the surrounding transaction commits

Use Cases with SQL DO

One-off maintenance scripts during migrations
Bulk data corrections that require procedural loops
Quick privilege-escalated operations with SECURITY DEFINER
Rapid prototyping of procedural logic before turning it into a function

Common Mistakes with SQL DO

Forgetting the terminating semicolon after the second $$ delimiter
Expecting DO to return query results; it cannot be used in SELECT
Using an untrusted language without superuser rights
Omitting dollar quoting or mismatching the opening and closing tags

First Introduced In

PostgreSQL 9.0

Frequently Asked Questions

What languages can I use inside DO?

PostgreSQL allows any installed procedural language marked as trusted, such as plpgsql, plpython3u, plperl, or plv8. Superuser rights are required for untrusted languages.

Does a DO block run in its own transaction?

No. It executes within the caller's current transaction. You may wrap the DO block in BEGIN/COMMIT to control commit behavior.

When should I prefer CREATE FUNCTION instead of DO?

Use CREATE FUNCTION for logic that will be reused, needs to return values, or must be called from other SQL statements. Choose DO for one-time maintenance or migration tasks.

Can I execute dynamic SQL inside DO?

Yes. With PL/pgSQL, use EXECUTE to run dynamic SQL strings constructed at runtime.