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

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

From pgsql-hackers-owner@postgresql.org Wed Nov 23 12:20:27 2005

X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id D62F7DB5DB
for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Wed, 23 Nov 2005 12:20:26 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 30039-04
for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
Wed, 23 Nov 2005 16:20:24 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from xor.sai.msu.ru (xor.sai.msu.ru [212.192.243.97])
by svr1.postgresql.org (Postfix) with ESMTP id 0800DD7AD7
for <pgsql-hackers@postgresql.org>; Wed, 23 Nov 2005 12:20:22 -0400 (AST)
Received: from [127.0.0.1] (localhost [127.0.0.1])
by xor.sai.msu.ru (Postfix) with ESMTP id 8CD861706E
for <pgsql-hackers@postgresql.org>; Wed, 23 Nov 2005 19:20:18 +0300 (MSK)
Message-ID: <438496C2.4090101@sigaev.ru>
Date: Wed, 23 Nov 2005 19:20:18 +0300
From: Teodor Sigaev <teodor@sigaev.ru>
User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.7.12) Gecko/20050927
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Pgsql Hackers <pgsql-hackers@postgresql.org>
Subject: core dump on 8.1 and no dump on REL8_1_STABLE
Content-Type: multipart/mixed;
boundary="------------060000060607080807090202"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level:
X-Archive-Number: 200511/1225
X-Sequence-Number: 76507

This is a multi-part message in MIME format.
--------------060000060607080807090202
Content-Type: text/plain; charset=KOI8-R; format=flowed
Content-Transfer-Encoding: 8bit

Hi!

Attached dump cause core on 8.1 release and works fine on REL8_1_STABLE and HEAD.
Am I missed some fixes/commits?

PS dump in KOI8, db should be initialized as
initdb -E KOI8-R --locale ru_RU.KOI8-R -D $DIR
and it should be installed ltree and tsearch2 modules.

PPS gdb output:
Program received signal SIGSEGV, Segmentation fault.
0xb70f3f55 in crc32_sz (buf=0x1c4fabe1 <Address 0x1c4fabe1 out of bounds>,
size=1282) at crc32.c:101
101 _CRC32_(crc, *p);
(gdb) bt
#0 0xb70f3f55 in crc32_sz (buf=0x1c4fabe1 <Address 0x1c4fabe1 out of bounds>,
size=1282) at crc32.c:101
#1 0xb70f5f9d in gtsvector_compress (fcinfo=0xffffffff) at gistidx.c:161
#2 0x0821e8fd in FunctionCall1 (flinfo=0xffffffff, arg1=16777215) at fmgr.c:1128
#3 0x0807f752 in gistcentryinit (giststate=0xffffffff, nkey=0, e=0xbfdfb0c4,
k=4294967295, r=0x0, pg=0x0, o=0, b=-1,
l=1 '\001', isNull=0 '\0') at gistutil.c:731
#4 0x0807f0e0 in gistinsert (fcinfo=0xffffffff) at gist.c:255
#5 0x0821ecbe in FunctionCall6 (flinfo=0xffffff, arg1=4294967295,
arg2=4294967295, arg3=4294967295, arg4=4294967295,
arg5=4294967295, arg6=4294967295) at fmgr.c:1267
#6 0x08091d1a in index_insert (indexRelation=0xb7121620, values=0xbfdfb39c,
isnull=0xbfdfb41c "", heap_t_ctid=0x837781c,
heapRelation=0xb7172ab8, check_uniqueness=0 '\0') at indexam.c:215
#7 0x08131f23 in ExecInsertIndexTuples (slot=0x8373900, tupleid=0x837781c,
estate=0x8373530, is_vacuum=0 '\0')
at execUtils.c:936
#8 0x0812ac67 in ExecutorRun (queryDesc=0x8373150,
direction=ForwardScanDirection, count=0) at execMain.c:1695
#9 0x081ab6a1 in ProcessQuery (parsetree=Variable "parsetree" is not available.
) at pquery.c:174
#10 0x081acb6a in PortalRun (portal=0x8370f78, count=2147483647, dest=0x82c517c,
altdest=0x82c517c,
completionTag=0xbfdfb688 "") at pquery.c:1076
#11 0x081a892f in exec_simple_query (
query_string=0x83591f8 "UPDATE \"�����������\" SET \"rubriks\"='\\'11\\'
\\'17\\' \\'116\\' \\'135\\' \\'253\\' \\'303\\' \\'409\\' \\'415\\' \\'772\\'
\\'1276\\' \\'1283\\' \\'1284\\' \\'1438\\' \\'1456\\'' WHERE \"id\"='66.20815';\n")
at postgres.c:1014
#12 0x081aa230 in PostgresMain (argc=1, argv=0x8307248, username=0x83094a8
"postgres") at postgres.c:3168
#13 0x08148fb4 in main (argc=6, argv=0x8307248) at main.c:322

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

--------------060000060607080807090202
Content-Type: application/x-tar;
name="tsearch2_crash.dump.gz"
Content-Transfer-Encoding: base64
Content-Disposition: inline;
filename="tsearch2_crash.dump.gz"

H4sICCeLhEMAA3RzZWFyY2gyX2NyYXNoLmR1bXAApVhtb9vWFf5M/YoLoQBt9IolqTdHRoHR
ttoaUWTPltcFyybQ0o3MWSIFkorrfdhvKx2zLmOZdCR5TjwgQLe1QzAUM5oNBfqp5/JFfLHc
AFsi8JLnnHvvuefleW5SKOQKBdSUB6SGbif26eXEeTlzXq6i1vEQRC1prVFfRbudAzKQa2g4
2u8rnVW0daQSHT41w+zpxABreb9PjKHcgTmwYC63vlOXWvVgPsonVs6jpRyCP/mfrHPr5o1z
fuuN3TwyyRcmam61UHOv0cCBxVv31D6fBrpQ9C/v9J/exDu18khRTdIjenbSd9adKZPLq7To
H95FHhkDud+HRULZv79y0kY/WC88d75NtNj4yzf29Wv73di7tlLm76wXs6QA4qX0kwbfeZML
qz31Tqaza9t1venMSS1wO3Eu7DeW69hpX62ZdetNx29vwOeubBK0Uf9E2mu0kKodLS1nj+9H
Z3E4/zt2nqd9/sE+H1sXKdnwQFNJW+lmzv0jJMudOk4c9sgLPjr/5cSb3quWO6aiqcZ96qGu
dMi9WkVVEx4aUItm4ntfVg8Tn4dGUqckVfmONhgQ1UyeV9O7RG9nkzUkegcMkToaEHBtqYLF
5UCjayO129ZltUfmWkGcq2VoB0L3SFYCTPC3UdRe28+gqQyIYcqDITpSzAP/E/0J4h7Yq+To
3lCoXaN9v3Nd0leeEf243YHGXOSePtrXFRoh4xnpmFqYXcMEp01I+hcZhdzt6ulCgGaHVe/z
TifdNixGzznSk/GcO6aTHtTB4vi3Ybtktv5jj+2vaAvuK724T0OpuFhcTIvTDrWJ2ltQ/lSc
Roxkk9/RJnHrjnJBk9+x8Vv0jtTvzqxUVuW+Yiqde7suBo3s1APZaCvqUw3ta1qfyOp86lO5
b5AoPH42IiScm1QqGewAPOjTIsmIVaCNdqDwn1mpH/CEpqvQZHTMDLhQiFuEbeFBiNxNLtJR
zOOgnzIQ7kNXtrbz39t/OYsxMSx4Iuudg4zlH0d6m7qesBtA/GnUU+5ClfqBDVqZPgL5+lZz
t7UjbTZbKb5rR4iaR+uf1dcfoqWlhE9/RuwffscXHvz+ww/YWo3uvLycW17N5XJSo1XfCfkz
oF0uTaNbnzepwVbMwjnKvMDmG7Ipo6ea/h5aRxtSS3ovtwdsvrX9OMviaQLHc7rGSZbGISHj
OQ/jgH5xyLg4IlqcJVcccioOqBQvZlCcIk6c5ksc8SGO2A/HpIfnXIcT/IYjJsMRZ+GQnTDl
IRzQD/ZZBwPZYMoxOKYWnEQ0HBEJTvIGjlkC3yUH7OM/TiI9TgM7jmAcJ5Ab+2CNQ4DGGeTD
WQTGWeDFMd7iBMjiBLJmF6XdjdMomk1iJLyDmYuzGalihMRJYMRzPMR3kA/PAQ+j6Ig0szEY
4Qww4TkchVVD1wCswQmECWsEIh33LA7hA88RA0dAgdPgsIw+2dl6BEnqKupqLv/N9cz75urU
8f5uu7NJnqmIvFASGYERy1W+xIvMCSMIIvOkyQgrAmOBtUWtp850Zl+8xohKps6Za42dG+u5
42A0+9J13DMYPdeZeNfnFkaX3ql3NrGv0em1c2bN7PFLjLyvJxYEuHDpOdNzyMpLFKxpIWlk
auuaqhKoH8ed2RPXnhUurDPrxnGZA9Mc1j766OjoiJPlDjfSOX3EwNuvRrrc5zS9x7y9pd6K
PF8uCHxBFJlvz2HLieOeXE3OmKsxh/5mjx0XNj3zqGWxVGR4+Euf8PkLP1izWOAF+CGer/m/
D/kyI5aE0IAVBBaxQpU+hAp9FsvwFMtFeBZ5+izxD+hToPJqVaQ2YtU3FVeKwVCiQ6m44g/l
CsuwV2N4P5l7zTJCtfRATPoL2arSg0RZmytofgp+yqzLS2tiza4mkBBd7ioaMuUhgbrs+FUF
5WQeECRD8A2lByWNjGPDJIM4oaamQpMxeemZqUkjWKC1Czig5+k29UMoLejB/RFkgMZ4DVpb
NQ61nsYUfT+fMpUK/DiRXxHKjMVdczPO4yzuijvlHHh7ztmcC7IJI3PPOJPTOJkbcV1OgTeT
MzjCqTDqtDJ5moNSQfTXFRg2PLQfaT8MrJ9Ulp6drQnVNcTSamNrKxJi/ViAdAWkE/vCc2ds
Tays4SIPguCobK0IhmHFgWlJwkIFtGGBgoSXsCjAHAGkUYmCmE6Lap2tleErKvhXbK0SK2ev
Am1Y+69fsLUH8DnvGLZWgs+r6cxyg0+xtIZF6jFNT8fvDdhOkGhV3HiX1pQiFSwqFsGpItj5
jRolHEzLML8ayi+8E2f8yqW7iiLYi6E8bmNQSOEacbPDKlIQyETbsrUqCK+dUztRXzCdTwSm
EEdSlJjvnb+eOy56x33LxX0qFopC7gmXiy4Ld+4I7eEhOY4uCvGl5n/5T4DkHWar2chcIfxb
k7Sxcf/NCRzJo+2dzUfSzmP0sP4YLSld/3KU8h2odyirCjHaATC3Q3oEWqTMGB1ls7lR/+3/
8V8Z/vz3bQbHzNyT9nY3m5+iHlANWoqIY87f4XGSp4n8CpyW1huxy4XFl7Od+m+2HtaR1GjQ
3XfhlvlICpcJCGh7b62xub76fsP4JvnpjgTZWGiZunH+sl20cXTC7WDe7q8b9N4s78sGXKBH
8M9gGtU+MQm129veoOFOR3G3DrURhi3/MfsEQP8Ji2CoBoNQCcZi2R+BBvwRiMAfgQqCUQj0
QAeBPRBC+LJSjF5KwQvQQvhSBhsWff5ZfQfcgov8x2wEruxq7mfcHgBNQRMAAA==
--------------060000060607080807090202--

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

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