Saturday, February 7, 2015

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.

Wednesday, February 4, 2015

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?

Monday, February 2, 2015

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)

Sunday, January 18, 2015

how to push parameters to DO statement from command line

PostgreSQL DO statement doesn't support parametrization. But with psql variables we are able to "inject" do statement safely and we can do it:
bash-4.1$ cat test.sh 
#!/bin/bash

echo "
set myvars.msgcount TO :'msgcount'; 
DO \$\$ 
BEGIN 
  FOR i IN 1..current_setting('myvars.msgcount')::int LOOP 
    RAISE NOTICE 'Hello';
  END LOOP; 
END \$\$" | psql postgres -v msgcount=$1

Usage:
bash-4.1$ ./test.sh 3
SET
Time: 0.386 ms
NOTICE:  Hello
NOTICE:  Hello
NOTICE:  Hello
DO
Time: 1.849 ms

Thursday, January 15, 2015

most simply implementation of history table with hstore extension

Postgres has this nice extension (hstore) lot of years. It can be used for simulation some features of doc databases - or can be used for implementation of generic triggers for history table:

I have a table test and table history:

CREATE TABLE test(a int, b int, c int);

CREATE TABLE history(
  event_time timestamp(2),
  executed_by text, 
  origin_value hstore, 
  new_value hstore
);

CREATE OR REPLACE FUNCTION history_insert()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO history(event_time, executed_by, new_value)
     VALUES(CURRENT_TIMESTAMP, SESSION_USER, hstore(NEW));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION history_delete()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO history(event_time, executed_by, origin_value)
     VALUES(CURRENT_TIMESTAMP, SESSION_USER, hstore(OLD));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION history_update()
RETURNS TRIGGER AS $$
DECLARE
  hs_new hstore := hstore(NEW);
  hs_old hstore := hstore(OLD);
BEGIN
  INSERT INTO history(event_time, executed_by, origin_value, new_value)
     VALUES(CURRENT_TIMESTAMP, SESSION_USER, hs_old - hs_new, hs_new - hs_old);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_history_insert AFTER INSERT ON test
  FOR EACH ROW EXECUTE PROCEDURE history_insert();

CREATE TRIGGER test_history_delete AFTER DELETE ON test
  FOR EACH ROW EXECUTE PROCEDURE history_delete();

CREATE TRIGGER test_history_update AFTER UPDATE ON test
  FOR EACH ROW EXECUTE PROCEDURE history_update();
Result:
INSERT INTO test VALUES(1000, 1001, 1002);
UPDATE test SET a = 10, c = 20;
DELETE FROM test;

postgres=# SELECT * FROM history ;
       event_time       | executed_by |           origin_value            |               new_value               
------------------------+-------------+-----------------------------------+---------------------------------------
 2015-01-15 20:59:05.52 | pavel       |                                   | "a"=>"1000", "b"=>"1001", "c"=>"1002"
 2015-01-15 20:59:05.6  | pavel       | "a"=>"1000", "c"=>"1002"          | "a"=>"10", "c"=>"20"
 2015-01-15 20:59:06.51 | pavel       | "a"=>"10", "b"=>"1001", "c"=>"20" | 
(3 rows)

Tested on PostgreSQL 9.2

Saturday, December 20, 2014

plpgsql_check is available via PGXN repository

Only small notice - plpgsql_check is available from PGXN repository. More about PGXN on FAQ page.

Sunday, December 14, 2014

plpgsql_check is ready for OS X

mscottie found a magic option -undefined dynamic_lookup. With this option we are able to compile plpgsql_check on OS X platform.