CODEWITHSUNDEEP
postgresql
Jeff U.
Jeff U.

Reputation: 656

Postgres run statement against multiple schemas

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?

Upvotes: 3

Views: 2412

Answers (1)

user330315
user330315

Reputation:

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)

Upvotes: 7

Related Questions