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.