From: Ignacio Balcarce <ignacio.balcarce@vivatia.com>
To: pgsql-sql@postgresql.org
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> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ignacio.balcarce@vivatia.com
Subject: Re: MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date
In-Reply-To: <000001cac5e1$8851dbe0$98f593a0$@balcarce@vivatia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox