Thread: How to use search_path in CASE
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
"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
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
Hello Tom,
If I rethink schema design I'm concerned about performance: There will be about 100thousand lines in each foo table. I
hopedperformance will be better with separate tables rather than a single one. Do you have any suggestions/experience
onthis?
Best regards, Lennart
-----Ursprüngliche Nachricht-----
Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Gesendet: Freitag, 2. Juli 2010 08:25
An: Lennart Ripke
Cc: pgsql-novice@postgresql.org
Betreff: Re: [NOVICE] 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
In response to Lennart Ripke : > Hello Tom, > > If I rethink schema design I'm concerned about performance: There will > be about 100thousand lines in each foo table. I hoped performance will > be better with separate tables rather than a single one. Do you have > any suggestions/experience on this? 100 thousand Rows per table isn't soo much, and you can use table-partitioning with constraint exclusion. 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