new PostgreSQL extension - plpgsql_check

Three years ago I worked on complex project, that was written in PL/pgSQL almost. I wrote a regress tests, but these tests was not able to find all errors due missing coverage some paths. So I wrote simple extension, that ensured a plan preparation for all embedded SQL on start. This extension helps me to find 99% bugs. I cleaned this extension and I published it as plpgsql_lint. As a plpgsql developer I am sure, so this functionality is missing in core, so I started in core implementation. Two years later I am still on start. I didn't find how to merge a requested functionality with current code cleanly (so committers will be happy) - probably we have to do some significant plpgsql refactoring first. And now is clean, so this code will not be in prepared release PostgreSQL 9.4. Although a code is not enough for committers, it can be useful for common plpgsql developers.

I started new github project - plpgsql_check. It is plpgsql_lint (next generation). plpgsql_lint checked functions on start. plpgsql_check is coming with plpgsql_check_function. You can check any plpgsql function without execution. It can be joined with event trigger and you can ensure a extended check after function creating.

Any comments, patches, ideas are welcome - you can try send me a email or you can join to related postgresql-extensions-hacking google group.

examples:

postgres=# LOAD 'plpgsql';
LOAD
postgres=# CREATE EXTENSION plpgsql_check;
LOAD
postgres=# CREATE TABLE t1(a int, b int);
CREATE TABLE

postgres=#
CREATE OR REPLACE FUNCTION public.f1()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE r record;
BEGIN
  FOR r IN SELECT * FROM t1
  LOOP
    RAISE NOTICE '%', r.c; -- there is bug - table t1 missing "c" column
  END LOOP;
END;
$function$;

CREATE FUNCTION

postgres=# select f1(); -- execution doesn't find a bug due empty table t1
  f1 
 ────

 (1 row)

postgres=# \x
Expanded display is on.
postgres=# select * from plpgsql_check_function_tb('f1()');
─[ RECORD 1 ]───────────────────────────
functionid │ f1
lineno     │ 6
statement  │ RAISE
sqlstate   │ 42703
message    │ record "r" has no field "c"
detail     │ [null]
hint       │ [null]
level      │ error
position   │ 0
query      │ [null]

postgres=# \sf+ f1
    CREATE OR REPLACE FUNCTION public.f1()
     RETURNS void
     LANGUAGE plpgsql
1       AS $function$
2       DECLARE r record;
3       BEGIN
4         FOR r IN SELECT * FROM t1
5         LOOP
6           RAISE NOTICE '%', r.c; -- there is bug - table t1 missing "c" column
7         END LOOP;
8       END;
9       $function$

select * from plpgsql_check_function('f1()', fatal_errors := false);
                         plpgsql_check_function                         
------------------------------------------------------------------------
 error:42703:4:SQL statement:column "c" of relation "t1" does not exist
 Query: update t1 set c = 30
 --                   ^
 error:42P01:7:RAISE:missing FROM-clause entry for table "r"
 Query: SELECT r.c
 --            ^
 error:42601:7:RAISE:too few parameters specified for RAISE
(7 rows)

postgres=# select * from plpgsql_check_function('fx()', format:='xml');
                 plpgsql_check_function                     
────────────────────────────────────────────────────────────────
 <function oid="16400">                                        ↵
   <issue>                                                     ↵
     <level>error</level>                                      ↵
     <sqlstate>42P01</Sqlstate>                                ↵
     <message>relation "foo111" does not exist</Message>       ↵
     <stmt lineno="3">RETURN</Stmt>                            ↵
     <query position="23">SELECT (select a from foo111)</Query>↵
   </Issue>                                                    ↵
  </Function>
 (1 row)