TIL: psql's \gexec runs the output of your query as SQL

Needed to drop every table in a schema whose name starts with stale_. My instinct was to pipe psql output to a shell script that pipes back into psql. Gross.

Turns out psql has \gexec, which executes the text output of the previous query as SQL.

SELECT 'DROP TABLE ' || quote_ident(schemaname) || '.' || quote_ident(tablename) || ' CASCADE;'
FROM pg_tables
WHERE tablename LIKE 'stale_%';
\gexec

The SELECT produces a column of DROP TABLE statements. \gexec runs them. No shell loop, no sed, no injection risk if you remember quote_ident.

Also works great for generating ALTER TABLE statements in bulk:

SELECT 'ALTER TABLE ' || quote_ident(table_name)
       || ' SET (autovacuum_vacuum_scale_factor = 0.05);'
FROM information_schema.tables
WHERE table_schema = 'public' AND table_name LIKE 'events_%';
\gexec

The only gotcha: output is run statement-by-statement, so if one fails the next ones still run (unless you’re in a transaction block). I now wrap it in BEGIN/COMMIT when I want all-or-nothing.

Wish I’d known this about five years ago.