Re: MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date

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.