Skip site navigation (1) Skip section navigation (2)

Re: How to use search_path in CASE

From: "Lennart Ripke" <l(dot)ripke(at)automationwr(dot)de>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: How to use search_path in CASE
Date: 2010-07-02 06:51:38
Message-ID: 43B631B7D85C9041AE58B7834DFF6A751F2299@server.Auto.local (view raw or flat)
Thread:
Lists: pgsql-novice
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 [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us] 
Gesendet: Freitag, 2. Juli 2010 08:25
An: Lennart Ripke
Cc: pgsql-novice(at)postgresql(dot)org
Betreff: Re: [NOVICE] How to use search_path in CASE 

"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




In response to

Responses

pgsql-novice by date

Next:From: A. KretschmerDate: 2010-07-02 07:17:06
Subject: Re: How to use search_path in CASE
Previous:From: A. KretschmerDate: 2010-07-02 06:31:48
Subject: Re: How to use search_path in CASE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group