Re: plpgsql variable assignment with union is broken

čt 7. 1. 2021 v 4:20 odesílatel Merlin Moncure <mmoncure(at)gmail(dot)com> napsal:

> On Tue, Jan 5, 2021 at 3:40 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > easteregg(at)verfriemelt(dot)org writes:
> > > i found, that the behaviour of variable assignment in combination with
> union is not working anymore:
> > > DO $$
> > > DECLARE t bool;
> > > begin
> > > t := a FROM ( SELECT true WHERE false ) t(a) UNION SELECT true
> AS a;
> > > END $$;
> >
> > > is this an intended change or is it a bug?
> >
> > It's an intended change, or at least I considered the case and thought
> > that it was useless because assignment will reject any result with more
> > than one row. Do you have any non-toy example that wouldn't be as
> > clear or clearer without using UNION? The above sure seems like an
> > example of awful SQL code.
>
> What is the definition of broken here? What is the behavior of the
> query with the change and why?
>
> OP's query provably returns a single row and ought to always assign
> true as written. A real world example might evaluate multiple
> condition branches so that the assignment resolves true if any branch
> is true. It could be rewritten with 'OR' of course.
>
> Is this also "broken"?
> t := a FROM ( SELECT 'something' WHERE _Flag) t(a) UNION SELECT
> 'something else' AS a WHERE NOT _Flag;
>
> What about this?
> SELECT INTO t true WHERE false
> UNION select true;
>

ANSI SQL allows only SELECT INTO or var := SQL expression and SQL
expression can be (subquery) too

do $$
declare t bool;
begin
t := (SELECT true WHERE false UNION SELECT true );
end;
$$;

Regards

Pavel

> merlin
>
>
>