PostgreSQL: Switching schema name in a loop

Question

I am trying to switch the schema name dynamically to do some data manipulation.

I am running the following.

DO $$
DECLARE schema_name TEXT;
BEGIN
FOR schema_name in
 select "name" from public.tenants
 Loop
 RAISE NOTICE 'The current schema is %', schema_name;
 INSERT INTO schema_name.campuses (select * from public.campuses where code = schema_name);
 End Loop;
END $$;

However I am presented with the following

NOTICE: The current schema is NTU

ERROR: relation "schema_name.campuses" does not exist LINE 1: INSERT INTO schema_name.campuses (select * from public.campu... ^ QUERY: INSERT INTO schema_name.campuses (select * from public.campuses where code = schema_name) CONTEXT: PL/pgSQL function inline_code_block line 8 at SQL statement SQL state: 42P01

I have tried wrapping the schema_name in "" as well as []. Is there a solution to this as my googling is not helping me at present.

Answer 1

This happens when you raise a WARNING try a INFO instead

Additional you need dynamic sql see https://www.postgresql.org/docs/13/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

DO $$
DECLARE schema_name TEXT;
BEGIN
SET client_min_messages TO INFO;
FOR schema_name in
 select "name" from test.public.tenants
 Loop 
 RAISE info 'The current schema is %', schema_name;
 EXECUTE CONCAT('INSERT INTO ', schema_name , '.campuses (select * from public.campuses where code = 1ドル);')
 USING schema_name;
 End Loop;
END $$;

• Dashwall: Thanks for the response @nbk still getting the following error ERROR: relation "schema_name.campuses" does not exist LINE 1: INSERT INTO schema_name.campuses (select * from public.campu...
• nbk: I changed my answer, to a prepared statenst