Обсуждение: Any reasons for 'DO' statement not returning result?
Hi,
References:
I'm just curious about the reasons of the design of 'DO' statement so that it is not able to return result of the SELECT in its body.
References:
With some former experience with MsSQL server, where 'complex' script is executed easily and straightforward without any 'wrapping', like this dummy-one ...
DECLARE @a int;
DECLARE @b int;
...
select @a + @b as "a+b"
... every time I need to execute some one-time-through-away complex code in PostgreSQL which returns rowset I'm disappointed - this has to be wrapped into normal 'temp' function which I have to delete all the time in current session, thus making an anonymous 'DO' statement use-less in 95% of my use-cases.
So ... may someone know good reasons for such inconvenient design of 'DO' statement?
Thanks.
On 8/8/16 7:25 PM, Xtra Coder wrote: > With some former experience with MsSQL server, where 'complex' script is > executed easily and straightforward without any 'wrapping', like this > dummy-one ... > > DECLARE @a int; > DECLARE @b int; > ... > select @a + @b as "a+b" > > ... every time I need to execute some one-time-through-away complex code > in PostgreSQL which returns rowset I'm disappointed - this has to be > wrapped into normal 'temp' function which I have to delete all the time > in current session, thus making an anonymous 'DO' statement use-less in > 95% of my use-cases. > > So ... may someone know good reasons for such inconvenient design of > 'DO' statement? I don't recall why DO was designed that way, but I created http://pgxn.org/dist/pg_lambda/ to do what you're looking for. Unfortunately it's not quite as convenient as DO, and you also must ALWAYS provide at least one correctly typed input (even if it's NULL) so the pseudotype will work. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461
On Mon, Aug 8, 2016 at 7:25 PM, Xtra Coder <xtracoder@gmail.com> wrote: > Hi, > > I'm just curious about the reasons of the design of 'DO' statement so that > it is not able to return result of the SELECT in its body. > > References: > https://www.postgresql.org/docs/current/static/sql-do.html > > http://stackoverflow.com/questions/14652477/how-to-perform-a-select-query-in-a-do-block > > With some former experience with MsSQL server, where 'complex' script is > executed easily and straightforward without any 'wrapping', like this > dummy-one ... > > DECLARE @a int; > DECLARE @b int; > ... > select @a + @b as "a+b" > > ... every time I need to execute some one-time-through-away complex code in > PostgreSQL which returns rowset I'm disappointed - this has to be wrapped > into normal 'temp' function which I have to delete all the time in current > session, thus making an anonymous 'DO' statement use-less in 95% of my > use-cases. > > So ... may someone know good reasons for such inconvenient design of 'DO' > statement? IIRC past discussion concluded DO statements should be allowed to return values. What you (or at least I-) really want though is stored procedures. To me, this means the following: *) Ability to embed collection of statements in the database under a name *) Ability to invoke those statements via CALL <name>, which does not automatically create a transaction and a snapshot (unlike functions/DO) I used to think that we needed to pick a procedural language (for example, pl/pgsql) to leverage the various programming niceties of the database (such as variables and flow control). Today I'm thinking it ought to be vanilla SQL for starters, with some judicious SQL extensions to be hashed out later. merlin
May you have the link to 'DO'-discussion to take a look on it? I was trying to google for something like that, but word 'DO' is too generic to bring useful results :(
In my particular case I'm more interested in an easy way to create complex SELECTs that require usage of variables in the one-time through-away scripts (some-time during experiments for implementation of functions, to see immediate results of the intermediate code). The easiest way would be MsSQL-like when declaring a variable outside of SP actually makes it visible globally in current session. In such case I do not need 'DO' at all and this is simple. Probably PostgreSQL has another way to make that thing simple.
On Fri, Aug 12, 2016 at 1:19 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
On Mon, Aug 8, 2016 at 7:25 PM, Xtra Coder <xtracoder@gmail.com> wrote:
> Hi,
>
> I'm just curious about the reasons of the design of 'DO' statement so that
> it is not able to return result of the SELECT in its body.
>
> References:
> https://www.postgresql.org/docs/current/static/sql-do. html
>
> http://stackoverflow.com/questions/14652477/how-to- perform-a-select-query-in-a- do-block
>
> With some former experience with MsSQL server, where 'complex' script is
> executed easily and straightforward without any 'wrapping', like this
> dummy-one ...
>
> DECLARE @a int;
> DECLARE @b int;
> ...
> select @a + @b as "a+b"
>
> ... every time I need to execute some one-time-through-away complex code in
> PostgreSQL which returns rowset I'm disappointed - this has to be wrapped
> into normal 'temp' function which I have to delete all the time in current
> session, thus making an anonymous 'DO' statement use-less in 95% of my
> use-cases.
>
> So ... may someone know good reasons for such inconvenient design of 'DO'
> statement?
IIRC past discussion concluded DO statements should be allowed to
return values.
What you (or at least I-) really want though is stored procedures. To
me, this means the following:
*) Ability to embed collection of statements in the database under a name
*) Ability to invoke those statements via CALL <name>, which does not
automatically create a transaction and a snapshot (unlike
functions/DO)
I used to think that we needed to pick a procedural language (for
example, pl/pgsql) to leverage the various programming niceties of the
database (such as variables and flow control). Today I'm thinking it
ought to be vanilla SQL for starters, with some judicious SQL
extensions to be hashed out later.
merlin
Xtra Coder wrote: > May you have the link to 'DO'-discussion to take a look on it? I was trying > to google for something like that, but word 'DO' is too generic to bring > useful results :( Probably this is one: https://www.postgresql.org/message-id/51B624C6.306@2ndQuadrant.com -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Fri, Aug 12, 2016 at 11:34 PM, Xtra Coder <xtracoder@gmail.com> wrote: ... > In my particular case I'm more interested in an easy way to create complex > SELECTs that require usage of variables in the one-time through-away scripts > (some-time during experiments for implementation of functions, to see > immediate results of the intermediate code). The easiest way would be > MsSQL-like when declaring a variable outside of SP actually makes it visible > globally in current session. In such case I do not need 'DO' at all and this > is simple. Probably PostgreSQL has another way to make that thing simple. If you are just interested in avoiding some mistakes, and/or parametrizing some queries from the command line, psql ( the CLI program ) has macro expansion with some sql quoting capabilities, see https://www.postgresql.org/docs/9.5/static/app-psql.html#APP-PSQL-VARIABLES and be sure to scroll down to "SQL Interpolation" after the built in variables list and read that. I've used it several times, just remember it's a macro processor and it's done by psql, not by the server. Francisco Olarte.
Thanks for the link. After looking through it i see following major points:
- thread is from 2013 and nothing changed today in 2016
- quote from that thread (C) Dimitri Fontaine
"That topic apparently raises each year and rehash the same points"
(So ... there should be more similar discussions in previous years, i.e. topic was first stated more than 3 years ago)
- most people agree that DO somehow needs to be made 'RETURNING', but there is no consensus how this should be made
And out of that I feel there is no luck with that feature in foreseeable future :(.
If all you want is a temporary function, you *can* create it in the pg_temp namespace though that seems hackish.
Maybe a better solution would be to extend CREATE FUNCTION in a way that allows you to CREATE TEMPORARY FUNCTION?
On Sun, Aug 14, 2016 at 9:28 AM, Xtra Coder <xtracoder@gmail.com> wrote:
Thanks for the link. After looking through it i see following major points:- thread is from 2013 and nothing changed today in 2016- quote from that thread (C) Dimitri Fontaine"That topic apparently raises each year and rehash the same points"(So ... there should be more similar discussions in previous years, i.e. topic was first stated more than 3 years ago)- most people agree that DO somehow needs to be made 'RETURNING', but there is no consensus how this should be madeAnd out of that I feel there is no luck with that feature in foreseeable future :(.
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
Thanks, I'm aware about ability to create temp functions, but this is actually too much overhead - I mean unneeded boilerplate code, but it seems in current state it is "the least evil" which I have to use.
I think 'what i need' is support for following
- ability to switch session language from 'sql' to 'pl/pgsql'
- in that mode - ability to declare session-scope variables, 'DO' is just not needed after that
- SELECTs not targeted into a variable - are written to client output
- (C) Merlin Moncure - "Ability to embed collection of statements in the database under a name and invoke those statements via CALL <name>, which does not automatically create a transaction and a snapshot (unlike functions/DO)"
All this seems to be a huge change which will definitely not appear any time soon.
On Sun, Aug 14, 2016 at 10:42 AM, Chris Travers <chris.travers@gmail.com> wrote:
If all you want is a temporary function, you *can* create it in the pg_temp namespace though that seems hackish.Maybe a better solution would be to extend CREATE FUNCTION in a way that allows you to CREATE TEMPORARY FUNCTION?...--Best Wishes,Chris TraversEfficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
On Sun, Aug 14, 2016 at 12:13 PM, Xtra Coder <xtracoder@gmail.com> wrote:
Thanks, I'm aware about ability to create temp functions, but this is actually too much overhead - I mean unneeded boilerplate code, but it seems in current state it is "the least evil" which I have to use.I think 'what i need' is support for following- ability to switch session language from 'sql' to 'pl/pgsql'- in that mode - ability to declare session-scope variables, 'DO' is just not needed after that- SELECTs not targeted into a variable - are written to client output- (C) Merlin Moncure - "Ability to embed collection of statements in the database under a name and invoke those statements via CALL <name>, which does not automatically create a transaction and a snapshot (unlike functions/DO)"All this seems to be a huge change which will definitely not appear any time soon.
I am willing to bet that DO $$ $$; blocks are neither planned nor parameterized. And the planner needs to know what is to be returned.
So anything you add to make DO work well with the planner will probably end you right back at the same amount of overhead as a temporary function.
On Sun, Aug 14, 2016 at 10:42 AM, Chris Travers <chris.travers@gmail.com> wrote:If all you want is a temporary function, you *can* create it in the pg_temp namespace though that seems hackish.Maybe a better solution would be to extend CREATE FUNCTION in a way that allows you to CREATE TEMPORARY FUNCTION?...--Best Wishes,Chris TraversEfficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
On Sun, Aug 14, 2016 at 5:58 AM, Chris Travers <chris.travers@gmail.com> wrote: > > On Sun, Aug 14, 2016 at 12:13 PM, Xtra Coder <xtracoder@gmail.com> wrote: >> >> Thanks, I'm aware about ability to create temp functions, but this is >> actually too much overhead - I mean unneeded boilerplate code, but it seems >> in current state it is "the least evil" which I have to use. >> >> I think 'what i need' is support for following >> - ability to switch session language from 'sql' to 'pl/pgsql' >> - in that mode - ability to declare session-scope variables, 'DO' is just >> not needed after that >> - SELECTs not targeted into a variable - are written to client output >> - (C) Merlin Moncure - "Ability to embed collection of statements in the >> database under a name and invoke those statements via CALL <name>, which >> does not automatically create a transaction and a snapshot (unlike >> functions/DO)" >> >> All this seems to be a huge change which will definitely not appear any >> time soon. > > I am willing to bet that DO $$ $$; blocks are neither planned nor > parameterized. And the planner needs to know what is to be returned. The statements within a do block are absolutely planned and parameterized. There was some recent discussion with respect to not planning statements except under certain conditions (in a loop basically) to reduce memory consumption of long 'do' blocks. merlin
On 8/14/16 5:13 AM, Xtra Coder wrote: > - ability to switch session language from 'sql' to 'pl/pgsql' Actually, something I wish I had was the ability to temporarily switch to an entirely different interpreter (such as ipython), while still retaining current database connection and context. That would be especially useful for debugging plpython functions. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461
On Tue, Aug 16, 2016 at 3:11 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
On Sun, Aug 14, 2016 at 5:58 AM, Chris Travers <chris.travers@gmail.com> wrote:
>>
>> All this seems to be a huge change which will definitely not appear any
>> time soon.
>
> I am willing to bet that DO $$ $$; blocks are neither planned nor
> parameterized. And the planner needs to know what is to be returned.
The statements within a do block are absolutely planned and
parameterized. There was some recent discussion with respect to not
planning statements except under certain conditions (in a loop
basically) to reduce memory consumption of long 'do' blocks.
Right. Which accounts for why people would expect to return results. But I would bet the block itself is not
I mean when you run a function it is handed off to the language handler, and that is totally planner opaque (I would expect that to be the case here too). That function may make queries, and those may be planned but because the functions can be in arbitrary languages, the planner cannot have internal knowledge of the functions or do blocks.
And since the planner usually needs to know what is returned in order to plan things like sorts, you'd have to be able to do something like:
do language plpgsql returning table (foo int, bar text) as
$$
$$
--- insert logic here.
$$;
But at that point how much are you really saving over
CREATE FUNCTION pg_temp.temptest() returns table (foo int, bar text) language plgsql as $$
-- insert logic here
$$; select pg_temp.temptest();
I am thinking adding a temporary keyword to functions would make a lot more sense.
merlin
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
On 08/16/2016 07:54 AM, Jim Nasby wrote: > On 8/14/16 5:13 AM, Xtra Coder wrote: >> - ability to switch session language from 'sql' to 'pl/pgsql' > > Actually, something I wish I had was the ability to temporarily switch > to an entirely different interpreter (such as ipython), while still > retaining current database connection and context. That would be > especially useful for debugging plpython functions. What aspects of ipython are you interested in? -- Adrian Klaver adrian.klaver@aklaver.com
On 8/16/16 11:17 AM, Chris Travers wrote: > I am thinking adding a temporary keyword to functions would make a lot > more sense. Well, right now that's just syntactic sugar, so I think the only real benefit might be visibility (though, really we should be marketing the idea that you can create almost *any* object in pg_temp!). What would be a lot more interesting is if creating a temp function didn't involve writing an entry to the catalog (something being discussed for temp tables right now). -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461
2016-08-16 21:50 GMT+02:00 Jim Nasby <Jim.Nasby@bluetreble.com>:
On 8/16/16 11:17 AM, Chris Travers wrote:I am thinking adding a temporary keyword to functions would make a lot
more sense.
Well, right now that's just syntactic sugar, so I think the only real benefit might be visibility (though, really we should be marketing the idea that you can create almost *any* object in pg_temp!).
What would be a lot more interesting is if creating a temp function didn't involve writing an entry to the catalog (something being discussed for temp tables right now).
Oracle has interesting temporary function defined inside CTE
Pavel
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 8/16/16 1:05 PM, Adrian Klaver wrote: > On 08/16/2016 07:54 AM, Jim Nasby wrote: >> On 8/14/16 5:13 AM, Xtra Coder wrote: >>> - ability to switch session language from 'sql' to 'pl/pgsql' >> >> Actually, something I wish I had was the ability to temporarily switch >> to an entirely different interpreter (such as ipython), while still >> retaining current database connection and context. That would be >> especially useful for debugging plpython functions. > > What aspects of ipython are you interested in? First, it's not ipython in particular, just a python environment. What would be *really* cool is finding a good way to integrate with Jupyter Notebook... Mostly in being able to deal with plpython code. Right now that's difficult because the plpy object is only exposed inside a plpython function, and it's a bit painful to get pythonic representations of data out of the plpython environment and into a python environment. Outside of that though, there's still useful things that can be done. I think a big part of why people keep asking to add things like IF/THEN to psql is because psql is an incredibly useful tool for handling lots of SQL statements (either DDL or DML). It's much better at that than any other tool I've seen. BUT, there's a limit to what psql or SQL can do. String manipulation (for example) pretty much sucks. python (or perl or ...) are quite good at that stuff though. It's certainly not terribly hard to run a query or two from python. But a dozen? That becomes very tedious very quickly. And if you're trying to do this interactively, it just sucks. (No \d et all, no tab completion, no \h, etc). So what I ultimately wish for is a way to blend these things together as needed. What would be truly amazing is if we had a way to pass a database connection around to different tools. Do a bunch of SQL stuff within psql... now I need to deal with a plpython function; do that in Jupyter... now I'm back to SQL, go back to psql. I wonder how hard it would be to allow psql to expose a local port that other stuff could connect to... -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461
On 08/16/2016 01:15 PM, Jim Nasby wrote: > On 8/16/16 1:05 PM, Adrian Klaver wrote: >> On 08/16/2016 07:54 AM, Jim Nasby wrote: >>> On 8/14/16 5:13 AM, Xtra Coder wrote: >>>> - ability to switch session language from 'sql' to 'pl/pgsql' >>> >>> Actually, something I wish I had was the ability to temporarily switch >>> to an entirely different interpreter (such as ipython), while still >>> retaining current database connection and context. That would be >>> especially useful for debugging plpython functions. >> >> What aspects of ipython are you interested in? > > First, it's not ipython in particular, just a python environment. What > would be *really* cool is finding a good way to integrate with Jupyter > Notebook... > > Mostly in being able to deal with plpython code. Right now that's > difficult because the plpy object is only exposed inside a plpython > function, and it's a bit painful to get pythonic representations of data > out of the plpython environment and into a python environment. > > Outside of that though, there's still useful things that can be done. I > think a big part of why people keep asking to add things like IF/THEN to > psql is because psql is an incredibly useful tool for handling lots of > SQL statements (either DDL or DML). It's much better at that than any > other tool I've seen. BUT, there's a limit to what psql or SQL can do. > String manipulation (for example) pretty much sucks. python (or perl or > ...) are quite good at that stuff though. > > It's certainly not terribly hard to run a query or two from python. But > a dozen? That becomes very tedious very quickly. And if you're trying to > do this interactively, it just sucks. (No \d et all, no tab completion, > no \h, etc). > > So what I ultimately wish for is a way to blend these things together as > needed. What would be truly amazing is if we had a way to pass a > database connection around to different tools. Do a bunch of SQL stuff > within psql... now I need to deal with a plpython function; do that in > Jupyter... now I'm back to SQL, go back to psql. So a mash up of: https://github.com/dbcli/pgcli https://github.com/catherinedevlin/ipython-sql and from Django python manage.py shell > > I wonder how hard it would be to allow psql to expose a local port that > other stuff could connect to... -- Adrian Klaver adrian.klaver@aklaver.com
On 08/16/2016 01:15 PM, Jim Nasby wrote: > On 8/16/16 1:05 PM, Adrian Klaver wrote: >> On 08/16/2016 07:54 AM, Jim Nasby wrote: >>> On 8/14/16 5:13 AM, Xtra Coder wrote: >>>> - ability to switch session language from 'sql' to 'pl/pgsql' >>> >>> Actually, something I wish I had was the ability to temporarily switch >>> to an entirely different interpreter (such as ipython), while still >>> retaining current database connection and context. That would be >>> especially useful for debugging plpython functions. >> >> What aspects of ipython are you interested in? > > First, it's not ipython in particular, just a python environment. What > would be *really* cool is finding a good way to integrate with Jupyter > Notebook... > > Mostly in being able to deal with plpython code. Right now that's > difficult because the plpy object is only exposed inside a plpython > function, and it's a bit painful to get pythonic representations of data > out of the plpython environment and into a python environment. > > Outside of that though, there's still useful things that can be done. I > think a big part of why people keep asking to add things like IF/THEN to > psql is because psql is an incredibly useful tool for handling lots of > SQL statements (either DDL or DML). It's much better at that than any > other tool I've seen. BUT, there's a limit to what psql or SQL can do. > String manipulation (for example) pretty much sucks. python (or perl or > ...) are quite good at that stuff though. > > It's certainly not terribly hard to run a query or two from python. But > a dozen? That becomes very tedious very quickly. And if you're trying to > do this interactively, it just sucks. (No \d et all, no tab completion, > no \h, etc). > > So what I ultimately wish for is a way to blend these things together as > needed. What would be truly amazing is if we had a way to pass a > database connection around to different tools. Do a bunch of SQL stuff > within psql... now I need to deal with a plpython function; do that in > Jupyter... now I'm back to SQL, go back to psql. Re: my previous post Someone has already done it: http://pgcli.com/tag/ipython.html > > I wonder how hard it would be to allow psql to expose a local port that > other stuff could connect to... -- Adrian Klaver adrian.klaver@aklaver.com