Need feedback on new feature (\for)

[Martijn van Oosterhout]

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_%';

Produces:

Expanded to: grant select on "test" to "postgres"
Expanded to: grant select on "test2" to "postgres"
[etc]

Anyway, I'm sure you can come up with all sorts of uses. It occurs to me to
be a way to produce a whole lot of commands to create very similar triggers
based on a list of tables. And other commands to remove them again. My
questions are as follows:

1. Due to the way that backslash commands are implemented, the entire second
query has to be on one line. I don't see a good way to deal with this.
2. If the results being substituted contain quotes, they might not
substitute cleanly. Do we care?
3. Do people even want something like this?
4. Should it list the commands being executed?
5. What should happen upon an error in the generated query?
6. Maybe \foreach is clearer?
7. There should probably be a test mode to just dump the queries without
executing.

Thoughts welcome.

Preliminary patch at:
http://svana.org/kleptog/pgsql/forloop1.patch

Anonymous CVS access isn't working for me so it's not against the latest,
hopefully it's not too changed. It's somewhere around 7.3.2.

Have a nice day,
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/

"All that is needed for the forces of evil to triumph is for enough good
men to do nothing." - Edmond Burke
"The penalty good people pay for not being interested in politics is to be
governed by people worse than themselves." - Plato

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/P6VNY5Twig3Ge+YRAsDdAJ9B6StPdrdLvFi6uEtZM/QKWX2UzQCeMuQg
7v1YTV8r1qwaGNj6ZMQSJlc=
=BnAp
-----END PGP SIGNATURE-----

[Karsten Hilbert]

> 3. Do people even want something like this?
yes

4. Should it list the commands being executed? perhaps just in not("\set export 1") mode ?
5. What should happen upon an error in the generated query? IMHO all the generated queries should be in one transaction
7. There should probably be a test mode to just dump the queries without
executing. Yes.
Thoughts welcome.

Thanks.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Martijn van Oosterhout <kl*****@svana.org> writes:

grant select on ":2" to ":1"=20
\for select usename, relname from pg_catalog.pg_user, pg_catalog.pg_class w=
here relname not like 'pg_%'; Thoughts welcome.
Interesting but it seems awfully messy as-is. How about something like

\for
.... control query here ...
\do
.... one or more queries here ...
\done

This would eliminate the single-line restriction on the control query
and also allow more than one query in the loop body. I don't have a
clear idea of what it would take to implement, but I'm visualizing
the \for and \do commands as setting flags that would prevent queries
from actually being sent to the backend; they'd just get stacked up in
a pair of buffers. Then \done executes the loop and resets the flags.
2. If the results being substituted contain quotes, they might not
substitute cleanly. Do we care?
Yes. I would argue that the style of substitution you propose is all
wrong. The substituter should not look inside single- or double-quoted
literals --- writing colon in a literal shouldn't become fraught with peril.
Rather, substitute for :n when it appears outside any quotes, and let
the substituted value include the needed quotes.
Maybe "for" could include some specification of the expected quoting
style, along the lines of
\for string,string,number
"string" would imply that the corresponding :n symbol is replaced by
a correctly single-quoted literal; perhaps "name" to replace by a
correctly double-quoted literal; "number" to just substitute exactly
what comes back from the query. (These choices of names could probably
be improved upon, but you get the idea --- sort of a weak form of
declaring datatypes for the parameters.)
4. Should it list the commands being executed?
Not by default, but I like the idea of a test mode.
5. What should happen upon an error in the generated query?

Abort the loop.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Karsten Hilbert]

> > 5. What should happen upon an error in the generated query?

IMHO all the generated queries should be in one transaction

which should get aborted, of course

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Scott Lamb]

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Martijn van Oosterhout]

On Sun, Aug 17, 2003 at 12:40:56PM -0400, Tom Lane wrote:

Martijn van Oosterhout <kl*****@svana.org> writes:

grant select on ":2" to ":1"=20
\for select usename, relname from pg_catalog.pg_user, pg_catalog.pg_class w=
here relname not like 'pg_%';

Thoughts welcome.

Interesting but it seems awfully messy as-is. How about something like

\for
... control query here ...
\do
... one or more queries here ...
\done

Indeed, I thought of this after I'd turned my machine off. You could do it
by keeping some buffers in the background. You'd need to indicate what
context you're in.
This would eliminate the single-line restriction on the control query
and also allow more than one query in the loop body. I don't have a
clear idea of what it would take to implement, but I'm visualizing
the \for and \do commands as setting flags that would prevent queries
from actually being sent to the backend; they'd just get stacked up in
a pair of buffers. Then \done executes the loop and resets the flags.
Something like that.

2. If the results being substituted contain quotes, they might not
substitute cleanly. Do we care?

Yes. I would argue that the style of substitution you propose is all
wrong. The substituter should not look inside single- or double-quoted
literals --- writing colon in a literal shouldn't become fraught with peril.
Rather, substitute for :n when it appears outside any quotes, and let
the substituted value include the needed quotes.
Maybe "for" could include some specification of the expected quoting
style, along the lines of
\for string,string,number
"string" would imply that the corresponding :n symbol is replaced by
a correctly single-quoted literal; perhaps "name" to replace by a
correctly double-quoted literal; "number" to just substitute exactly
what comes back from the query. (These choices of names could probably
be improved upon, but you get the idea --- sort of a weak form of
declaring datatypes for the parameters.)

Yes, I see that but it also limits what you could do. For example:

\for
select oid from <some funky query here>
\do
drop constraint "RI_ConstraintTrigger_:1"
\done

(Actually, the clash of \do and \done with the ordinary \d commands will get
very irritating. Need better names.) I was actually leaning the other way,
always substitute but add escapes if inside a string. Incidently, the above
case could be handled by performing the concatintation in the query.

Alternatively, allow you to name the variables but then you get a parsing
problem. If the variable is "var", do you substitute :variable? How do you
choose the other style (like the shell has $hello and ${h}ello).

I'd could probably live with being strict and require you to do all your
trickery in the control query. It will look a little strange if you do:

\for
select tablename, '<begin code>' || field || 'end code' from <blah>
\do
create trigger on table :1 as :2
\done

But it may be worth it for the robustness provided. It's not like we are
conforming to any standard here.

4. Should it list the commands being executed?

Not by default, but I like the idea of a test mode.

OK

5. What should happen upon an error in the generated query?

Abort the loop.

OK

Thanks for your ideas.
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ "All that is needed for the forces of evil to triumph is for enough good
men to do nothing." - Edmond Burke
"The penalty good people pay for not being interested in politics is to be
governed by people worse than themselves." - Plato

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/QBWaY5Twig3Ge+YRAqcfAJ0eY4PlFrQ+wJoX2DSOpi6wjgh6ng CghOda
RPpvcMQQdTPkaJUnl8eCDj4=
=hu75
-----END PGP SIGNATURE-----

[Tom Lane]

Martijn van Oosterhout <kl*****@svana.org> writes:

Yes, I see that but it also limits what you could do. For example: \for
select oid from <some funky query here>
\do
drop constraint "RI_ConstraintTrigger_:1"
\done .. Incidently, the above
case could be handled by performing the concatintation in the query.

Yes. I'd argue that that's a much cleaner approach. I really dislike
the notion of doing substitutions inside quoted strings. (You'll notice
that psql does not do that now for :name variables --- I think that
these substitutions should follow the exact same rules.)

In general, you can do any sort of calculation on the strings with ease
in the control query, while the substituter in psql will necessarily be
pretty stupid. So I think it makes much more sense to push the
complexity into the control query and keep the substitution rules
non-surprising.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings