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)

some basic PostgreSQL orchestration from Bash

I had to designed some scripts for migration, upgrade and rebuild PostgreSQL. Some fragments can be useful for others:

Prolog:

#!/bin/bash

# Installation prefix
prefix=/usr/local/pgsql

PGCTL="$prefix/bin/pg_ctl"
PGUSER=postgres

if [ ! -n "$PGDATA" ]; then
  PGDATA="$prefix/data"
fi

Ensure a stop of PostgreSQL, continue when server is down:

echo "shutdown server"
su - $PGUSER -c "$PGCTL status -D $PGDATA" 
if [ $? != 1 ]; then
  su - $PGUSER -c "$PGCTL stop -D  $PGDATA -m fast"
  if [ $? != 1 -a $? != 0 ]; then
    su - $PGUSER  -c "$PGCTL status -D  $PGDATA"
    while [ $? != 1 -a $? != 3 ]; do
      echo "PostgreSQL still running.."
      sleep 1
      su - $PGUSER -c "$PGCTL status -D  $PGDATA"
    done
  fi
fi

Start server and continue when server is ready to accept connection:

echo "start server"
/etc/init.d/pgsql start
$prefix/bin/pg_isready
while [ $? != 0 ]; do
  sleep 1;
  $prefix/bin/pg_isready
done

make_date, make_time functions

Years I miss a functionality for simple constructing dates and times from numeric fields.

There was a more possibilities - one via to_date function

postgres=# select to_date(2014::text || 10::text || 28::text ,'YYYYMMDD');
  to_date   
------------
 2014-10-28
(1 row)

But I was not too happy with it. It works, but it is missing any elegance (and there are a useless type transformations):

I wrote a two functions, that will be available in PostgreSQL 9.4

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f901bb50e33ad95593bb68f7b3b55eb2e47607dc

postgres=# select make_date(2014,10,28);
 make_date  
------------
 2014-10-28
(1 row)

postgres=# select make_time(10,20,30.323);
  make_time   
--------------
 10:20:30.323
(1 row)

Now I am happy - it was my dream.