Re: How to use search_path in CASE

"Lennart Ripke" <l.ripke@automationwr.de> writes:
> I have a database with 6 schemas (named schema1 to schema6), each
> contains a table 'foo'. I want to insert testvalues into these tables
> using a plpgsql function.

> I learned you can't use SET search_path  (or SET LOCAL) in a CASE
> structure because the function is using the current search path , which
> does only change at session scope.

> But how to accomplish this task?

1. Rethink that schema design.  It seems pretty poorly chosen.
A rule of thumb is that N identical tables should be replaced by
1 table with one more primary-key column.  A design with N identical
tables in N schemas might make sense if you typically only need to
access one schema at a time, but that isn't your requirement.

or...

2. Do something like

   CASE s
     WHEN 1 THEN
       INSERT INTO schema1.foo ...
     WHEN 2 THEN
       INSERT INTO schema2.foo ...
     etc

            regards, tom lane