Re: How to use search_path in CASE

In response to Lennart Ripke :
> Hello!
>
>
>
> 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.
>
>
>
> Something like:
>
>
>
> For s in 1..6 LOOP
>
> CASE s
>
> WHEN 1 THEN
>
> SET LOCAL search_path TO schema1;
> ...
>
> END CASE;
>
>
>
> INSERT INTO foo (...) VALUES (...);
>
> END LOOP;
>
>
>
> This does not work. All values are directed into table ?schema1.foo?.

Right. This can't work. You have to use dynamic SQL in this case. That's
the way:

- define a text-variable my_sql = 'insert into ';
- build a string that contains your SQL:
case s when 1 then my_sql = my_sql || 'schema1.' ... and so on
- EXECUTE that: execute my_sql;

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99