Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0B26C798CC9 for ; Wed, 17 Mar 2010 12:29:47 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 34551-10 for ; Wed, 17 Mar 2010 15:29:36 +0000 (UTC) X-Greylist: delayed 00:32:09.88283 by SQLgrey-1.7.6 Received: from hapkido.dreamhost.com (hapkido.dreamhost.com [66.33.216.122]) by mail.postgresql.org (Postfix) with ESMTP id 0D60D797EFF for ; Wed, 17 Mar 2010 12:29:36 -0300 (ADT) Received: from randymail-a1.g.dreamhost.com (caiajhbdcagg.dreamhost.com [208.97.132.66]) by hapkido.dreamhost.com (Postfix) with ESMTP id 88EAE178C10 for ; Wed, 17 Mar 2010 07:57:26 -0700 (PDT) Received: from otto (unknown [200.123.146.108]) by randymail-a1.g.dreamhost.com (Postfix) with ESMTP id CCDE018CEEF for ; Wed, 17 Mar 2010 07:57:20 -0700 (PDT) From: "Ignacio Balcarce" To: Subject: MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date Date: Wed, 17 Mar 2010 11:52:53 -0300 Message-ID: <000001cac5e1$8851dbe0$98f593a0$@balcarce@vivatia.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01CAC5C8.6304A3E0" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AcrF4YR7oLSGaSy8RaKT8Y7qkGvwHw== Content-Language: es-ar X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.148 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001, MSGID_MULTIPLE_AT=1.449, UPPERCASE_50_75=0.001 X-Spam-Level: X-Archive-Number: 201003/141 X-Sequence-Number: 33848 This is a multi-part message in MIME format. ------=_NextPart_000_0001_01CAC5C8.6304A3E0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi all, I am facing a problem trying to convert from MSSQL procedure to PostgreSQL function. CREATE PROCEDURE dbo.THUBAN_SP_GENERATEID @NEWID VARCHAR(20) OUTPUT AS SET @NEWID = ( SELECT REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','') + CAST(REPLICATE(0,8-LEN (ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS INTEGER),0) + 1)) AS VARCHAR) + CAST(ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS INTEGER),0) + 1 AS VARCHAR) FROM THUBAN_SEQ WHERE SUBSTRING(SEQ_ID,1,8)= REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','') ) INSERT INTO THUBAN_SEQ VALUES (@NEWID) SELECT @NEWID AS ITEM_ID; GO This is what I made, CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID() RETURNS VARCHAR AS $$ DECLARE NEWID VARCHAR; DECLARE SEQID VARCHAR; BEGIN SELECT INTO NEWID TO_CHAR(CURRENT_DATE::DATE, 'YYYYMMDD'); -- IF EXISTS A ROW IN THE TABLE STARTING WITH THE CURRENT_DATE, SELECT THE MAX OF THEM. IF EXISTS(SELECT(MAX(SEQ_ID)) FROM THUBAN_SEQ WHERE SEQ_ID LIKE (SELECT TO_CHAR(CURRENT_DATE::DATE, 'YYYYMMDD') || '%')) THEN SELECT INTO NEWID ((SELECT(MAX(SEQID)) FROM THUBAN_SEQ WHERE SEQ_ID LIKE NEWID || '%') + 1); ELSE -- THIS IS NOT RIGHT AT ALL, RIGHT? HOW CAN I DO TO CONCATENATE AN INTEGER NUMBER LIKE 14 + SOME NUMBER OF 0 BEFORE? SEQID := '00000001'; NEWID := NEWID + SEQID; END IF; RETURN NEWID; END; $$ LANGUAGE plpgsql; SELECT THUBAN_SP_GENERATEID(); Beside this, there is something than I would like to ask than I couldn't find. How can I do to set a variable in a way like this as MSSQL does: SET @NEWID = (SELECT.. And not doing SELECT INTO VARIABLE_TO_SET (SELECT... All comments will be welcome, I am pretty new with PostgreSQL but I find It very interesting. Thanks & Regards, Ignacio ------=_NextPart_000_0001_01CAC5C8.6304A3E0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi all,

 

I am facing a problem trying to = convert from MSSQL procedure to PostgreSQL function.

 

CREATE PROCEDURE dbo.THUBAN_SP_GENERATEID

 

         @NEWID VARCHAR(20)  OUTPUT

AS

 

 

 

 

        SET @NEWID =3D (

           &= nbsp;            SELECT REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','')

           &= nbsp;           &n= bsp;        + CAST(REPLICATE(0,8-LEN = (ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS

INTEGER),0) + 1)) = AS VARCHAR)

           &= nbsp;           &n= bsp;           &nb= sp;    + CAST(ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS INTEGER),0) + = 1  AS

VARCHAR)

           &= nbsp;            FROM THUBAN_SEQ

           &= nbsp;            WHERE SUBSTRING(SEQ_ID,1,8)=3D

REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','')

           &= nbsp;            )

 

 

        INSERT INTO THUBAN_SEQ VALUES (@NEWID)

 

        SELECT @NEWID AS ITEM_ID;

GO

 

This is what I made, =

 

CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID()

RETURNS = VARCHAR

AS $$

DECLARE NEWID = VARCHAR;

DECLARE         = ;    SEQID VARCHAR;

BEGIN

 

          =       SELECT INTO NEWID TO_CHAR(CURRENT_DATE::DATE, = 'YYYYMMDD');

 

          =       -- IF EXISTS A ROW IN THE TABLE STARTING WITH THE CURRENT_DATE, SELECT THE = MAX OF THEM.

 

          =       IF EXISTS(SELECT(MAX(SEQ_ID)) FROM THUBAN_SEQ WHERE SEQ_ID LIKE (SELECT TO_CHAR(CURRENT_DATE::DATE, 'YYYYMMDD') || '%')) = THEN

 

          =             &= nbsp;        SELECT INTO NEWID ((SELECT(MAX(SEQID)) FROM THUBAN_SEQ WHERE SEQ_ID LIKE NEWID = || '%') + 1);

          =             &= nbsp;       

          =       ELSE

          =             &= nbsp;        -- THIS IS NOT RIGHT AT ALL, RIGHT? HOW CAN I DO TO CONCATENATE AN INTEGER = NUMBER LIKE 14 + SOME NUMBER OF 0 BEFORE?

          =             &= nbsp;        SEQID :=3D '00000001';

          =             &= nbsp;        NEWID :=3D NEWID + SEQID;

          =             &= nbsp;       

          =       END IF;

          =      

RETURN = NEWID;

END;

$$ LANGUAGE = plpgsql;

 

SELECT = THUBAN_SP_GENERATEID();

 

Beside this, there is something = than I would like to ask than I couldn’t find. How can I do to set a = variable in a way like this as MSSQL does:

 

SET = @NEWID =3D (SELECT……

 

And not doing SELECT INTO VARIABLE_TO_SET = (SELECT…..

 

All comments will be welcome, I am pretty new with PostgreSQL but I find It very interesting.

 

Thanks & Regards,

 

 

Ignacio

------=_NextPart_000_0001_01CAC5C8.6304A3E0--