Can we use subquery in GRANT statement in postgres 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
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$