Re: plpgsql variable assignment with union is broken

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> 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?

The OP is complaining that that gets a syntax error since c9d529848.

> OP's query provably returns a single row and ought to always assign
> true as written.

My opinion is that (a) it's useless and (b) there has never been any
documentation that claimed that you could do this.

As for (a), given the execution restriction that you can't return more
than one row, I can't think of anything you could do with this that
couldn't be done, both more clearly and far more cheaply, with a CASE
or similar construct.

As for (b), the docs have always given the syntax of plpgsql assignment as
"variable := expression"; whatever you might think an "expression" is,
I dispute that a syntactically-invalid fragment of a UNION query
qualifies. The fact that it was accepted is a completely accidental
artifact of the lax way that plpgsql assignment has been parsed up to now.

(Having said that, I would've preserved it if I could, but it's exactly
the fact that it *isn't* a syntactically valid UNION construct that
makes it hard to do so. If it's possible at all, it would take some
really ugly refactoring of the grammar.)

One last point is that if you're really intent on writing things this
way, you can still do it with SELECT INTO instead of assignment syntax.

In case you're wondering, INTERSECT and EXCEPT are in the same boat.

regards, tom lane