Thread: can a insert with a returning clause be subquery ?
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-with-a-returning-clause-be-subquery---tp15248628p15248628.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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/
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-general/2006-09/msg00803.php http://archives.postgresql.org/pgsql-hackers/2006-10/msg00693.php -- Adrian Klaver aklaver@comcast.net
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/