Re: Proposal: USING clause for DO statement

2009/11/21 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> On Sat, Nov 21, 2009 at 3:32 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2009/11/21 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>>> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>>>> Hypothetically - when we are able to pass any value to DO script, then
>>>> I don't see problem. If I use Andrew's design - ${shellvar} and add it
>>>> to psql parser, then I could to write
>>>
>>>> \set par1 world
>>>
>>>> do $$
>>>>   begin
>>>>     raise notice 'Helo, % and %', $1, $2;
>>>>   end;
>>>> $$ using :par1, ${USER};
>>>
>>> Ick.  Double, triple ick.  It is astonishing to me how many people think
>>> that the solution to today's problem is always to invent some weird new
>>> syntax to plaster over SQL.  Which for some reason invariably involves
>>> dollar signs and/or curly braces ... there isn't even any originality
>>> involved :-(.
>>>
>>> Maybe we should accept one of these proposals, just so that it usurps
>>> that part of the syntax space forever and we can reject the next ten bad
>>> ideas out-of-hand.  Of course, if the SQL committee ever gets around to
>>> defining curly braces as doing something, we'll be screwed.
>>>
>>> colon-foo is bad enough.  Let's not add more.
>>
>> I have a no problem. Syntax should be defined later. there is simple
>> workaround (using shellvariables):
>>
>> psql ... -v user = $USER
>>
>>
>> I repeat it again and finish:
>>
>> proposal is related only to DO statement (what is Pg specific).
>> Doesn't propose psql changes, doesn't propose PL changes.
>
> I code all day long by editing various text files and pasting some/all
> of them into psql.  Once in a while I \i a file from psql or cat foo |
> psql.  Only the last method is addressed by using a wrapper script or
> "bash/psql -c"...wrappers aren't really a general solution.   If psql
> could introduce variables into arbitrary sql somehow, I could load a
> bunch of variables in .psqlrc, have constants set up, etc.
>
> For example, when I have a constant (let's say, and advisory lock#) I
> want to define in a plpgsql function, I have a choice between three
> not very pleasant options:
> *) simply hard code the value
> *) look it up every time from a global control table
> *) wrap it in an immutable sql function
>
> I don't care much about the syntax, but it would be absolutely
> wonderful if psql could operate on a function body in some sort of
> regular way before it gets to the backend.  if it solves
> parameterizing 'do' statements, so much the better.

you need C preprocessor or Oracle's packages ;)

Oracle's packages would be better.

Pavel

>
> merlin
>