plpgsql lint

Hello I wrote a new contrib module plpgsql_lint for PostgreSQL 9.0. It is a validator of embeded SQL in plpgsql functions. PLpgSQL runtime checks a syntax when function is created, but it doesn't check a semantic of sql - so you can refer to not existing tables, columns, variables. These errors are found when interpret try to evaluate some SQL:

 postgres=# select * from tablename ;
 columnname 
────────────
         10
(1 row)

        CREATE OR REPLACE FUNCTION public.foo(x integer)
         RETURNS integer
         LANGUAGE plpgsql
1       AS $function$
2       declare r record;
3       begin
4         if x > 0 then
5           for r in select columnam from tablename
6           loop
7             x := x + r.colname;
8           end loop;
9         end if;
10        return x;
11      end;
12      $function$

Function foo contains a two errors - usage of non existing column "columnnam" and reference on non existing field in record "colname". These bugs are not detected when I run function with negative argument:

postgres=# select foo(-1);
 foo 
─────
  -1
(1 row)

Time: 0.352 ms

with active plpgsql_lint module, these bugs are diagnosed without dependency on function's parameter value:

postgres=# load 'plpgsql';
LOAD
Time: 0.139 ms
postgres=# load 'plpgsql_lint';
LOAD
Time: 0.137 ms

postgres=# select foo(-1);
ERROR:  column "columnam" does not exist
LINE 1: select columnam from tablename
               ^
QUERY:  select columnam from tablename
CONTEXT:  PL/pgSQL function "foo" line 4 at FOR over SELECT rows

-- after fixing FOR statement
postgres=# select foo(-1);
ERROR:  record "r" has no field "colname"
CONTEXT:  SQL statement "SELECT x + r.colname"
PL/pgSQL function "foo" line 6 at assignment

This module helps to find all errors in code.

It can do a little bit more - it watches a coverage of plpgsql statements in nodes:

you can get a value of coverage tests:

postgres=# select statement_coverage('foo');
 statement_coverage 
────────────────────
                0.5
(1 row)

Time: 0.423 ms
postgres=# select decision_coverage('foo');
 decision_coverage 
───────────────────
                 0
(1 row)

Time: 0.333 ms

and you can see, what lines of function was executed:

postgres=# select coverage_get_model('foo');
                      coverage_get_model                      
──────────────────────────────────────────────────────────────
 <blo count="0" lineno="2" stmt="statement block">           
   <ifs count="1" lineno="3" stmt="IF">                      
     <ift>                                                   
       <lop count="0" lineno="4" stmt="FOR over SELECT rows">
         <stm count="0" lineno="6" stmt="assignment">       
       </stm></lop>                                                
     </ift>                                                  
   </ifs>                                                    
   <ret count="1" lineno="9" stmt="RETURN">                 
 </ret>
</blo>    
(1 row)

Installation

You have to put code to contrib directory and compile and install from source code. A other installation methods are not supported now:

[pavel@pavel-stehule postgresql-9.0.4]$ pwd
/home/pavel/src/postgresql-9.0.4
[pavel@pavel-stehule postgresql-9.0.4]$ cd contrib/plpgsql_lint
[pavel@pavel-stehule plpgsql_lint]$ make clean
rm -f plpgsql_lint.so   libplpgsql_lint.a 
rm -f plpgsql_coverage.so plpgsql_lint.so plpgsql_coverage.o plpgsql_lint.o
rm -f plpgsql_lint.sql
rm -f plpgsql_coverage.o plpgsql_lint.o
[pavel@pavel-stehule plpgsql_lint]$ make all
sed 's,MODULE_PATHNAME,$libdir/plpgsql_lint,g' plpgsql_lint.sql.in >plpgsql_lint.sql
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -fpic -fpic -I../../src/pl/plpgsql/src -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2   -c -o plpgsql_coverage.o plpgsql_coverage.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -fpic -fpic -I../../src/pl/plpgsql/src -L../../src/port -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql90/lib',--enable-new-dtags  -shared -o plpgsql_coverage.so plpgsql_coverage.o
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -fpic -fpic -I../../src/pl/plpgsql/src -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2   -c -o plpgsql_lint.o plpgsql_lint.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -fpic -fpic -I../../src/pl/plpgsql/src -shared -o plpgsql_lint.so plpgsql_coverage.o plpgsql_lint.o -L../../src/port -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql90/lib',--enable-new-dtags  
[pavel@pavel-stehule plpgsql_lint]$ su
Password: 
[root@pavel-stehule plpgsql_lint]# make install
/bin/mkdir -p '/usr/local/pgsql90/lib'
/bin/mkdir -p '/usr/local/pgsql90/share/contrib'
/bin/mkdir -p '/usr/local/pgsql90/lib'
/bin/sh ../../config/install-sh -c -m 755  plpgsql_lint.so '/usr/local/pgsql90/lib/plpgsql_lint.so'
/bin/sh ../../config/install-sh -c -m 644 plpgsql_lint.sql '/usr/local/pgsql90/share/contrib'
 /bin/sh ../../config/install-sh -c -m 755  plpgsql_coverage.so '/usr/local/pgsql90/lib'
 /bin/sh ../../config/install-sh -c -m 755  plpgsql_lint.so '/usr/local/pgsql90/lib'

and in console as user postgres:

test=# load 'plpgsql';
LOAD
Time: 0.232 ms
test=# \i /usr/local/pgsql90/share/contrib/plpgsql_lint.sql 
CREATE FUNCTION
Time: 21.790 ms
CREATE FUNCTION
Time: 8.356 ms
CREATE FUNCTION
Time: 8.145 ms
CREATE FUNCTION
Time: 8.487 ms
test=# 

After loading module with command LOAD:

test=# load 'plpgsql';
LOAD
test=# load 'plpgsql_lint';
LOAD

the modul is active.

Limits

It cannot to work with temporary objects - because these objects does not exist when validation is processed.

It cannot to work with record variables, that has a value assigned via assign statement:

create or replace function fx(out a int, out b int)
as $$ ... $$

DECLARE r record;
BEGIN
  r := fx();
  RAISE NOTICE '% %', r.a, r.b; -- this is not possible with plpgsql_lint
  ...

you should to fix function or disable lint:

CREATE TYPE fx_type AS (a int, b int);
CREATE OR REPLACE FUNCTION fx() 
RETURNS fx_type AS $$ .. $$

DECLARE r fx_type; -- don't use combination record and assign statement
BEGIN
  ..

Blocking lint of some function is possible via option

test=# CREATE OR REPLACE FUNCTION public.foo(x integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
declare r record;
begin
  if x > 0 then
    for r in select columnname from tablename
    loop
      x := x + r.columnname;
    end loop;
  end if; 
  return x;
end;
$function$ set plpgsql.disable_lint to on; -- don't use a validator on this function

This module is released under BSD license and source codes are downloadable from http://kix.fsv.cvut.cz/~stehule/download/plpgsql_lint_2011-07-15.tgz.