Re: plpgsql variable assignment not supporting distinct anymore

pá 22. 1. 2021 v 15:10 odesílatel <easteregg(at)verfriemelt(dot)org> napsal:

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

ok

then you don't need to use group by or DISTINCT

just use

_t := (SELECT ...);

The performance will be same and less obfuscate and you will not use
undocumented feature

Regards

Pavel

> richard
>