FAQ
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

Search Discussions

  • Tom Lane at Jul 2, 2010 at 6:24 am

    "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
  • Lennart Ripke at Jul 2, 2010 at 6:56 am
    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?

    Best regards, Lennart


    -----Ursprüngliche Nachricht-----
    Von: Tom Lane
    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
  • Andreas Kretschmer at Jul 2, 2010 at 7:17 am

    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
  • Andreas Kretschmer at Jul 2, 2010 at 6:31 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJul 2, '10 at 6:16a
activeJul 2, '10 at 7:17a
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase