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@joh.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