Thread: cannot use result of (insert..returning)
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
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
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
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
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +