Re: plpgsql variable assignment not supporting distinct anymore

> Probably the fix is not hard, but it is almost the same situation as the
> UNION case. The result of your code is not deterministic
>
> If there are more different ti_resource_id then some values can be randomly
> ignored - when hash agg is used.
>
> The safe fix should be
>
> _resource_id := (SELECT ti_resource_id
>        FROM tabk.resource_timeline
>       WHERE ti_a2_id = _ab2_id
>         AND ti_type = 'task');
>
> and you get an exception if some values are ignored. Or if you want to
> ignore some values, then you can write
>
> _resource_id := (SELECT MIN(ti_resource_id) -- or MAX
>        FROM tabk.resource_timeline
>       WHERE ti_a2_id = _ab2_id
>         AND ti_type = 'task');
>
> Using DISTINCT is not a good solution.
>

in my usecase it was perfectly fine, because there is a constraint ensuring that here can never be more than on ti_resource_id at any given time for a given _ab2_id.
also, whenever there would be more data ( for example if the constraint trigger would have a bug ) you will get an error like this:


  create table a ( t int );
  insert into a values (1),(2);

  do $$
  declare _t int;
  begin
    _t := distinct t from a;
  end $$;

  Query failed: ERROR:  query "SELECT distinct t from a" returned more than one row
  CONTEXT:  PL/pgSQL function inline_code_block line 4 at assignment

no doubt, that this piece of code might not look optimal at first glance, but i like my code to fail fast. because with the min() approach, you will not notice, that the constraint trigger is not doing its job, until you get other strange sideeffects down the road.

richard