PostgreSQL – How to Revoke Privileges for User on Tables with Like Names

postgresqlpostgresql-9.6

Attempting to adapt this solution, but for revoking privileges in this way:

DO
$$
DECLARE
    t record;
BEGIN
    FOR t IN
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE table_schema = 'my_schema'
        AND table_name LIKE 'test\_%'
        LOOP
            EXECUTE format('REVOKE DELETE, INSERT, UPDATE ON TABLE %I.%I FROM test;', table_schema, table_name);
        END LOOP;
END;
$$ LANGUAGE plpgsql;

However, I'm getting "column table_schema does not exist". Am I approaching this the right way?

Using postgresql 9.6 (french locale).

Best Answer

You need to reference the record of the cursor loop:

EXECUTE format('REVOKE DELETE, INSERT, UPDATE ON TABLE %I.%I FROM test;', 
               t.table_schema, t.table_name);
               ^-- here        ^-- here
Related Question