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