Thread: multiple results from a function
I see that I can use PQgetResult to get results from an async query, and this allows for multiple results, presumably whenthe query is like "SELECT 123, 456; SELECT 'ABC', 'DEF';". Is there a way for a C function, using SPI, to send resultsets 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? Thanks James
On Thu, Feb 27, 2014 at 6:23 PM, James Harper <james.harper@bendigoit.com.au> wrote: > I see that I can use PQgetResult to get results from an async query, and this allows for multiple results, presumably whenthe query is like "SELECT 123, 456; SELECT 'ABC', 'DEF';". Is there a way for a C function, using SPI, to send resultsets 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. merlin
> > On Thu, Feb 27, 2014 at 6:23 PM, James Harper > <james.harper@bendigoit.com.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 thingsa little differently, for instance in addition to a functions it has stored procedures that can contain multiple selectstatement 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, butI noticed that 9.4 allows dynamic backend worker processes which would allow my code to run server side and spawn a processfor each TDS connection. I've worked around the multiple results issue by running the stored procedure outside of my custom language, which allowsme 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. Thanks James
On Mon, Mar 3, 2014 at 2:10 PM, James Harper <james.harper@bendigoit.com.au> wrote: >> >> On Thu, Feb 27, 2014 at 6:23 PM, James Harper >> <james.harper@bendigoit.com.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 thingsa little differently, for instance in addition to a functions it has stored procedures that can contain multiple selectstatement 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, butI noticed that 9.4 allows dynamic backend worker processes which would allow my code to run server side and spawn a processfor 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 allowsme 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
> > 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. That's about it. There are a few other differences too apart from the stored procs, like just about any default MSSQL collationis case insensitive. I ended up writing my own set of custom types that match the MSSQL types in terms of behaviour(eg case insensitive compare, varchar(max) works as expected, etc), with the advantage that because the types aremine I can send/recv in a binary format compatible with MSSQL. I've got the basic types working but a lot of the codeis just placeholder at the moment, and I'm not exactly sure how I'll handle the billion different collations. The multi-database issue is a bit of a pain too. MSSQL expects to be able to access different databases from the one connectionbut obviously postgres doesn't allow this. I've worked around this by incorporating the database name into theschema name, eg 'master.dbo' and maintaining the search path appropriately, which seems to work well. Right now, I can: . connect MSSQL Management Studio and log in . create databases, logins, and users . create tables, indexes, and constraints (probably missing a bunch of options) . execute basic queries (SELECT, FROM, JOIN, WHERE, GROUP BY all seem to translate pretty cleanly) . create stored functions and procedures . execute simple procedures (not functions yet) with basic content (IF/WHILE work, probably lots of other stuff doesn't) To be actually useful I need to flesh out the types fully, especially the decimal/numeric/money types which are totally justplaceholders which just return 42, and implement cursors in stored procedures. Ultimately I'd hope that any MSSQL application can connect and 'just work' without any modification. Not sure how I'll licenseit just yet. James
James Harper wrote on 03.03.2014 21:10: > 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. Did you have a look at tPostgres? http://tpostgres.org/se/ It might make developing your proxy easier. Thomas
> > James Harper wrote on 03.03.2014 21:10: > > 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. > > Did you have a look at tPostgres? > > http://tpostgres.org/se/ > > It might make developing your proxy easier. > I looked at that. I'm not sure their approach is going to achieve the desired level of compatibility when trying to emulatethe mssql types using the native postgres types. I'm working from tds (Sybase/mssql network protocol) up, while theyappear to be working from their t-sql implementation downwards, and I suspect that adding tds onto what they have mightnot be as easy as the "Leverage the FreeTDS implementation..." statement on their roadmap implies. That said, I haven't looked at their project in great detail. I could be complete wrong in what I've said above. James