I am trying to accomplish this:

update p2user.party set ADDR_KEY =
(insert into p2user.address ( street_no, street_name)
values ('22', 'Geek St.')
returning addr_key )
where party_key = 22 ;

But I am getting error:
ERROR: syntax error at or near "into"
LINE 1: update p2user.party set ADDR_KEY = (insert into p2user.addre...
^(is actually under the
"into")

The insert by itself works fine and returns a valid integer.

I know I could do this easily with a stored proc,
but it seems like an obvious use of the returning clause

Am I missing some syntax error


--
View this message in context: http://www.nabble.com/can-a-insert-w...p15248628.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Feb 2, 2008 6:22 PM, chrisj <chrisj.wood@sympatico.ca> wrote:
> I know I could do this easily with a stored proc,
> but it seems like an obvious use of the returning clause
>
> Am I missing some syntax error

Nope, you're not missing anything. When I initially wrote this for
Postgres, it didn't support all cases; including the one you're
running into. It's not extremely difficult to extend, but as there
are so few people asking for this functionality, no one has found it
important enough to warrant extension. Perhaps I'll get to it for
8.4.

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Saturday 02 February 2008 4:12 pm, Jonah H. Harris wrote:
> On Feb 2, 2008 6:22 PM, chrisj <chrisj.wood@sympatico.ca> wrote:
> > I know I could do this easily with a stored proc,
> > but it seems like an obvious use of the returning clause
> >
> > Am I missing some syntax error
>
> Nope, you're not missing anything. When I initially wrote this for
> Postgres, it didn't support all cases; including the one you're
> running into. It's not extremely difficult to extend, but as there
> are so few people asking for this functionality, no one has found it
> important enough to warrant extension. Perhaps I'll get to it for
> 8.4.

Would this be a variation of this item from the TODO:

* Allow INSERT/UPDATE ... RETURNING inside a SELECT 'FROM' clause

http://archives.postgresql.org/pgsql...9/msg00803.php
http://archives.postgresql.org/pgsql...0/msg00693.php


--
Adrian Klaver
aklaver@comcast.net

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Feb 2, 2008 7:40 PM, Adrian Klaver <aklaver@comcast.net> wrote:
> Would this be a variation of this item from the TODO:
>
> * Allow INSERT/UPDATE ... RETURNING inside a SELECT 'FROM' clause

Exactly.

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly