Re: MS SQL Server compatibility functions

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(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Bill Bartlett
Sent: Wednesday, November 23, 2005 10:01 AM
To: 'Fredrik Olsson'; pgsql-hackers(at)postgresql(dot)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(dot)olsson(at)treyst(dot)se
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org