new version of plpgsql_lint released (for PostgreSQL 9.0 and 9.1)

Just notice: I moved a development of plpgsql_lint to github.

• removed coverage tests - no usefull as I though
• cleaned code and enhance support for cursors

Change a unknown record's field in PL/pgSQL

Hello

Week ago was a request on stackoverflow about change of composite variable's field by dynamic SQL. I wrote a first very simply solution:

CREATE OR REPLACE FUNCTION public.setfield(a anyelement, text, text)
RETURNS anyelement
LANGUAGE plpgsql
AS $function$
begin
  create temp table aux as select $1.*;
  execute 'update aux set ' || quote_ident($2) || ' = ' || quote_literal($3);
  select into $1 * from aux;
  drop table aux;
  return $1;
end;
$function$

It's works, but it's slow and it consumes lot of shared memory (impracticable for repeated using in one transaction). Next version was better - it is faster and isn't hungry:

CREATE OR REPLACE FUNCTION x.setfield2(anyelement, text, text)
 RETURNS anyelement
 LANGUAGE plpgsql
AS $function$
DECLARE 
  _name text;
  _values text[];
  _value text;
  _attnum int;
BEGIN
  FOR _name, _attnum
     IN SELECT a.attname, a.attnum
           FROM pg_catalog.pg_attribute a 
          WHERE a.attrelid = (SELECT typrelid
                                 FROM pg_type
                                WHERE oid = pg_typeof($1)::oid) 
  LOOP
    IF _name = $2 THEN
      _value := $3;
    ELSE
      EXECUTE 'SELECT (($1).' || quote_ident(_name) || ')::text' INTO _value USING $1;
    END IF;
    _values[_attnum] :=  COALESCE('"' || replace(replace(_value, '"', '""'), '''', '''''') || '"', '');
  END LOOP;
  EXECUTE 'SELECT (' || pg_typeof($1)::text || '''(' || array_to_string(_values,',') || ')'').*' INTO $1; 
  RETURN $1;
END;
$function$;

There are a few slower points: EXECUTE in loop, array's update in loop.

But Erwin Brandstetter found a probably best and most simply solution - there are no more ways on PL/pgSQL level.

CREATE OR REPLACE FUNCTION public.setfield3(anyelement, text, text)
RETURNS anyelement
AS $body$
DECLARE
 _list text;

BEGIN
_list := (
   SELECT string_agg(x.fld, ',')
   FROM   (
      SELECT CASE WHEN a.attname = $2
              THEN quote_literal($3)
              ELSE quote_ident(a.attname)
             END AS fld
      FROM   pg_catalog.pg_attribute a 
      WHERE  a.attrelid = (SELECT typrelid
                           FROM   pg_type
                           WHERE  oid = pg_typeof($1)::oid) 
      ORDER BY a.attnum
   ) x
);

EXECUTE '
SELECT ' || _list || '
FROM   (SELECT $1.*) x'
USING  $1
INTO   $1;

RETURN $1;
END;
$body$ LANGUAGE plpgsql;

There is no PL/pgSQL's loop and there is just one EXECUTE. More - this code is just simple - without lot of string (quoting) operations.

Very nice, Erwin!

updated plpgsql_lint released

Hello

I uploaded a bugfix version of plpgsql_lint.

http://kix.fsv.cvut.cz/~stehule/download/plpgsql_lint_2011-07-20.tgz

It support a functions with OUT parameters now:

CREATE OR REPLACE FUNCTION public.gg(OUT a integer, OUT b integer)
 RETURNS record
 LANGUAGE sql
AS $function$ select 10,20 $function$

CREATE OR REPLACE FUNCTION public.t1()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare r record;
begin 
  raise notice 'start';
  r := gg();
  raise notice '% %', r.a, r.x; -- bug function returns (a,b)
  return;
end;
$function$

postgres=# select t1();
ERROR:  record "r" has no field "x" -- this bug was found before own execution, there is no message "start"
CONTEXT:  SQL statement "SELECT r.x"
PL/pgSQL function "t1" line 5 at RAISE

it running after fixing:

postgres=# select t1();
NOTICE:  start
NOTICE:  10 20
 t1 
────
 
(1 row)

Time: 10.287 ms

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.

some news

Hello I uploaded a new version of Orafce - devel version, with support for 9.1 extension feature.

test=# \dx
                                         List of installed extensions
  Name   │ Version │   Schema   │                                 Description                                  
─────────┼─────────┼────────────┼──────────────────────────────────────────────────────────────────────────────
 orafce  │ 3.03    │ public     │ functions and operators that emulates a basic functions and packages from Or…
         │         │            │…acle RDBMS
 plpgsql │ 1.0     │ pg_catalog │ PL/pgSQL procedural language
(2 rows)

Other note - my last work on PL/PSM implementation was released under BSD licence - http://archives.postgresql.org/pgsql-announce/2011-05/msg00012.php. Thank you very much CZ.NIC (my current employer).

PSM (zero) is completed, and needs you

Hello,

yesterday I finished work on prototype implementation of SQL/PSM language for PostgreSQL. Now, this language supports all necessary features:

• basic features - arrays, composite types, triggers
• advanced features - table returned functions, IN/OUT variables
• SQL/PSM specific features - warning, exceptions (general, SQLCODE) handlers, SIGNAL, RESIGNAL statements
• some DB2 or MySQL features - multi assign statement, support for magic SQLSTATE and SQLCODE variables

some samples:

create or replace function test74_2()
returns text as $$
begin atomic
  declare not_found condition for sqlstate '03000';
  declare undo handler for not_found
    begin
      declare xx, yy text;
      get stacked diagnostics xx = condition_identifier, yy = returned_sqlstate;
      return xx || ' Signal handled ' || yy;
    end;
  signal not_found;
end;
$$ language psm0;

create or replace function test66(a int, out r int) as $$
begin
  declare continue handler for sqlstate '01002'
    set r = r + 1;
  declare continue handler for sqlstate '01003'
    set r = r + 2;
  set r = 0;
x: while a > 0 do
     if a % 2 = 0 then
       signal sqlstate '01002';
     else
       signal sqlstate '01003';
     end if;
     set a = a - 1;
   end while;
end;
$$ language psm0;

This PL isn't designed as an replacement of PL/pgSQL. It is designed as second language with little bit different philosophy and goals.

• complete validation of embedded SQL in compile time,
• result of embedded SQL is transformed to target type early

The main goal is a searching bugs inside embeded SQL early - usually in compile time. This goal changes a rules in languages. PSM is very static language. There are more tasks, that should be solved via dynamic SQL than in PL/pgSQL. On second hand, lot of PL/pgSQL runtime bugs can be detected in PSM in compile time.

ToDo:

• Complete revision and review, more comments - any volunteers ?
• Performance optimizations,
• Cleaning of error messages

Source code is available from github.

I invite any help!

MySQL div

I have to port one benchmark from MySQL. Test wasn't success because there was a div by zero. A result in MySQL is NULL, PostgreSQL raises a exception. I created new operator, that simulates MySQL's behave:

postgres=# CREATE OR REPLACE FUNCTION my_div(double precision, double precision) 
           RETURNS double precision AS $$ 
             SELECT CASE WHEN $2 = 0 
                              THEN NULL::double precision 
                              ELSE $1::double precision / $2::double precision END; 
           $$ LANGUAGE sql;
CREATE FUNCTION
postgres=# select my_div(2,3);
      my_div       
-------------------
 0.666666666666667
(1 row)

postgres=# select my_div(2,0);
 my_div 
--------
       
(1 row)

postgres=# CREATE OPERATOR // (PROCEDURE = my_div, LEFTARG = double precision, RIGHTARG= double precision);
CREATE OPERATOR
postgres=# SELECT 10//3;
     ?column?     
------------------
 3.33333333333333
(1 row)

postgres=# SELECT 10//0;
 ?column? 
----------
         
(1 row)