Run same script on under different schema - postgres

Question

I have a script called populate.sql which contains create tables.

CREATE TABLE "EXAMPLE" (
.................
..............
);
CREATE TABLE "BlaBla" (
..........
........
);
CREATE TABLE ...

This script creates more than 20 tables. I want to run this populate.sql on top of different schemas. Let's say I want to run this script on schema1, schema2 and schema3.

Then I can write;

CREATE SCHEMA IF NOT EXISTS "schema1";
SET SCHEMA 'schema1';

on populate.sql and create those tables on one schema.

how can I create those tables on all schema within one psql command?

As far as I feel I have to do FOR LOOP on psql and create schema first and create tables on top of that scheme.

Community · Accepted Answer · 2017-05-23 12:23:03Z

1

Tables will get created in the currently set search_path (if not otherwise specifically set in the create statement).

You could use a loop. In that loop you have to set the searchpath to your schema.

DO 
$$
  DECLARE schemaname text;
BEGIN
    FOR i IN 1..3 LOOP

    schemaname := 'schema' || i::text;

    execute 'CREATE SCHEMA ' || schemaname;
    execute 'SET SCHEMA ' || schemaname;    
    execute 'SET search_path TO ' || schemaname;

    -- conent of populate.sql

  END LOOP;
END
$$;

You cannot call external scripts inside this do block as mentioned by a_horse_with_no_name in the comments. Therefore this answer is only relevant if you want to extend your populate.sql file and wrap this do block around it.

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Oct 19, 2016 at 12:07

ChrisB

2,6152 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

But you can't include a SQL script from inside a do block

Collectives™ on Stack Overflow

Run same script on under different schema - postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related