Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Adrian Klaver]

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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Adrian Klaver]

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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Jim Nasby]

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Pavel Stehule]

2016-08-16 21:50 GMT+02:00 Jim Nasby :

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

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Jim Nasby]

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Adrian Klaver]

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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Chris Travers]

On Tue, Aug 16, 2016 at 3:11 PM, Merlin Moncure  wrote:

> On Sun, Aug 14, 2016 at 5:58 AM, Chris Travers 
> 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.
http://www.efficito.com/learn_more

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Jim Nasby]

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Merlin Moncure]

On Sun, Aug 14, 2016 at 5:58 AM, Chris Travers  wrote:
>
> On Sun, Aug 14, 2016 at 12:13 PM, Xtra Coder  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 , 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Chris Travers]

On Sun, Aug 14, 2016 at 12:13 PM, Xtra Coder  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 , 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 
> 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 Travers
>>
>> Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
>> lock-in.
>> http://www.efficito.com/learn_more
>>
>
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Xtra Coder]

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 , 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 
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 Travers
>
> Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
> lock-in.
> http://www.efficito.com/learn_more
>

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Chris Travers]

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  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 made
>
> And 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.
http://www.efficito.com/learn_more

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Xtra Coder]

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 :(.

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Francisco Olarte]

On Fri, Aug 12, 2016 at 11:34 PM, Xtra Coder  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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Alvaro Herrera]

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@2ndquadrant.com

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Xtra Coder]

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  wrote:

> On Mon, Aug 8, 2016 at 7:25 PM, Xtra Coder  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 , 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
>

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Merlin Moncure]

On Mon, Aug 8, 2016 at 7:25 PM, Xtra Coder  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 , 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Jim Nasby]

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Any reasons for 'DO' statement not returning result?

[Xtra Coder]

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?

Thanks.