Skip site navigation (1) Skip section navigation (2)

Re: [PATCHES] extension for sql update

From: Rod Taylor <pg(at)rbt(dot)ca>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: pgsql-hackers(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Susanne Ebrecht <miracee(at)miracee(dot)de>
Subject: Re: [PATCHES] extension for sql update
Date: 2006-07-31 16:08:22
Message-ID: 1154362102.24186.394.camel@home (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
On Mon, 2006-07-31 at 17:26 +0200, Peter Eisentraut wrote:
> Am Mittwoch, 26. Juli 2006 22:58 schrieb Tom Lane:
> > The reason people want this syntax is that they expect to be
> > able to write, say,
> >
> > 	UPDATE mytab SET (foo, bar, baz) =
> > 	    (SELECT alpha, beta, gamma FROM othertab WHERE key = mytab.key);
> 
> I don't find any derivation in the standard that would permit this.  The only 
> thing I could find are variations on
> 
> SET (a) = x  -- no parentheses
> SET (a, b) = (x, y)
> SET (a, b) = ROW (x, y)
> 
> where x and y are some sort of value expression.  I would have expected the 
> sort of thing that you describe, but if you know how to derive that, I'd like 
> to see it.

I believe <contextually typed row value constructor element list> can be
one or more <value expressions> which includes a <row value expression>.
<row value expression> gives us the <row subquery> option.

For that matter the below portion of <contextually typed row value
constructor> gives us:
  | <left paren> <contextually typed row value constructor element>
<comma>
    <contextually typed row value constructor element list> <right
paren>

This breaks down into one or more comma separated <row subquery>s.

UPDATE tab SET (...) = ((SELECT foo, bar from a), (select bif,baz from
b));

-- 


In response to

pgsql-hackers by date

Next:From: Michael FuhrDate: 2006-07-31 16:15:51
Subject: Re: tg_trigtuple not NULL in AFTER STATEMENT triggers?
Previous:From: Andrew DunstanDate: 2006-07-31 15:58:30
Subject: Re: Connection limit and Superuser

pgsql-patches by date

Next:From: Jim C. NasbyDate: 2006-07-31 16:40:21
Subject: Re: [HACKERS] extension for sql update
Previous:From: Peter EisentrautDate: 2006-07-31 15:26:10
Subject: Re: [PATCHES] extension for sql update

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group