Postgres Pro
Downloads
Home   >   mailing lists

Re: plpgsql variable assignment not supporting distinct anymore - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: plpgsql variable assignment not supporting distinct anymore
Date
Msg-id CAFj8pRCicOt5+2A+qnYiZR+ryPDUV3Rof9nBZhy=K1jPkXNyYQ@mail.gmail.com
Whole thread Raw
Responses Re: plpgsql variable assignment not supporting distinct anymore
List pgsql-hackers
Tree view


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

pgsql-hackers by date:

Previous
From: Bharath Rupireddy
Date:
Subject: Re: Logical Replication - behavior of ALTER PUBLICATION .. DROP TABLE and ALTER SUBSCRIPTION .. REFRESH PUBLICATION
Next
From: "Joel Jacobson"
Date:
Subject: Re: Add primary keys to system catalogs