From: | Scott Lamb <slamb(at)slamb(dot)org> |
---|---|
To: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
Cc: | Postgresql General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Need feedback on new feature (\for) |
Date: | 2003-08-17 20:04:03 |
Message-ID: | 3F3FDFB3.1050507@slamb.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Martijn van Oosterhout wrote:
> I've had some fun in the past where I've had to grant a lot of tables and
> other similar system commands. Unfortunatly, you can't use queries to fill
> in fields for you. Anyway, I've implemented a patch which allows the
> following:
>
> grant select on ":2" to ":1"
> \for select usename, relname from pg_catalog.pg_user, pg_catalog.pg_class where relname not like 'pg_%';
That's definitely a useful thing to do, but I'm not sure I like your
syntax. As someone else mentioned, the ":2" is confusing; it's like a
bind variable, but isn't. And real bind variables don't work, as you are
substituting identifiers, not literals.
You're not completely out in the cold doing something like this without
a patch. Right now, I believe you can do something like (in Oracle
PL/SQL-ish syntax; it's more familiar to me):
declare
grantcursor cursor as
select usename, relname
from pg_catalog.pg_user, pg_catalog.pg_class
where relname not like 'pg_%';
begin
for grantline in grantcursor loop
execute immediate 'grant select on '
|| quoteident(grantline.relname)
|| ' to ' || quoteident(grantline.usename) || '"';
end loop;
end;
(I'm not sure how to do an anonymous plpgsql block. Anyone?)
This is more wordy, but should work.
Scott
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2003-08-17 23:29:02 | Re: Need feedback on new feature (\for) |
Previous Message | James Moe | 2003-08-17 19:15:57 | Too many clients |