returning multiple result sets from a stored procedure

Excerpts from John Adams's message of jue sep 02 18:25:45 -0400 2010:

I noticed in postgres you cannot return multiple result sets from a stored
procedure (surprisingly as it looks like a very good dbms).

If you're really intent about doing this, you can emulate it by
returning a set of refcursors.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Thu, Sep 2, 2010 at 7:17 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

Excerpts from John Adams's message of jue sep 02 18:25:45 -0400 2010:

I noticed in postgres you cannot return multiple result sets from a stored
procedure (surprisingly as it looks like a very good dbms).

If you're really intent about doing this, you can emulate it by
returning a set of refcursors.

Also arrays can work pretty well, depending on how much data there is
and where it's going.

merlin

I noticed in postgres you cannot return multiple result sets from a
stored procedure (surprisingly as it looks like a very good dbms).

That feature has been on the TODO list for years. However, nobody has
stepped forward to either write it, or to fund working on it. If your
company has programmers or money to build this feature, it could
probably get done fairly quickly (as in, next version).

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

Josh Berkus <josh@agliodbs.com> writes:

I noticed in postgres you cannot return multiple result sets from a
stored procedure (surprisingly as it looks like a very good dbms).

That feature has been on the TODO list for years. However, nobody has
stepped forward to either write it, or to fund working on it. If your
company has programmers or money to build this feature, it could
probably get done fairly quickly (as in, next version).

Part of the reason it's sat on TODO is lack of consensus about how such
a feature ought to look/work; particularly since most of the discussion
about it has considered that it'd go along with stored procedures
executing outside of transactions. It's not just a matter of needing to
find some programming manpower.

regards, tom lane

OT:

OFF TOPIC:
I honestly do not mean any offence, just out of curiosity.
If you guys care about money and time why would you spend the best years of your
life basically copying commercial products for free? Because for a person with
higher than average IQ far less than one percent of any program is creative and
needs some thinking and the bulk of it is just a million stupid details.

I just don't follow/understand your thinking. Maybe I am naïve.
I do not have experience with open source and I kind of thought open source guys
do not need or care about money and time.

John

________________________________
From: Josh Berkus <josh@agliodbs.com>
To: John Adams <john_adams_mail@yahoo.com>
Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Sent: Fri, September 3, 2010 1:07:03 PM
Subject: Re: [HACKERS] returning multiple result sets from a stored procedure

I noticed in postgres you cannot return multiple result sets from a
stored procedure (surprisingly as it looks like a very good dbms).

That feature has been on the TODO list for years. However, nobody has
stepped forward to either write it, or to fund working on it. If your
company has programmers or money to build this feature, it could
probably get done fairly quickly (as in, next version).

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

John,

I honestly do not mean any offence, just out of curiosity.

If you guys care about money and time why would you spend the best years
of your life basically copying commercial products for free?

We don't do it to copy commercial products. We do it to build something
better than them.

I do not have experience with open source and I kind of thought open
source guys do not need or care about money and time.

It's a common misapprehension that open source software is somehow
produced for free. The press has contributed to this myth a great deal
by calling open source "socialism" and "altruism". What's actually true
about open source is that the organization which releases the product
(the open source project) is not necessarily the same organzation which
pays the developers. However, if you look at any mature, large open
source project you will find that at least 1/4 of its code contributors
are paid to work on the project by *someone*, and that those paid
developers account for 70% to 95% of the code. PostgreSQL is no
exception to this rule.

The three differences between an open source project and proprietary
software in terms of adding new features are:

a) it's "pay or play", which means that you have the option of writing
the new feature yourself instead of funding it in cash, and

b) the cost of developing new features if you choose to fund them is
much cheaper (generally a couple orders of magnitude cheaper) than
proprietary software because of the open market for developers and
greater efficiency of OSS development, and

c) it's *much* easier for multiple companies to contribute to the same
project if that project is open source than if it's proprietary.

Ultimately, however, if a feature is going to be added to any OSS
project, that feature is going to be paid for by someone, either in
money, time, or both.

It does help us to get feedback like the feedback you gave eariler, even
if you can't contribute to the project because it helps us prioritize
new features. But you should recognize that if you're not contributing
money or time to the project, you may have a long wait for the feature
*you* want.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On 9/3/10 2:20 PM, Josh Berkus wrote:

However, if you look at any mature, large open
source project you will find that at least 1/4 of its code contributors
are paid to work on the project by *someone*, and that those paid
developers account for 70% to 95% of the code.

Relevant link for this:
http://apcmag.com/linux-now-75-corporate.htm

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Fri, Sep 3, 2010 at 4:07 PM, Josh Berkus <josh@agliodbs.com> wrote:

I noticed in postgres you cannot return multiple result sets from a
stored procedure (surprisingly as it looks like a very good dbms).

That feature has been on the TODO list for years.  However, nobody has
stepped forward to either write it, or to fund working on it.  If your
company has programmers or money to build this feature, it could
probably get done fairly quickly (as in, next version).

Also as mentioned upthread there are effective workarounds if you poke
around a bit. This is a FAQ, and there are about 3-4 solid methods
(if you search the archives) that cover most problems you would be
looking at multiple results sets to solve. I suppose this is why
there hasn't been more of an effort to do this earlier. People asking
for this are typically dispossessed SQL server developers who haven't
quite gotten used to the postgres way of things. Not that proper
stored procedures wouldn't be great -- they would be -- but they are
not the only way to solve these types of problems.

merlin

2010/9/4 Merlin Moncure <mmoncure@gmail.com>:

On Fri, Sep 3, 2010 at 4:07 PM, Josh Berkus <josh@agliodbs.com> wrote:

I noticed in postgres you cannot return multiple result sets from a
stored procedure (surprisingly as it looks like a very good dbms).

That feature has been on the TODO list for years.  However, nobody has
stepped forward to either write it, or to fund working on it.  If your
company has programmers or money to build this feature, it could
probably get done fairly quickly (as in, next version).

Also as mentioned upthread there are effective workarounds if you poke
around a bit.  This is a FAQ, and there are about 3-4 solid methods
(if you search the archives) that cover most problems you would be
looking at multiple results sets to solve.  I suppose this is why
there hasn't been more of an effort to do this earlier.  People asking
for this are typically dispossessed SQL server developers who haven't
quite gotten used to the postgres way of things. Not that proper
stored procedures wouldn't be great -- they would be -- but they are
not the only way to solve these types of problems.

I had a prototype that can do multirecordset. But implementation of
non transact procedures needs a hundreds hours of work:

* outer SPI
* parametrization for non planner statements - for CALL statement
* explicit transaction control for procedures.
* client API support for multirecordset
* better support for OUT variables.

Regards

Pavel Stehule

On Sat, Sep 4, 2010 at 10:10 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Also as mentioned upthread there are effective workarounds if you poke
around a bit.  This is a FAQ, and there are about 3-4 solid methods
(if you search the archives) that cover most problems you would be
looking at multiple results sets to solve.  I suppose this is why
there hasn't been more of an effort to do this earlier.  People asking
for this are typically dispossessed SQL server developers who haven't
quite gotten used to the postgres way of things. Not that proper
stored procedures wouldn't be great -- they would be -- but they are
not the only way to solve these types of problems.

I had a prototype that can do multirecordset. But implementation of
non transact procedures needs a hundreds  hours of work:

* outer SPI
* parametrization for non planner statements - for CALL statement
* explicit transaction control for procedures.
* client API support for multirecordset
* better support for OUT variables.

Curious: is mulitset handling as you see it supported by the current
v3 protocol?

merlin

2010/9/4 Merlin Moncure <mmoncure@gmail.com>:

On Sat, Sep 4, 2010 at 10:10 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Also as mentioned upthread there are effective workarounds if you poke
around a bit.  This is a FAQ, and there are about 3-4 solid methods
(if you search the archives) that cover most problems you would be
looking at multiple results sets to solve.  I suppose this is why
there hasn't been more of an effort to do this earlier.  People asking
for this are typically dispossessed SQL server developers who haven't
quite gotten used to the postgres way of things. Not that proper
stored procedures wouldn't be great -- they would be -- but they are
not the only way to solve these types of problems.

I had a prototype that can do multirecordset. But implementation of
non transact procedures needs a hundreds  hours of work:

* outer SPI
* parametrization for non planner statements - for CALL statement
* explicit transaction control for procedures.
* client API support for multirecordset
* better support for OUT variables.

Curious: is mulitset handling as you see it supported by the current
v3 protocol?

if you see multirecordset as setof cursors, then you don't need
changes. But in my implementation, I did a few changes, if I remember
well, because my implementation wasn't based on "setof" cursors.

Pavel

On 04/09/10 17:16, Merlin Moncure wrote:

Curious: is mulitset handling as you see it supported by the current
v3 protocol?

The manual says:

The response to a SELECT query (or other queries that return row sets, such as EXPLAIN or SHOW) normally consists of RowDescription, zero or more DataRow messages, and then CommandComplete. COPY to or from the frontend invokes special protocol as described in Section 46.2.5. All other query types normally produce only a CommandComplete message.

Since a query string could contain several queries (separated by semicolons), there might be several such response sequences before the backend finishes processing the query string. ReadyForQuery is issued when the entire string has been processed and the backend is ready to accept a new query string.

If a multiple return sets from a procedure are returned just like
multiple return sets from multiple queries, that's already covered by
the protocol.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:

On 04/09/10 17:16, Merlin Moncure wrote:

Curious: is mulitset handling as you see it supported by the current
v3 protocol?

If a multiple return sets from a procedure are returned just like
multiple return sets from multiple queries, that's already covered by
the protocol.

Well, the protocol says you can do it, but it would likely require
significant work to make clients deal with it sanely.

Also, the part of the protocol document Heikki is quoting is for the
legacy "simple query" mode. We deliberately designed this behavior
*out* of the extended query mode. So for example you couldn't use
out-of-line parameters with such a feature, unless there's a protocol
redesign.

regards, tom lane

On fre, 2010-09-03 at 16:18 -0400, Tom Lane wrote:

Part of the reason it's sat on TODO is lack of consensus about how
such a feature ought to look/work; particularly since most of the
discussion about it has considered that it'd go along with stored
procedures executing outside of transactions.

I would probably be a mistake to tie these features together. They are
tricky enough separately.

On 9/5/2010 2:05 PM, Heikki Linnakangas wrote:

On 04/09/10 17:16, Merlin Moncure wrote:

Curious: is mulitset handling as you see it supported by the current
v3 protocol?

The manual says:

The response to a SELECT query (or other queries that return row sets, such as
EXPLAIN or SHOW) normally consists of RowDescription, zero or more DataRow
messages, and then CommandComplete. COPY to or from the frontend invokes
special protocol as described in Section 46.2.5. All other query types
normally produce only a CommandComplete message.

Since a query string could contain several queries (separated by semicolons),
there might be several such response sequences before the backend finishes
processing the query string. ReadyForQuery is issued when the entire string
has been processed and the backend is ready to accept a new query string.

If a multiple return sets from a procedure are returned just like multiple
return sets from multiple queries, that's already covered by the protocol.

Just as a side note, libpqtypes can emulate this using composite arrays; a
feature we abuse internally. It is actually the primary justification we had
for developing that portion of libpqtypes; initially we stayed clear of arrays
and composites.

create table fork_t (fork_id, rev_id, size, block_ids int8[], ...)
create table rev_t (rev_id, blah, blah, fork_t[]);

/* this is my favorite part of libpqtypes */
PGarray arr;
PQgetf(result, tup_num, "%rev_t[]", field_num, &arr);

Now loop the array "arr" and getf(arr.res) for each rev_t, which allows you to
getf each fork_t in the fork_t[], etc....

I *know* it is not pure multiset'n, but it sure gets the job done (in a
completely different way, I know). However, I'm sure those reading this list
can see the possiblities ;)

Andrew Chernow
eSilo, LLC.

2010/9/3 John Adams <john_adams_mail@yahoo.com>:

OT:

OFF TOPIC:

I honestly do not mean any offence, just out of curiosity.

If you guys care about money and time why would you spend the best years of
your life basically copying commercial products for free? Because for a
person with higher than average IQ far less than one percent of any program
is creative and needs some thinking and the bulk of it is just a million
stupid details.

I just don't follow/understand your thinking. Maybe I am naïve.

I do not have experience with open source and I kind of thought open source
guys do not need or care about money and time.

The work on PostgreSQL is adventure, and very good experience, very
good school for me. It's job only for people who like programming, who
like hacking, it isn't job for people, who go to office on 8 hours.
Next I use PostgreSQL for my job - and hacking on PostgreSQL put me a
perfect knowledge, perfect contacts to developers, and I can work
together with best programmers on planet. and I can create some good
things. Probably if I work on commercial projects I can have a better
money - but life is only one, and money is important, but not on top
for me - life have to be adventure!

Regards

Pavel Stehule

On Sep 6, 2010, at 12:07 AM, Pavel Stehule wrote:

The work on PostgreSQL is adventure, and very good experience, very
good school for me. It's job only for people who like programming, who
like hacking, it isn't job for people, who go to office on 8 hours.
Next I use PostgreSQL for my job - and hacking on PostgreSQL put me a
perfect knowledge, perfect contacts to developers, and I can work
together with best programmers on planet. and I can create some good
things. Probably if I work on commercial projects I can have a better
money - but life is only one, and money is important, but not on top
for me - life have to be adventure!

Could not have said it better myself.

Best,

David

On Fri, Sep 03, 2010 at 01:40:56PM -0700, John Adams wrote:

OT:

OFF TOPIC:
I honestly do not mean any offence, just out of curiosity.
If you guys care about money and time why would you spend the best
years of your life basically copying commercial products for free?
Because for a person with higher than average IQ far less than one
percent of any program is creative and needs some thinking and the
bulk of it is just a million stupid details.

It's difficult to answer a question when there are so many different
wrong assumptions that underlie it. I'll take pieces of the
questions, explicitly state the assumptions that underlie them, and
explain what I mean by "wrong."

"If you guys care about money"

Here you're assuming that open source code development on large
projects like PostgreSQL is done in people's spare time. In
reality, 80-95% of such development is done by people who are paid
by their workplace to do so. In the case of PostgreSQL
developers, this pay is at least comfortable, so your assumption
that this is done uncompensated, in terms of money, is simply
wrong.

For those who do development and are not directly compensated by
their employer for doing so, there are other monetary rewards,
such as being able to put such projects on r�sum�s/CVs, which in
turn results in better job prospects, consulting fees for
specialized knowledge, etc., etc.

"and time why would you spend the best years of your life"

That time's compensated, in many different ways, as illustrated
above. Perhaps your life is in such desperate straits that you
can devote time to nothing but acquiring money. If this is true,
I feel very sorry for you. I feel even sorrier for you if you are
not in such desperate straits, but you are nevertheless devoting
every waking hour to the pursuit of money. It's a sad and lonely
way to waste your precious days of life.

"basically copying"

In a technological sense, FLOSS often leads the way and "products"
catch up later if at all. FLOSS technologies are frequently so
much better than their proprietary counterparts that they kill
existing markets (C compilers, e.g.), and cause markets in other
technologies (dynamic languages, e.g.) never to form.

"commercial products for free?"

There's a lot of confusion about this word. "Commercial" means
"of or pertaining to commerce." It has nothing to do with whether
the license is permissive like PostgreSQL's or extremely
restrictive as it is with, say the Windows EULA. In future, if
you wish to contrast licenses, it's free vs. proprietary, and if
you wish to contrast usage, it's hobby vs. commerce vs. science,
roughly speaking.

"Because for a person with higher than average IQ far less than one
percent of any program is creative and needs some thinking and the
bulk of it is just a million stupid details."

The difference between imagining something and actually
accomplishing it is precisely those "million stupid details."

The truly rewarding thing isn't dreaming up some wonderful dream.
That's easy. The truly rewarding thing is in bringing that dream
from a lonely and ethereal state to one that's shared and
concrete, where it can in turn help spawn new dreams, which people
then realize and share, and on and on and on.

I just don't follow/understand your thinking. Maybe I am na�ve.

You're that, clearly, along with being misinformed, young, and
arrogant.

Fortunately, all of these things but youth are fixable if you decide
to do the work to fix them, and by the time you've done that work,
your youth will also be waning ;)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Fri, Sep 3, 2010 at 4:40 PM, John Adams <john_adams_mail@yahoo.com> wrote:

If you guys care about money and time why would you spend the best years of
your life basically copying commercial products for free?

I don't work for free. :-)

There was a point at which this was just a hobby for me, but as it has
since turned into a job, it's hard for me to say that the time I spent
on it had no economic value. But it is also true that it was a great
hobby. Working on PostgreSQL gave me an opportunity to work with some
absolutely brilliant programmers, which is not something I've
frequently gotten a chance to do in the course of my previous
employment. And it's also fun to feel like you're contributing
something back to a project that you've gotten so much out of.

With respect to copying commerical products, we may be doing that to
some extent, but it's not because we're sitting around going "oh, so
what has Oracle done lately?". We tend to think about what PostgreSQL
needs and work on that. Sometimes there's overlap, other times not.

Because for a
person with higher than average IQ far less than one percent of any program
is creative and needs some thinking and the bulk of it is just a million
stupid details.

I haven't written a program that matched this expectation since I was
in high school. And I think that was only because I wasn't as good a
programmer then as I thought I was. My experience is that most
programming requires a lot of careful thought and good design, and
that doing this well is not easy. This is doubly true for a large,
complex, and mature project like PostgreSQL, where changes need to be
exceedingly carefully thought out.

I just don't follow/understand your thinking. Maybe I am naïve.

I do not have experience with open source and I kind of thought open source
guys do not need or care about money and time.

I try not to make money the center of my life, but I like to get paid
as much as the next guy. Many of the regulars here derive a
substantial portion of their income from PostgreSQL-related work of
one kind or another. Even when my PostgreSQL development was a hobby,
a big part of my job revolved around developing FOR PostgreSQL.
Filing down some of the rough edges I encountered during that
development was one of the things that drew me to the project (the
other being the aforementioned really smart people).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company