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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers