Results 1 to 1 of 1
  1. #1
    Join Date
    Apr 2011
    Posts
    4

    Unanswered: Problem with function merge_db

    Hello,

    I am using this function to update a database, but I need it not to check only one key, but two. This is if it only matches one key it's not the same record, then it will insert the row.

    Some insights?

    This is the example, and my function has the same structure:
    Code:
    CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
    
    CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
    $$
    BEGIN
        LOOP
            -- first try to update the key
            UPDATE db SET b = data WHERE a = key;
            IF found THEN
                RETURN;
            END IF;
            -- not there, so try to insert the key
            -- if someone else inserts the same key concurrently,
            -- we could get a unique-key failure
            BEGIN
                INSERT INTO db(a,b) VALUES (key, data);
                RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- do nothing, and loop to try the UPDATE again
            END;
        END LOOP;
    END;
    $$
    LANGUAGE plpgsql;
    EDIT: It's solved, adding in the update "and field=field", the other check field.

    Thanks.
    Last edited by ciglesias; 04-15-11 at 08:45.

Posting Permissions

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