Thread: 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;
$$;

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

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