WITH upd AS ( UPDATE univ.products SET source = source::int || _source WHERE project_id = _project_id AND product_id = _products AND NOT(_source = ANY(source)) RETURNING id ) , ins AS ( INSERT INTO univ.products(project_id, product_id, source, tracked) SELECT _project_id, _product_id, ARRAY[_source], _tracked WHERE NOT EXISTS (SELECT 1 FROM upd) RETURNING id ) SELECT id FROM upd NATURAL FULL OUTER JOIN ins INTO _project_product_id;
My situation, I have this function which contains all the above and more, what I am trying to avoid is duplicating the project/products pair, but in the same time update the sources by appending new sources. Now the sources for each product/project pair need to be unique, thats why I've added
NOT(_source = ANY(source)), but if this is true the
select 1 from up will not return anything so will try to insert the records into the db to avoid this I've added the below exception.
EXCEPTION WHEN UNIQUE_VIOLATION THEN EXIT; END;
This is almost doing what I want, but is not returning any id is there a better way of doing this?
Also I have noticed that if I insert a record, then I insert the same record with a different source(the row id will be 1), and then I insert a different record the row id will be 3 instead of 2;
[EDIT] I've solved my problem by doing:
SET source = CASE WHEN NOT(source::int @> ARRAY[_source]) THEN source::int || _source ELSE source END
Is there a better solution? This one seems a bit slow.