Re: strange construct with RETURN within plpgsql

2018-02-16 13:31 GMT+01:00 mariusz <marius(at)mtvk(dot)pl>:

>
> hello all,
>
> i just noticed some strange thing in plpgsql, that is keyword RETURN is
> allowed as noop after a valid statement.
> shame on me, after so many years of using plpgsql i happened to write a
> bug omitting semicolon after statement just before RETURN, and so i
> found that "special"? construct.
>
> this may be a parser oversight or something, but how could it be that i
> am the only person that forgot semicolon at least once?
>
> this may be somewhat dangerous within function with OUT parameters
> allowing RETURN statement without expression, as in example below
> (RETURN with expression produces syntax error when creating function).
> such a bug with missing semicolon is easy to spot and fix, but
> nonetheless is possible to create when parser allows something like
> this.
>
> so, if there is a reason for such a construct and it does something i
> didn't notice, please let me know what is the purpose of keyword RETURN
> after a valid statement.
>
> let me show very simplified example (not the real life one i was working
> on, datatype doesn't matter here). of course it could be even simpler,
> but intensionally i put early return within conditional block to show
> where the danger lies
>
> version 9.6.3, if that matters
>
>
> CREATE OR REPLACE FUNCTION
> testret(a bool, OUT ret bool)
> RETURNS bool
> LANGUAGE plpgsql
> AS
> $$
> begin
> RAISE NOTICE 'testret(%)',a;
> IF a IS NULL
> THEN
> ret := TRUE
> RETURN;
> RAISE NOTICE 'testret after return';
> END IF;
> IF TRUE RETURN
> THEN
> RAISE NOTICE 'return allowed here too';
> END IF;
> RETURN;
> end;
> $$;
>
> # select testret(null);
> NOTICE: testret(<NULL>)
> NOTICE: testret after return
> NOTICE: return allowed here too
> testret
> ---------
> t
> (1 row)
>
>
> as you can see, statements with RETURN are allowed, and RETURN keyword
> does nothing in them, like these:
>
> SELECT INTO ret TRUE RETURN;
> ret := TRUE RETURN;
> IF TRUE RETURN THEN ... END IF;
>
> the last one seems most curious to me, but is relatively harmless (just
> hurts one's eyes)
>
>
It is not a bug, it is feature. Sometimes not nice. RETURN is keyword in
procedural part, but it is nothing in sql part.

You can look to plpgsql parser on command IF.

It is designed like

1. if there are keyword IF, then next symbols to keyword THEN will be SQL
expression

IF TRUE RETURN THEN is translated to

IF node with "TRUE RETURN" SQL expression

2. try run SELECT TRUE RETURN;

postgres=# SELECT true RETURN;
┌────────┐
│ return │
╞════════╡
│ t │
└────────┘
(1 row)

It is valid SQL expression

So, there are nothing special or strange - just PLpgSQL is mix of two
languages with different keywords - and what people usually don't know,
PLpgSQL expressions are SQL expressions.

Regards

Pavel

PLpgSQL parser try to separate code to procedural and sql part.

>
> regards,
> mariusz jadczak
>
>
>
>