Thread: Re: plpgsql variable assignment not supporting distinct anymore

Re: plpgsql variable assignment not supporting distinct anymore

From
Pavel Stehule
Date:


pá 22. 1. 2021 v 9:21 odesílatel <easteregg@verfriemelt.org> napsal:
hi,

no noticed after the assignment with union ( https://www.postgresql.org/message-id/flat/20210105201257.f0d76aff%40mail.verfriemelt.org ), that the assignment with distinct is broken aswell.



  DO $$
  DECLARE
    _test bool;
  BEGIN

    _test := DISTINCT a FROM ( VALUES ( (true), ( true ) ) )t(a);

  END $$;

i would argue, that thats a way more common usecase than the union, which was merely bad code.

What is the sense of this code?

This is strange with not well defined behavior (in dependency on data type the result can depend on collate).

More - because this breaks simple expression optimization (10x), then the code will be significantly slower, than you use IF or CASE.

Regards

Pavel


tested with version 14~~devel~20210111.0540-1~299.gitce6a71f.pgdg110+1 from the apt repo

with kind redards,
richard

Re: plpgsql variable assignment not supporting distinct anymore

From
easteregg@verfriemelt.org
Date:
the code provided is just a little poc to get the error ( which i have not included with my first mail sorry. )

   ERROR:  syntax error at or near "DISTINCT"
   LINE 8:     _test := DISTINCT a FROM ( VALUES ( (true), ( true ) ) )...


the code in production looked like this:


    _resource_id :=
        DISTINCT ti_resource_id
           FROM tabk.resource_timeline
          WHERE ti_a2_id = _ab2_id
            AND ti_type = 'task'
    ;

this is backed up by a trigger function, that will ensure to every instance with the same ti_a2_id exists only one
ti_resource_id,hence the query can never fail due to more than one row beeing returned. but this syntax is not
supportedanymore, which will break BC. up until PG 13, the assignment statement was just an implizit SELECT
<expression>Query. 
Since Tom Lane didn't mentioned this change in the other thread, i figured the devteam might not be aware of this
chance.

i can refactor this line into

    _resource_id :=
        ti_resource_id
       FROM tabk.resource_timeline
      WHERE ti_a2_id = _ab2_id
        AND ti_type = 'task'
      GROUP BY ti_resource_id
    ;

but concerns about BC was already raised, although with UNION there might be far less people affected.
with kind regards, richard