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!

Answer 1

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

• speendo: 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: Yes, with a tweak. Change the definition to do_stuff(text), and be sure to use quote_ident() where appropriate.