CODEWITHSUNDEEP
sqlpostgresqlsubquerysql-insert
Ruben
Ruben

Reputation: 1091

Get the id when inserted or selected

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:

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

Upvotes: 0

Views: 49

Answers (1)

klin
klin

Reputation: 121604

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;

Upvotes: 2

Related Questions