Re: multiple results from a function

> > 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