How to use search_path in CASE

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