Re: Need feedback on new feature (\for) - Mailing list pgsql-general

On Sun, Aug 17, 2003 at 12:40:56PM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@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   <kleptog@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