sql - Get the id when inserted or selected -

i want fill value of product_id. if article_code not in table, executes insert, if record exists don't know how select id of record , assign product_id.

the table "core_product" looks that:

• id
• article_code

here code (inside of function):

declare     product_id int; begin     insert core_product(article_code)          select new.article_code         not exists (             select id product_id             core_product             article_code = new.article_code         )         returning id product_id; end 

use special variable found:

declare     product_id int; begin     select id product_id     core_product     article_code = new.article_code;      if not found         insert core_product(article_code)          select new.article_code         returning id product_id;     end if; end 

---

if there unique constraint on article_code, can harden function against race condition using retry loop (as craig suggested in comment):

begin     loop         select id product_id         core_product         article_code = new.article_code;          if found             exit; -- exit loop         end if;          begin             insert core_product(article_code)              select new.article_code             returning id product_id;             exit; -- exit loop          exception when unique_violation             -- nothing, go beginning of loop             -- , check once more if article_code exists         end;     end loop;     -- product_id end;