Originally the PL/pgSQL was very simple language and integration procedural language with SQL was very primitive. Very specific feature of PL/pgSQL is translation of every expression to SQL. Thanks to this translation, the PL/pgSQL is very simple and really fully integrated with SQL engine. Bad side is possibility to write dirty or broken code, that is not detected.
Every expression is translated to queries. So expression `10` is translated to `SELECT 10`. The PL/pgSQL allows dirty expressions like `a FROM foo WHERE x = 10`. Although this syntax is ugly, it was very popular and it is supported and will be supported for ever.
Unfortunately, this support of this syntax with removing some limits in PostgreSQL 14, allows new kind of bugs (see https://www.postgresql.org/message-id/CALyvM2bp_CXMH_Gyq87pmHJRuZDEhV40u9VP8rX=CAnEt2wUXg@mail.gmail.com).
When somebody write code:
DECLARE var int;
BEGIN
var := 10
DELETE FROM tab WHERE varx = 20;
END;
This code on PostgreSQL 13 and older fails, but on PostgreSQL 14 and higher just quietly does nothing. Why?
The problem is in missing semicolon after `10`. PL/pgSQL translates the code to query:
`SELECT 10 DELETE FROM tab WHERE varx = 20`. This query fails on older Postgres, because `DELETE` is keyword and requires usage of `AS` keyword. But PostgreSQL 14 doesn't need it - It allows to usage a keywords like column names without necessity to use `AS` keyword.
I wrote a patch to Postgres - that implements new extra check that can detect this issue https://commitfest.postgresql.org/52/5044/. But this patch is waiting for review half year. I am not sure how much of plpgsql's developers using extra checks.
Today I found a way, how it is possible to detect this issue without necessity to modify SQL parser, and I wrote new check to plpgsql_check (it is merged in master branch):
(2025-02-05 22:17:04) postgres=# create or replace function foofoo()
returns void as $$
declare x int;
begin
x := 1 delete from foo where x = 10 ;
end;
$$ language plpgsql;
CREATE FUNCTION
(2025-02-05 22:17:07) postgres=# select plpgsql_check_function('foofoo()');
┌────────────────────────────────────────────────────────────────────┐
│ plpgsql_check_function │
╞════════════════════════════════════════════════════════════════════╡
│ warning extra:00000:4:assignment:expression is not pure expression │
│ Query: x := 1 delete from foo where x = 10 │
│ -- ^ │
│ Detail: there is a possibility of unwanted behave │
│ Context: at assignment to variable "x" declared on line 2 │
│ warning extra:00000:2:DECLARE:never read variable "x" │
└────────────────────────────────────────────────────────────────────┘
(6 rows)
