Change owner to all tables with PostgreSQL function

Question

I want change owner for all tables in specific table_catalog with function:

CREATE OR REPLACE function change_owner_table_catalog(catalog_arg varchar, owner_arg varchar)
    RETURNS void AS $$
    DECLARE
    table_name_value varchar;
    sequence_name_value varchar;
    BEGIN
        for table_name_value in (SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_catalog = catalog_arg) loop
            ALTER TABLE table_name_value OWNER TO owner_arg;
        end loop;
    END;
    $$ language plpgsql;

I execute it:

select change_owner_table_catalog('cinema', 'cinema_user')

And have the next error:

ERROR: relation "table_name_value" does not exist CONTEXT: SQL statement "ALTER TABLE table_name_value OWNER TO owner_arg" PL/pgSQL function change_owner_table_catalog(character varying,character varying) line 7 at SQL statement SQL state: 42P01

Laurenz Albe · Accepted Answer · 2021-09-10 10:38:16Z

2

You need dynamic SQL, since ALTER TABLE does not support parameters:

EXECUTE format('ALTER TABLE public.%I OWNER TO %I', table_name_value, owner_arg);

answered Sep 10, 2021 at 10:38

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Change owner to all tables with PostgreSQL function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related