Can we use subquery in GRANT statement in postgres

Question

I want to grant select privilege on few selected tables.

Can I use sub-query in GRANT statement????

I have tried the following query:

GRANT SELECT ON TABLE  
(SELECT array_to_string( array_agg(table_schema||'.'||table_name),', ' ) as  flttab 
FROM information_schema.tables 
where table_schema = 'try3' AND table_name like 'ph1_part_03%') a TO xyz;

But I have got the below error:

ERROR: syntax error at or near "("
LINE 5: GRANT SELECT ON TABLE (SELECT array_to_string( array_agg(ta...
^

********** Error **********

ERROR: syntax error at or near "("
SQL state: 42601
Character: 282

Answer 1

No that's not possible.

The typical solution for this kind of problems is to either use dynamic SQL, or to use a query that generates the desired statement. Then spool that to a script and run the script.

To use dynamic SQL you can use a DO block

do
$body$
declare 
  table_list text;
begin 
  SELECT array_to_string( array_agg(table_schema||'.'||table_name),', ' ) 
    into table_list
  FROM information_schema.tables 
  where table_schema = 'try3' 
  AND table_name like 'ph1_part_03%';

  if table_list is not null then 
    execute 'GRANT SELECT ON '||table_list||' TO xyz';
  end if;
end;
$body$