automate schema creation in PostgreSQL (maybe plpgsql?)

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 开发者_JS百科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!

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