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 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?
Thanks
James
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 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.
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.
Thanks
James
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 collation is 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 are mine I can send/recv in a binary format compatible with MSSQL. I've got the basic types working but a lot of the code is 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 connection but obviously postgres doesn't allow this. I've worked around this by incorporating the database name into the schema 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 just placeholders 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 license it just yet.
James
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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?
It might make developing your proxy easier.
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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?
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 emulate the mssql types using the native postgres types. I'm working from tds (Sybase/mssql network protocol) up, while they appear to be working from their t-sql implementation downwards, and I suspect that adding tds onto what they have might not 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general