PostgreSQL: Executing DDL on every schema I've a multi-tenant DB setup, and need to add some columns.  I'm using schemas (and search_path) to partition my users, so I'm looking for a ubiquitous way to apply a DDL-schema change to all my databases.  Initially, I'd thought I might be able to do it as a single query (cursor on pg_catalog), but thinking a command-line invocation of psql -f might be the preferred way.
I would prefer the latter solution.  You can collect the schema names into a file (one schema per line) in psql:

\o change_schema.sql
\t on

SELECT n.nspname
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname &lt;&gt; 'information_schema';

-- reset the output
\o
\t off


Then you can easily do the following:

Have a DDL changing script (for example, change_schema.sql), without reference to the including schema

SET search_path TO :schema;

BEGIN;
...
...
ALTER TABLE orders
ADD COLUMN last_modified timestamp;
...
...
COMMIT;


Then you can turn every line of the schema list into a line like

psql -h dbhost -d targetdb -f change_schema.sql -v schema=&lt;schema_name&gt;


with a simple sed command, for example - then you just have to run these commands.   Of course, you can turn it into a proper shell script if you like.