Re: multiple results from a function

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: James Harper <james(dot)harper(at)bendigoit(dot)com(dot)au>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: multiple results from a function
Date: 2014-03-03 20:49:36
Message-ID: CAHyXU0zbup8CdhWMhnrMyM+XBCsTDuqKNmhutFqtxZ4biqwpkg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Mon, Mar 3, 2014 at 2:10 PM, James Harper
<james(dot)harper(at)bendigoit(dot)com(dot)au> wrote:
>>
>> On Thu, Feb 27, 2014 at 6:23 PM, James Harper
>> <james(dot)harper(at)bendigoit(dot)com(dot)au> wrote:
>> > I see that I can use PQgetResult to get results from an async query, and this
>> allows for multiple results, presumably when the query is like "SELECT 123,
>> 456; SELECT 'ABC', 'DEF';". Is there a way for a C function, using SPI, to send
>> result sets without waiting for completion and returning a potentially huge
>> set of results right at the end of the function?
>> >
>> > Failing that, is there a way for a C function to send to STDOUT in the same
>> way that a COPY (...) TO STDOUT would?
>>
>> Why does this have to be an SPI function? Maybe a little more context
>> here would help.
>>
>
> I'm working on an application to accept TDS (Microsoft SQL Server) connections and proxy them to postgres. MSSQL does things a little differently, for instance in addition to a functions it has stored procedures that can contain multiple select statement that return results to clients.
>
> I'm building against 9.3 at the moment, and my code is external to the postgres process and using libpq connections, but I noticed that 9.4 allows dynamic backend worker processes which would allow my code to run server side and spawn a process for each TDS connection.

Hm, interesting. I assume that this code is 'man in the middle';
you've written a server that masquerades as a SQL server relays the
request to postgres. That's going to be a lot of work but would be a
fascinating project if you could pull it off.

> I've worked around the multiple results issue by running the stored procedure outside of my custom language, which allows me to return the results as they happen. I had the idea that I could use COPY ... STDOUT to emulate the return of results, but that would have had too many shortcomings anyway.

Yeah, that's not going to work. I don't think the background feature
is going to buy you much here. The model I'd be following is
pgbouncer.

FYI there are at least two other useful methods for interfacing SQL
server from postgres:
1. jdbc-fdw: create a postgres table linked to a query against SQL sever
2. sqsh/plsh: fire off a query to sql server through sqsh invoked via
pl/sh -- then load it in via COPY.

merlin

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2014-03-03 20:50:27 Re: Efficiently delete rows not referenced by a foreign key
Previous Message Evan Martin 2014-03-03 20:21:21 Efficiently delete rows not referenced by a foreign key