cannot use result of (insert..returning)

Поиск
Список
Период
Сортировка
Искать

cannot use result of (insert..returning)

От:
"dvs" <dvs@fon.kamchatka.ru>
Дата:
Hello,

I need to use query like:   select (insert into test (a) values (x) returning b),c from anytable 
where condition
but it say   ERROR: syntax error at or near "into"

Is this a bug?

Function does not work too: create function addt(..) returning .. as 'insert ... returning ..' 
language 'sql'
ERROR:...
DETAIL: Function's final statement must be a SELECT.

BUT:  create function addt(..) returning .. as 
'insert...(nextval(..)...);select currval(..)' language 'sql'
work in   select addt(x),c from anytable where condition

but this function is analog of "insert...returning" in any case
Why analog work "better" then original?
What is my mistake? (I dont want use functions here!)

dvs

Re: cannot use result of (insert..returning)

От:
Bruce Momjian <bruce@momjian.us>
Дата:
Heikki Linnakangas wrote:
> dvs wrote:
> > Hello,
> > 
> > I need to use query like:
> >    select (insert into test (a) values (x) returning b),c from anytable 
> > where condition
> > but it say
> >    ERROR: syntax error at or near "into"
> > 
> > Is this a bug?
> 
> No, it's a known limitation.

Is there a TODO item for this?  I don't see one, do you?


--  Bruce Momjian          http://momjian.us EnterpriseDB                             http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

Re: cannot use result of (insert..returning)

От:
Bruce Momjian <bruce@momjian.us>
Дата:
Andrew Dunstan wrote:
> >>> I need to use query like:
> >>>    select (insert into test (a) values (x) returning b),c from anytable 
> >>> where condition
> >>> but it say
> >>>    ERROR: syntax error at or near "into"
> >>>
> >>> Is this a bug?
> >>>       
> >> No, it's a known limitation.
> >>     
> >
> > Is there a TODO item for this?  I don't see one, do you?
> >
> >   
> 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

Yes, but the TODO item talks about its use in the FROM clause, while the
failed query is using it in the target list.

Updated TODO with new URL is:

* Allow INSERT/UPDATE ... RETURNING inside a SELECT 'FROM' clause or target list
 http://archives.postgresql.org/pgsql-general/2006-09/msg00803.php http://archives.postgresql.org/pgsql-hackers/2006-10/msg00693.php http://archives.postgresql.org/pgsql-hackers/2008-06/msg00124.php


--  Bruce Momjian          http://momjian.us EnterpriseDB                             http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

Re: cannot use result of (insert..returning)

От:
"Heikki Linnakangas" <heikki@enterprisedb.com>
Дата:
dvs wrote:
> Hello,
> 
> I need to use query like:
>    select (insert into test (a) values (x) returning b),c from anytable 
> where condition
> but it say
>    ERROR: syntax error at or near "into"
> 
> Is this a bug?

No, it's a known limitation.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com

Re: cannot use result of (insert..returning)

От:
Andrew Dunstan <andrew@dunslane.net>
Дата:


Bruce Momjian wrote:
> Heikki Linnakangas wrote:
>   
>> dvs wrote:
>>     
>>> Hello,
>>>
>>> I need to use query like:
>>>    select (insert into test (a) values (x) returning b),c from anytable 
>>> where condition
>>> but it say
>>>    ERROR: syntax error at or near "into"
>>>
>>> Is this a bug?
>>>       
>> No, it's a known limitation.
>>     
>
> Is there a TODO item for this?  I don't see one, do you?
>
>   
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

?

cheers

andrew
FAQ