Thread: MS SQL Server compatibility functions
Hi. In the course of porting a database from Microsoft SQL Server to PostgreSQL I have rewritten a few of the date and string functions in pl/pgSQL. Started with just datepart, datediff and soundex, but once started I continued and rewrote all date/time and string functions supported by MS SQL 2005. Leaving only compatibility with unicode-handling and binary objects (that MS SQL Server for some reason overloads string functions to work with). I guess I am not the only one moving from MS SQL Server, so is there interest for others to use my work, as a contrib perhaps. And how should I continue from here in that case? regards -- //Fredrik Olsson Treyst AB +46-19-362182 fredrik.olsson@treyst.se
I'd be _very_ interested. I'll also volunteer to help out on this if you need assistance -- we have somewhat of a mixed environment here, so I already have a few (simple) functions that allow some compatibility between MS SQL Server and PostgreSQL (supporting "nextval" on SQL Server, etc.), but it sounds like your work has gone far beyond my work. - Bill > > Hi. > > In the course of porting a database from Microsoft SQL Server to > PostgreSQL I have rewritten a few of the date and string functions in > pl/pgSQL. Started with just datepart, datediff and soundex, but once > started I continued and rewrote all date/time and string functions > supported by MS SQL 2005. Leaving only compatibility with > unicode-handling and binary objects (that MS SQL Server for > some reason > overloads string functions to work with). > > I guess I am not the only one moving from MS SQL Server, so is there > interest for others to use my work, as a contrib perhaps. And > how should > I continue from here in that case? > > regards > > -- > //Fredrik Olsson > Treyst AB > +46-19-362182 > fredrik.olsson@treyst.se >
If this gets added as a contrib, here's a version of uniqueidentifier
and newid() I wrote that maintains the same format as the SQL Server
version:
CREATE SCHEMA sqlserver AUTHORIZATION postgres;
GRANT ALL ON SCHEMA sqlserver TO public;
CREATE SEQUENCE sqlserver.uniqueidentifier0 INCREMENT 1 MINVALUE 0 MAXVALUE 9223372036854775807 START 0 CACHE 1;
GRANT ALL ON TABLE sqlserver.uniqueidentifier0 TO public;
CREATE SEQUENCE sqlserver.uniqueidentifier1 INCREMENT 1 MINVALUE 0 MAXVALUE 9223372036854775807 START 0 CACHE 1;
GRANT ALL ON TABLE sqlserver.uniqueidentifier1 TO public;
--
-- use newid(n) to increment the sequences n times. Useful
-- for testing and timing the newid() function.
--
CREATE OR REPLACE FUNCTION sqlserver.newid(l integer)
RETURNS CHAR(36) AS $$
DECLARE n CHAR(36); maxl INTEGER;
BEGIN maxl := l; WHILE (maxl > 0) LOOP n := sqlserver.newid(); maxl := maxl - 1; END LOOP;
RETURN(n);
END;$$ LANGUAGE plpgsql;
--
-- use newid() to acquire the next uniqueidentifier value.
-- This uses two sequences. Since a sequence returns an
-- 8-byte number, we just convert those into two 16 character
-- hex strings. Normally we just need to increment the second
-- sequence, but when that fills up, we increment the first
-- one and then reset the second one to 0. To prevent concerns
-- over a race condition, we then get the nextval of the second
-- sequence.
--
-- Note that this algorithm assumes that int8 works properly.
-- If you are porting this to a platform without a working int8,
-- then you will need to use 4 4-byte sequences instead.
--
CREATE OR REPLACE FUNCTION sqlserver.newid()
RETURNS CHAR(36) AS $$
DECLARE numbers0 CHAR(16); numbers1 CHAR(16); formatted_id CHAR(36); sq0 INT8; sq1 INT8;
BEGIN -- get the current sequence values SELECT INTO sq0 last_value FROM sqlserver.uniqueidentifier0; SELECT INTO sq1
last_valueFROM sqlserver.uniqueidentifier1; -- if sq1 is wrapped, then increment sq0 and restart sq1 at 0 IF (sq1 =
9223372036854775807)THEN sq0 := NEXTVAL('sqlserver.uniqueidentifier0'); sq1 :=
SETVAL('sqlserver.uniqueidentifier1',0); -- get nextval; ensures no race condition sq1 :=
NEXTVAL('sqlserver.uniqueidentifier1');ELSE sq1 := NEXTVAL('sqlserver.uniqueidentifier1'); END IF;
numbers0 := UPPER(LPAD(TO_HEX(sq0), 16, '0')); numbers1 := UPPER(LPAD(TO_HEX(sq1), 16, '0'));formatted_id :=
SUBSTRING(numbers0,1, 8) || '-' ||
SUBSTRING(numbers0, 9, 4) || '-' || SUBSTRING(numbers0, 13, 4) || '-' || SUBSTRING(numbers1, 1, 4) ||
'-'||
SUBSTRING(numbers1, 5, 12);
return(formatted_id);
END;$$ LANGUAGE plpgsql;
CREATE DOMAIN sqlserver.uniqueidentifier AS char(36) DEFAULT sqlserver.newid();
Mike Pollard
SUPRA Server SQL Engineering and Support
Cincom Systems, Inc.
--------------------------------Better to remain silent and be thought a fool than to speak out and
remove all doubt. Abraham Lincoln
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Bill Bartlett
Sent: Wednesday, November 23, 2005 10:01 AM
To: 'Fredrik Olsson'; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] MS SQL Server compatibility functions
I'd be _very_ interested. I'll also volunteer to help out on this if
you need assistance -- we have somewhat of a mixed environment here, so
I already have a few (simple) functions that allow some compatibility
between MS SQL Server and PostgreSQL (supporting "nextval" on SQL
Server, etc.), but it sounds like your work has gone far beyond my work.
- Bill
>
> Hi.
>
> In the course of porting a database from Microsoft SQL Server to
> PostgreSQL I have rewritten a few of the date and string functions in
> pl/pgSQL. Started with just datepart, datediff and soundex, but once
> started I continued and rewrote all date/time and string functions
> supported by MS SQL 2005. Leaving only compatibility with
> unicode-handling and binary objects (that MS SQL Server for
> some reason
> overloads string functions to work with).
>
> I guess I am not the only one moving from MS SQL Server, so is there
> interest for others to use my work, as a contrib perhaps. And
> how should
> I continue from here in that case?
>
> regards
>
> --
> //Fredrik Olsson
> Treyst AB
> +46-19-362182
> fredrik.olsson@treyst.se
>
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Hi, On Wed, 23 Nov 2005, Fredrik Olsson wrote: <snip> > I guess I am not the only one moving from MS SQL Server, so is there interest > for others to use my work, as a contrib perhaps. And how should I continue > from here in that case? I'd start a new project at pgfoundry and then would begin talking about a contrib module. Regards, -- Devrim GUNDUZ Kivi Bilişim Teknolojileri - http://www.kivi.com.tr devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org
Devrim GUNDUZ wrote: > > Hi, > > On Wed, 23 Nov 2005, Fredrik Olsson wrote: > > <snip> >> I guess I am not the only one moving from MS SQL Server, so is there >> interest for others to use my work, as a contrib perhaps. And how >> should I continue from here in that case? > > I'd start a new project at pgfoundry and then would begin talking > about a contrib module. > Good idea. I have registered mssqlsupport as project name there, and will add all there is as soon as/if the project gets accepted. Regards -- //Fredrik Olsson Treyst AB +46-19-362182 fredrik.olsson@treyst.se
I just started a MySQL compatibility functions project on pgfoundry.org. I suggest starting an MSSQL one as well. I'd beinterested if you could mail me your code for your functions so far because many of the MySQL functions are copied from MSSQL... Chris Fredrik Olsson wrote: > Hi. > > In the course of porting a database from Microsoft SQL Server to > PostgreSQL I have rewritten a few of the date and string functions in > pl/pgSQL. Started with just datepart, datediff and soundex, but once > started I continued and rewrote all date/time and string functions > supported by MS SQL 2005. Leaving only compatibility with > unicode-handling and binary objects (that MS SQL Server for some reason > overloads string functions to work with). > > I guess I am not the only one moving from MS SQL Server, so is there > interest for others to use my work, as a contrib perhaps. And how should > I continue from here in that case? > > regards >
Hello DB2, MySQL and MsSQL has shared group of function (date, time, strings). You can do it a bit complex - use variable which direct behavior, but there isn't bigger differences, I hope. Please (for start), use mycode, orafunc from pgfoundry. Regards Pavel Stehule >From: Christopher Kings-Lynne <chriskl@familyhealth.com.au> >To: Fredrik Olsson <fredrik.olsson@treyst.se> >CC: pgsql-hackers@postgresql.org >Subject: Re: [HACKERS] MS SQL Server compatibility functions >Date: Thu, 24 Nov 2005 09:24:06 +0800 > >I just started a MySQL compatibility functions project on pgfoundry.org. I >suggest starting an MSSQL one as well. I'd be interested if you could mail >me your code for your functions so far because many of the MySQL functions >are copied from MSSQL... > >Chris > >Fredrik Olsson wrote: >>Hi. >> >>In the course of porting a database from Microsoft SQL Server to >>PostgreSQL I have rewritten a few of the date and string functions in >>pl/pgSQL. Started with just datepart, datediff and soundex, but once >>started I continued and rewrote all date/time and string functions >>supported by MS SQL 2005. Leaving only compatibility with unicode-handling >>and binary objects (that MS SQL Server for some reason overloads string >>functions to work with). >> >>I guess I am not the only one moving from MS SQL Server, so is there >>interest for others to use my work, as a contrib perhaps. And how should I >>continue from here in that case? >> >>regards >> > > >---------------------------(end of broadcast)--------------------------- >TIP 2: Don't 'kill -9' the postmaster _________________________________________________________________ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/
Fredrik Olsson wrote: > Devrim GUNDUZ wrote: >> >> Hi, >> >> On Wed, 23 Nov 2005, Fredrik Olsson wrote: >> >> <snip> >>> I guess I am not the only one moving from MS SQL Server, so is there >>> interest for others to use my work, as a contrib perhaps. And how >>> should I continue from here in that case? >> >> I'd start a new project at pgfoundry and then would begin talking >> about a contrib module. >> > Good idea. I have registered mssqlsupport as project name there, and > will add all there is as soon as/if the project gets accepted. > Said and done, project is up at http://pgfoundry.org/projects/mssqlsupport/ regards -- //Fredrik Olsson Treyst AB +46-19-362182 fredrik.olsson@treyst.se
Am Mittwoch, 23. November 2005 16:32 schrieb Pollard, Mike: > If this gets added as a contrib, here's a version of uniqueidentifier > and newid() I wrote that maintains the same format as the SQL Server > version: > > CREATE SCHEMA sqlserver > AUTHORIZATION postgres; > GRANT ALL ON SCHEMA sqlserver TO public; > > CREATE SEQUENCE sqlserver.uniqueidentifier0 > INCREMENT 1 > MINVALUE 0 > MAXVALUE 9223372036854775807 > START 0 > CACHE 1; > GRANT ALL ON TABLE sqlserver.uniqueidentifier0 TO public; > > CREATE SEQUENCE sqlserver.uniqueidentifier1 > INCREMENT 1 > MINVALUE 0 > MAXVALUE 9223372036854775807 > START 0 > CACHE 1; > GRANT ALL ON TABLE sqlserver.uniqueidentifier1 TO public; Why do you use "GRANT ALL" and not "GRANT SELECT, UPDATE"? All means everybody can do bad things with those sequences. Regards,Mario Weilguni
> Why do you use "GRANT ALL" and not "GRANT SELECT, UPDATE"? All means everybody > can do bad things with those sequences. GRANT ALL on a sequence IS GRANT SELECT & UPDATE. Chris
If you're referring to my procedure for newid(), then it was just because of pure laziness; it was an internal proof of concept project, and I was still concentrating on getting it working. Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc. --------------------------------Better to remain silent and be thought a fool than to speak out and remove all doubt. Abraham Lincoln -----Original Message----- From: Christopher Kings-Lynne [mailto:chriskl@familyhealth.com.au] Sent: Thursday, November 24, 2005 5:57 AM To: Mario Weilguni Cc: pgsql-hackers@postgresql.org; Pollard, Mike; Bill Bartlett; Fredrik Olsson Subject: Re: [HACKERS] MS SQL Server compatibility functions > Why do you use "GRANT ALL" and not "GRANT SELECT, UPDATE"? All means everybody > can do bad things with those sequences. GRANT ALL on a sequence IS GRANT SELECT & UPDATE. Chris