Re: multiple results from a function

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