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