EXECUTE and format() in SQL

SQL built at run time, with format() doing the quoting a paste-up would botch.

-- EXECUTE runs SQL built at run time; format() quotes it safely.
-- Dynamic strings stay lowercase: they are data here, not statements.
DO $$
DECLARE
    tally BIGINT;
    chosen TEXT := 'products';
BEGIN
    EXECUTE format('select count(*) from %I', chosen)
    INTO tally;
    RAISE NOTICE '% holds % rows', chosen, tally;
END;
$$;

How it works

format('... %I', chosen) quotes the identifier safely — injection has no seam.
EXECUTE ... INTO tally runs the built string and captures the result.
The dynamic string stays lowercase: it is data here, not a statement.

Keywords and builtins used here

BEGIN
BIGINT
DECLARE
DO
END
EXECUTE
INTO
TEXT

The run, in numbers

Lines: 12
Characters to type: 314
Tokens: 39
Three-star pace: 60 tpm

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

Type this snippet

Step 4 of 4 in Queries in code, step 16 of 23 in PostgreSQL Stored Procedures.

← Previous Next →