BUG #6067: In PL/pgsql, EXISTS(SELECT ... INTO...) fails
The following bug has been logged online:
Bug reference: 6067
Logged by: David Fetter
Email address: dfetter@vmware.com
PostgreSQL version: 9.0.4
Operating system: Linux
Description: In PL/pgsql, EXISTS(SELECT ... INTO...) fails
Details:
Here's some example code that reproduces the problem:
CREATE OR REPLACE FUNCTION foo()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
i int;
BEGIN
IF EXISTS (SELECT 1 INTO STRICT i) THEN
RAISE NOTICE '%', a;
END IF;
RETURN;
END;
$$;
ERROR: syntax error at or near "i"
LINE 8: IF EXISTS (SELECT 1 INTO STRICT i) THEN
^
This came up in a case where there was an IF block that checked some
conditions before checking whether there was a row. If it found a row, it
was supposed to use it in an EXCEPTION. Instead, I had to do the query
unconditionally, check the FOUND block in a separate nested IF statement,
and generally uglify the code.
Not everybody in IRC agreed that this is a bug, though.
Hello
2011/6/18 David Fetter <dfetter@vmware.com>:
The following bug has been logged online:
Bug reference: 6067
Logged by: David Fetter
Email address: dfetter@vmware.com
PostgreSQL version: 9.0.4
Operating system: Linux
Description: In PL/pgsql, EXISTS(SELECT ... INTO...) fails
Details:Here's some example code that reproduces the problem:
CREATE OR REPLACE FUNCTION foo()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
i int;
BEGIN
IF EXISTS (SELECT 1 INTO STRICT i) THEN
RAISE NOTICE '%', a;
END IF;
RETURN;
END;
$$;
ERROR: syntax error at or near "i"
LINE 8: IF EXISTS (SELECT 1 INTO STRICT i) THEN
You cannot to use PLpgSQL's addition "INTO" inside SQL statement. This
is not bug. INTO and STRICT are not SQL keywords, so they cannot be
used inside SQL expression.
Regards
Pavel Stehule
Show quoted text
^
This came up in a case where there was an IF block that checked some
conditions before checking whether there was a row. If it found a row, it
was supposed to use it in an EXCEPTION. Instead, I had to do the query
unconditionally, check the FOUND block in a separate nested IF statement,
and generally uglify the code.Not everybody in IRC agreed that this is a bug, though.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
"David Fetter" <dfetter@vmware.com> writes:
IF EXISTS (SELECT 1 INTO STRICT i) THEN
RAISE NOTICE '%', a;
END IF;
Umm ... are you just complaining that the error message isn't very
helpful, or are you actually expecting that to do something useful?
If the latter, what exactly? I'm particularly confused by your use
of the STRICT option here, because if we did support that, I would
expect the STRICT to throw an error if there were not exactly one
matching row, making the EXISTS test 100% pointless.
But the short answer is that we don't support INTO in sub-selects,
and in general I doubt that we ever will, since in most cases the
behavior wouldn't be very well-defined. It might be worth a TODO
to provide a better error message than "syntax error", though.
regards, tom lane
Tom Lane wrote:
"David Fetter" <dfetter@vmware.com> writes:
IF EXISTS (SELECT 1 INTO STRICT i) THEN
RAISE NOTICE '%', a;
END IF;Umm ... are you just complaining that the error message isn't very
helpful, or are you actually expecting that to do something useful?
If the latter, what exactly? I'm particularly confused by your use
of the STRICT option here, because if we did support that, I would
expect the STRICT to throw an error if there were not exactly one
matching row, making the EXISTS test 100% pointless.But the short answer is that we don't support INTO in sub-selects,
and in general I doubt that we ever will, since in most cases the
behavior wouldn't be very well-defined. It might be worth a TODO
to provide a better error message than "syntax error", though.
Is it worth documenting, fixing, or adding this to the TODO list?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Tue, Nov 29, 2011 at 9:32 PM, Bruce Momjian <bruce@momjian.us> wrote:
Tom Lane wrote:
"David Fetter" <dfetter@vmware.com> writes:
IF EXISTS (SELECT 1 INTO STRICT i) THEN
RAISE NOTICE '%', a;
END IF;Umm ... are you just complaining that the error message isn't very
helpful, or are you actually expecting that to do something useful?
If the latter, what exactly? I'm particularly confused by your use
of the STRICT option here, because if we did support that, I would
expect the STRICT to throw an error if there were not exactly one
matching row, making the EXISTS test 100% pointless.But the short answer is that we don't support INTO in sub-selects,
and in general I doubt that we ever will, since in most cases the
behavior wouldn't be very well-defined. It might be worth a TODO
to provide a better error message than "syntax error", though.Is it worth documenting, fixing, or adding this to the TODO list?
At most I would say we could try to improve the error message.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Wed, Nov 30, 2011 at 03:36:11PM -0500, Robert Haas wrote:
On Tue, Nov 29, 2011 at 9:32 PM, Bruce Momjian <bruce@momjian.us> wrote:
Tom Lane wrote:
"David Fetter" <dfetter@vmware.com> writes:
� � IF EXISTS (SELECT 1 INTO STRICT i) THEN
� � � � RAISE NOTICE '%', a;
� � END IF;Umm ... are you just complaining that the error message isn't very
helpful, or are you actually expecting that to do something useful?
If the latter, what exactly? �I'm particularly confused by your use
of the STRICT option here, because if we did support that, I would
expect the STRICT to throw an error if there were not exactly one
matching row, making the EXISTS test 100% pointless.But the short answer is that we don't support INTO in sub-selects,
and in general I doubt that we ever will, since in most cases the
behavior wouldn't be very well-defined. �It might be worth a TODO
to provide a better error message than "syntax error", though.Is it worth documenting, fixing, or adding this to the TODO list?
At most I would say we could try to improve the error message.
I researched this and it seems to complex to improve the error message.
I am afraid it would have to bleed into the main backend parser.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +