2014/1/14 Marko Tiikkaja <ma...@joh.to> > 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 <ma...@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); >> > only this form is allowed in SQL/PSM - and it has some logic - you can assign result of subquery (should be one row only) to variable.
> > 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; > > I prefer subquery only syntax - a := (some) or (a,b,c) = (some a,b,c) with possible enhancing for statements with RETURNING a advance is compatibility with DB2 (SQL/PSM) syntax - and this code is written now - it is done in my sql/psm implementation Regards Pavel > 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 >
