Re: plpgsql.consistent_into

On 1/14/14 12:28 PM, Marti Raudsepp wrote:
> I've always hated INTO in procedures since it makes the code harder to
> follow and has very different behavior on the SQL level, in addition
> to the multi-row problem you bring up. If we can make assignment
> syntax more versatile and eventually replace INTO, then that solves
> multiple problems in the language without breaking backwards
> compatibility.

I don't personally have a problem with INTO other than the behaviour
that started this thread. But I'm willing to consider other options.

> On Tue, Jan 14, 2014 at 4:30 AM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
>> On 2014-01-14 02:54, Marti Raudsepp wrote:
>>> But PL/pgSQL already has an assignment syntax with the behavior you want:
>>
>> According to the docs, that doesn't set FOUND which would make this a pain
>> to deal with..
>
> Right you are. If we can extend the syntax then we could make it such
> that "= SELECT" sets FOUND and other diagnostics, and a simple
> assignment doesn't. Which makes sense IMO:
>
> a = 10; -- simple assignments really shouldn't affect FOUND

With you so far.

> With explicit SELECT, clearly the intent is to perform a query:
> a = SELECT foo FROM table;
> And this could also work:
> a = INSERT INTO table (foo) VALUES (10) RETURNING foo_id;

I'm not sure that would work with the grammar. Basically what PL/PgSQL
does right now is for a statement like:

a = 1;

It parses the "a =" part itself, and then just reads until the next
unquoted semicolon without actually looking at it, and slams a "SELECT "
in front of it. With this approach we'd have to look into the query and
try and guess what it does. That might be possible, but I don't like
the idea.

> AFAICT the fact that this works is more of an accident and should be
> discouraged. We can leave it as is for compatibility's sake:
> a = foo FROM table;

I've always considered that ugly (IIRC it's still undocumented as well),
and would encourage people not to do that.

> Now, another question is whether it's possible to make the syntax
> work. Is this an assignment from the result of a subquery, or is it a
> query by itself?
> a = (SELECT foo FROM table);

That looks like a scalar subquery, which is wrong because they can't
return more than one column (nor can they be INSERT etc., obviously).

How about:

(a) = SELECT 1;
(a, b) = SELECT 1, 2;
(a, b) = INSERT INTO foo RETURNING col1, col2;

Same semantics: TOO_MANY_ROWS on rows > 1, sets FOUND and row_count.
AFAICT this can be parsed unambiguously, too, and we don't need to look
at the query string because this is new syntax.

Regards,
Marko Tiikkaja