#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Location
    Swindon, UK
    Posts
    8
    Rep Power
    0

    Loop Schema Duplicate


    Hello All,

    I have created a schema, now I would like to duplicate that schema and name it plus 1, so for example.

    Create schema
    name = name +1
    loop until > 5

    I have the code to create a new schema and implement the name +1
    do
    $body$
    declare
    counter integer;
    stmt text;
    begin
    counter :=1;
    loop
    stmt := 'create schema ljschema' || counter::text;
    execute stmt;
    counter := counter + 1;
    exit when counter > 5;
    end loop;
    end
    $body$;

    commit;

    But I am asking to be able to do this with an already created schema so I can pretty much loop duplicate it, and implement the name each time.

    Thanks in advanced,

    LJ
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    11
    I feel like this might be an X Y problem. Why do you need to copy the schema? What effect are you trying to accomplish?

    Most of the time needs fall in two categories:
    • You actually want a copy of a database's layout, not just one schema, but you don't realize that at first.
    • You actually want to partition tables, but don't realize that at first, either.

    The universality of the two cases above is so strong that there is a db template feature and a host of slick table partitioning features, but no automatic way to copy a schema (the hack is to use pg_dump to dump only the DDL (structure definition) of a schema with no data, and then use COPY to pull it back in under a new name).

    So anyway, tell me what the problem is you're trying to solve and maybe we can discover something more useful than command line DDL tricks.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Location
    Swindon, UK
    Posts
    8
    Rep Power
    0
    Thanks for your help so far.

    Yes it is because I want to partition the database.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    11
    About how large are you expecting the table(s) to get? Or why else are you partitioning?

    If I have some idea of the reason why then I'll be able to answer better. The answer might range from "you need a compound key, not a partition" to "you should create a template in the cluster and duplicate the whole db" and everything in between. Most of the time the answer is the first one.

IMN logo majestic logo threadwatch logo seochat tools logo