3
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.

  • 1
    The main of the query seems to be missing, and it's the part that should return results. – Daniel Vérité Feb 5 '15 at 17:58
  • What you really need is a generic version of the intarray idx function. I've wanted one often, but the C API for PostgreSQL arrays is horrible so I can never be bothered writing one to submit to core PostgreSQL. I suggest writing a simple SQL function that returns true if an element exists in an array and false otherwise, then using that in your expression. – Craig Ringer Feb 6 '15 at 4:24
  • @DanielVérité i've updated my question, sorry about that. – RockNinja Feb 6 '15 at 8:08
  • OK. But why trying to cram this complex logic into a single statement? That does not insulate from a race condition between concurrent transactions, if that was the purpose. – Daniel Vérité Feb 6 '15 at 13:28
1

It's not super efficient, but you can define something like your solution as a function:

create or replace function set_append(anyarray, anyelement)
  returns anyarray
  immutable
  PARALLEL safe
  language sql as
$$
  select case when $2=any($1) then $1 else $1 || $2 end;
$$;

You can also use that to define an aggregate:

CREATE AGGREGATE set_agg( ANYELEMENT ) (
  SFUNC = set_append,
  STYPE = ANYARRAY,
  INITCOND = '{}');

This will let you select set_agg(x order by y) to control the order of the distinct elements where you can't do select array_agg(distinct x order by y).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.