| Lists: | pgsql-hackers |
|---|
| From: | "dvs" <dvs(at)fon(dot)kamchatka(dot)ru> |
|---|---|
| To: | <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | cannot use result of (insert..returning) |
| Date: | 2008-06-04 05:25:11 |
| Message-ID: | 006e01c8c603$5ca703b0$0e09a8c0@dvs |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
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
| From: | "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> |
|---|---|
| To: | "dvs" <dvs(at)fon(dot)kamchatka(dot)ru> |
| Cc: | <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: cannot use result of (insert..returning) |
| Date: | 2008-06-04 12:22:58 |
| Message-ID: | 48468922.9030108@enterprisedb.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
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
| From: | Bruce Momjian <bruce(at)momjian(dot)us> |
|---|---|
| To: | Heikki Linnakangas <heikki(at)enterprisedb(dot)com> |
| Cc: | dvs <dvs(at)fon(dot)kamchatka(dot)ru>, pgsql-hackers(at)postgresql(dot)org |
| Subject: | Re: cannot use result of (insert..returning) |
| Date: | 2008-06-12 00:50:04 |
| Message-ID: | 200806120050.m5C0o4O22459@momjian.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
| From: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
|---|---|
| To: | Bruce Momjian <bruce(at)momjian(dot)us> |
| Cc: | Heikki Linnakangas <heikki(at)enterprisedb(dot)com>, dvs <dvs(at)fon(dot)kamchatka(dot)ru>, pgsql-hackers(at)postgresql(dot)org |
| Subject: | Re: cannot use result of (insert..returning) |
| Date: | 2008-06-12 01:14:57 |
| Message-ID: | 48507891.9020807@dunslane.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
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
| From: | Bruce Momjian <bruce(at)momjian(dot)us> |
|---|---|
| To: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
| Cc: | Heikki Linnakangas <heikki(at)enterprisedb(dot)com>, dvs <dvs(at)fon(dot)kamchatka(dot)ru>, pgsql-hackers(at)postgresql(dot)org |
| Subject: | Re: cannot use result of (insert..returning) |
| Date: | 2008-06-12 18:09:09 |
| Message-ID: | 200806121809.m5CI99J11694@momjian.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +