Thread: 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: =C2=A0 =C2=A0 =C2=A06067 > Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0David Fetter > Email address: =C2=A0 =C2=A0 =C2=A0dfetter@vmware.com > PostgreSQL version: 9.0.4 > Operating system: =C2=A0 Linux > Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0In PL/pgsql, EXISTS(SELECT ... IN= TO...) fails > Details: > > Here's some example code that reproduces the problem: > > CREATE OR REPLACE FUNCTION foo() > RETURNS void > LANGUAGE plpgsql > AS $$ > DECLARE > =C2=A0 =C2=A0i int; > BEGIN > =C2=A0 =C2=A0IF EXISTS (SELECT 1 INTO STRICT i) THEN > =C2=A0 =C2=A0 =C2=A0 =C2=A0RAISE NOTICE '%', a; > =C2=A0 =C2=A0END IF; > =C2=A0 =C2=A0RETURN; > END; > $$; > ERROR: =C2=A0syntax error at or near "i" > LINE 8: =C2=A0 =C2=A0 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 > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0^ > > This came up in a case where there was an IF block that checked some > conditions before checking whether there was a row. =C2=A0If it found a r= ow, it > was supposed to use it in an EXCEPTION. =C2=A0Instead, I had to do the qu= ery > 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: >> > =A0 =A0 IF EXISTS (SELECT 1 INTO STRICT i) THEN >> > =A0 =A0 =A0 =A0 RAISE NOTICE '%', a; >> > =A0 =A0 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? =A0I'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. =A0It 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. --=20 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. +