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

Re: How to use search_path in CASE

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: How to use search_path in CASE
Date: 2010-07-02 06:31:48
Message-ID: 20100702063148.GD15961@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Lennart RipkeDate: 2010-07-02 06:51:38
Subject: Re: How to use search_path in CASE
Previous:From: Tom LaneDate: 2010-07-02 06:24:45
Subject: Re: How to use search_path in CASE

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