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.

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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