Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 2765C6322DD
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 18 Mar 2010 07:35:40 -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 68397-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 18 Mar 2010 10:35:29 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from moon.antora.ru (moon.antora.ru [89.111.189.180])
	by mail.postgresql.org (Postfix) with ESMTP id 049CC64351E
	for <pgsql-sql@postgresql.org>; Thu, 18 Mar 2010 07:35:16 -0300 (ADT)
Envelope-to: pgsql-sql@postgresql.org
Received: from moon.inner.space ([10.1.1.5] helo=moon.antora.ru)
	by moon.antora.ru with esmtp (Exim 4.67)
	(envelope-from <sad@bankir.ru>) id 1NsD4L-0004I3-2p
	for pgsql-sql@postgresql.org; Thu, 18 Mar 2010 13:35:13 +0300
Received: from [192.168.1.107] [178.34.53.117] by moon.antora.ru (Axigen)
	with (DHE-RSA-AES256-SHA encrypted) ESMTPSA id 1A47F1;
	Thu, 18 Mar 2010 13:35:12 +0300
Message-ID: <4BA20184.3080000@bankir.ru>
Date: Thu, 18 Mar 2010 13:33:40 +0300
From: silly sad <sad@bankir.ru>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.5)
	Gecko/20100111 Thunderbird/3.0
MIME-Version: 1.0
To: Ignacio Balcarce <ignacio.balcarce@vivatia.com>
CC: pgsql-sql@postgresql.org
Subject: Re: MSSQL to PostgreSQL - Issue trying to generate unique ID using
	actual date
References: <000001cac5e1$8851dbe0$98f593a0$@balcarce@vivatia.com>
In-Reply-To: <000001cac5e1$8851dbe0$98f593a0$@balcarce@vivatia.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Scan-Signature: 4c1c50e7a45403496e7b7de131a9065e
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.109 tagged_above=-10 required=5
	tests=BAYES_05=-1.11, UPPERCASE_50_75=0.001
X-Spam-Level: 
X-Archive-Number: 201003/143
X-Sequence-Number: 33850

On 03/17/10 17:52, Ignacio Balcarce wrote:
> 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
>

At a first glance
it looks like an

INSERT INTO thuban_seq(seq_id)
  VALUES (your_strange_string_processing( now() ))
   RETURNING seq_id;

But i couldn't interpret your extremely strange string processing with 
dates.
Please, FIRST OF ALL, get rid of this unnecessary brainfuck,
use postgres date-time arithmetic and clarify the idea of this routine.