From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Lennart Ripke" <l(dot)ripke(at)automationwr(dot)de> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: How to use search_path in CASE |
Date: | 2010-07-02 06:24:45 |
Message-ID: | 7228.1278051885@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
"Lennart Ripke" <l(dot)ripke(at)automationwr(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2010-07-02 06:31:48 | Re: How to use search_path in CASE |
Previous Message | Lennart Ripke | 2010-07-02 06:11:10 | How to use search_path in CASE |