Обсуждение: 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.stehule@gmail.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@dunslane.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.stehule@gmail.com> writes: > 2009/11/21 Andrew Dunstan <andrew@dunslane.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@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> 2009/11/21 Andrew Dunstan <andrew@dunslane.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@pjmodos.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@dunslane.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@gmail.com>: > On Sat, Nov 21, 2009 at 12:36 PM, Andrew Dunstan <andrew@dunslane.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.stehule@gmail.com> wrote: > 2009/11/21 Merlin Moncure <mmoncure@gmail.com>: >> On Sat, Nov 21, 2009 at 12:36 PM, Andrew Dunstan <andrew@dunslane.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 $$ BEGINRAISE NOTICE 'Hello, %', :msg; END $$;
2009/11/21 Merlin Moncure <mmoncure@gmail.com>: > On Sat, Nov 21, 2009 at 1:24 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> 2009/11/21 Merlin Moncure <mmoncure@gmail.com>: >>> On Sat, Nov 21, 2009 at 12:36 PM, Andrew Dunstan <andrew@dunslane.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@dunslane.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.stehule@gmail.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@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.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.stehule@gmail.com> wrote: > 2009/11/21 Tom Lane <tgl@sss.pgh.pa.us>: >> Pavel Stehule <pavel.stehule@gmail.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@gmail.com>: > On Sat, Nov 21, 2009 at 3:32 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> 2009/11/21 Tom Lane <tgl@sss.pgh.pa.us>: >>> Pavel Stehule <pavel.stehule@gmail.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 >