From: | "Lennart Ripke" <l(dot)ripke(at)automationwr(dot)de> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | How to use search_path in CASE |
Date: | 2010-07-02 06:11:10 |
Message-ID: | 43B631B7D85C9041AE58B7834DFF6A751F2298@server.Auto.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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;
WHEN 2 THEN
SET LOCAL search_path TO schema2;
WHEN 3 THEN
SET LOCAL search_path TO schema3;
WHEN 4 THEN
SET LOCAL search_path TO schema4;
WHEN 5 THEN
SET LOCAL search_path TO schema5;
WHEN 6 THEN
SET LOCAL search_path TO schema6;
END CASE;
INSERT INTO foo (...) VALUES (...);
END LOOP;
This does not work. All values are directed into table 'schema1.foo'.
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? I don't want to put the INSERT
statement into the CASE structure for reasons of clarity and
readability.
Best regards, Lennart
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-07-02 06:24:45 | Re: How to use search_path in CASE |
Previous Message | David Jarvis | 2010-07-02 02:00:52 | Re: Invalid syntax for integer |