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.
Aucun commentaire:
Enregistrer un commentaire