BUG #13708: strange behaviour instead of syntax error
The following bug has been logged on the website:
Bug reference: 13708
Logged by: Albert Nurgaleyev
Email address: yozh.ne@gmail.com
PostgreSQL version: 9.2.4
Operating system: Red Hat
Description:
In the code below - if I remove semicolon after "po_result := 'ERROR: No
uncollected prize found!'" then no syntax error occurs, return statement is
just ignored instead
create table test_table(
col1 INTEGER, col2 INTEGER
);
DO $$
DECLARE
v_status INTEGER;
po_result VARCHAR;
BEGIN
UPDATE test_table
SET col1 = 2
WHERE 2=3
RETURNING col2
INTO v_status;
IF NOT FOUND THEN
po_result := 'ERROR: No uncollected prize found!'
RETURN;
END IF;
RAISE EXCEPTION 'Error';
END;
$$;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
yozh.ne@gmail.com writes:
In the code below - if I remove semicolon after "po_result := 'ERROR: No
uncollected prize found!'" then no syntax error occurs, return statement is
just ignored instead
IF NOT FOUND THEN
po_result := 'ERROR: No uncollected prize found!'
RETURN;
END IF;
Hm, yeah. The problem is that plpgsql doesn't have any native
intelligence about what expressions can contain. It just takes
everything between ":=" and ";" and hands that to the main parser
with a SELECT in front, ie what you have here is
SELECT 'ERROR: No uncollected prize found!' RETURN;
which is legal syntax -- the RETURN is an AS-less column label.
I'm not sure about a reasonably low-effort way to fix this (and,
given the lack of previous complaints, I doubt it's worth a lot
of work). I experimented with forcibly adding an AS clause,
so that what would get parsed is
SELECT 'ERROR: No uncollected prize found!' RETURN AS collabel;
but the error message that the main parser would produce is along the
line of "ERROR: syntax error at or near "AS"", which would be quite
opaque from the user's viewpoint since there is no AS in the text.
Hmmm ... maybe just adding parens would do the trick.
regression=# SELECT ('ERROR: No uncollected prize found!' RETURN);
ERROR: syntax error at or near "RETURN"
That looks more promising ... though I'm not sure if there are
any cases it would break.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On 24 Oct 2015 00:03, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
Hmmm ... maybe just adding parens would do the trick.
regression=# SELECT ('ERROR: No uncollected prize found!' RETURN);
ERROR: syntax error at or near "RETURN"That looks more promising ... though I'm not sure if there are
any cases it would break.
That does seem like a good idea. There are plennty of keywords that can
follow a target list that would lead to strange behaviour, not the last of
which would be FROM... Adding parentheses should mean only a valid
expression should be accepted.
2015-10-24 14:04 GMT+02:00 Greg Stark <stark@mit.edu>:
On 24 Oct 2015 00:03, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
Hmmm ... maybe just adding parens would do the trick.
regression=# SELECT ('ERROR: No uncollected prize found!' RETURN);
ERROR: syntax error at or near "RETURN"That looks more promising ... though I'm not sure if there are
any cases it would break.That does seem like a good idea. There are plennty of keywords that can
follow a target list that would lead to strange behaviour, not the last of
which would be FROM... Adding parentheses should mean only a valid
expression should be accepted.
I used same technique in PLPSM - and it is working. But If I remember well,
there will be complication with multi assign SET (a,b,c) = (10,2,3) because
I had other level of nesting. But PLpgSQL doesn't support thes feature.
Cannot be used a bison functionality - "multiple start points"
http://www.gnu.org/software/bison/manual/html_node/Multiple-start_002dsymbols.html
?
Regards
Pavel