plpgsql_check is available for Microsoft Windows

I compiled the plpgsql_check by Visual Studio 2010 Express. The dll for PostgreSQL 9.2, 9.3, 9.4 and x86 and x64 platform are available in zip archive http://pgsql.cz/files/plpgsql_check-1.0.1-1-mswin.zip.

Installation

1. Download, unzip and choose related dll file
2. rename to plpgsql_check.dll and copy to PostgreSQL's lib directory (Program Files/PostgreSQL/9.2/lib)
3. copy plpgsql_check-1.0.sql and plpgsql_check.control to PostgreSQL's share/extension directory (PostgreSQL/9.2/share/extension).

It can needed installed a Microsoft Visual C++ 2010 SP1 Redistributable Package http://www.microsoft.com/en-us/download/details.aspx?id=8328.

Please, check it.

Simple multicolumn ouput in psql

There are interesting idea on Depesz's blog .

But some possibility has a Linux itself. There is simple pager column.

You can try (blogger engine break formatting):

postgres=# \pset tuples_only
postgres=# \setenv PAGER column

postgres=# select typname from pg_type limit 100;
 bool   pg_type  line   _bool   _varchar  _inet   _numeric
 bytea   pg_attribute  _line   _bytea   _int8   _cidr   timetz
 char   pg_proc  float4   _char   _point   _cstring  _timetz
 name   pg_class  float8   _name   _lseg   bpchar   bit
 int8   json   abstime  _int2   _path   varchar  _bit
 int2   xml   reltime  _int2vector  _box   date   varbit
 int2vector  _xml   tinterval  _int4   _float4  time   _varbit
 int4   _json   unknown  _regproc  _float8  timestamp  numeric
 regproc  pg_node_tree  circle   _text   _abstime  _timestamp  refcursor
 text   smgr   _circle  _oid   _reltime  _date   _refcursor
 oid   point   money   _tid   _tinterval  _time
 tid   lseg   _money   _xid   _polygon  timestamptz
 xid   path   macaddr  _cid   aclitem  _timestamptz
 cid   box   inet   _oidvector  _aclitem  interval
 oidvector  polygon  cidr   _bpchar  _macaddr  _interval

postgres=# 

It works together with less

postgres=# \setenv PAGER '(column | less)'
postgres=# select typname from pg_type;
 ...

Do you know some other nice pagers?

template_fdw

I wrote a template foreign data wrapper. It is very simple FDW, that doesn't allow any DML and SELECT operation over table. It is based on Andrew Dunstan's blackhole FDW. What is benefit of this strange data wrapper? I wrote this for possibility to check plpgsql code that uses temporary tables. plpgsql_check cannot do a static validation of functions that uses temporary tables.

I have a function test:

CREATE OR REPLACE FUNCTION public.test()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE r record;
BEGIN
  BEGIN
    DELETE FROM foo; -- temp table
  EXCEPTION WHEN OTHERS THEN
    CREATE TEMP TABLE foo(a int, b int);
  END;
  INSERT INTO foo VALUES(10,20);
  FOR r IN SELECT * FROM foo
  LOOP
    RAISE NOTICE '% %', r.a, r.b;
  END LOOP;
END;
$function$

This code I cannot to verify with plpgsql_check due dependency on temp table foo:

postgres=# select plpgsql_check_function('test()', fatal_errors := false);
                           plpgsql_check_function                           
----------------------------------------------------------------------------
 error:42P01:5:SQL statement:relation "foo" does not exist
 Query: DELETE FROM foo
 --                 ^
 error:42P01:9:SQL statement:relation "foo" does not exist
 Query: INSERT INTO foo VALUES(10,20)
 --                 ^
 error:42P01:10:FOR over SELECT rows:relation "foo" does not exist
 Query: SELECT * FROM foo
 --                   ^
 error:55000:12:RAISE:record "r" is not assigned yet
 Detail: The tuple structure of a not-yet-assigned record is indeterminate.
 Context: SQL statement "SELECT r.a"
 error:55000:12:RAISE:record "r" is not assigned yet
 Detail: The tuple structure of a not-yet-assigned record is indeterminate.
 Context: SQL statement "SELECT r.b"
(15 rows)

I can create persistent table foo. But if I forgot to drop this table, I can have lot of problems, but some one can be invisible. So I created "template storage" that disallow any DML or SELECT. This decrease a risk and I can have these fake tables persistent:

CREATE SERVER template FOREIGN DATA WRAPPER template_fdw;
CREATE FOREIGN TABLE foo(a int, b int) SERVER template;

postgres=# SELECT * FROM foo;
ERROR:  cannot read from table "public.foo"
DETAIL:  Table is template.
HINT:  Create temp table by statement "CREATE TEMP TABLE foo(LIKE public.foo INCLUDING ALL);"

-- but EXPLAIN is valid
postgres=# EXPLAIN SELECT * FROM foo;
                      QUERY PLAN                       
-------------------------------------------------------
 Foreign Scan on foo  (cost=0.00..0.00 rows=0 width=8)
(1 row)

-- and now I can do static validation
postgres=# select plpgsql_check_function('test()', fatal_errors := false);
 plpgsql_check_function 
------------------------
(0 rows)