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
