Re: Proposal: USING clause for DO statement

Hello,

I am still thinking, about using DO statement from psql console. I am
missing any parametrisation. It could not be a problem. All pl have a
support for parameters, we have a libpq function PQexecParams - so we
need only some "USING" clause. I propose following syntax (and using
from client environments)

DO $$ body $$ USING expr [ ,expr [...]];

body should to have a unnamed parameters in syntax related to any PL.
I'll use plpgsql for this moment.

so some the most simply sample should look like:

DO $$
BEGIN
RAISE NOTICE 'Hello, %', $1;
END
$$ USING 'World';

>From psql:

\set message World
DO $$BEGIN RAISE NOTICE 'Hello, %', $1; END$$ USING :message;

>From pgscript:

SET @message = 'World';
DO $$BEGIN RAISE NOTICE 'Hello, %', $1; END$$ USING @message;

>From C

values[0] = "World";
result = PQexecParams(cn, "DO $$BEGIN RAISE NOTICE 'Hello, %', $1;
END;$$ USING $1",
1,
NULL, values,
NULL, NULL,
0);

What do you thing about this proposal?

Regards
Pavel Stehule

2009/11/21 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> Hello,
>
> I am still thinking, about using DO statement from psql console. I am
> missing any parametrisation. It could not be a problem. All pl have a
> support for parameters, we have a libpq function PQexecParams - so we
> need only some "USING" clause. I propose following syntax (and using
> from client environments)
>
> DO $$ body $$ USING expr [ ,expr [...]];
>
> body should to have a unnamed parameters in syntax related to any PL.
> I'll use plpgsql for this moment.
>
> so some the most simply sample should look like:
>
> DO $$
> BEGIN
>  RAISE NOTICE 'Hello, %', $1;
> END
> $$ USING 'World';
>
> From psql:
>
> \set message World
> DO $$BEGIN RAISE NOTICE 'Hello, %', $1; END$$ USING :message;
>
> From pgscript:
>
> SET @message = 'World';
> DO $$BEGIN RAISE NOTICE 'Hello, %', $1; END$$ USING @message;
>
> From C
>
> values[0] = "World";
> result = PQexecParams(cn, "DO $$BEGIN RAISE NOTICE 'Hello, %', $1;
> END;$$ USING $1",
>                                                 1,
>                                                  NULL, values,
>                                                  NULL, NULL,
>                                                  0);
>

last note - type of parameter is taken from a value. When value will
be NULL, then type will be unknown.

Pavel

> What do you thing about this proposal?
>
> Regards
> Pavel Stehule
>

Pavel Stehule wrote:
> What do you thing about this proposal?
>
>
>

I think it's premature. Before we start adding bells and whistles to the
feature, let's give it a turn in the field.

One possible problem: what type would these anonymous params be? (And,
BTW, don't kid yourself that there would not very soon be pressure to
name them).

cheers

andrew

2009/11/21 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
>
> Pavel Stehule wrote:
>>
>> What do you thing about this proposal?
>>
>>
>>
>
> I think it's premature. Before we start adding bells and whistles to the
> feature, let's give it a turn in the field.

why? It thing so not. My opinion - it is incomplete. It has similar
function like function without parameters now. It's good for some
static task (single-use). But it isn't practical for using in shell
scripts. Do you have a other mechanism for parametrisation? I would to
use it from bash - and I need some mechanism for passing an parameter
from command line to DO statement. I

>
> One possible problem: what type would these anonymous params be? (And, BTW,
> don't kid yourself that there would not very soon be pressure to name them).
>

It is solved long time - without specification, any parameter is
'unknown text'. It is based on PQexecParam functionality. In this
case, the situation is simpler - we know value - it same as EXECUTE
statement in plpgsql - type is defined by call. So we knows params (it
is supported by protocol), PL languages support params. So it needs
only some relation. It's need a few lines more in executor (copy a
four pointers and one int) and some in PL (local variable declaring
and copy values) + 5 lines in gram.y

Pavel

p.s. Maybe it is premature - We had to live with EXECUTE (without
USING clause) twelve years. But an life should be comfortable. I don't
would to wait twelve years :)

> cheers
>
> andrew
>

Pavel Stehule wrote:
> p.s. Maybe it is premature - We had to live with EXECUTE (without
> USING clause) twelve years. But an life should be comfortable. I don't
> would to wait twelve years :)
>

I think you should take heed of Tom's words:

> I think adding onto DO capabilities is something we could do later
> if demand warrants. I'd prefer to underdesign it for starters than
> to encrust it with features that might not be needed.

That doesn't mean waiting 12 years, but it does mean taking it a step at
a time. This feature hasn't even had one release in the field yet.

Perhaps part of the problem is that psql can't interpolate its variable
into strings. Solving that might lessen the impetus for this, and have
other uses besides.

cheers

andrew

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> 2009/11/21 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>> One possible problem: what type would these anonymous params be?

> It is solved long time - without specification, any parameter is
> 'unknown text'.

Nonsense.

We do have the ability to infer parameter types when parsing a SQL
statement. That does not extend to any random PL being able to do it.
In fact, NONE of them can do it, not even plpgsql. They all expect
incoming parameter types to be predetermined.

Without types *and* names, there is no point in considering parameters.
And the problem with that, which is why we didn't put parameters into
DO in the first place, is that it raises the minimum notational bar
quite a lot. You might as well go ahead and define a temporary
function.

regards, tom lane

2009/11/21 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2009/11/21 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>>> One possible problem: what type would these anonymous params be?
>
>> It is solved long time - without specification, any parameter is
>> 'unknown text'.
>
> Nonsense.
>

ok.

> We do have the ability to infer parameter types when parsing a SQL
> statement. That does not extend to any random PL being able to do it.
> In fact, NONE of them can do it, not even plpgsql.  They all expect
> incoming parameter types to be predetermined.
>

When we use DO statement from clients without possibility to specify
type, an usedr have to specify type with explicit casting. Inside DO
statement or in USING clause. Then any outer value has type or is
unknown. When is unknown - then any usage needs explicit casting
inside DO body. When has type - there are no problem. But we will have
a way for passing a parameters.

an sample should be

DO $$ .... $$ USING psqlvar::text, psqlothervar numeric;

I don't expect typed variables inside psql. It's not impossible, but
why? There are pgScript and DO statement. Theoretically we can
identify the most common type for untyped variable in compile stage of
any PL. It have to be a similar mechanism like now - with existing
hooks it could not be a problem, but I don't thing it is necessary
(for DO stament - functions are different, because are registered in
moment when some related objects doesn't exist). Explicit casting in
USING clause is enough. It is safe and simply. Explicit casting is
less work then some bash regxep substitution alchemy.

> Without types *and* names, there is no point in considering parameters.
> And the problem with that, which is why we didn't put parameters into
> DO in the first place, is that it raises the minimum notational bar
> quite a lot.  You might as well go ahead and define a temporary
> function.
>

I understand. It's reason why I don't propose named parameters.

p.s. I don't see sense of temporary functions, when we have a
anonymous block. All temporary objects are problematic - I have not a
reason, thing about temporary functions some else.

regards
Pavel Stehule

>                        regards, tom lane
>

I don't see point in having parameters for DO as an utility command. If
you need to reuse some value you can define those variables at the
beginning of code block in the language itself (in DECLARE section in
plpgsql for example), defining them in outer SQL command does not really
help anything. Now, if/when we add support to put DO inside standard sql
query, it would be vastly more useful to be able to use parameters. But
adding that support will be probably least of our problems if we try to
do that, and we might even want to use different syntax/behavior then,
so I would really not rush with this.

--
Regards
Petr Jelinek (PJMODOS)

2009/11/21 Petr Jelinek <pjmodos(at)pjmodos(dot)net>:
> I don't see point in having parameters for DO as an utility command. If you
> need to reuse some value you can define those variables at the beginning of
> code block in the language itself (in DECLARE section in plpgsql for
> example), defining them in outer SQL command does not really help anything.

no - this isn't a problem. Current design of DO statement allows only
single-use using. I cannot to wrap "do" statement. I would to use this
statement for some non trivial maintenance tasks - and I would to use
external parameter (from command line). My question is - what is a
good way for passing some value (from command line) to DO statement
body? I accept any mechanism.

> Now, if/when we add support to put DO inside standard sql query, it would be
> vastly more useful to be able to use parameters. But adding that support
> will be probably least of our problems if we try to do that, and we might
> even want to use different syntax/behavior then, so I would really not rush
> with this.

It doesn't need too much, Petr - I thing, so all necessary is done.
And this proposal doesn't do incompatible changes in syntax. It add
only new optional clause.

>
> --
> Regards
> Petr Jelinek (PJMODOS)
>
>

Pavel Stehule wrote:
> I would to use this
> statement for some non trivial maintenance tasks - and I would to use
> external parameter (from command line). My question is - what is a
> good way for passing some value (from command line) to DO statement
> body? I accept any mechanism.
>
>

See my earlier comment:

> Perhaps part of the problem is that psql can't interpolate its
> variable into strings. Solving that might lessen the impetus for this,
> and have other uses besides.

cheers

andrew

On Sat, Nov 21, 2009 at 12:36 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>> Perhaps part of the problem is that psql can't interpolate its variable
>> into strings. Solving that might lessen the impetus for this, and have other
>> uses besides.

+1!

This would have a _lot_ of uses.

merlin

2009/11/21 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> On Sat, Nov 21, 2009 at 12:36 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>> Perhaps part of the problem is that psql can't interpolate its variable
>>> into strings. Solving that might lessen the impetus for this, and have other
>>> uses besides.
>
> +1!
>
> This would have a _lot_ of uses.
>

I am not sure, if I understand well. Can you show some use cases, please?

Pavel

> merlin
>

On Sat, Nov 21, 2009 at 1:24 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2009/11/21 Merlin Moncure <mmoncure(at)gmail(dot)com>:
>> On Sat, Nov 21, 2009 at 12:36 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>>> Perhaps part of the problem is that psql can't interpolate its variable
>>>> into strings. Solving that might lessen the impetus for this, and have other
>>>> uses besides.
> I am not sure, if I understand well. Can you show some use cases, please?

If I understand Andrew correctly,

\set msg world

DO $$
BEGIN
RAISE NOTICE 'Hello, %', :msg;
END
$$;

2009/11/21 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> On Sat, Nov 21, 2009 at 1:24 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2009/11/21 Merlin Moncure <mmoncure(at)gmail(dot)com>:
>>> On Sat, Nov 21, 2009 at 12:36 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>>>> Perhaps part of the problem is that psql can't interpolate its variable
>>>>> into strings. Solving that might lessen the impetus for this, and have other
>>>>> uses besides.
>> I am not sure, if I understand well. Can you show some use cases, please?
>
> If I understand Andrew correctly,
>
> \set msg world
>
> DO $$
> BEGIN
>  RAISE NOTICE 'Hello, %', :msg;
> END
> $$;
>

This is base of my proposal :). But we cannot do it directly:

a) the body of DO statement is black box for psql parser,
b) psql does know nothing about used PL language - without this
knowledge cannot do correct substitution: plpgsq use ', java use "",
perl use ""

these points are reason, why I propose "external USING clause" - it
outside blackbox, it is common for all PL

Point b is solved via using a real params - not substitution.

Regards
Pavel

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> See my earlier comment:
>> Perhaps part of the problem is that psql can't interpolate its
>> variable into strings. Solving that might lessen the impetus for this,
>> and have other uses besides.

It seems to me that this is sliding down the wrong slope. You're
basically assuming that psql itself is or should be a programming
language. It's not. The variable mechanism is an enormous kluge
with limited flexibility caused by a horrid syntax; and psql hasn't
got any control structures at all.

I think Petr was on the right track. What people really want is not
psql scripts but plpgsql scripts. DO effectively gives that to them,
with a few characters' worth of overhead. The problem they have to
solve is to interpolate actual-parameter values into such a script;
but it's not clear to me that that's noticeably harder than getting
such values into a psql script. I foresee people doing things like

psql -c 'DO $$'"declare x int := $SHELLVARIABLE; ... "'$$;' ...

The fact that $ is special to the shell as well as to DO is
kind of a PITA here, but it's not that hard to work around.

The main limitation of this type of approach is that it's hard to
properly quote a variable value that might contain any random character
sequence. However, that's also true of the variable-interpolation stuff
Pavel was proposing. In any case I don't think that "getting stuff from
psql variables into a DO script" is the way to define the problem.
It's "getting stuff from shell variables into a DO script" that is the
real-world problem. Maybe psql is the wrong tool altogether.

regards, tom lane

>
> The main limitation of this type of approach is that it's hard to
> properly quote a variable value that might contain any random character
> sequence.  However, that's also true of the variable-interpolation stuff
> Pavel was proposing.  In any case I don't think that "getting stuff from
> psql variables into a DO script" is the way to define the problem.
> It's "getting stuff from shell variables into a DO script" that is the
> real-world problem.

I am probably out, Tom

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

> Maybe psql is the wrong tool altogether.

why - psql is very good tool. I am able to do all what I need - but
sometimes I have to use shell expansion - it's need quoting, and the
code isn't much readable. With parameters we can to separate code from
values - and an code should very clean.

Pavel

>
>                        regards, tom lane
>

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.

regards, tom lane

Tom Lane wrote:
> In any case I don't think that "getting stuff from
> psql variables into a DO script" is the way to define the problem.
> It's "getting stuff from shell variables into a DO script" that is the
> real-world problem.

Indeed. But setting psql variables from the command line is easy. We
have a nifty switch for it. So we could reduce one problem to the other.

> Maybe psql is the wrong tool altogether.
>
>
>

Yeah. The workaround is to use some sort of wrapper, in shell script,
perl or whatever. Maybe we should just let sleeping dogs lie.

I think you and I are in agreement though that an SQL level mechanism is
not the way to go, at least for now.

cheers

andrew

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.

good night
Pavel

>
>                        regards, tom lane
>

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.

merlin

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
>