vendredi 6 février 2015

Append elements to array only if the element does't exist



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