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

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: How to use search_path in CASE
Date: 2010-07-02 06:11:10
Message-ID: 43B631B7D85C9041AE58B7834DFF6A751F2298@server.Auto.local (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-07-02 06:24:45
Subject: Re: How to use search_path in CASE
Previous:From: David JarvisDate: 2010-07-02 02:00:52
Subject: Re: Invalid syntax for integer

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