plpgsql_check
- plpgsql_check 2.0.1. Although there are only two new features (and few bugfixes), these two features are important.
I wrote about benefits of plpgsql_check
for PL/pgSQL
language developers in my blog Why you need plpgsql_check (if you write procedures in PLpgSQL). The plpgsql_check
is PostgreSQL extensions, that does static analyze of PL/pgSQL
code. It can detect lot of possible runtime bugs before execution, it can detect some performance or security isues too. More plpgsql_check
can do coverage analyze, and has integrated profiler and tracer.
The PL/pgSQL
language is relative static type strict language, and then the static analyze is working well. But there are two limits. The statis analyze cannot to work with objects and values that are created (calculated) at runtime. These objects are local temporary tables (PostgreSQL doesn't support global temporary tables yet) and the results of dynamic SQL:
postgres=# \sf+ fx1 CREATE OR REPLACE FUNCTION public.fx1(tablename text) RETURNS void LANGUAGE plpgsql 1 AS $function$ 2 DECLARE r record; 3 BEGIN 4 EXECUTE format('SELECT * FROM %I', tablename) INTO r; 5 RAISE NOTICE 'id=%', r.id; 6 END; 7 $function$ postgres=# SELECT * FROM plpgsql_check_function('fx1'); ┌──────────────────────────────────────────────────────────────────────────────────────┐ │ plpgsql_check_function │ ╞══════════════════════════════════════════════════════════════════════════════════════╡ │ warning:00000:4:EXECUTE:cannot determinate a result of dynamic SQL │ │ Detail: There is a risk of related false alarms. │ │ Hint: Don't use dynamic SQL and record type together, when you would check function. │ │ error:55000:5:RAISE:record "r" is not assigned yet │ │ Detail: The tuple structure of a not-yet-assigned record is indeterminate. │ │ Context: SQL expression "r.id" │ └──────────────────────────────────────────────────────────────────────────────────────┘ (6 rows) postgres=# \sf+ fx2 CREATE OR REPLACE FUNCTION public.fx2() RETURNS void LANGUAGE plpgsql 1 AS $function$ 2 BEGIN 3 CREATE TEMP TABLE IF NOT EXISTS ltt(a int); 4 DELETE FROM ltt; 5 INSERT INTO ltt VALUES(10); 6 END; 7 $function$ postgres=# SELECT * FROM plpgsql_check_function('fx2'); ┌───────────────────────────────────────────────────────────┐ │ plpgsql_check_function │ ╞═══════════════════════════════════════════════════════════╡ │ error:42P01:4:SQL statement:relation "ltt" does not exist │ │ Query: DELETE FROM ltt │ │ -- ^ │ └───────────────────────────────────────────────────────────┘ (3 rows)In
plpgsql_check 2.0.1
I can use pragmas TYPE
and TABLE
(note: an syntax of pragma in plpgsql_check is little bit strange, because the language PL/pgSQL
doesn't support native syntax for pragma (custom compiler directive) (like ADA language or PL/SQL language):
CREATE OR REPLACE FUNCTION public.fx1(tablename text) RETURNS void LANGUAGE plpgsql 1 AS $function$ 2 DECLARE r record; 3 BEGIN 4 PERFORM plpgsql_check_pragma('TYPE: r (id int)'); 5 EXECUTE format('SELECT * FROM %I', tablename) INTO r; 6 RAISE NOTICE 'id=%', r.id; 7 END; 8 $function$ postgres=# SELECT * FROM plpgsql_check_function('fx1'); ┌────────────────────────┐ │ plpgsql_check_function │ ╞════════════════════════╡ └────────────────────────┘ (0 rows) postgres=# \sf+ fx2 CREATE OR REPLACE FUNCTION public.fx2() RETURNS void LANGUAGE plpgsql 1 AS $function$ 2 BEGIN 3 CREATE TEMP TABLE IF NOT EXISTS ltt(a int); 4 PERFORM plpgsql_check_pragma('TABLE: ltt (a int)'); 5 DELETE FROM ltt; 6 INSERT INTO ltt VALUES(10); 7 END; 8 $function$ postgres=# SELECT * FROM plpgsql_check_function('fx2'); ┌────────────────────────┐ │ plpgsql_check_function │ ╞════════════════════════╡ └────────────────────────┘ (0 rows)Note: if you use
plpgsql_check 2.0.2
, then you can use shorter form for PRAGMA:
BEGIN CREATE TEMP TABLE IF NOT EXISTS ltt(a int); PERFORM 'PRAGMA:TABLE: ltt (a int)'; ...
No comments:
Post a Comment