plpgsql_check 2.0.1

I released new significant version of 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)';
  ...