PostgreSQL: Switching schema name in a loop

multi-tenantpostgresqlschema

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.

Best Answer

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 $$;
Related Question