Thread: Schema handling within plpgsql

Hello list,

I need to setup some 80 schemas for work.

What is the proper plpgsql syntax for looping through schemas?
(postgresql 7.4.1)

Ideally it would be nice to do something like:

for schema in 1,2,3,4,5
do
   create $schema.table ...
   insert into $schema.table select ....
done

I know this can be done from a shell and invoke psql each time, just
wondering if it all can be contained inside plpgsql scripts.

Thanks

george

On Wed, Oct 05, 2005 at 05:45:45PM -0500, George McQuade wrote:
> What is the proper plpgsql syntax for looping through schemas?
> (postgresql 7.4.1)
>
> Ideally it would be nice to do something like:
>
> for schema in 1,2,3,4,5
> do
>    create $schema.table ...
>    insert into $schema.table select ....
> done

See "Executing Dynamic Commands" in the PL/pgSQL documentation:

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

--
Michael Fuhr