0

I want to fill the value of product_id. If article_code is not in the table, it executes the insert, but if record exists I don't know how to select the id of that record and assign to product_id.

The table "core_product" looks like that:

  • id
  • article_code

Here the code (inside of a function):

DECLARE
    product_id int;
BEGIN
    INSERT INTO core_product(article_code) 
        SELECT NEW.article_code
        WHERE NOT EXISTS (
            SELECT id INTO product_id
            FROM core_product
            WHERE article_code = NEW.article_code
        )
        RETURNING id INTO product_id;
END
Ruben
  • 1,065
  • 5
  • 18
  • 44

1 Answers1

2

Use a special variable FOUND:

DECLARE
    product_id int;
BEGIN
    SELECT id INTO product_id
    FROM core_product
    WHERE article_code = NEW.article_code;

    IF NOT FOUND THEN
        INSERT INTO core_product(article_code) 
        SELECT NEW.article_code
        RETURNING id INTO product_id;
    END IF;
END

If there is an unique constraint on article_code, you can harden the function against a race condition using retry loop (as Craig suggested in a comment): 

BEGIN
    LOOP
        SELECT id INTO product_id
        FROM core_product
        WHERE article_code = NEW.article_code;

        IF FOUND THEN
            EXIT; -- exit loop
        END IF;

        BEGIN
            INSERT INTO core_product(article_code) 
            SELECT NEW.article_code
            RETURNING id INTO product_id;
            EXIT; -- exit loop

        EXCEPTION WHEN unique_violation THEN
            -- do nothing, go to the beginning of the loop
            -- and check once more if article_code exists
        END;
    END LOOP;
    -- do something with product_id
END;
klin
  • 112,967
  • 15
  • 204
  • 232
  • 1
    ... and before anyone asks, doing this as two statements instead of one like the original question doesn't change its concurrency properties significantly. The original is prone to a race condition that means it can still fail with a unique violation or insert a duplicate if there's no unique constraint. The version klin posted has the same race condition. The only way to avoid it use to use `insert ... on conflict ...` (from PostgreSQL 9.5), lock the table in exclusive mode for the insert, or use a retry loop. See http://stackoverflow.com/q/17267417/398670 – Craig Ringer Sep 30 '15 at 14:41