473,320 Members | 2,110 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Need feedback on new feature (\for)

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-----

Nov 11 '05 #1
6 1982
> 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

Nov 11 '05 #2
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

Nov 11 '05 #3
> > 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

Nov 11 '05 #4
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

Nov 11 '05 #5
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-----

Nov 11 '05 #6
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

Nov 11 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: smilesinblues | last post by:
Hi, I have seen this on google and MSN. Google groups has star on which if you click the topic becomes stared... MSN has 5 stars to get feedback from user for giving rank to the pages. Sorry I...
1
by: Alan Shi [MSFT] | last post by:
I am collecting data for two features that shipped with the CLR in v1.0 and v1.1. If you use and/or depend on these features, I would appreciate hearing from you with a brief description of how you...
7
by: Mike Kamermans | last post by:
I hope someone can help me, because what I'm going through at the moment trying to edit XML documents is enough to make me want to never edit XML again. I'm looking for an XML editor that has a...
0
by: Nick White [MSFT] | last post by:
Hello all: I'd like to take a moment to inform you of the existence of new product group-monitored feedback email aliases we've instituted for Windows Embedded. This feedback medium allows you...
1
by: stax | last post by:
Hello, I would like to add a Send Feedback feature to my application. Does anybody know or easy way to do this, any applications that do this, articles, samples or anything else that could give me...
4
by: clintonG | last post by:
I'm wondering if anybody has figured out how to implement this feedback methodology which displays Characters Remaining when using the TextBox TextMode="MultiLine" attribute which will cause the...
2
by: anony | last post by:
Maybe this feature is already out there. I guess you could write triggers to do some of this. Often when designing a database I add a start_date and end_date column to the table. The start_date...
38
by: George Sexton | last post by:
We're in the process of re-launching our web calendar product. We would like to get your opinions on a couple of things about our site and product. This is a relaunch of an existing product with...
2
by: lorlarz | last post by:
Looking for feedback on Matching Exercises Maker/ Builder: http://mynichecomputing.com/ReadIt/translateT.html For one thing, I am concerned about storing the matching kwork (known word) as...
9
by: moondaddy | last post by:
using c# 3.5 I have list of business objects which I will use in lists for databinding and I want to hide some of the fields so they don't show up in the list control. some of my list will be:...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.