Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2010
    Posts
    5

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

    Hi all, this is my first post here.

    I am writing because I'm facing a problem trying to convert from MSSQL procedure to PostgreSQL function.
    Code:
    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,

    Code:
    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

  2. #2
    Join Date
    May 2008
    Posts
    277
    This seems like an extraordinarily bad way to create ids. Why not just do this?

    Code:
    CREATE TABLE foo (
        id SERIAL PRIMARY KEY,
        creation_date DATE NOT NULL DEFAULT CURRENT_DATE
        ...
    );

  3. #3
    Join Date
    Mar 2010
    Posts
    5
    Quote Originally Posted by futurity View Post
    This seems like an extraordinarily bad way to create ids. Why not just do this?

    Code:
    CREATE TABLE foo (
        id SERIAL PRIMARY KEY,
        creation_date DATE NOT NULL DEFAULT CURRENT_DATE
        ...
    );
    futurity, I thought the same when they asked me to translate this. The thing is that they like to have their ids in that way YYYYMMDD + 0000...ID, for that table.

    I am pretty new with PostgreSQL sintax (SQL 99), I am used to use MSSQL. But I find PostgreSQL very nice and I would like to improve my sintax.

    Did you see anything that could be better in this function?

    This is how table was created.

    Code:
    CREATE TABLE THUBAN_SEQ ( 
    	SEQ_ID	varchar(20) NOT NULL 
    	);

    Thanks for your answer.


    Ignacio
    Last edited by IgnacioSebastian; 03-17-10 at 13:06.

  4. #4
    Join Date
    May 2008
    Posts
    277
    Do the ids really need to be stored like that in the database, or just displayed that way upon retrieval? Just because something needs to be displayed in a certain way doesn't mean it needs to be recorded like that in the database.

  5. #5
    Join Date
    Mar 2010
    Posts
    5
    Quote Originally Posted by futurity View Post
    Do the ids really need to be stored like that in the database, or just displayed that way upon retrieval? Just because something needs to be displayed in a certain way doesn't mean it needs to be recorded like that in the database.
    They really need to be recorded like that. How could you change my code to could get that done??? Any ideas?

    Thanks & Regards,


    Ignacio

  6. #6
    Join Date
    May 2008
    Posts
    277
    Here's how I'd do it with an insert trigger:
    Code:
    CREATE TABLE foo (
        creation_date   DATE NOT NULL DEFAULT CURRENT_DATE,
        creation_seq    INT NOT NULL,
        PRIMARY KEY (creation_date, creation_seq)
    );
    
    CREATE OR REPLACE FUNCTION insert_foo()
    RETURNS TRIGGER
    AS $$
    BEGIN
        SELECT COALESCE(MAX(creation_seq), 0) + 1
        INTO NEW.creation_seq
        FROM foo
        WHERE creation_date = NEW.creation_date;
        
        RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
    
    CREATE TRIGGER insert_foo
    BEFORE INSERT ON foo
    FOR EACH ROW EXECUTE PROCEDURE insert_foo();
    Upon retrieval:
    Code:
    SELECT to_char(creation_date, 'YYYYMMDD') || lpad(creation_seq::char, 10, '0') AS id
    FROM foo;
    If you really need to store the id in the table, then just add it as an additional column and populate it in the trigger (using the SELECT statement as a guide).

    edit: lpad() truncates strings that are longer than the specified length. Since the max value an INT can hold is 2147483647, lpad needs to have a length of (at least) 10. I've updated the code for this.
    Last edited by futurity; 03-17-10 at 13:41.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Be aware that this will not work in a multi-user environment when more than one user inserts rows into that table.

  8. #8
    Join Date
    Mar 2010
    Posts
    5
    No?? Well, it is a multi-user enviroment, so I need another thing to try. But, just to know, how do you know that this solution doesn't work in a multi user enviroment?

    It will be great for me to improve the function than I have already made. I think with some improvements I could get the job done.

    Whats your opinion guys ??
    Last edited by IgnacioSebastian; 03-17-10 at 17:22.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by IgnacioSebastian View Post
    No?? Well, it is a multi-user enviroment, so I need another thing to try. But, just to know, how do you know that this solution doesn't work in a multi user enviroment?
    Because the max() function will not work in that situation.

    Consider the following:

    Let's assume the current highest seq_id is 100

    If two transactions do the insert at the same time, both will get 100, and will both increment it by one. But essentially both will insert an ID with 101 at the end. Now when it comes to committing there might be an error (because of the concurrent updates to seq_id) or if transaction 1 commits before transaction 2 updates seq_id the seq_id will correctly show 102 but you still have two identical IDs in the database.

    This can only be prevented if you exclusively lock the table for the entire transaction that needs a new generated ID. That will work, but is not very scalable (because the users must wait for each other)

    If you can afford gaps in the IDs then use a sequence and append that to the date:
    Code:
    CREATE SEQUENCE seq_id;
    SELECT to_char(current_date, 'YYYYMMDD') || to_char(nextval('seq_id'), 'FM00000000');
    A sequence is transaction safe, will never return the same number twice but might lose numbers (e.g. if the transaction rolls back due to an error)

  10. #10
    Join Date
    May 2008
    Posts
    277
    Thanks for pointing that out, shammat. I was wondering about that after I posted.

    Quote Originally Posted by shammat
    A sequence is transaction safe, will never return the same number twice but might lose numbers (e.g. if the transaction rolls back due to an error)
    This is what I originally suggested, but it looks like from the original code snippet he wants the counter to reset every day.

  11. #11
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by futurity View Post
    This is what I originally suggested, but it looks like from the original code snippet he wants the counter to reset every day.
    Oh, I missed that

    Well he could simply run a cron job that resets the sequence every midnight.

  12. #12
    Join Date
    Mar 2010
    Posts
    5
    Thanks for your help guys! But unfortunally, I don't have the chance to change the tables. They need to keep the original table designs and just rewrite that awful procedure.

    Furthermore, this procedure is being called from Java Code, this is another reason of why they dont want to change tables and procedures.

    I am agree that making a function which will be called any time that trigger is execute by an action is the best way to perform this.

    Is there any chance to change my code or make something similar trying to get the last id, substringing it, and then adding the next number from the last number prior to the actual date?

    Thanks for all your replies.
    Last edited by IgnacioSebastian; 03-18-10 at 10:26.

  13. #13
    Join Date
    May 2008
    Posts
    277
    Code:
    CREATE OR REPLACE FUNCTION insert_foo()
    RETURNS TRIGGER
    AS $$
    DECLARE
        new_id      INT;
        new_date    CHAR(8) := to_char(CURRENT_DATE, 'YYYYMMDD');
    BEGIN
        -- acquire table lock here
        
        SELECT COALESCE(MAX(substring(id from 9)::int), 0) + 1
        INTO new_id
        FROM foo
        WHERE id LIKE new_date || '%';
    
        NEW.id := new_date || lpad(new_id::text, 8, '0');
    
        RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
    I've never had to use table locks, and frankly, I find the documentation on them confusing. Perhaps someone else can help with what the appropriate table lock you need is.

  14. #14
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    maybe...

    don't reset the sequence, but, each time it's called (via the insert trigger,) the trigger updates a log table. The log table contains a date column and the max sequence for that date. If a date record does not exist for the current date, one is added.

    When the trigger stores the current value of the sequence, it retrieves the max sequence number for the most recent prior date. Which it subtracts from the current sequence and inserts this 'resetting' sequence value into the data table.

    This approach is ... Convoluted. Resetting the sequence each night at midnight would be simpler and cleaner. Note that you can grant reset privileges to your function (which would reset the sequence,) but revoke sequence reset to other roles/users...
    • Create a group role used for resetting the sequence.
    • Write a function used to trigger the sequence reset. Something similar to
      Code:
      CREATE OR REPLACE FUNCTION reset_test_seq()
        RETURNS integer AS
          $BODY$
          select setval('test_seq',1)::integer
          $BODY$
        LANGUAGE 'sql' VOLATILE;
    • Revoke update on the sequence to public.
    • Grant usage on the sequence to public.
    • Grant update on the sequence to the reset group role.
    • Grant execute privilege to the function to the group role or user who is 'authorized' to reset the sequence.
    Last edited by loquin; 03-31-10 at 19:13.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •