automate schema creation in PostgreSQL (maybe plpgsql?)

Question

I have a big database (in PostgreSQL 8.4) which I try to analyse in smaller parts. To do that, I copy parts of the content of the big database to other schemas (I know it is somehow against the philosophy of databases to copy data, but without that step the analysis is too slow).

There are quite a few SQL-commands that I need to perform, to get a new schema with all necessary tables inside it. However, the difference between the creation of one schema to the creation of another schema is very small (in principle its just the name of the schema and a different value in a "WHERE"-clause).

My question is the following:

Is it possible to write a function that takes a certain value as a parameter and uses this parameter in the where clause (and as the name of the schema?) If it is possible, which program-language would you suggest (maybe plpgsql), and what would such a script look like (just as a skeleton)?

Thank you in advance!

Denis de Bernardy · Accepted Answer · 2011-06-16 14:21:54Z

2

Not sure I'm making perfect sense of your question, but it sounds like should be using the temporary schema:

create temporary table foo as select * from bar where ...

On occasion it's also useful to use the same name:

create temporary table foo as select * from foo where ...

Else yes, dynamic SQL works:

create function do_stuff(_table regclass) returns void as $$
begin
  execute 'select 1 from ' || _table;
end; $$ language plpgsql strict;
select do_stuff('schemaname.tablename');

http://www.postgresql.org/docs/9.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

answered Jun 16, 2011 at 14:21

Denis de Bernardy

79.2k14 gold badges138 silver badges158 bronze badges

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

2 Comments

speendo Over a year ago

thank you! the dynamic sql-part of your answer seems to be what I am looking for! Would this also work with ... BEGIN EXECUTE 'CREATE SCHEMA '|| schemaname; ... or ... BEGIN EXECUTE 'CREATE TABLE '|| tablename; ...?

Denis de Bernardy Over a year ago

Yes, with a tweak. Change the definition to do_stuff(text), and be sure to use quote_ident() where appropriate.

Collectives™ on Stack Overflow

automate schema creation in PostgreSQL (maybe plpgsql?)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related