Loop Schema Duplicate
I have created a schema, now I would like to duplicate that schema and name it plus 1, so for example.
name = name +1
loop until > 5
I have the code to create a new schema and implement the name +1
stmt := 'create schema ljschema' || counter::text;
counter := counter + 1;
exit when counter > 5;
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,
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.
Thanks for your help so far.
Yes it is because I want to partition the database.
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.