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$

• Nik Rav: Thanks for prompt reply......... It's really useful.......