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

I would like to suggest adding this feature.
- It is very usefull
- It is supported by all other dbmss I have worked with.
- makes porting applications to postgres very difficult (we have used this
feature in our stored procedures and now there is no easy way of porting to
postgres)
Thanks and we are waiting

Search Discussions

  • Alvaro Herrera at Sep 2, 2010 at 11:17 pm

    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
  • Merlin Moncure at Sep 3, 2010 at 7:27 pm

    On Thu, Sep 2, 2010 at 7:17 PM, Alvaro Herrera 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
  • Josh Berkus at Sep 3, 2010 at 8:07 pm

    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
  • Tom Lane at Sep 3, 2010 at 8:19 pm

    Josh Berkus 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
  • Peter Eisentraut at Sep 5, 2010 at 6:26 pm

    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.
  • Merlin Moncure at Sep 7, 2010 at 12:27 pm

    On Sun, Sep 5, 2010 at 2:26 PM, Peter Eisentraut wrote:
    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.
    Hm, do you think it would be possible to request manual transaction
    state when setting up the procedure (or reserve that ability for the
    future)?

    merlin
  • John Adams at Sep 3, 2010 at 8:41 pm
    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
  • Josh Berkus at Sep 3, 2010 at 9:20 pm
    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
  • Josh Berkus at Sep 3, 2010 at 9:24 pm

    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
  • Pavel Stehule at Sep 6, 2010 at 7:08 am

    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
    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
  • David E. Wheeler at Sep 6, 2010 at 6:07 pm

    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
  • David Fetter at Sep 6, 2010 at 9:40 pm

    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
  • Robert Haas at Sep 6, 2010 at 9:59 pm

    On Fri, Sep 3, 2010 at 4:40 PM, John Adams 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
  • Merlin Moncure at Sep 4, 2010 at 1:39 pm

    On Fri, Sep 3, 2010 at 4:07 PM, Josh Berkus 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
  • Pavel Stehule at Sep 4, 2010 at 2:11 pm

    2010/9/4 Merlin Moncure <mmoncure@gmail.com>:
    On Fri, Sep 3, 2010 at 4:07 PM, Josh Berkus 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
    merlin

    --
    Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers
  • Merlin Moncure at Sep 4, 2010 at 2:16 pm

    On Sat, Sep 4, 2010 at 10:10 AM, Pavel Stehule 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
  • Pavel Stehule at Sep 4, 2010 at 3:01 pm

    2010/9/4 Merlin Moncure <mmoncure@gmail.com>:
    On Sat, Sep 4, 2010 at 10:10 AM, Pavel Stehule 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
    merlin
  • Heikki Linnakangas at Sep 5, 2010 at 6:08 pm

    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
  • Tom Lane at Sep 5, 2010 at 6:18 pm

    Heikki Linnakangas 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
  • Andrew Chernow at Sep 6, 2010 at 1:00 am

    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.
  • John Adams at Sep 8, 2010 at 11:44 pm
    OK, how much are we talking about?
    ________________________________
    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
  • Pavel Stehule at Sep 9, 2010 at 7:13 am
    Hello

    2010/9/9 John Adams <john_adams_mail@yahoo.com>:
    OK, how much are we talking about?
    about 2 months for full time and 2 months for partial time - is my tip

    depends on set of implemented features

    regards

    Pavel Stehule
    ________________________________
    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






    --
    Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers
  • David E. Wheeler at Sep 9, 2010 at 3:58 pm

    On Sep 9, 2010, at 12:12 AM, Pavel Stehule wrote:

    about 2 months for full time and 2 months for partial time - is my tip
    Two months full or two months partial? I'll take the latter, please!

    David
  • Pavel Stehule at Sep 9, 2010 at 5:14 pm

    2010/9/9 David E. Wheeler <david@kineticode.com>:
    On Sep 9, 2010, at 12:12 AM, Pavel Stehule wrote:

    about 2 months for full time and 2 months for partial time - is my tip
    Two months full or two months partial? I'll take the latter, please!
    2 months - basic implementation
    1 months - cleaning and work on commiting
    ---- sum - 3 month ----

    Regards

    Pavel

    p.s. I am working on basic syntax - CALL and OUT variables. But I'll
    not try to implement a transaction related features.

    Regards

    Pavel Stehule

    Two months full for basic implementation and two months partial
    David
  • Tom Lane at Sep 9, 2010 at 5:51 pm

    Pavel Stehule writes:
    2010/9/9 David E. Wheeler <david@kineticode.com>:
    On Sep 9, 2010, at 12:12 AM, Pavel Stehule wrote:
    about 2 months for full time and 2 months for partial time - is my tip
    Two months full or two months partial? I'll take the latter, please!
    2 months - basic implementation
    1 months - cleaning and work on commiting
    ---- sum - 3 month ----
    And zero time spent on convincing -hackers that the design is good?
    Not likely to get committed that way.

    regards, tom lane
  • Pavel Stehule at Sep 9, 2010 at 5:56 pm

    2010/9/9 Tom Lane <tgl@sss.pgh.pa.us>:
    Pavel Stehule <pavel.stehule@gmail.com> writes:
    2010/9/9 David E. Wheeler <david@kineticode.com>:
    On Sep 9, 2010, at 12:12 AM, Pavel Stehule wrote:
    about 2 months for full time and 2 months for partial time - is my tip
    Two months full or two months partial? I'll take the latter, please!
    2 months - basic implementation
    1 months - cleaning and work on commiting
    ---- sum - 3 month ----
    And zero time spent on convincing -hackers that the design is good?
    Not likely to get committed that way.
    then I have to add 6 months more :)

    regards

    Pavel Stehule
    regards, tom lane
  • Pavel Stehule at Sep 9, 2010 at 6:30 pm

    2010/9/9 Tom Lane <tgl@sss.pgh.pa.us>:
    Pavel Stehule <pavel.stehule@gmail.com> writes:
    2010/9/9 David E. Wheeler <david@kineticode.com>:
    On Sep 9, 2010, at 12:12 AM, Pavel Stehule wrote:
    about 2 months for full time and 2 months for partial time - is my tip
    Two months full or two months partial? I'll take the latter, please!
    2 months - basic implementation
    1 months - cleaning and work on commiting
    ---- sum - 3 month ----
    And zero time spent on convincing -hackers that the design is good?
    Not likely to get committed that way.
    there are lot of questions - and I am not sure if procedures
    implementation can be done in one release cycle. The basic questions:

    * should be special catalog for procedures or we will use pg_proc?
    * how can be implemented OUT variables - the original implementation
    is simple - it's just pointer, but it's not directly possible inside
    postgres, because we use a MemoryContexts?
    * how can be implement a CALL statement - as plan statement or as command?
    * how can be implemented variables inside psql console, if we allows them?
    * how can be implement an overloading of procedures - can we use for
    selection OUT variables too?
    * what is procedure? It's like void function, or it can return status
    code like procedures in SQL/PSM (DB2)?

    --- As long years a stored procedures developer, I can say, so just
    minimal implementation of procedures can help with writing little bit
    more readable code for functions that return more then one scalar
    result. But other features can be nice too - explicit transaction
    control and unbind selects. But these features are killing gun.

    Regards

    Pavel Stehule

    regards, tom lane
  • Alvaro Herrera at Sep 9, 2010 at 6:37 pm

    Excerpts from Pavel Stehule's message of jue sep 09 14:29:57 -0400 2010:

    * how can be implemented OUT variables - the original implementation
    is simple - it's just pointer, but it's not directly possible inside
    postgres, because we use a MemoryContexts?
    Why is this a problem? Just return a bunch of tuplestores, no?

    --
    Álvaro Herrera <alvherre@commandprompt.com>
    The PostgreSQL Company - Command Prompt, Inc.
    PostgreSQL Replication, Consulting, Custom Development, 24x7 support
  • Pavel Stehule at Sep 9, 2010 at 6:44 pm

    2010/9/9 Alvaro Herrera <alvherre@commandprompt.com>:
    Excerpts from Pavel Stehule's message of jue sep 09 14:29:57 -0400 2010:
    * how can be implemented OUT variables - the original implementation
    is simple - it's just pointer, but it's not directly possible inside
    postgres, because we use a MemoryContexts?
    Why is this a problem?  Just return a bunch of tuplestores, no?
    and what context do you use? And you will pack and unpack tuple when
    some field will be changed every time?

    this is an possibility to solve our performance problems with arrays
    or strings.

    Regards

    Pavel Stehule
    --
    Álvaro Herrera <alvherre@commandprompt.com>
    The PostgreSQL Company - Command Prompt, Inc.
    PostgreSQL Replication, Consulting, Custom Development, 24x7 support
  • Darren Duncan at Sep 9, 2010 at 8:08 pm

    Pavel Stehule wrote:
    there are lot of questions - and I am not sure if procedures
    implementation can be done in one release cycle. The basic questions:

    * should be special catalog for procedures or we will use pg_proc?
    * how can be implemented OUT variables - the original implementation
    is simple - it's just pointer, but it's not directly possible inside
    postgres, because we use a MemoryContexts?
    * how can be implement a CALL statement - as plan statement or as command?
    * how can be implemented variables inside psql console, if we allows them?
    * how can be implement an overloading of procedures - can we use for
    selection OUT variables too?
    * what is procedure? It's like void function, or it can return status
    code like procedures in SQL/PSM (DB2)?

    --- As long years a stored procedures developer, I can say, so just
    minimal implementation of procedures can help with writing little bit
    more readable code for functions that return more then one scalar
    result. But other features can be nice too - explicit transaction
    control and unbind selects. But these features are killing gun.
    I've often considered that the main distinction between a function and a
    procedure is that the former is intended to be invoked as a value-resulting
    expression while the latter is intended to be invoked as a non-value-resulting
    statement. The SQL standard uses separate FUNCTION and PROCEDURE for these.

    Since Pg's FUNCTION already seems to take on both roles, so overloading the
    meaning of the FUNCTION keyword, like what a C function or a Perl sub does,
    where returning VOID means procedure, then what is being added by a distinct
    PROCEDURE? Or is the VOID-returning FUNCTION going to be deprecated or
    discouraged at the same time?

    -- Darren Duncan
  • Tom Lane at Sep 9, 2010 at 8:16 pm

    Darren Duncan writes:
    Since Pg's FUNCTION already seems to take on both roles, so overloading the
    meaning of the FUNCTION keyword, like what a C function or a Perl sub does,
    where returning VOID means procedure, then what is being added by a distinct
    PROCEDURE?
    You might care to go back and re-read some of the extensive prior
    threads about this, but to my mind the main thing that would justify
    inventing a separate PROCEDURE facility is if procedures were to execute
    outside the transaction system, so that they could start and stop
    transactions for themselves. This is unlike a function which
    necessarily executes inside an already-running transaction. Of course
    a lot of questions would need to be answered about error-handling
    behavior and so forth, but that's a capability that a LOT of people
    have asked for.
    Or is the VOID-returning FUNCTION going to be deprecated or
    discouraged at the same time?
    Certainly not.

    regards, tom lane
  • Pavel Stehule at Sep 9, 2010 at 8:23 pm

    2010/9/9 Tom Lane <tgl@sss.pgh.pa.us>:
    Darren Duncan <darren@darrenduncan.net> writes:
    Since Pg's FUNCTION already seems to take on both roles, so overloading the
    meaning of the FUNCTION keyword, like what a C function or a Perl sub does,
    where returning VOID means procedure, then what is being added by a distinct
    PROCEDURE?
    You might care to go back and re-read some of the extensive prior
    threads about this, but to my mind the main thing that would justify
    inventing a separate PROCEDURE facility is if procedures were to execute
    outside the transaction system, so that they could start and stop
    transactions for themselves.  This is unlike a function which
    necessarily executes inside an already-running transaction.  Of course
    a lot of questions would need to be answered about error-handling
    behavior and so forth, but that's a capability that a LOT of people
    have asked for.
    it's only one request from two mayor request

    * transaction handling
    * unbound SELECTs and multirecordset support

    and some more classic handling of OUT variables.

    Pavel

    Or is the VOID-returning FUNCTION going to be deprecated or
    discouraged at the same time?
    Certainly not.
    regards, tom lane

    --
    Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers
  • Bruce Momjian at Sep 10, 2010 at 3:27 pm

    Pavel Stehule wrote:
    2010/9/9 Tom Lane <tgl@sss.pgh.pa.us>:
    Darren Duncan <darren@darrenduncan.net> writes:
    Since Pg's FUNCTION already seems to take on both roles, so overloading the
    meaning of the FUNCTION keyword, like what a C function or a Perl sub does,
    where returning VOID means procedure, then what is being added by a distinct
    PROCEDURE?
    You might care to go back and re-read some of the extensive prior
    threads about this, but to my mind the main thing that would justify
    inventing a separate PROCEDURE facility is if procedures were to execute
    outside the transaction system, so that they could start and stop
    transactions for themselves. ?This is unlike a function which
    necessarily executes inside an already-running transaction. ?Of course
    a lot of questions would need to be answered about error-handling
    behavior and so forth, but that's a capability that a LOT of people
    have asked for.
    it's only one request from two mayor request

    * transaction handling
    * unbound SELECTs and multirecordset support

    and some more classic handling of OUT variables.
    I assume the current thought is that our "functions" would remain
    unchanged and new "procedures" would allow either of these. I have
    updated the "procedure" todo item to read:

    Implement stored procedures

    This might involve the control of transaction state and the return of multiple result sets

    * PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
    * Proposal: real procedures again (8.4)
    * http://archives.postgresql.org/pgsql-hackers/2010-09/msg00542.php

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Kevin Grittner at Sep 9, 2010 at 8:26 pm

    Tom Lane wrote:

    to my mind the main thing that would justify inventing a separate
    PROCEDURE facility is if procedures were to execute outside the
    transaction system, so that they could start and stop transactions
    for themselves.
    That is the biggest distinction in my mind, too. Supporting
    multiple result sets just as if the queries were run as independent
    client-side statements would also be very important. I have seen
    implementations which support, for a single stored procedure, OUT
    parameters, a RETURN value, and multiple result sets -- all at the
    same time, as separate things. I haven't reviewed stored procedures
    in the SQL standard since an early draft proposal years ago, so I
    don't know what the current state of that is, but if PostgreSQL
    approaches this, it'd be nice to implement as many of the above as
    are not in conflict with requirements of the standard.

    -Kevin
  • Tom Lane at Sep 9, 2010 at 8:59 pm

    "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
    Tom Lane wrote:
    to my mind the main thing that would justify inventing a separate
    PROCEDURE facility is if procedures were to execute outside the
    transaction system, so that they could start and stop transactions
    for themselves.
    That is the biggest distinction in my mind, too. Supporting
    multiple result sets just as if the queries were run as independent
    client-side statements would also be very important. I have seen
    implementations which support, for a single stored procedure, OUT
    parameters, a RETURN value, and multiple result sets -- all at the
    same time, as separate things.
    That seems rather overkill to me --- in particular, I don't understand
    the point of a RETURN value when there can be no caller to return a
    value to. Scalar OUT parameters could be sensible though; those could
    be returned to the client as a one-row result set.

    One point that has to be made is that returning multiple result sets
    as if they were successive queries restricts the client to reading the
    result sets serially; that is, you must read all of result A before you
    can look at result B, etc. While that's frequently good enough, an
    advantage of the return-some-cursors approach is that you can scan the
    cursors in parallel. I'm not sure whether we need to provide that
    flexibility in a procedure facility. One reason not to worry about it
    is that you can't return a cursor if the procedure is outside any
    transaction --- unless you make it a WITH HOLD cursor, which is mighty
    expensive and should certainly not be the default behavior. It might
    be sufficient to say that anyone needing that capability can return
    names of WITH HOLD cursors as scalar OUT parameters, or use the existing
    FUNCTION infrastructure.

    regards, tom lane
  • Kevin Grittner at Sep 9, 2010 at 9:10 pm

    Tom Lane wrote:

    I don't understand the point of a RETURN value when there can be
    no caller to return a value to.
    The version of the draft standard I last saw allowed something like:

    SET x = CALL sp(param_a, param_b);

    I seem to remember Sybase supported a return value as well as OUT
    parameters, too; I think there it was limited to integer values and
    was conventionally used to indicate overall success or failure of
    the procedure.

    -Kevin
  • Darren Duncan at Sep 9, 2010 at 9:52 pm

    Tom Lane wrote:
    One point that has to be made is that returning multiple result sets
    as if they were successive queries restricts the client to reading the
    result sets serially; that is, you must read all of result A before you
    can look at result B, etc.
    One aspect that I don't really like about SQL contrasted with typical other
    languages is in how query results are typically returned "out of band" like the
    above describes, rather than explicitly either via an OUT/INOUT parameter or as
    a function result relation value. -- Darren Duncan
  • Darren Duncan at Sep 9, 2010 at 9:12 pm

    Kevin Grittner wrote:
    Tom Lane wrote:
    to my mind the main thing that would justify inventing a separate
    PROCEDURE facility is if procedures were to execute outside the
    transaction system, so that they could start and stop transactions
    for themselves.
    That is the biggest distinction in my mind, too. Supporting
    multiple result sets just as if the queries were run as independent
    client-side statements would also be very important. I have seen
    implementations which support, for a single stored procedure, OUT
    parameters, a RETURN value, and multiple result sets -- all at the
    same time, as separate things. I haven't reviewed stored procedures
    in the SQL standard since an early draft proposal years ago, so I
    don't know what the current state of that is, but if PostgreSQL
    approaches this, it'd be nice to implement as many of the above as
    are not in conflict with requirements of the standard.
    If it was reasonable I would go further in splitting and have at least 4
    distinct kinds of routines, here listed in order of invocablility (each routine
    kind can invoke anything above it on the list but not anything below it):

    1. Expression-invoked pure functions that only have IN parameters and can not
    directly see the database or have any side-effects and are always in a
    transaction. Most operators are of this kind.

    2. Statement-invoked routines that are pure like #1 but also have OUT/INOUT
    parameters instead of resulting in a value like a function. The assignment
    operator is of this kind.

    3. Routines that *can* see and update the database but are otherwise like #2,
    and are always in a transaction. The general case of a SELECT or DML or DDL are
    of this kind.

    4. Routines that can cross transaction boundaries or control transactions but
    are otherwise like #2 or #3. Transaction control statements are of this kind.

    If I understand correctly, the existing Pg FUNCTION is essentially #3 and the
    proposed PROCEDURE is essentially #4.

    Maybe I just have to RTFM but I don't know if it is possible now to declare a Pg
    FUNCTION that it stays in the restrictions of #1 or #2. But if not, then I
    think it would be valuable to do so, for assisting reliability and performance.

    -- Darren Duncan
  • Pavel Stehule at Sep 9, 2010 at 9:53 pm

    2010/9/9 Darren Duncan <darren@darrenduncan.net>:
    Kevin Grittner wrote:
    Tom Lane wrote:
    to my mind the main thing that would justify inventing a separate
    PROCEDURE facility is if procedures were to execute outside the
    transaction system, so that they could start and stop transactions
    for themselves.
    That is the biggest distinction in my mind, too.  Supporting
    multiple result sets just as if the queries were run as independent
    client-side statements would also be very important.  I have seen
    implementations which support, for a single stored procedure, OUT
    parameters, a RETURN value, and multiple result sets -- all at the
    same time, as separate things.  I haven't reviewed stored procedures
    in the SQL standard since an early draft proposal years ago, so I
    don't know what the current state of that is, but if PostgreSQL
    approaches this, it'd be nice to implement as many of the above as
    are not in conflict with requirements of the standard.
    If it was reasonable I would go further in splitting and have at least 4
    distinct kinds of routines, here listed in order of invocablility (each
    routine kind can invoke anything above it on the list but not anything below
    it):

    1.  Expression-invoked pure functions that only have IN parameters and can
    not directly see the database or have any side-effects and are always in a
    transaction.  Most operators are of this kind.

    2.  Statement-invoked routines that are pure like #1 but also have OUT/INOUT
    parameters instead of resulting in a value like a function.  The assignment
    operator is of this kind.

    3.  Routines that *can* see and update the database but are otherwise like
    #2, and are always in a transaction.  The general case of a SELECT or DML or
    DDL are of this kind.

    4.  Routines that can cross transaction boundaries or control transactions
    but are otherwise like #2 or #3.  Transaction control statements are of this
    kind.

    If I understand correctly, the existing Pg FUNCTION is essentially #3 and
    the proposed PROCEDURE is essentially #4.
    Immutable functions are very near to #1. Actually PostgreSQL OUT
    parameters are implemented as returned one composite value.

    Regards

    Pavel
    Maybe I just have to RTFM but I don't know if it is possible now to declare
    a Pg FUNCTION that it stays in the restrictions of #1 or #2.  But if not,
    then I think it would be valuable to do so, for assisting reliability and
    performance.

    -- Darren Duncan


    --
    Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers
  • Darren Duncan at Sep 9, 2010 at 8:35 pm

    Tom Lane wrote:
    Darren Duncan <darren@darrenduncan.net> writes:
    Since Pg's FUNCTION already seems to take on both roles, so overloading the
    meaning of the FUNCTION keyword, like what a C function or a Perl sub does,
    where returning VOID means procedure, then what is being added by a distinct
    PROCEDURE?
    You might care to go back and re-read some of the extensive prior
    threads about this, but to my mind the main thing that would justify
    inventing a separate PROCEDURE facility is if procedures were to execute
    outside the transaction system, so that they could start and stop
    transactions for themselves. This is unlike a function which
    necessarily executes inside an already-running transaction. Of course
    a lot of questions would need to be answered about error-handling
    behavior and so forth, but that's a capability that a LOT of people
    have asked for.
    That is a very strong rationale in my mind to have clearly distinct kinds of
    routines, where one kind is implicitly entirely contained in a transaction and
    the other kind can cross transaction boundaries or control transactions. I
    support the separation on those grounds alone, though it also makes sense that
    the 2 kinds can have additional ways to distinguish them. -- Darren Duncan
  • Pavel Stehule at Sep 9, 2010 at 8:45 pm

    2010/9/9 Darren Duncan <darren@darrenduncan.net>:
    Tom Lane wrote:
    Darren Duncan <darren@darrenduncan.net> writes:
    Since Pg's FUNCTION already seems to take on both roles, so overloading
    the meaning of the FUNCTION keyword, like what a C function or a Perl sub
    does, where returning VOID means procedure, then what is being added by a
    distinct PROCEDURE?
    You might care to go back and re-read some of the extensive prior
    threads about this, but to my mind the main thing that would justify
    inventing a separate PROCEDURE facility is if procedures were to execute
    outside the transaction system, so that they could start and stop
    transactions for themselves.  This is unlike a function which
    necessarily executes inside an already-running transaction.  Of course
    a lot of questions would need to be answered about error-handling
    behavior and so forth, but that's a capability that a LOT of people
    have asked for.
    That is a very strong rationale in my mind to have clearly distinct kinds of
    routines, where one kind is implicitly entirely contained in a transaction
    and the other kind can cross transaction boundaries or control transactions.
    I support the separation on those grounds alone, though it also makes sense
    that the 2 kinds can have additional ways to distinguish them. -- Darren
    Duncan
    Functions should be under transaction always, but procedures when
    people like. There is "BEGIN ATOMIC ... END" block defined in SQL/PSM
    and procedure can be defined as ATOMIC or non ATOMIC. For me - most
    important difference is activation - function is activated from SELECT
    statement - and SELECT has plan - the result is hardly specified,
    procedure is activated by CALL statement - there are not plan - the
    result isn't limited.

    Regards

    Pavel Stehule


    --
    Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers
  • Peter Eisentraut at Sep 10, 2010 at 4:49 am

    On tor, 2010-09-09 at 16:16 -0400, Tom Lane wrote:
    You might care to go back and re-read some of the extensive prior
    threads about this, but to my mind the main thing that would justify
    inventing a separate PROCEDURE facility is if procedures were to
    execute outside the transaction system, so that they could start and
    stop transactions for themselves.
    Given what the SQL standard says, a "procedure" certainly has to be
    defined as syntactic sugar for "function returns void". Special
    transaction handling would then have to be an additional attribute of
    the procedure.
  • Pavel Stehule at Sep 9, 2010 at 8:18 pm

    2010/9/9 Darren Duncan <darren@darrenduncan.net>:
    Pavel Stehule wrote:
    there are lot of questions - and I am not sure if procedures
    implementation can be done in one release cycle. The basic questions:

    * should be special catalog for procedures or we will use pg_proc?
    * how can be implemented OUT variables - the original implementation
    is simple - it's just pointer, but it's not directly possible inside
    postgres, because we use a MemoryContexts?
    * how can be implement a CALL statement - as plan statement or as command?
    * how can be implemented variables inside psql console, if we allows them?
    * how can be implement an overloading of procedures - can we use for
    selection OUT variables too?
    * what is procedure? It's like void function, or it can return status
    code like procedures in SQL/PSM (DB2)?

    --- As long years a stored procedures developer, I can say, so just
    minimal implementation of procedures can help with writing little bit
    more readable code for functions that return more then one scalar
    result. But other features can be nice too - explicit transaction
    control and unbind selects. But these features are killing gun.
    I've often considered that the main distinction between a function and a
    procedure is that the former is intended to be invoked as a value-resulting
    expression while the latter is intended to be invoked as a
    non-value-resulting statement.  The SQL standard uses separate FUNCTION and
    PROCEDURE for these.

    Since Pg's FUNCTION already seems to take on both roles, so overloading the
    meaning of the FUNCTION keyword, like what a C function or a Perl sub does,
    where returning VOID means procedure, then what is being added by a distinct
    PROCEDURE?  Or is the VOID-returning FUNCTION going to be deprecated or
    discouraged at the same time?
    the overloading of function is based only on input parameters -
    because there are not entered output variables - it is just some
    record. But overloading of procedures, can be based on input and
    output variables.

    so I can to write

    CREATE PROCEDURE foo(OUT a int)
    ...

    and
    CREATE PROCEDURE foo(OUT a varchar)
    ...

    and then when I use a statement CALL is correct procedure selected

    CALL foo(textvariable)

    Regards

    Pavel
    -- Darren Duncan


    --
    Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers
  • Robert Haas at Sep 9, 2010 at 8:22 pm

    On Thu, Sep 9, 2010 at 4:17 PM, Pavel Stehule wrote:
    2010/9/9 Darren Duncan <darren@darrenduncan.net>:
    Pavel Stehule wrote:
    there are lot of questions - and I am not sure if procedures
    implementation can be done in one release cycle. The basic questions:

    * should be special catalog for procedures or we will use pg_proc?
    * how can be implemented OUT variables - the original implementation
    is simple - it's just pointer, but it's not directly possible inside
    postgres, because we use a MemoryContexts?
    * how can be implement a CALL statement - as plan statement or as command?
    * how can be implemented variables inside psql console, if we allows them?
    * how can be implement an overloading of procedures - can we use for
    selection OUT variables too?
    * what is procedure? It's like void function, or it can return status
    code like procedures in SQL/PSM (DB2)?

    --- As long years a stored procedures developer, I can say, so just
    minimal implementation of procedures can help with writing little bit
    more readable code for functions that return more then one scalar
    result. But other features can be nice too - explicit transaction
    control and unbind selects. But these features are killing gun.
    I've often considered that the main distinction between a function and a
    procedure is that the former is intended to be invoked as a value-resulting
    expression while the latter is intended to be invoked as a
    non-value-resulting statement.  The SQL standard uses separate FUNCTION and
    PROCEDURE for these.

    Since Pg's FUNCTION already seems to take on both roles, so overloading the
    meaning of the FUNCTION keyword, like what a C function or a Perl sub does,
    where returning VOID means procedure, then what is being added by a distinct
    PROCEDURE?  Or is the VOID-returning FUNCTION going to be deprecated or
    discouraged at the same time?
    the overloading of function is based only on input parameters -
    because there are not entered output variables - it is just some
    record. But overloading of procedures, can be based on input and
    output variables.

    so I can to write

    CREATE PROCEDURE foo(OUT a int)
    ...

    and
    CREATE PROCEDURE foo(OUT a varchar)
    ...

    and then when I use a statement CALL is correct procedure selected

    CALL foo(textvariable)
    That seems like a lot of complexity for no real benefit, to me.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
  • Pavel Stehule at Sep 9, 2010 at 8:30 pm

    2010/9/9 Robert Haas <robertmhaas@gmail.com>:
    On Thu, Sep 9, 2010 at 4:17 PM, Pavel Stehule wrote:
    2010/9/9 Darren Duncan <darren@darrenduncan.net>:
    Pavel Stehule wrote:
    there are lot of questions - and I am not sure if procedures
    implementation can be done in one release cycle. The basic questions:

    * should be special catalog for procedures or we will use pg_proc?
    * how can be implemented OUT variables - the original implementation
    is simple - it's just pointer, but it's not directly possible inside
    postgres, because we use a MemoryContexts?
    * how can be implement a CALL statement - as plan statement or as command?
    * how can be implemented variables inside psql console, if we allows them?
    * how can be implement an overloading of procedures - can we use for
    selection OUT variables too?
    * what is procedure? It's like void function, or it can return status
    code like procedures in SQL/PSM (DB2)?

    --- As long years a stored procedures developer, I can say, so just
    minimal implementation of procedures can help with writing little bit
    more readable code for functions that return more then one scalar
    result. But other features can be nice too - explicit transaction
    control and unbind selects. But these features are killing gun.
    I've often considered that the main distinction between a function and a
    procedure is that the former is intended to be invoked as a value-resulting
    expression while the latter is intended to be invoked as a
    non-value-resulting statement.  The SQL standard uses separate FUNCTION and
    PROCEDURE for these.

    Since Pg's FUNCTION already seems to take on both roles, so overloading the
    meaning of the FUNCTION keyword, like what a C function or a Perl sub does,
    where returning VOID means procedure, then what is being added by a distinct
    PROCEDURE?  Or is the VOID-returning FUNCTION going to be deprecated or
    discouraged at the same time?
    the overloading of function is based only on input parameters -
    because there are not entered output variables - it is just some
    record. But overloading of procedures, can be based on input and
    output variables.

    so I can to write

    CREATE PROCEDURE foo(OUT a int)
    ...

    and
    CREATE PROCEDURE foo(OUT a varchar)
    ...

    and then when I use a statement CALL is correct procedure selected

    CALL foo(textvariable)
    That seems like a lot of complexity for no real benefit, to me.
    no, you can to specify a expected result type - it's very for some
    convert or import functions. So we expect so out procedures will
    supports to OUT parameters, then implementation of this mechanism has
    minimal overhead to current implementation. Just to add types of OUT
    parameters to searching algorithm.

    More - it is just consistent with overloading idea. Why the OUT
    parameters should be removed from procedure parameters?
    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
  • Robert Haas at Sep 9, 2010 at 8:46 pm

    On Thu, Sep 9, 2010 at 4:29 PM, Pavel Stehule wrote:
    so I can to write

    CREATE PROCEDURE foo(OUT a int)
    ...

    and
    CREATE PROCEDURE foo(OUT a varchar)
    ...

    and then when I use a statement CALL is correct procedure selected

    CALL foo(textvariable)
    That seems like a lot of complexity for no real benefit, to me.
    no, you can to specify a expected result type - it's very  for some
    convert or import functions. So we expect so out procedures will
    supports to OUT parameters, then implementation of this mechanism has
    minimal overhead to current implementation. Just to add types of OUT
    parameters to searching algorithm.

    More - it is just consistent with overloading idea. Why the OUT
    parameters should be removed from procedure parameters?
    I think the question is whether there's something broken enough about
    the current system to warrant doing something different, and I guess
    my answer would be no. To be honest, I am already pretty unhappy with
    the changes that make it impossible to redefined foo(a int) as
    foo(anteater int), which is a perfectly reasonable thing to want to do
    but which is now forbidden because someone MIGHT have called the
    function as foo(a := 3), and I certainly don't want to make it any
    worse. Whether there are actually any stored queries that call the
    function this way (or at all) is doesn't matter: it's not allowed. So
    for a marginal notational convenience we have created dependency hell,
    where you must drop and recreate every dependent object to perform a
    trivial renaming. I think this is really quite horrible and would
    have argued against accepting this patch at the time if I'd realized
    what effect it was going to have.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
  • Pavel Stehule at Sep 9, 2010 at 8:53 pm

    2010/9/9 Robert Haas <robertmhaas@gmail.com>:
    On Thu, Sep 9, 2010 at 4:29 PM, Pavel Stehule wrote:
    so I can to write

    CREATE PROCEDURE foo(OUT a int)
    ...

    and
    CREATE PROCEDURE foo(OUT a varchar)
    ...

    and then when I use a statement CALL is correct procedure selected

    CALL foo(textvariable)
    That seems like a lot of complexity for no real benefit, to me.
    no, you can to specify a expected result type - it's very  for some
    convert or import functions. So we expect so out procedures will
    supports to OUT parameters, then implementation of this mechanism has
    minimal overhead to current implementation. Just to add types of OUT
    parameters to searching algorithm.

    More - it is just consistent with overloading idea. Why the OUT
    parameters should be removed from procedure parameters?
    I think the question is whether there's something broken enough about
    the current system to warrant doing something different, and I guess
    my answer would be no.  To be honest, I am already pretty unhappy with
    the changes that make it impossible to redefined foo(a int) as
    foo(anteater int), which is a perfectly reasonable thing to want to do
    but which is now forbidden because someone MIGHT have called the
    function as foo(a := 3), and I certainly don't want to make it any
    worse.  Whether there are actually any stored queries that call the
    function this way (or at all) is doesn't matter: it's not allowed.  So
    for a marginal notational convenience we have created dependency hell,
    where you must drop and recreate every dependent object to perform a
    trivial renaming.  I think this is really quite horrible and would
    have argued against accepting this patch at the time if I'd realized
    what effect it was going to have.
    yes, named parameters for functions created a new dependency. But this
    isn't possible for procedures. You can not to use a procedure inside
    view. So new dependency are not possible there. This important on
    procedures - it is little bit more outer from database.

    Pavel


    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
  • Pavel Stehule at Sep 9, 2010 at 8:59 pm

    2010/9/9 Robert Haas <robertmhaas@gmail.com>:
    On Thu, Sep 9, 2010 at 4:29 PM, Pavel Stehule wrote:
    so I can to write

    CREATE PROCEDURE foo(OUT a int)
    ...

    and
    CREATE PROCEDURE foo(OUT a varchar)
    ...

    and then when I use a statement CALL is correct procedure selected

    CALL foo(textvariable)
    That seems like a lot of complexity for no real benefit, to me.
    no, you can to specify a expected result type - it's very  for some
    convert or import functions. So we expect so out procedures will
    supports to OUT parameters, then implementation of this mechanism has
    minimal overhead to current implementation. Just to add types of OUT
    parameters to searching algorithm.

    More - it is just consistent with overloading idea. Why the OUT
    parameters should be removed from procedure parameters?
    I think the question is whether there's something broken enough about
    the current system to warrant doing something different, and I guess
    my answer would be no.  To be honest, I am already pretty unhappy with
    the changes that make it impossible to redefined foo(a int) as
    foo(anteater int), which is a perfectly reasonable thing to want to do
    but which is now forbidden because someone MIGHT have called the
    function as foo(a := 3), and I certainly don't want to make it any
    worse.  Whether there are actually any stored queries that call the
    function this way (or at all) is doesn't matter: it's not allowed.  So
    for a marginal notational convenience we have created dependency hell,
    where you must drop and recreate every dependent object to perform a
    trivial renaming.
    I don't agree with you - this behave is because pg doesn't hold
    dependency between functions and preparsed SQL - so this is one the
    most simple protection. But if somebody appends a relations between
    views and functions to dictionary, then he can rechecks necessary
    views automatically.

    Regards

    Pavel Stehule

    I think this is really quite horrible and would
    have argued against accepting this patch at the time if I'd realized
    what effect it was going to have.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
  • Tom Lane at Sep 9, 2010 at 9:07 pm

    Robert Haas writes:
    To be honest, I am already pretty unhappy with
    the changes that make it impossible to redefined foo(a int) as
    foo(anteater int), which is a perfectly reasonable thing to want to do
    but which is now forbidden because someone MIGHT have called the
    function as foo(a := 3), and I certainly don't want to make it any
    worse. Whether there are actually any stored queries that call the
    function this way (or at all) is doesn't matter: it's not allowed.
    BTW, before anyone suggests it: it wouldn't really improve matters if
    we were to allow renaming so long as we couldn't find any such calls in
    stored queries. We don't have any ability to track calls occuring in
    stored procedures, let alone on the client side; so a rename would still
    put you at very substantial risk of breaking things.

    regards, tom lane
  • Tom Lane at Sep 9, 2010 at 9:13 pm

    Pavel Stehule writes:
    2010/9/9 Robert Haas <robertmhaas@gmail.com>:
    That seems like a lot of complexity for no real benefit, to me.
    no, you can to specify a expected result type - it's very for some
    convert or import functions. So we expect so out procedures will
    supports to OUT parameters, then implementation of this mechanism has
    minimal overhead to current implementation. Just to add types of OUT
    parameters to searching algorithm.
    It's *not* trivial, not at all. You are ignoring all of the semantic
    implications. Should foo(IN x int, OUT y int) be considered different
    from, and thus allowed to exist at the same time as, foo(IN x int,
    OUT y float)? If so, how do you represent that in the catalogs?
    Possibly more to the point, any such decision means that it'll be
    impossible to call any stored procedure without fully specifying the
    types of output arguments as well as input arguments, else the system
    can't tell which procedure you meant to call. That doesn't sound like
    a notational improvement to me.

    I'm with Robert: this would be a huge extra complication for a
    remarkably small amount of benefit.

    regards, tom lane

Related Discussions

People

Translate

site design / logo © 2022 Grokbase