Skip to content
Advertisement

How to grant permissions in Postgres on tables that start with parcel?

GRANT UPDATE 
ON (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'parcel%') 
TO police;

Advertisement

Answer

GRANT takes only fixed table names (or list of tables). What you want require dynamic SQL, ie dynamically building a statement and executing it in an anonymous block.

Consider:

do
$$
begin
    execute (
        select 
            'grant all on table '
            || string_agg (format('%I.%I', table_schema, table_name), ', ')
            || ' to police'
        from information_schema.tables
        where table_name like 'parcel%'
   )    ;
end
$$;
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement