Postgres run statement against multiple schemas

Question

I have a mult-tenant application where tenants are set up on different schemas within the same database. The reason being that they have some shared data they use on one of the schemas.

Up until now I've been using a bash script with a list of the schemas in it that needs to be updated whenever a new schema is added and I need to do things like table schema changes across the accounts. For instance adding a new column to a table.

Is there a way in Postgres, psql, etc... to run for instance

ALTER TABLE some_table ADD COLUMN some_column TEXT NOT NULL DEFAULT '';

without having to do string replacement in another script like bash for instance.

So in other words is there an easy enough way to get the schemas, and write in psql a for loop that will iterate through the schemas and run the statement each by setting search_path for instance.

The reason being that the number of tenants is growing, and new tenants can be added by admin users that aren't devs, so I'm constantly updating my shell scripts. This will only grow exponentially. Is there a standard way of handling this kind of problem?

Yes, you can create a procedure on the main schema (posgres) to fetch all schemas in the INFORMATION_SCHEMA schema and make your change running the command for it — Jorge Campos
– Jorge Campos, Commented Mar 2, 2017 at 14:55

user330315 · Accepted Answer · 2017-03-02 15:02:47Z

You can do that with a little PL/pgSQL block:

do
$$
declare
  s_rec record;
begin
  for s_rec in select schema_name 
               from information_schema.schemata
               where schema_name not in ('pg_catalog', 'information_schema')
  loop
     execute format ('ALTER TABLE if exists %I.some_table ADD COLUMN some_column TEXT NOT NULL DEFAULT ''''), s_rec.schema_name);
  end loop;
end;
$$

The if exists will make sure the statement doesn't fail if that table doesn't exist in the schema.

If you over-simplified your question and want in fact run complete scripts once for each schema, generating a script for each schema that includes the actual script is probably easier:

select concat(format('set search_path = %I;', schema_name), 
              chr(10), 
              '\i complete_migration_script.sql')
from information_schema.schemata
where schema_name not in ('pg_catalog', 'information_schema')

You can spool the output of that statement into a file and then run that file using psql (of course you need to replace complete_migration_script.sql with the actual name of your script)

Collectives™ on Stack Overflow

Postgres run statement against multiple schemas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related