1

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.

0

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 $$;
3
  • 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... – Dashwall Mar 5 at 13:02
  • I changed my answer, to a prepared statenst – nbk Mar 5 at 13:34
  • This is great thank you. It works – Dashwall Mar 5 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.