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