How to execute query statements generated in pgAdmin4?

Question

I have the following query which generates a list of ALTER TABLE statements in the 'Data Output' field in pgAdmin4. I can copy & paste them back into the query pane and execute them there.

select 'ALTER TABLE ' || table_name || ' OWNER TO myuser;' from information_schema.tables where table_schema = 'public';

How can I execute the generated statements without having to copy & paste them?

Answer 1

You can use the function for that.

CREATE OR REPLACE FUNCTION change_permission_table() 
RETURNS VOID AS $$
DECLARE
    rec text;
BEGIN
    FOR rec IN SELECT 'ALTER TABLE ' || table_name || ' OWNER TO maz;' FROM information_schema.tables WHERE table_schema = 'public'
    LOOP 
      EXECUTE rec;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Run the function to change the permission
SELECT change_permission_table()