2018-02-16 13:31 GMT+01:00 mariusz <marius@xxxxxxx>:
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)
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
