Re: Stored procedures and out parameters

From: David Fetter <david(at)fetter(dot)org>
To: Shay Rojansky <roji(at)roji(dot)org>
Cc: Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, daniel(at)manitou-mail(dot)org, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>, Robert Haas <robertmhaas(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Stored procedures and out parameters
Date: 2018-08-04 16:46:40
Message-ID: 20180804164640.GB29092@fetter.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Sat, Aug 04, 2018 at 07:03:47AM +0100, Shay Rojansky wrote:
> > Shay>Npgsql currently always sends a describe as part of statement
> > execution (for server-prepared messages the describe is done only once, at
> > preparation-time). Vladimir, are you doing things differently here?
> >
> > The same thing is for pgjdbc. It does use describe to identify result row
> > format.
> > However, "CALL my_proc()" works just fine with current git master for both
> > simple and extended protocol.
> >
>
> In one way that's good, but I wonder how this squares with the following
> written by David above:
>
> > 1. A stored procedure should be able to return multiple resultsets with
> different structures.
> > 2. A stored procedure can decide dynamically of the structure of the
> resultset(s) it returns, and the caller will discover it as they're
> returned, not before.
>
> Both of the above seem to be simply incompatible with the current
> PostgreSQL protocol. Describe currently returns a single RowDescription,
> which describes a single resultset, not more.

Long ago, when I was trying to simulate this behavior, I created
functions which returned SETOF REFCURSOR. It worked at least up to the
extent of being able to use multiple result sets. I don't recall
whether I had a good way to describe the rowtypes, but I suspect one
could be hacked together by having one refcursor be of a specific
rowtype whose job is to describe all the rest.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Jonathan S. Katz 2018-08-04 17:17:10 Re: Draft release notes are up
Previous Message Jonathan S. Katz 2018-08-04 16:17:57 Re: Draft release notes are up