Re: proposal: plpgsql, new check for extra_errors - strict_expr_check - Mailing list pgsql-hackers

Hi,

assigned patch try to solve issue reported by Mor Lehr (Missing semicolon in anonymous plpgsql block does not raise syntax error).

https://www.postgresql.org/message-id/CALyvM2bp_CXMH_Gyq87pmHJRuZDEhV40u9VP8rX=CAnEt2wUXg@mail.gmail.com

by introducing a new extra error check. With this check only a_expr exprs are allowed as plpgsql expressions. This is a small step to behaviour described in SQL/PSM standard (although the language is different, the expression syntax and features are almost similar. With this check the undocumented (but supported syntax)

var := column FROM tab

is disallowed. Only ANSI syntax for embedded queries (inside assignment statement) is allowed

var := (SELECT column FROM tab);

With this check, the reported issue (by Mor Lehr) is detected

default setting

CREATE TABLE foo3(id serial PRIMARY key, txt text);
INSERT INTO foo3 (txt) VALUES ('aaa'),('bbb');

DO $$
DECLARE
    l_cnt int;
BEGIN
    l_cnt := 1
    DELETE FROM foo3 WHERE id=1;
END; $$

-- without reaction - just don't work

(2024-06-16 16:05:55) postgres=# set plpgsql.extra_errors to 'strict_expr_check';
SET
(2024-06-16 16:06:43) postgres=# DO $$                                          
DECLARE
    l_cnt int;
BEGIN
    l_cnt := 1
    DELETE FROM foo3 WHERE id=1;
END; $$;
ERROR:  syntax error at or near "DELETE"
LINE 11:     DELETE FROM foo3 WHERE id=1;
             ^

This patch has three parts

1. Introduction strict_expr_check

2. set strict_expr_check as default, and impact on regress tests

3. revert @2

I don't propose to be strict_expr_check active  by default.

Comments, notes?

Regards

Pavel