Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 18D35632277 for ; Thu, 18 Mar 2010 14:58:40 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 90052-07 for ; Thu, 18 Mar 2010 17:58:23 +0000 (UTC) X-Greylist: from auto-whitelisted 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 97A186328D9 for ; Thu, 18 Mar 2010 14:58:25 -0300 (ADT) Received: from randymail-a10.g.dreamhost.com (caiajhbdcahe.dreamhost.com [208.97.132.74]) by hapkido.dreamhost.com (Postfix) with ESMTP id BF2CA17AE0C for ; Thu, 18 Mar 2010 10:58:24 -0700 (PDT) Received: from otto (unknown [200.123.146.108]) by randymail-a10.g.dreamhost.com (Postfix) with ESMTP id 1CD64D0967; Thu, 18 Mar 2010 10:58:18 -0700 (PDT) From: "Ignacio Balcarce" To: "'Justin Graf'" Cc: References: <000001cac5e1$8851dbe0$98f593a0$@balcarce@vivatia.com> <4BA25C8C.10305@magwerks.com> In-Reply-To: <4BA25C8C.10305@magwerks.com> Subject: Re: MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date Date: Thu, 18 Mar 2010 14:53:52 -0300 Message-ID: <000b01cac6c3$f9f58520$ede08f60$@balcarce@vivatia.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000C_01CAC6AA.D4A84D20" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AcrGvL9aAoVEnKejSMmvgYdQ6FFZAAABsIjg Content-Language: es-ar X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.149 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001, MSGID_MULTIPLE_AT=1.449 X-Spam-Level: X-Archive-Number: 201003/152 X-Sequence-Number: 33859 This is a multi-part message in MIME format. ------=_NextPart_000_000C_01CAC6AA.D4A84D20 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Justin, =20 Thanks in advance for your email. I forgot to tell than everyday IDs = must start from 0. So=E2=80=A6 sequence id would look like: YYYYMMDD = 00000001, YYYYMMDD 00000002, etc. =20 Is there any way to make this sequence start from 0 every day? =20 Thanks & Regards, =20 Ignacio =20 De: Justin Graf [mailto:justin@magwerks.com]=20 Enviado el: Jueves, 18 de Marzo de 2010 02:02 p.m. Para: Ignacio Balcarce CC: pgsql-sql@postgresql.org Asunto: Re: [SQL] MSSQL to PostgreSQL - Issue trying to generate unique = ID using actual date =20 On 3/17/2010 9:52 AM, Ignacio Balcarce wrote:=20 Hi all, =20 I am facing a problem trying to convert from MSSQL procedure to = PostgreSQL function.=20 =20 CREATE PROCEDURE dbo.THUBAN_SP_GENERATEID =20 @NEWID VARCHAR(20) OUTPUT AS =20 =20 =20 =20 SET @NEWID =3D ( 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)=3D REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','') ) =20 =20 INSERT INTO THUBAN_SEQ VALUES (@NEWID) =20 SELECT @NEWID AS ITEM_ID; GO =20 I surprised this works in MSSQL CREATE SEQUENCE THUBAN_SEQ INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1; Now for the function to generate the ID with the date leading=20 CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID() RETURNS VARCHAR AS $$ BEGIN --now we get the next value from the thuban_seq and add the date to the = front. =20 return to_char( current_timestamp, 'MMDDYYYY')::varchar || = nextval('THUBAN_SEQ')::varchar =20 RETURN NEWID; END; $$ LANGUAGE plpgsql; If this is not what your after you need to give more information what = you want to accomplish=20 All legitimate Magwerks Corporation quotations are sent in a .PDF file = attachment with a unique ID number generated by our proprietary = quotation system. Quotations received via any other form of = communication will not be honored.=20 CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain = legally privileged, confidential or other information proprietary to = Magwerks Corporation and is intended solely for the use of the = individual to whom it addresses. If the reader of this e-mail is not the = intended recipient or authorized agent, the reader is hereby notified = that any unauthorized viewing, dissemination, distribution or copying of = this e-mail is strictly prohibited. If you have received this e-mail in = error, please notify the sender by replying to this message and destroy = all occurrences of this e-mail immediately.=20 Thank you.=20 ------=_NextPart_000_000C_01CAC6AA.D4A84D20 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Justin,

 

Thanks = in advance for your email. I forgot to tell than everyday IDs must start from 0. = So=E2=80=A6 sequence id would look like: YYYYMMDD 00000001, YYYYMMDD = 00000002, = etc.

 

Is there = any way to make this sequence start from 0 every day?

 

Thanks = & Regards,

 

Ignacio

 

De: Justin Graf [mailto:justin@magwerks.com]
Enviado el: Jueves, 18 de Marzo de 2010 02:02 p.m.
Para: Ignacio Balcarce
CC: pgsql-sql@postgresql.org
Asunto: Re: [SQL] MSSQL to PostgreSQL - Issue trying to generate = unique ID using actual date

 

On 3/17/2010 9:52 AM, Ignacio Balcarce wrote: =

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

 



I surprised this works in MSSQL

CREATE SEQUENCE THUBAN_SEQ
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;


Now for the function to generate the ID with the date leading =

CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID()
RETURNS VARCHAR

AS $$

BEGIN

--now we get the next value from the thuban_seq and add the date to = the  front. 

return  to_char( current_timestamp,   = 'MMDDYYYY')::varchar || nextval('THUBAN_SEQ')::varchar

          =      

RETURN = NEWID;

END;

$$ LANGUAGE plpgsql;

If this is not what your after you need to give more information what = you want to accomplish




All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary = quotation system. Quotations received via any other form of communication will not = be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain = legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom = it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized = viewing, dissemination, distribution or copying of this e-mail is strictly = prohibited. If you have received this e-mail in error, please notify the sender by = replying to this message and destroy all occurrences of this e-mail immediately. =
Thank you.

------=_NextPart_000_000C_01CAC6AA.D4A84D20--