Pavel Stehule's blog

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

2011-11-12T01:12:00.000-08:00

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

2011-10-23T01:32:00.000-07:00

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

2011-07-18T03:22:00.000-07:00

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

2011-07-14T22:03:00.000-07:00

HelloI 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

2011-05-27T04:31:00.000-07:00

HelloI 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

2011-04-22T01:46:00.000-07:00

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

2011-03-28T10:21:00.000-07:00

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)

simple function for test if somebody is superuser

2011-03-27T11:12:00.000-07:00

I needed a small functions:


CREATE OR REPLACE FUNCTION is_superuser(text)
RETURNS boolean AS $$
  SELECT EXISTS(SELECT 1 FROM pg_roles WHERE rolname = $1 AND rolsuper);
$$ LANGUAGE sql;

postgres=# SELECT is_superuser('tom');
 is_superuser 
--------------
 f
(1 row)

SQL/PSM runtime

2011-02-04T03:22:00.000-08:00

I enhanced a PLPSM executor engine to show a more informations when exception is raised. Now PLPSM has same and maybe better error processing than PL/pgSQL. - Almost all code coming from PL/pgSQL. Still, there are lot of work - mainly about exception trapping.


pavel=# \sf fib
CREATE OR REPLACE FUNCTION public.fib(n integer)
 RETURNS double precision
 LANGUAGE psm0
AS $function$xxx:begin
  declare f1 double precision default 0.0;
  declare f2 double precision default 1.0;
  declare cnt int default 1;
  while cnt <= n do
    set (f2, f1, cnt) = (f1 + f2, f2, cnt + 1);
  end while;
  return f2;
end;
$function$
pavel=# select fib(1000);
          fib          
───────────────────────
 7.03303677114228e+208
(1 row)

Time: 16.680 ms
pavel=# select fib(2000);
ERROR:  value out of range: overflow
CONTEXT:  SQL statement "SELECT ($2 + $3)::double precision,($3)::double precision,($4 + 1)::integer"
PLPSM function "fib"  Oid 604602 line 6

   4   declare cnt int default 1;
   5   while cnt <= n do
   6     set (f2, f1, cnt) = (f1 + f2, f2, cnt + 1);
   7   end while;
   8   return f2;

Local variables:

   0 fib.$1  integer              = 2000
   0 fib.n  integer              = 2000
   1 xxx.f1  double precision     = 8.07763763215622e+307
   2 xxx.f2  double precision     = 1.3069892237634e+308
   3 xxx.cnt  integer              = 1476

The work on SQL/PSM compiler started

2011-01-07T01:12:00.000-08:00

There are a basic code for plpsm language implementation. It's downloadable from github. Now only basic statements are supported - see a test.sql file.


pavel=# create or replace function test12(a int)
returns int as $$
begin
  declare b,c,d int default 0;
  set b = a + 1, c = b + 1, d = c + 1;
  return d;
end;
$$ language psm0;
NOTICE:  
   Datums: 4 variables 
   Size: 16 instruction

    0  CopyParam 0, 0, size:4, byval:BYVAL
    1  ExecExpr "SELECT (0)::integer",{}
    2  SaveTo 1, size:4, byval:BYVAL
    3  ExecExpr "SELECT (0)::integer",{}
    4  SaveTo 2, size:4, byval:BYVAL
    5  ExecExpr "SELECT (0)::integer",{}
    6  SaveTo 3, size:4, byval:BYVAL
    7  ExecExpr "SELECT ($1 + 1)::integer",{23,23,23,23}
    8  SaveTo 1, size:4, byval:BYVAL
    9  ExecExpr "SELECT ($2 + 1)::integer",{23,23,23,23}
   10  SaveTo 2, size:4, byval:BYVAL
   11  ExecExpr "SELECT ($3 + 1)::integer",{23,23,23,23}
   12  SaveTo 3, size:4, byval:BYVAL
   13  ExecExpr "SELECT ($4)::integer",{23,23,23,23}
   14  Return size:4, byval:BYVAL
   15  Done.

CREATE FUNCTION

pltoolbox released

2011-01-01T08:27:00.000-08:00

Hello
I released new version of pst collection. This version was enhanced about a regress tests and documentation. At the same time I renamed this package to PL toolbox, because functions from this package are intended for using in stored procedures.
Happy New Year

Bitmapset for PL/pgSQL

2010-12-29T10:58:00.000-08:00

PostgreSQL has a nice functions for operations over bitmapset. A bitmapset is set of positive integers. It should be smaller than array, and, what is important for me. it support very fast test if some value is in set or not. PostgreSQL internally use this functions, but there are not possibility to use it from SQL (PL/pgSQL). I wrote a wrapper for this functionality and moved it to PST collection - http://pgfoundry.org/frs/download.php/2909/pstcoll-10-12-29.tgz.so there is some preview:

pavel=# select pg_column_size(pst.bitmapset '{1,2,3,4}');
 pg_column_size 
----------------
              8
(1 row)

pavel=# select pg_column_size(array[1,2,3,4]);
 pg_column_size 
----------------
             40
(1 row)

pavel=# select pst.add_members('{}', 1, 2, 4,8);
 add_members 
-------------
 {1,2,4,8}
(1 row)

pavel=# select pst.is_member(pst.add_members('{}', 1, 2, 4,8), 8);
 is_member 
-----------
 t
(1 row)

pavel=# select pst.bitmapset_union('{1,2,3}','{6,2,9}');
 bitmapset_union 
-----------------
 {1,2,3,6,9}
(1 row)

Without bitmapsets we have to use a arrays. But bitmaps are better for storing some flags - it's more adequate tool.

pavel=# select count(*) from omega;
  count  
---------
 1010000
(1 row)

pavel=# select bitmapset_collect(a) from omega;
    bitmapset_collect     
--------------------------
 {0,1,2,3,4,5,6,7,8,9,10}
(1 row)

Time: 558.667 ms

pavel=# select array_agg(distinct a) from omega;
        array_agg         
--------------------------
 {0,1,2,3,4,5,6,7,8,9,10}
(1 row)

Time: 3859.567 ms

Using a bitmapset is about 7x faster.

pavel=# select del_members('{2,3,4,5,1}',2,3);
 del_members 
-------------
 {1,4,5}
(1 row)

pavel=# select bitmapset_is_subset('{1,2,3}','{1,3}');
 bitmapset_is_subset 
---------------------
 f
(1 row)

pavel=# select bitmapset_overlap('{1,2,3}','{1,3}');
 bitmapset_overlap 
-------------------
 t
(1 row)

pavel=# select bitmapset_difference('{1,2,3}','{1,3}');
 bitmapset_difference 
----------------------
 {2}
(1 row)

Probably this simple wrapper can be enhanced - some GiST or GIN index can be nice. The bitmapset is limited only on integers. There is not simple way to use it together with enums for example.

Iteration over record in PL/pgSQL

2010-12-10T07:39:00.000-08:00

Hello

A iteration over record in PL/pgSQL is old well known problem. When we try to write a general triggers in PL/pgSQL we can find a break, because we are not able to iterate over record. There was a some workarounds, but these solutions are simply slow and complex. So I wrote a set of functions that can helps.

First functions is record_expand. This function is similar to unnest function, but related object is record:

 
postgres=# select * from pst.record_expand(row('10',null,'Ahoj', current_date)); 
 name |   value    |   typ    
------+------------+--------- 
 f1   | 10         | unknown 
 f2   |            | unknown 
 f3   | Ahoj       | unknown 
 f4   | 2010-12-10 | date 
(4 rows)

Now isn't problem to write general trigger for detecting a changed colums:


CREATE TABLE foo(a int, b int, c text, d int); 
 
CREATE OR REPLACE FUNCTIO update_trg_func() 
RETURNS trigger as $$ 
DECLARE r record; 
BEGIN 
  FOR r IN SELECT n.name, o.value as oldval, n.value as newval 
              FROM pst.record_expand(new) n, 
                   pst.record_expand(old) o 
             WHERE n.name = o.name 
               AND n.value IS DISTINCT FROM o.value 
  LOOP  
    RAISE NOTICE '% % %', r.name, r.oldval, r.newval; 
END LOOP; 
RETURN NULL; 
END; 
$$ LANGUAGE plpgsql; 
 
CREATE TRIGGER update_trg 
  AFTER UPDATE ON foo 
  FOR EACH ROW EXECUTE PROCEDURE update_trg_func(); 
 
postgres=# UPDATE foo SET c = 'Pavel', a = 40; 
NOTICE:  a 30 40 
NOTICE:  c omega Pavel 
UPDATE 1

Next function allows update of any dynamic record. This function is record_set_fields: It's a variadic functions - you can enter a fields and values to change:


postgres=# SELECT pst.record_set_fields(row(0,0,'',0)::foo, 'd', 100, 'c', 'Hello');
  record_set_fields
-------------------
  (0,0,Hello,100)

It can be used for dynamic initialization of wide tables - for some OLAP purposes:

  
 CREATE TABLE t(a0 int, a1 int, a2 int, a3 int, a4 int, a5 int); 
 
-- set all null fields ax on value -1000 
 
CREATE OR REPLACE FUNCTION insert_trg_func()
RETURNS trigger as $$
DECLARE name text;
BEGIN
   FOR name IN SELECT x.name 
                  FROM pst.record_expand(new) x 
                 WHERE x.value IS NULL AND x.name LIKE 'a%'
   LOOP  
     new = pst.record_set_fields(new, name, -1000);
   END LOOP; 
   RETURN new;
END; 
$$ LANGUAGE plpgsql;  

CREATE TRIGGER insert_trg 
   BEFORE INSERT ON t 
   FOR EACH ROW EXECUTE PROCEDURE insert_trg_func(); 
 
postgres=# INSERT INTO t(a3,a5) VALUES(100,100);
INSERT 0 1
postgres=# SELECT * FROM t;
  a0   |  a1   |  a2   | a3  |  a4   | a5  
-------+-------+-------+-----+-------+-----
 -1000 | -1000 | -1000 | 100 | -1000 | 100
(1 row)

Last function is record_get_field. Its returns a value of entered field.


CREATE OR REPLACE FUNCTION insert_trg_func() 
RETURNS TRIGGER AS $$ 
DECLARE name text; 
BEGIN
  FOR i IN 0..5 LOOP
    IF pst.record_get_field(new, 'a'||i) IS NULL THEN
      new := pst.record_set_fields(new, 'a'||i, -1000); 
    END IF; 
  END LOOP; r
  RETURN new; 
END; 
$$ LANGUAGE plpgsql;

These package is available from pgfoundry http://pgfoundry.org/frs/shownotes.php?release_id=1749.

new version of pst collection is relased

2010-11-26T05:56:00.000-08:00

Hello
I released a updated version of pst collection. What is a pst collection? It is a collection of usable functions for PostgreSQL valuable for my work. Some from these function will be in next version of PostgreSQL, others not. You can download pst collection from http://pgfoundry.org/frs/download.php/2890/pstcoll-10-11-27.tgz pgfoundry.What you can find there: formatting functions - sprintf, format (with same behave like buildin function from 9.1), left, right, reverse, concat, concat_ws, concat_js, concat_sql. Parse functions - chars_to_array, diff_string, lc_substring. Date function - next_day and last_day. Utility function - counter.Some examples:

pavel=# set search_path = 'pst';
SET
Time: 0.411 ms
pavel=# select sprintf('|%10s|', 'hello');
   sprintf    
──────────────
 |     hello|
(1 row)

pavel=# select sprintf('|%-10s|', 'hello');
   sprintf    
──────────────
 |hello     |
(1 row)

pavel=# select format('INSERT INTO %I VALUES(%L)', 'tabname', 'some value');
                  format                  
──────────────────────────────────────────
 INSERT INTO tabname VALUES('some value')
(1 row)

pavel=# select concat_js(10,22,'weqwe',true, current_date);
            concat_js            
─────────────────────────────────
 10,22,"weqwe",true,"2010-11-26"

pavel=# select chars_to_array('příliš žlutý kůň');
            chars_to_array             
───────────────────────────────────────
 {p,ř,í,l,i,š," ",ž,l,u,t,ý," ",k,ů,ň}
(1 row)

pavel=# select diff_string('Ahoj','Ahojky');
  diff_string   
────────────────
 Ahoj<ins>ky</>
(1 row)

pavel=# select diff_string('Ahojky','Ahoj');
  diff_string   
────────────────
 Ahoj<del>ky</>
(1 row)

pavel=# select diff_string('red dog and cat','green dog and horse');
                           diff_string                           
─────────────────────────────────────────────────────────────────
 <ins>g</>re<del>d</><ins>en</> dog and <del>cat</><ins>horse</>
(1 row)

pavel=# create table target(a int);
CREATE TABLE
Time: 53.819 ms
pavel=# insert into target select counter(i, 5000, true) from generate_series(1,20000) g(i);
NOTICE:  processed 5000 rows, current value is '5000'
NOTICE:  processed 10000 rows, current value is '10000'
NOTICE:  processed 15000 rows, current value is '15000'
NOTICE:  processed 20000 rows, current value is '20000'
INSERT 0 20000
Time: 1170.040 ms

I hope so these function will by useful for you too.

This package should to work on PostgreSQL 8.4, 9.0
Pavel

Simple counter for batch operations

2010-10-28T03:03:00.000-07:00

HelloI am working with large transformations now. It is some like:


CREATE TABLE source_table(..);
CREATE TABLE destination_table(..);

/* transformation function */
CREATE OR REPLACE FUNCTION to_destination_table(source_table) RETURNS RECORD AS $$ ...

/* transformation */
INSERT INTO destination_table
   SELECT (r).*
  FROM (SELECT to_destionation_table(_source) r
           FROM source _source) x

The batch operation is relative long. so I had to wrote simple counter. It is simple function, that counts a call and returns input value without any change. My function has a three parameters, first is a value for forwarding, second is a frequency of notification, and third parameter specifies a printing input value:


nic=# select sum(counter(v, 20, false)) from generate_series(1,100) g(v);
NOTICE:  processed 20 rows
NOTICE:  processed 40 rows
NOTICE:  processed 60 rows
NOTICE:  processed 80 rows
NOTICE:  processed 100 rows
 sum  
------
 5050
(1 row)

nic=# select sum(counter(v, 20, true)) from generate_series(1,100) g(v);
NOTICE:  processed 20 rows, current value is '20'
NOTICE:  processed 40 rows, current value is '40'
NOTICE:  processed 60 rows, current value is '60'
NOTICE:  processed 80 rows, current value is '80'
NOTICE:  processed 100 rows, current value is '100'
 sum  
------
 5050
(1 row)

This function is very simple - see source code:


#include "funcapi.h"
#include "utils/lsyscache.h"

PG_FUNCTION_INFO_V1(pst_counter);

Datum pst_counter(PG_FUNCTION_ARGS);

typedef struct
{
 long int iterations;
 int freq;
 Oid  typoutput;
} counter_cache;

/*
 * raise notice every n call,
 * returns input without change
 */
Datum
pst_counter(PG_FUNCTION_ARGS)
{
 Datum value = PG_GETARG_DATUM(0);
 counter_cache *ptr = (counter_cache *) fcinfo->flinfo->fn_extra;

 if (ptr == NULL)
 {

  fcinfo->flinfo->fn_extra = MemoryContextAlloc(fcinfo->flinfo->fn_mcxt,
          sizeof(counter_cache));
  ptr = (counter_cache *) fcinfo->flinfo->fn_extra;
  ptr->iterations = 0;
  ptr->typoutput = InvalidOid;

  if (PG_ARGISNULL(1))
   elog(ERROR, "second parameter (output frequency) must not be NULL");

  ptr->freq = PG_GETARG_INT32(1);

  if (!PG_ARGISNULL(2) && PG_GETARG_BOOL(2))
  {
   Oid valtype;
   Oid typoutput;
   bool typIsVarlena;

   valtype = get_fn_expr_argtype(fcinfo->flinfo, 0);
   getTypeOutputInfo(valtype, &typoutput, &typIsVarlena);
   ptr->typoutput = typoutput;
  }
 }
 
 if (++ptr->iterations % ptr->freq == 0)
 {
  if (!OidIsValid(ptr->typoutput))
  {
   elog(NOTICE, "processed %ld rows", ptr->iterations);
  }
  else
  {
   /* show a processed row, when it's requested */
   if (PG_ARGISNULL(0))
    elog(NOTICE, "processed %ld rows, current value is null", ptr->iterations);
   else
   {
    elog(NOTICE, "processed %ld rows, current value is '%s'", ptr->iterations,
              OidOutputFunctionCall(ptr->typoutput, value));
   }
  }
 }
 

 PG_RETURN_DATUM(value);
}


CREATE OR REPLACE FUNCTION counter(anyelement, int, bool)
RETURNS anyelement
AS 'MODULE_PATHNAME','pst_counter' LANGUAGE C;

And usage isn't complex too:


/* transformation */
INSERT INTO destination_table
   SELECT (r).*
  FROM (SELECT counter(to_destionation_table(_source), 1000, true) r
           FROM source _source) x

real output:


psql84:convert.sql:183: NOTICE:  processed 1230000 rows, current value is '(959674199,"2010-10-25 23:50:29.404568","2010-10-25 23:50:29.491842",,3,501,190000029,XXXXXX,f,9,21)'
psql84:convert.sql:183: NOTICE:  processed 1240000 rows, current value is '(959473959,"2010-10-25 19:20:52.498152","2010-10-25 19:20:52.564384",,3,501,189854563,XXXXXX,f,9,23)'
psql84:convert.sql:183: NOTICE:  processed 1250000 rows, current value is '(958884965,"2010-10-25 08:53:30.026877","2010-10-25 08:53:30.106039",,3,101,189849045,XXXXXX,f,13,48)'
psql84:convert.sql:183: NOTICE:  processed 1260000 rows, current value is '(959483959,"2010-10-25 19:32:02.136491","2010-10-25 19:32:04.070922",,3,500,189969079,XXXXXX,f,9,48)'
psql84:convert.sql:183: NOTICE:  processed 1270000 rows, current value is '(959488959,"2010-10-25 19:36:40.691078","2010-10-25 19:36:41.155615",,3,500,189037026,,f,9,)'
psql84:convert.sql:183: NOTICE:  processed 1280000 rows, current value is '(959493959,"2010-10-25 19:41:59.21508","2010-10-25 19:41:59.244398",,3,101,189971167,XXXXXX,f,13,42)'
psql84:convert.sql:183: NOTICE:  processed 1290000 rows, current value is '(959498959,"2010-10-25 19:49:22.494294","2010-10-25 19:49:22.516124",,3,1010,189971599,XXXXXX,f,9,53)'

diff string for PostgreSQL

2010-10-21T01:02:00.000-07:00

Hello,I am working on migration a large dataset from archive to a new format. This work is joined with searching a small differences between original and transformed data. For this work I implemented a small library for PostgreSQL. It contains a two functions: diff_string and lc_substring.These functions should to support multibyte encoding. I hope, so this can be useful for someone.


postgres=# select lc_substring('Hello World','ello');
 lc_substring 
──────────────
 ello
(1 row)

postgres=# select diff_string('Hello World','ello');
         diff_string         
─────────────────────────────
 <del>H</>ello<del> World</>
(1 row)

This library exists on pgFoundry http://pgfoundry.org/frs/?group_id=1000457

use a plpgsql well

2010-10-13T23:10:00.000-07:00

Hello

I found a following code on net. It's good example of bad code (written by PHP coder):

01.CREATE OR REPLACE FUNCTION delete_data(IN data integer[]) RETURNS integer AS
02.--DECLARATION OF FUNCTION
03.$$
04.DECLARE
05.--DECLARATION OF LOCAL VARIABLES
06.sql varchar;
07.i integer;
08.BEGIN
09.--START OF THE PROCEDURE
10.i := 0;
11.loop
12.--LOOP THROUGH AOUR ARRAY OF DATA
13.if (data[i][1] IS NULL) then
14.return 1;
15.--IF WE LOOPED THROUG ALL OF ARRAY, IT'S DONE
16.exit;
17.end if;
18.sql := 'DELETE FROM data_table WHERE data_table.id='||data[i];
19.--THE SQL ITSELF, COMBINED WITH VARIABLE
20.execute(sql);
21.--THE EXECUTE FUNCTION BUILT IN PL/pgSQL, EXECUTES VARCHAR SQLs
22.i := i + 1;
23.end loop;
24.--END OF THE PROCEDURE
25. 
26.END;
27.--END OF FUNCTION'S LOGIC
28. 
29.$$
30.LANGUAGE 'plpgsql';
31.--DECLARATION OF LANGUAGE USED

what is wrong:

using a dynamic SQL - really, there cannot be a dynamic SQL
this code is cryptographic - It needs big fantasy to see iteration over array in this code
inconsistency - keywords, useless braces in IF statement (PL/pgSQL isn't PHP)
useless return value - not handled case, when input array is empty

why the coder didn't write just?:

01.CREATE OR REPLACE FUNCTION delete_data(IN data integer[]) 
02.RETURNS void AS $$
03.BEGIN
04.  FOR i IN array_lower(data,1)..array_upper(data,1)
05.  LOOP
06.    DELETE FROM data_table WHERE data_table.id = data[i];
07.  END LOOP;
08.END;
09.$$ LANGUAGE plpgsql;

or better (if you like procedures and dislike SQL in your app. code) (inpiration by Thermic):


  CREATE OR REPLACE FUNCTION delete_data(data integer[])
  RETURNS void AS $$
    DELETE FROM data_table WHERE data_table.id = ANY($1)
  $$ LANGUAGE sql;

note: you can use a plpgsql or sql. Both environments has a few advantages and disadvantages. For using in web environments I little bit prefer SQL language (when it's called once per session, when size of input array can be significantly different).

PostgreSQL 9.1 - iteration over array

2010-09-30T09:09:00.000-07:00

I finished patch that enable iteration over array. I hope so new syntax can helps to better readability. There are a few samples from regress tests:


create or replace function fora_test()
returns int as $$
declare x int; s int = 0;
begin
  for x in array array[1,2,3,4,5,6,7,8,9,10]
  loop
    s := s + x;
  end loop;
  return s;
end;
$$ language plpgsql;

select fora_test();

create or replace function subscripts(anyarray, int)
returns int[] as $$
select array(select generate_subscripts($1,$2));
$$ language sql;

create or replace function fora_test()
returns int as $$
declare x int; s int = 0;
   a int[] := array[1,2,3,4,5,6,7,8,9,10];
begin
  for x in array subscripts(a, 1)
  loop
    s := s + a[x];
  end loop;
  return s;
end;
$$ language plpgsql;

create or replace function fora_test()
returns int as $$
declare x record;
   a fora_point[] := array[(1,2),(3,4),(5,6)];
begin
  for x in array a
  loop
    raise notice 'point=%', x.x;
  end loop;
  return 0;
end;
$$ language plpgsql;

select fora_test();

create or replace function fora_test()
returns int as $$
declare x int; y int;
   a fora_point[] := array[(1,2),(3,4),(5,6)];
begin
  for x, y in array a
  loop
    raise notice 'point=%,%', x, y;
  end loop;
  return 0;
end;
$$ language plpgsql;

performance issue of SQL functions

2010-08-29T09:39:00.000-07:00

I though so sql functions is always faster than plpgsql function. But it's not true. Only when sql function is inlined, then function is faster, else plpgsql can be faster. Now I know, so sql functions where parameter is twice and more used are not inlined - because it means double parameter evaluation.you can see:


CREATE OR REPLACE FUNCTION is_empty(text) 
RETURNS bool AS $$ 
SELECT $1 IS NULL OR $1 = '' 
$$ LANGUAGE sql;

This function returns true when parameter is empty string or is null. The first parameter is used twice and then the body of this function isn't inlined.


postgres=# EXPLAIN VERBOSE SELECT count(is_empty(CASE WHEN random() < 0.5 THEN NULL ELSE 'x' END)) FROM generate_series(1,100000);
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=12.50..12.77 rows=1 width=0)
   Output: count(is_empty(CASE WHEN (random() < 0.5::double precision) THEN NULL::text ELSE 'x'::text END))
   ->  Function Scan on pg_catalog.generate_series  (cost=0.00..10.00 rows=1000 width=0)
         Output: generate_series
         Function Call: generate_series(1, 100000)
(5 rows)

The execution time is 458ms. When I modify the body for one time paremeter using:


CREATE OR REPLACE FUNCTION is_empty(text) 
RETURNS bool AS $$
SELECT COALESCE($1,'') = '' 
$$ LANGUAGE sql;

Then function is inlined:


postgres=# EXPLAIN VERBOSE SELECT count(is_empty(CASE WHEN random() < 0.5 THEN NULL ELSE 'x' END)) FROM generate_series(1,100000);
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=12.50..12.52 rows=1 width=0)
   Output: count((COALESCE(CASE WHEN (random() < 0.5::double precision) THEN NULL::text ELSE 'x'::text END, ''::text) = ''::text))
   ->  Function Scan on pg_catalog.generate_series  (cost=0.00..10.00 rows=1000 width=0)
         Output: generate_series
         Function Call: generate_series(1, 100000)
(5 rows)

and execution time is only 68ms (5 x faster than not inlined function).Attention: this note is valid for some more complex parameters - like volatile functions. The life isn't simple - for basic parameters (like columns or simple expression), the most simple SQL function (in my article function with OR operator and twice used parameter) can be better - wirking better with planner.

divide a table into a partitions

2010-08-17T04:59:00.000-07:00

HelloI had to verify a speed of two methods of dividing data to partion. First method is based on dynamic SQL, second is based on cursor scan and prepared statements. I was surprised - both variants has a same speed.


CREATE OR REPLACE FUNCTION public.foo()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
  FOR i IN 0..9
  LOOP
    RAISE NOTICE '***** % *****', i;
    EXECUTE 'TRUNCATE TABLE ' || ('x' || i)::regclass;
    EXECUTE 'INSERT INTO ' || ('x' || i)::regclass ||
                ' SELECT * FROM x WHERE a >= $1 AND a < $1 + 100000'
       USING i * 100000;
  END LOOP;
  RETURN;
END;
$function$

This variant is shorter, but needs a active index on source table.


CREATE OR REPLACE FUNCTION public.foo1()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
  c CURSOR FOR SELECT * FROM x;
  r RECORD;
BEGIN
  TRUNCATE TABLE x0;
  TRUNCATE TABLE x1;
  TRUNCATE TABLE x2;
  TRUNCATE TABLE x3;
  TRUNCATE TABLE x4;
  TRUNCATE TABLE x5;
  TRUNCATE TABLE x6;
  TRUNCATE TABLE x7;
  TRUNCATE TABLE x8;
  TRUNCATE TABLE x9;

  FOR r IN c
  LOOP
    CASE
        WHEN r.a BETWEEN 0 AND 99999 THEN
            INSERT INTO x0 VALUES(r.a);
        WHEN r.a BETWEEN 100000 AND 199999 THEN
            INSERT INTO x1 VALUES(r.a);
        WHEN r.a BETWEEN 200000 AND 299999 THEN
            INSERT INTO x2 VALUES(r.a);
        WHEN r.a BETWEEN 300000 AND 399999 THEN
            INSERT INTO x3 VALUES(r.a);
        WHEN r.a BETWEEN 400000 AND 499999 THEN
            INSERT INTO x4 VALUES(r.a);
        WHEN r.a BETWEEN 500000 AND 599999 THEN
            INSERT INTO x5 VALUES(r.a);
        WHEN r.a BETWEEN 600000 AND 699999 THEN
            INSERT INTO x6 VALUES(r.a);
        WHEN r.a BETWEEN 700000 AND 799999 THEN
            INSERT INTO x7 VALUES(r.a);
        WHEN r.a BETWEEN 800000 AND 899999 THEN
            INSERT INTO x8 VALUES(r.a);
        WHEN r.a BETWEEN 900000 AND 1000000 THEN
            INSERT INTO x9 VALUES(r.a);
    END CASE;
  END LOOP;
END;
$function$

This variant is longer, but doesn't need a active index on source table.

missing %type[] - workaround

2010-07-26T00:51:00.000-07:00

We are not able to do some more complex functions in plpgsql when returns is polymorphic array type. Why - we are not able to define variable as array of some variable - so input have to be anyarray (and we can do copy of this type) or we to calculate result in one expression used in RETURN statement.But there is one workaround. We can use a OUT polymorphic parameter - function with one OUT parameter is scalar function still.


-- we cannot do
CREATE OR REPLACE FUNCTION foo(a anyelement)
RETURNS anyarray AS $$
DECLARE b a%type[]; 
BEGIN
  ...

-- we can
CREATE OR REPLACE FUNCTION foo(a anyelement, OUT b anyarray)
AS $$
BEGIN
  b := array_fill(a, ARRAY[1]);
  ...

the longest common substring of two strings

2010-06-04T00:59:00.000-07:00

HelloI had to solve one simple task - to find the longest common substring only in SQL:I have a two functions - one returns all substrings of entered string:


CREATE OR REPLACE FUNCTION substrings(text) 
RETURNS text[] AS $$ 
SELECT array(SELECT DISTINCT substring($1 from i for j) 
                FROM generate_series(1, length($1)) g(i), 
                     generate_series(1, length($1)) h(j)
            )
$$ LANGUAGE sql;

postgres=# SELECT substrings('pavel');
                     substrings                      
-----------------------------------------------------
 {e,vel,avel,p,pav,a,pavel,pa,av,ve,ave,l,pave,el,v}
(1 row)

Second function returns the longest common substrings:


CREATE OR REPLACE FUNCTION max_common_substring(text, text) 
RETURNS text[] AS $$ 
  SELECT ARRAY(SELECT x 
                  FROM (SELECT $1, x, length(x), rank() OVER (ORDER BY length(x) DESC) 
                           FROM unnest(substrings($2)) g(x) 
                          WHERE strpos($1,x) > 0) xx 
                 WHERE rank = 1) 
$$ LANGUAGE sql;

postgres=# SELECT max_common_substring('stěhula','stěhule');
 max_common_substring 
----------------------
 {stěhul}
(1 row)

function to_string and to_array

2010-05-04T12:56:00.000-07:00

Hello

I like function string_to_array and array_to_string. The power of these functions is unlimited. Last week somebody noticed problems with NULLs related to string_to_array function. It is true - these function doesn't handle NULL well :(.


postgres=# SELECT string_to_array('1,2,3,NULL,5',',')::int[];
ERROR:  invalid input syntax for integer: "NULL"

I would to prepare new version of these function (with better support of NULL) - functions to_array and to_string. It can be prepared for 9.1.


CREATE OR REPLACE FUNCTION to_string(anyarray, sep text, nullstr text DEFAULT '') 
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT coalesce(v::text, $3) 
                                FROM unnest($1) g(v)),
                       $2)
$$ LANGUAGE sql;

postgres=# select to_string(array[1,2,3,4,null,5],','); 
 to_string  
------------
 1,2,3,4,,5
(1 row)

postgres=# select to_string(array[1,2,3,4,null,5],',','<NULL>');
    to_string     
------------------
 1,2,3,4,<NULL>,5
(1 row)

CREATE OR REPLACE FUNCTION to_array(text, sep text, nullstr text DEFAULT '') 
RETURNS text[] AS $$ 
  SELECT ARRAY(SELECT CASE 
                           WHEN v = $3 THEN NULL::text 
                           ELSE v END 
                  FROM unnest(string_to_array($1,$2)) g(v)) 
$$ LANGUAGE sql;

postgres=# select to_array('1,2,3,4,,5',',');
     to_array     
------------------
 {1,2,3,4,NULL,5}
(1 row)

postgres=# select to_array('1,2,3,4,<NULL>,5',',','<NULL>');
     to_array     
------------------
 {1,2,3,4,NULL,5}
(1 row)

postgres=# select to_array('1,2,3,,5',',')::int[];
    to_array    
----------------
 {1,2,3,NULL,5}
(1 row)

These SQL functions can be used for now.

Pavel

two small functions for arrays

2010-04-21T02:41:00.000-07:00

Hello,If you miss some functions for arrays look here:


CREATE OR REPLACE FUNCTION array_append_distinct(anyarray, anyelement) 
RETURNS anyarray AS $$ 
  SELECT ARRAY(SELECT unnest($1) union SELECT $2) 
$$ LANGUAGE sql;

postgres=# select array_append_distinct(array[1,2,3,4],1);
 array_append_distinct 
-----------------------
 {1,2,3,4}
(1 row)

postgres=# select array_append_distinct(array[1,2,3,4],7);
 array_append_distinct 
-----------------------
 {1,2,3,4,7}
(1 row)

CREATE OR REPLACE FUNCTION array_erase(anyarray, anyelement) 
RETURNS anyarray AS $$
  SELECT ARRAY(SELECT v FROM unnest($1) g(v) WHERE v <> $2)
$$ LANGUAGE sql;

postgres=# select array_erase(array[1,2,3,2,5], 2);
 array_erase 
-------------
 {1,3,5}
(1 row)

postgres=# select array_erase(array[1,2,3,2,5], 1);
 array_erase 
-------------
 {2,3,2,5}
(1 row)

frequent mistakes in plpgsql design

2010-04-20T04:28:00.000-07:00

Hello

last days I had to correct some typical errors in audited code. There are:

non-use coalesce:


-- bad
BEGIN
  IF x1 IS NOT NULL THEN
    s := s || x1 || ',';
  ELSE
    s := s || 'NULL, ';
  END IF;
  IF x2 IS NOT NULL THEN ...

-- good
BEGIN
  s := coalesce(x1 || ',', 'NULL,') || ...

using implicit cast date to text:


-- bad
BEGIN
  month = substring(current_date::text FROM 6 FOR 2)::int;

-- good
BEGIN
  month = substring(to_char(current_date,'YYYY-MM-DD') FROM 6 FOR 2)::int;

-- but better
BEGIN
  month = EXTRACT(month FROM current_date);

using EXECUTE instead PERFORM
using explicit cursor instead FOR IN SELECT

unsecured dynamic SQL:


--bad
EXECUTE 'SELECT ' || column_name || ' FROM ' || table_name
           || ' WHERE ' || column_name || e'=\'' || some_variable || e'\''
  INTO var;

--good
EXECUTE 'SELECT ' quote_ident(column_name) || ' FROM ' || quote_ident(table_name)
           || WHERE ' || quote_ident(column_name) || '=' || quote_literal(some_variable)
  INTO var;

-- or on 8.4 and higher
EXECUTE 'SELECT ' quote_ident(column_name) || ' FROM ' || table_name::regclass
           || WHERE ' || quote_ident(column_name) || '= $1'
  INTO var
  USING some_variable

--bad
CREATE OR REPLACE FUNCTION create_table(schemaname varchar, tablename varchar)
RETURNS void AS $$
BEGIN
  EXECUTE 'CREATE TABLE ' || coalesce(schemaname || '.','') || tablename;
  RETRUN;
END;
$$ LANGUAGE plpgsql;

--good
CREATE OR REPLACE FUNCTION create_table(schemaname varchar, tablename varchar)
RETURNS void AS $$
BEGIN
  EXECUTE 'CREATE TABLE ' || coalesce(quote_ident(schemaname) || '.','') || quote_ident(tablename);
  RETURN;
END;
$$ LANGUAGE plpgsql;

long lines - 100 chars per line is perfect
too short functions - Don't wrap any and only one SQL statement

Macros for epsql

2009-12-31T02:15:00.000-08:00

HelloI finished scripting in psql. Now, we are able to write some simply macros for epsql. This feature allows some better customisation. The syntax is trivial:


\newcommand string
... commands ...
\endnewcommand

We can use a parameters inside command definition. It is usual psql variables named 1 .. 10.


\newcommand desc
\ifdef 1
\d :1
\else
\echo 'missing table name'
\endifdef
\endnewcommand

Usage is very simple. New command is written to .psqlrc file. After registration, we can use new command:


postgres=# desc tab
       Table "public.tab"
┌────────┬─────────┬───────────┐
│ Column │  Type   │ Modifiers │
├────────┼─────────┼───────────┤
│ a      │ integer │           │
│ b      │ integer │           │
│ c      │ integer │           │
└────────┴─────────┴───────────┘
Indexes:
    "ff" btree (a)

postgres=# desc
missing table name
postgres=#

You can download this patch from pgFoundry.

Samples

I wrote some macros:

Top Ten


\newcommand \tt
\ifdef 1
select relname, relpages, reltuples from pg_class order by relpages desc limit :1;
\else
select relname, relpages, reltuples from pg_class order by relpages desc;
\endifdef
\endnewcommand

Usage:


postgres=# \tt 3
┌──────────────┬──────────┬───────────┐
│   relname    │ relpages │ reltuples │
├──────────────┼──────────┼───────────┤
│ pg_proc      │       54 │      2232 │
│ pg_depend    │       41 │      5557 │
│ pg_attribute │       36 │      1960 │
└──────────────┴──────────┴───────────┘
(3 rows)

Regtype


\newcommand \rt
select :{1}::regtype as :[1];
\endnewcommand

Usage:


postgres=# \rt 23
┌─────────┐
│   23    │
├─────────┤
│ integer │
└─────────┘
(1 row)

Show tables


\newcommand show tables
\ifdef 1
\dt :1
\else
\dt
\endifdef
\endnewcommand

Usage:


postgres=# show tables
        List of relations
┌────────┬──────┬───────┬───────┐
│ Schema │ Name │ Type  │ Owner │
├────────┼──────┼───────┼───────┤
│ public │ tab  │ table │ pavel │
└────────┴──────┴───────┴───────┘
(1 row)

show create table


\newcommand show create table
\echo `/usr/local/pgsql/bin/pg_dump --schema-only -t :1 :DBNAME| grep -v -P '(^SET)|(^[-])|(^ALTER)|(^$)'`
\endnewcommand

Usage:


postgres=# show create table tab
CREATE TABLE tab (
    a integer,
    b integer,
    c integer
);
CREATE INDEX ff ON tab USING btree (a);

New release of epsql

2009-12-18T02:07:00.000-08:00

I released a new version of epsql - for 8.5. More on http://www.postgres.cz/index.php/Enhanced-psql.

enhanced psql console - second version

2009-12-15T05:01:00.000-08:00

HelloI am working on new version of epsql. It has almost all old features and some new:

Scripting support

I add support for "if" and "forc" statements. It is a complement to new "DO" statement. DO is nice, but doesn't allow parametrisation and doesn't return a value. Iteration over cursor hasn't these limits:


\timing off
\set VERBOSITY terse
BEGIN;
DECLARE t CURSOR FOR SELECT generate_series(1,5) g;
\forc t
  \if :g % 2 = 0
    DECLARE t2 CURSOR FOR SELECT generate_series(1,4) z;
    \forc t2
      SELECT :g AS g, :z AS z;
    \endforc t2
    CLOSE t2;
  \endif
\endforc t
CLOSE t;
COMMIT;

\timing off
\set VERBOSITY terse
BEGIN;
DECLARE t CURSOR FOR SELECT generate_series(1,3) g;
\forc t
  DECLARE t2 CURSOR FOR SELECT generate_series(1,4) z;
  \forc t2
    DECLARE t3 CURSOR FOR SELECT generate_series(1,3) j;
    \forc t3
      DECLARE t4 CURSOR FOR SELECT generate_series(1,5) k;
      \forc t4
        SELECT :g AS g, :z AS z, :j AS j, :k AS k;
      \endforc t4
      CLOSE t4;
    \endforc t3
    CLOSE t3;
  \endforc t2
  CLOSE t2;
\endforc t
CLOSE t;
COMMIT;

\if position('PostgreSQL 8.5' in version()) <> 0
  \echo 'PostgreSQL verze 8.5'
\else
  \echo 'Other version of PostgreSQL'
\endif

BEGIN;
DECLARE t CURSOR 
   FOR SELECT table_schema || '.' || table_name AS tn 
          FROM information_schema.tables 
         WHERE table_schema = 'pg_catalog';
\forc t
  SELECT * FROM :tn LIMIT 10;
\endforc
COMMIT;

quote_ident and quote_literal integration

Using psql variables could be dificult, when we would to use it correctly. psql doesn't have a syntax for automatic quoting. epsql will use :[var] for quoting as identifier and :{var} for quoting as literal:


postgres=# \set x 'my tab'
postgres=# select * from :[x];
ERROR:  relation "my tab" does not exist
LINE 1: select * from "my tab";

postgres=# select :{x};
 ?column? 
----------
 my tab
(1 row)

-- versus
postgres=# select :x;
ERROR:  column "my" does not exist
LINE 1: select my tab;
               ^

How not to write a plpgsql functions

2009-12-13T12:16:00.000-08:00

I found very strange PL/pgSQL code. PL/pgSQL has some specifics, and is good, if programmer has good knowledge about it. The basic rule - don't repeat string or array concat too often inside loop in function. So bad code:


CREATE OR REPLACE FUNCTION generate_uuid_v4() RETURNS uuid AS $$
DECLARE
    value VARCHAR(36);
BEGIN
    value =          lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || '-';
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || '-';
    value = value || lpad((to_hex((ceil(random() * 255)::int & 15) | 64)), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || '-';
    value = value || lpad((to_hex((ceil(random() * 255)::int & 63) | 128)), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || '-';
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    RETURN value::uuid;
END;
$$ LANGUAGE 'plpgsql';

postgres=# select generate_uuid_v4() from generate_series(1,10000);
           generate_uuid_v4           
--------------------------------------
 9833de6f-4ba6-49f1-8d5f-d98d10d79346
 340c6008-b8fe-4267-bcc5-96bcddb5f675
...
 6f54c524-bd02-4efc-84b3-b56d1c93f3ec
 96ee245e-2990-4842-9d36-45a66dab8adb
Time: 407,082 ms

This is good sample of wrong using PL/pgSQL language. PLPerl, maybe PLPython will do much better work. When we cannot use these languages, we have to use some tricks. With some modifications original code - we can have 2x faster code.


CREATE OR REPLACE FUNCTION _generate_uuid_v4() RETURNS uuid AS $$
BEGIN
    RETURN  (lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || '-'
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || '-'
   || lpad((to_hex((ceil(random() * 255)::int & 15) | 64)), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || '-'
   || lpad((to_hex((ceil(random() * 255)::int & 63) | 128)), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || '-'
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0'))::uuid;
END;
$$ LANGUAGE 'plpgsql'

postgres=# select _generate_uuid_v4() from generate_series(1,10000);
          _generate_uuid_v4           
--------------------------------------
 5134fe69-5d2a-4419-bf84-d72dc290f72f
 919e8663-b384-4eae-89d9-1026d6be4127
 f23f8f4f-6914-47e8-983f-155ace0d8860
...
 39951d8f-fbd1-48bf-9c5d-157eefbd5af6
 73318784-2cfa-4bcc-ad42-307fc4c173fc
Time: 203,516 ms

Original needs 400ms for ten thousand values. Edited code needs only 200ms for same number of values.This code should be inlined - when we use SQL language:


CREATE OR REPLACE FUNCTION _generate_uuid_v4() RETURNS uuid AS $$
SELECT (lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || '-'
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || '-'
   || lpad((to_hex((ceil(random() * 255)::int & 15) | 64)), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || '-'
   || lpad((to_hex((ceil(random() * 255)::int & 63) | 128)), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || '-'
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0'))::uuid;
$$ LANGUAGE SQL;

postgres=# select _generate_uuid_v4() from generate_series(1,10000);
          _generate_uuid_v4           
--------------------------------------
 e725eb84-67ad-485e-bec2-d73485db4e69
 49f605d1-226e-4d9e-8457-30a925c98a0c
 ed759055-23e7-4322-a98a-552f65a297ae
...
 414e62c3-1b84-4bc9-8fd7-ba8bd0a72403
 03e27594-42c0-4ec9-a493-636b6d130aad
Time: 148,184 ms

With SQL function we safe other 50ms. PLpgSQL is good language - but it isn't C language or Fortran. It needs some different manners.

Aggregate function MEDIAN in PostgreSQL

2009-11-25T11:18:00.000-08:00

Searching and calculating Median in databases was terrible. Still median isn't ANSI SQL aggregate function. There are two commons method how to calculate median of some column. First - very old, and very slow based on self join alchemy, second - new - based on analytic function. Now, I will be test some newer methods on one million rows large table:

postgres=# create table milrows(a real);
CREATE TABLE
Time: 7,975 ms
postgres=# insert into milrows select random()*1000000 from generate_series(1,1000000);
INSERT 0 1000000
Time: 6863,575 ms

simple scan on this table takes 200ms

Frompostgres=# select avg(a) from milrows ;
       avg        
──────────────────
 499515.883033113
(1 row)

Time: 200,176 ms

In 8.4 we can use analytic functions. These functions uses TupleStore - internal store feature - it allows work with very large tables - limit is free space on disc.

Analytic methods

--Joe Celko's method
postgres=# SELECT avg(a)::float
              FROM (SELECT a, row_number() OVER (ORDER BY a asc) AS hi,
                              count(*) OVER () + 1 - row_number() OVER (ORDER BY a) AS lo
                       FROM milrows) qs
             WHERE hi IN (lo-1,lo,lo+1);
      avg      
───────────────
 499188.546875
(1 row)

Time: 4922,678 ms

-- Andrew Gierth's method 
postgres=# select avg(a)
   from ( select a, row_number() over (order by a),count(*) over () from milrows ) s
  where row_number between floor((count::float8-1)/2+1) and ceil((count::float8-1)/2+1)
;
      avg      
───────────────
 499188.546875
(1 row)

Time: 5021,001 ms

-- modified Andrew's method (count(*) over () is slow)
postgres=# select avg(a)
   from ( select a, row_number() over (order by a),(select count(*) from milrows) as count from milrows ) s
  where row_number between floor((count::float8-1)/2+1) and ceil((count::float8-1)/2+1)
;
      avg      
───────────────
 499188.546875
(1 row)

Time: 3931,922 ms

Array based methods

Next methods are based on using an arrays. These methods are fast, but limit for this methods is size of operation memory. For very very large tables could to take all application memory.

--Regina's method -- it's not 100% correct http://www.postgresonline.com/journal/index.php?/archives/67-Build-Median-Aggregate-Function-in-SQL.html#extended

CREATE OR REPLACE FUNCTION array_median(double precision[])
  RETURNS double precision AS
$$
    SELECT CASE WHEN array_upper($1,1) = 0 THEN null 
                ELSE asorted[ceiling(array_upper(asorted,1)/2.0)]::double precision END
       FROM (SELECT ARRAY(SELECT $1[n] 
                FROM generate_series(1, array_upper($1, 1)) AS n
               WHERE $1[n] IS NOT NULL
               ORDER BY $1[n]) As asorted) As foo 
$$ LANGUAGE 'sql' IMMUTABLE;

CREATE AGGREGATE median(double precision) (
  SFUNC=array_append,
  STYPE=double precision[],
  FINALFUNC=array_median
);

postgres=# select median(a) from milrows ;
^CCancel request sent
ERROR:  canceling statement due to user request -- killed 5 minutes !don't use array_append for bigger arrays (length > 10000)
postgres=# 

--My method
postgres=# create or replace function median(anyarray) 
returns double precision as $$
  select ($1[array_upper($1,1)/2+1]::double precision + $1[(array_upper($1,1)+1) / 2]::double precision) / 2.0; 
$$ language sql immutable strict;
CREATE FUNCTION
Time: 1,557 ms

Time: 2574,677 ms
postgres=# select median(array(select a from milrows where a is not null order by a));
    median     
───────────────
 499188.546875
(1 row)

Time: 2555,342 ms

This week I added support for median aggregate to orafce package. You can download it from url http://pgfoundry.org/frs/download.php/2472/orafce-3.0.2-devel.tar.gz . Function median use some fetures 8.4 and needs 8.4 - it isn't supported on PostgreSQL 8.3 and older.

-- orafce 3.0.2 median (needs PostgreSQL 8.4 and higher)

postgres=# select median(a::float8) from milrows;
    median     
───────────────
 499188.546875
(1 row)

Time: 687,577 ms

It's very fast - if your table has about one million rows (1000000) you can use it (for this table size takes max. 15MB RAM (for one column)).

longtime plpgsql misfeature removed

2009-11-14T10:23:00.000-08:00

Tom Lane did refactoring of plpgsql source code. These changes are very very important. plpgsql is good language - simple, robust with good error diagnostic. But it had one bizarre behave. plpgsql connects two worlds - procedural ADA like code and SQL statements. Usually there are not problems. But there are one exception - collision of identifiers. Older behave was too simply. Plpgsql identifiers win every-time. It was a source of some bizarre bugs. Look on code:

postgres=# select * from omega;
 a  
────
 10
 20
 30
(3 rows)

create or replace function foo() 
returns void as $$
#variable_conflict use_variable -- compatible with 8.4 and older
declare a integer; 
begin 
  for a in select a from omega 
  loop 
    raise notice '%', a;
  end loop; 
end; 
$$ language plpgsql;

This code is very simple. Just show content of table omega.

postgres=# select foo();
NOTICE:  <null>
NOTICE:  <null>
NOTICE:  <null>
 foo 
─────
 
(1 row)

or not? Why we don't see values 10,20,30? Because interpret prefer plpgsql identifier against to sql identifier omega.a. This bug is very strange and some time is very difficult to find it. But it is a history. plpgsql 8.5 is much more cleaner. Wrong code raises en exception:

postgres=# 
create or replace function foo() 
returns void as $$
declare a integer;                                              
begin              
  for a in select a from omega 
  loop                         
    raise notice '%', a;
  end loop; 
end; 
$$ language plpgsql;
CREATE FUNCTION
Time: 3,501 ms
postgres=# select foo();
ERROR:  column reference "a" is ambiguous
LINE 1: select a from omega
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  select a from omega
CONTEXT:  PL/pgSQL function "foo" line 3 at FOR over SELECT rows

We could to fix this problem and we get a good answer:

postgres=# 
create or replace function foo() 
returns void as $$
declare a integer; 
begin 
  for a in select omega.a from omega 
  loop 
    raise notice '%', a;
  end loop; 
end; 
$$ language plpgsql;
CREATE FUNCTION
Time: 2,289 ms
postgres=# select foo();
NOTICE:  10
NOTICE:  20
NOTICE:  30
 foo 
─────
 
(1 row)

I am very happy from this changes. Thanks Tom.

Named function parameters - a feature of PostgreSQL 8.5

2009-10-29T03:06:00.000-07:00

Hello

New PostgreSQL 8.5 alfa 2 allows to use named parameters. You may to know this feature from Oracle or MS SQL server. This feature is possible in PostgreSQL now too. Following function add n working days to specified date. What is new? You can specify a free days (default is based on europen traditions).

CREATE TYPE dayname AS ENUM ('sun','mon','tue','wed','thu','fri','sat');

CREATE OR REPLACE FUNCTION dow(dayname) 
RETURNS int AS $$ 
SELECT CASE $1 
           WHEN 'sun' THEN 0 
           WHEN 'mon' THEN 1 
           WHEN 'tue' THEN 2 
           WHEN 'wed' THEN 3 
           WHEN 'thu' THEN 4 
           WHEN 'fri' THEN 5 
           WHEN 'sat' THEN 6 END; 
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION dows(dayname[]) 
RETURNS int[] AS $$ 
SELECT ARRAY(SELECT dow(d) FROM UNNEST($1) d(d))
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION addwdays(date, int, freedays text[] = ARRAY['sat','sun']) 
RETURNS date AS $$
SELECT ad 
   FROM (SELECT $1 + i as ad, 
                extract (dow from $1 + i), 
                sum(CASE WHEN EXTRACT(dow FROM $1 + i) = ANY(dows($3::dayname[])) THEN 0 ELSE 1 END) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS x 
            FROM generate_series(1,1000) g(i)) s 
  WHERE x = $2 AND date_part <> ALL(dows($3::dayname[]));
$$ LANGUAGE sql;

postgres=# select current_date, addwdays(current_date,1);
    date    │  addwdays  
────────────┼────────────
 2009-10-29 │ 2009-10-30
(1 row)

Time: 1,768 ms
postgres=# select current_date, addwdays(current_date,1, ARRAY['fri'] AS freedays);
    date    │  addwdays  
────────────┼────────────
 2009-10-29 │ 2009-10-31
(1 row)

Please, test it.
see http://www.postgresql.org/about/news.1152

Dynamic access to record fields in PL/pgSQL

2009-10-01T05:00:00.000-07:00

PL/pgSQL isn't dynamic language. Usually it isn't problem. This language is best as glue of SQL statements. One typical feature is strongly static access to record or row fields. But sometime we need dynamic access. This is very hard task in older version. Some better situation is in 8.4, and maybe in 8.5, this problem is definitely out. New an enhancing of hstore module allows conversion from ROW type to hstore type (this is similar to hash table in Perl). We could to use this functionality:

postgres=# SELECT * FROM foo;
 a  | b  |   c    
----+----+--------
 10 | 20 | Pavel
 30 | 40 | Zdenek
(2 rows)

postgres=# select (each(hstore(foo))).* from foo;
 key | value  
-----+--------
 a   | 10
 b   | 20
 c   | Pavel
 a   | 30
 b   | 40
 c   | Zdenek
(6 rows)

CREATE OR REPLACE FUNCTION trgfce() 
RETURNS trigger AS $$
DECLARE r record; 
BEGIN 
  FOR r IN SELECT (each(hstore(NEW))).* 
  LOOP 
    RAISE NOTICE 'key:%, value: %', r.key, r.value; 
  END LOOP; 
  RETURN new; 
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE trgfce();

postgres=# INSERT INTO foo VALUES(80,90,'Zbynek');
NOTICE:  key:a, value: 80
NOTICE:  key:b, value: 90
NOTICE:  key:c, value: Zbynek
INSERT 0 1

MySQL functions for PostgreSQL

2009-08-22T09:26:00.000-07:00

Hello

I found article about MySQL function field. PostgreSQL doesn't support similar function (it is nice together with ORDER BY expression clause), but with new variadic parameters is easy to develop it in pg:

CREATE OR REPLACE FUNCTION field(text, variadic text[])
RETURNS int AS $$
  SELECT i
     FROM generate_subscripts($2,1) g(i)
    WHERE $1 = $2[i]
    UNION ALL
    SELECT 0
    LIMIT 1
$$ LANGUAGE sql STRICT;

An using looks like:

select * from pet order by field(species, 'cat', 'dog', 'bird');

+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birthday   | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
+----------+--------+---------+------+------------+------------+

When I played with it, I ported mostly used MySQL functions to PostgreSQL. I hope, so this file can help somebody with porting applications to PostgreSQL or with better orientation in PostgreSQL. Lot of thing with strings or date PostgreSQL is done little bit different than MySQL - these functions can help.

String Functions

CREATE OR REPLACE FUNCTION concat(variadic str text[])
RETURNS text AS $$
SELECT array_to_string($1, '');
$$ LANGUAGE sql

CREATE OR REPLACE FUNCTION concat_ws(separator text, variadic str text[])
RETURNS text as $$
SELECT array_to_string($2, $1);
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION elt(int, VARIADIC text[])
RETURNS text AS $$
SELECT $2[$1];
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION field(varchar, VARIADIC text[])
RETURNS int AS $$
SELECT i
FROM generate_subscripts($2,1) g(i)
WHERE $1 = $2[i]
UNION ALL
SELECT 0
LIMIT 1
$$ LANGUAGE sql STRICT;

CREATE OR REPLACE FUNCTION find_in_set(str text, strlist text)
RETURNS int AS $$
SELECT i
   FROM generate_subscripts(string_to_array($2,','),1) g(i)
  WHERE (string_to_array($2, ','))[i] = $1
  UNION ALL
  SELECT 0
  LIMIT 1
$$ LANGUAGE sql STRICT;

CREATE OR REPLACE FUNCTION hex(int)
RETURNS text AS $$
SELECT upper(to_hex($1));
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION hex(bigint)
RETURNS text AS $$
SELECT upper(to_hex($1));
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION hex(text)
RETURNS text AS $$
SELECT upper(encode($1::bytea, 'hex'))
$$ LANGUAGE sql;

/*
* char is keyword, double quotes are necessary.
*
*  postgres=# select "char"(77,121,83,81,'76');
*  char
* -------
*  MySQL
*/
CREATE OR REPLACE FUNCTION "char"(VARIADIC int[])
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT chr(unnest($1))),'')
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION lcase(str text)
RETURNS text AS $$
SELECT lower($1)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION left(str text, len int)
RETURNS text AS $$
SELECT substring($1 FROM 1 FOR $2)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION locate(substr text, str text)
RETURNS int AS $$
SELECT position($1 in $2)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION reverse(str text)
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT substring($1 FROM i FOR 1)
                                FROM generate_series(length($1),1,-1) g(i)),
                       '')
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION right(str text, len int)
RETURNS text AS $$
SELECT substring($1 FROM length($1) - $2 FOR $2)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION space(n int)
RETURNS text AS $$
SELECT repeat(' ', $1)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION strcmp(text, text)
RETURNS int AS $$
SELECT CASE WHEN $1 < $2 THEN -1
WHEN $1 > $2 THEN 1
ELSE 0 END;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION substring_index(str text, delim text, count int)
RETURNS text AS $$
SELECT CASE WHEN $3 > 0 
THEN array_to_string((string_to_array($1, $2))[1:$3], $2)
ELSE array_to_string(ARRAY(SELECT unnest(string_to_array($1,$2))
                             OFFSET array_upper(string_to_array($1,$2),1) + $3),
                     $2)
END
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION ucase(str text)
RETURNS text AS $$
SELECT upper($1)
$$ LANGUAGE sql;

CREATE CAST (bytea AS text) WITHOUT FUNCTION AS ASSIGNMENT;

/*
* SELECT hex('žlutý kůň'), unhex(hex('žlutý kůň'))
*/
CREATE OR REPLACE FUNCTION unhex(text)
RETURNS text AS $$
SELECT decode($1, 'hex')::text;
$$ LANGUAGE sql;

Note: On some newer PostgreSQL the function unhex doesn't work well - you can use a second version, that works well for ascii chars or for utf8:


CREATE OR REPLACE FUNCTION unhex(text)
RETURNS text AS $$
SELECT encode(decode($1, 'hex'),'escape');
$$ LANGUAGE sql;

-- UTF8 version
CREATE OR REPLACE FUNCTION unhex(text)
RETURNS text AS $$
SELECT convert_from(decode($1, 'hex'),'utf8');
$$ LANGUAGE sql;

if you have a superuser rights, you can use a "better" auxilary function, that ensure casting from bytea to text type without changes (it's relative dirty trick):


CREATE OR REPLACE FUNCTION public.direct_bytea_to_cstring(bytea)
 RETURNS cstring
 LANGUAGE internal
 IMMUTABLE STRICT
AS $function$textout$function$

CREATE OR REPLACE FUNCTION unhex(text)
RETURNS text AS $$
SELECT direct_bytea_to_cstring(decode($1, 'hex'))::text;
$$ LANGUAGE sql;
CREATE FUNCTION

You can to try use cast (and use a original function) - you have to have a superuser rights too (this cast can be entered by postgres user only):


create cast (bytea as text) without function;

Date, time Functions

/*
* postgres=# select adddate('2008-01-02','31 day');
*   adddate
* -----------
*  2008-02-02
*/
CREATE OR REPLACE FUNCTION adddate(date, interval)
RETURNS date AS $$
SELECT ($1 + $2)::date; $$
LANGUAGE sql;

CREATE OR REPLACE FUNCTION curdate()
RETURNS date AS $$
SELECT CURRENT_DATE
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION convert_tz(dt timestamp, from_tz text, to_tz text)
RETURNS timestamp AS $$
SELECT ($1 AT TIME ZONE $2) AT TIME ZONE $3;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION date(anyelement)
RETURNS date AS $$
SELECT $1::date;
$$ LANGUAGE sql;

SELECT OR REPLACE FUNCTION datediff(date, date)
RETURNS int AS $$
SELECT $1 - $2
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION date_add(date, interval)
RETURNS date AS $$
SELECT adddate($1, $2)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION date_format(date, text)
RETURNS text AS $$
SELECT to_char($1, _mysqlf_pgsql($2))
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION date_format(timestamp, text)
RETURNS text AS $$
SELECT to_char($1, _mysqlf_pgsql($2))
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION date_sub(date, interval)
RETURNS date AS $$
SELECT ($1 - $2)::date;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION dayofmonth(date)
RETURNS int AS $$
SELECT EXTRACT(day from $1)::int
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION day(date)
RETURNS int AS $$
SELECT dayofmonth($1)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION dayname(date)
RETURNS text AS $$
SELECT to_char($1, 'TMDay')
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION dayofweek(date)
RETURNS int AS $$
SELECT EXTRACT(dow FROM $1)::int
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION dayofyear(date)
RETURNS int AS $$
SELECT EXTRACT(doy FROM $1)::int
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION from_days(int)
RETURNS date AS $$
SELECT date '0001-01-01bc' + $1
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION from_unixtime(double precision)
RETURNS timestamp AS $$
SELECT to_timestamp($1)::timestamp
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _mysqlf_pgsql(text)
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT s
FROM (SELECT CASE WHEN substring($1 FROM i FOR 1) <> '%'
AND substring($1 FROM i-1 FOR 1) <> '%'
THEN substring($1 FROM i for 1)
ELSE CASE substring($1 FROM i FOR 2)
WHEN '%H' THEN 'HH24'
WHEN '%p' THEN 'am'
WHEN '%Y' THEN 'YYYY'
WHEN '%m' THEN 'MM'
WHEN '%d' THEN 'DD'
WHEN '%i' THEN 'MI'
WHEN '%s' THEN 'SS'
WHEN '%a' THEN 'Dy'
WHEN '%b' THEN 'Mon'
WHEN '%W' THEN 'Day'
WHEN '%M' THEN 'Month'
END
END s
FROM generate_series(1,length($1)) g(i)) g
WHERE s IS NOT NULL),
'')
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION get_format(text, text)
RETURNS text AS $$
SELECT CASE lower($1)
WHEN 'date' THEN
CASE lower($2)
WHEN 'usa' THEN '%m.%d.%Y'
WHEN 'jis' THEN '%Y-%m-%d'
WHEN 'iso' THEN '%Y-%m-%d'
WHEN 'eur' THEN '%d.%m.%Y'
WHEN 'internal' THEN '%Y%m%d'
END
WHEN 'datetime' THEN
CASE lower($2)
WHEN 'usa' THEN '%Y-%m-%d %H-.%i.%s'
WHEN 'jis' THEN '%Y-%m-%d %H:%i:%s'
WHEN 'iso' THEN '%Y-%m-%d %H:%i:%s'
WHEN 'eur' THEN '%Y-%m-%d %H.%i.%s'
WHEN 'internal' THEN '%Y%m%d%H%i%s'
END
WHEN 'time' THEN
CASE lower($2)
WHEN 'usa' THEN '%h:%i:%s %p'
WHEN 'jis' THEN '%H:%i:%s'
WHEN 'iso' THEN '%H:%i:%s'
WHEN 'eur' THEN '%H.%i.%s'
WHEN 'internal' THEN '%H%i%s'
END
END;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION hour(time)
RETURNS int AS $$
SELECT EXTRACT(hour FROM $1)::int;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION hour(timestamp)
RETURNS int AS $$
SELECT EXTRACT(hour FROM $1)::int;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION last_day(date)
RETURNS date AS $$
SELECT (date_trunc('month',$1 + interval '1 month'))::date - 1
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION makedate(year int, dayofyear int)
RETURNS date AS $$
SELECT (date '0001-01-01' + ($1 - 1) * interval '1 year' + ($2 - 1) * interval '1 day'):: date
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION maketime(int, int, double precision)
RETURNS time AS $$
SELECT time '00:00:00' + $1 * interval '1 hour' + $2 * interval '1 min'
+ $3 * interval '1 sec'
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION minute(timestamp)
RETURNS int AS $$
SELECT EXTRACT(minute FROM $1)::int
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION month(date)
RETURNS int AS $$
SELECT EXTRACT(month FROM $1)::int
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION monthname(date)
RETURNS text AS $$
SELECT to_char($1, 'TMMonth')
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION str_to_date(text, text)
RETURNS date AS $$
SELECT to_date($1, _mysqlf_pgsql($2))
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION time(timestamp)
RETURNS time AS $$
SELECT $1::time
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION to_days(date)
RETURNS int AS $$
SELECT $1 - '0001-01-01bc'
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION unix_timestamp()
RETURNS double precision AS $$
SELECT EXTRACT(epoch FROM current_timestamp)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION unix_timestamp(timestamp)
RETURNS double precision AS $$
SELECT EXTRACT(epoch FROM $1)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION year(date)
RETURNS int AS $$
SELECT EXTRACT(year FROM $1)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION week(date)
RETURNS int AS $$
SELECT EXTRACT(week FROM $1)::int;
$$ LANGUAGE sql;

GROUP_CONCAT

PostgreSQL doesn't support aggregate function group_concat. But you can use aggregate array_agg:

postgres=# select * from omega;
+---+
| x |
+---+
| 1 |
| 3 |
| 6 |
+---+
(3 rows)

postgres=# select array_to_string(array_agg(x),',') from omega;
+-----------------+
| array_to_string |
+-----------------+
| 1,3,6           |
+-----------------+
(1 row)

If you search more complete MySQL API, look on mysqlcompat project.RegardsPavel

epsql available

2009-08-13T08:28:00.000-07:00

Hello

Today I uploaded patch enhancing psql on pgfoundry. You can download it from url http://pgfoundry.org/frs/download.php/2335/epsql.diff. This patch should be applied on postgresql 8.4 source:

cd postgresql-8.4.0/src/bin/psql
patch -p1 < epsql.diff
make clean
make all
su
make install

Enhanced psql has new metacommand \lf and new formating option linestyle (+2 new border styles).

\lf

\lf print function's source code (with and without row numbers):

postgres=# \lf foo(int)
****    CREATE OR REPLACE FUNCTION public.foo(_a integer)
****     RETURNS integer
****     LANGUAGE plpgsql
****    AS $function$
1    BEGIN
2      RETURN _a + 1;
3    END;
****    $function$

postgres=# \lf- foo(int)
CREATE OR REPLACE FUNCTION public.foo(_a integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN _a + 1;
END;
$function$

smart wrap mode

original wrap mode:

postgres=# \pset format wrapped
Output format is wrapped.
postgres=# select * from test;
+--------------------------------------------------------------------------------------+
|                                          a                                           |
+--------------------------------------------------------------------------------------+
| Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor inci |
| didunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exerc |
| itation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dol |
| or in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.  |
| Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt m |
| ollit anim id est laborum.                                                           |
+--------------------------------------------------------------------------------------+
(1 row)

modified wrap mode:

postgres=# select * from test;
+--------------------------------------------------------------------------------------+
|                                          a                                           |
+--------------------------------------------------------------------------------------+
| Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor      |
| incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud   |
| exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute      |
| irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla   |
| pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia  |
| deserunt mollit anim id est laborum.                                                 |
+--------------------------------------------------------------------------------------+
(1 row)

Border styles

postgres=# \pset border 0
Border style is 0.
postgres=# \d pg_database
Table "pg_catalog.pg_database"
Column       Type    Modifiers
------------- --------- ---------
datname       name      not null
datdba        oid       not null
encoding      integer   not null
datcollate    name      not null
datctype      name      not null
datistemplate boolean   not null
datallowconn  boolean   not null
datconnlimit  integer   not null
datlastsysoid oid       not null
datfrozenxid  xid       not null
dattablespace oid       not null
datconfig     text[] 
datacl        aclitem[]

postgres=# \pset border 1
Border style is 1.
postgres=# \d pg_database
 Table "pg_catalog.pg_database"
 Column     |   Type    | Modifiers
---------------+-----------+-----------
datname       | name      | not null
datdba        | oid       | not null
encoding      | integer   | not null
datcollate    | name      | not null
datctype      | name      | not null
datistemplate | boolean   | not null
datallowconn  | boolean   | not null
datconnlimit  | integer   | not null
datlastsysoid | oid       | not null
datfrozenxid  | xid       | not null
dattablespace | oid       | not null
datconfig     | text[]    |
datacl        | aclitem[] |

postgres=# \pset border 2
Border style is 2.
postgres=# \d pg_database
  Table "pg_catalog.pg_database"
+---------------+-----------+-----------+
|    Column     |   Type    | Modifiers |
+---------------+-----------+-----------+
| datname       | name      | not null  |
| datdba        | oid       | not null  |
| encoding      | integer   | not null  |
| datcollate    | name      | not null  |
| datctype      | name      | not null  |
| datistemplate | boolean   | not null  |
| datallowconn  | boolean   | not null  |
| datconnlimit  | integer   | not null  |
| datlastsysoid | oid       | not null  |
| datfrozenxid  | xid       | not null  |
| dattablespace | oid       | not null  |
| datconfig     | text[]    |           |
| datacl        | aclitem[] |           |
+---------------+-----------+-----------+

postgres=# \pset border 3
Border style is 3.
postgres=# \d pg_database
  Table "pg_catalog.pg_database"
+---------------+-----------+-----------+
|    Column     |   Type    | Modifiers |
+---------------+-----------+-----------+
| datname       | name      | not null  |
+---------------+-----------+-----------+
| datdba        | oid       | not null  |
+---------------+-----------+-----------+
| encoding      | integer   | not null  |
+---------------+-----------+-----------+
| datcollate    | name      | not null  |
+---------------+-----------+-----------+
| datctype      | name      | not null  |
+---------------+-----------+-----------+
| datistemplate | boolean   | not null  |
+---------------+-----------+-----------+
| datallowconn  | boolean   | not null  |
+---------------+-----------+-----------+
| datconnlimit  | integer   | not null  |
+---------------+-----------+-----------+
| datlastsysoid | oid       | not null  |
+---------------+-----------+-----------+
| datfrozenxid  | xid       | not null  |
+---------------+-----------+-----------+
| dattablespace | oid       | not null  |
+---------------+-----------+-----------+
| datconfig     | text[]    |           |
+---------------+-----------+-----------+
| datacl        | aclitem[] |           |
+---------------+-----------+-----------+

postgres=# \pset border 4
Border style is 4.
postgres=# \d pg_database
  Table "pg_catalog.pg_database"
   Column     |   Type    | Modifiers
---------------+-----------+----------
datname       | name      | not null
---------------+-----------+----------
datdba        | oid       | not null
---------------+-----------+----------
encoding      | integer   | not null
---------------+-----------+----------
datcollate    | name      | not null
---------------+-----------+----------
datctype      | name      | not null
---------------+-----------+----------
datistemplate | boolean   | not null
---------------+-----------+----------
datallowconn  | boolean   | not null
---------------+-----------+----------
datconnlimit  | integer   | not null
---------------+-----------+----------
datlastsysoid | oid       | not null
---------------+-----------+----------
datfrozenxid  | xid       | not null
---------------+-----------+----------
dattablespace | oid       | not null
---------------+-----------+----------
datconfig     | text[]    |
---------------+-----------+----------
datacl        | aclitem[] |

Linestyles

postgres=# \pset linestyle 0
Border line style is 0.
postgres=# \d pg_user_mapping
Table "pg_catalog.pg_user_mapping"
+-----------+--------+-----------+
|  Column   |  Type  | Modifiers |
+-----------+--------+-----------+
| umuser    | oid    | not null  |
+-----------+--------+-----------+
| umserver  | oid    | not null  |
+-----------+--------+-----------+
| umoptions | text[] |           |
+-----------+--------+-----------+

postgres=# \pset linestyle 1
Border line style is 1.
postgres=# \d pg_user_mapping
Table "pg_catalog.pg_user_mapping"
┌───────────┬────────┬───────────┐
│  Column   │  Type  │ Modifiers │
├───────────┼────────┼───────────┤
│ umuser    │ oid    │ not null  │
├───────────┼────────┼───────────┤
│ umserver  │ oid    │ not null  │
├───────────┼────────┼───────────┤
│ umoptions │ text[] │           │
└───────────┴────────┴───────────┘

postgres=# \pset linestyle 2
Border line style is 2.
postgres=# \d pg_user_mapping
Table "pg_catalog.pg_user_mapping"
┌───────────┬────────┬───────────┐
│  Column   │  Type  │ Modifiers │
├───────────┴────────┴───────────┤
│ umuser    │ oid    │ not null  │
├───────────┼────────┼───────────┤
│ umserver  │ oid    │ not null  │
├───────────┼────────┼───────────┤
│ umoptions │ text[] │           │
└────────────────────────────────┘

postgres=# \pset linestyle 3
Border line style is 3.
postgres=# \d pg_user_mapping
Table "pg_catalog.pg_user_mapping"
╔═══════════╤════════╤═══════════╗
║  Column   │  Type  │ Modifiers ║
╟───────────┼────────┼───────────╢
║ umuser    │ oid    │ not null  ║
╟───────────┼────────┼───────────╢
║ umserver  │ oid    │ not null  ║
╟───────────┼────────┼───────────╢
║ umoptions │ text[] │           ║
╚═══════════╧════════╧═══════════╝

postgres=# \pset linestyle 4
Border line style is 4.
postgres=# \d pg_user_mapping
Table "pg_catalog.pg_user_mapping"
╔═══════════╤════════╤═══════════╗
║  Column   │  Type  │ Modifiers ║
╠═══════════╪════════╪═══════════╣
║ umuser    │ oid    │ not null  ║
╟───────────┼────────┼───────────╢
║ umserver  │ oid    │ not null  ║
╟───────────┼────────┼───────────╢
║ umoptions │ text[] │           ║
╚═══════════╧════════╧═══════════╝

postgres=# \pset linestyle 5
Border line style is 5.
postgres=# \d pg_user_mapping
Table "pg_catalog.pg_user_mapping"
╔═══════════╦════════╦═══════════╗
║  Column   ║  Type  ║ Modifiers ║
╠═══════════╩════════╩═══════════╣
║ umuser    │ oid    │ not null  ║
╟───────────┼────────┼───────────╢
║ umserver  │ oid    │ not null  ║
╟───────────┼────────┼───────────╢
║ umoptions │ text[] │           ║
╚════════════════════════════════╝

postgres=# \pset linestyle 6
Border line style is 6.
postgres=# \d pg_user_mapping
Table "pg_catalog.pg_user_mapping"
╔═══════════╦════════╦═══════════╗
║  Column   ║  Type  ║ Modifiers ║
╠═══════════╬════════╬═══════════╣
║ umuser    ║ oid    ║ not null  ║
╟───────────╫────────╫───────────╢
║ umserver  ║ oid    ║ not null  ║
╟───────────╫────────╫───────────╢
║ umoptions ║ text[] ║           ║
╚═══════════╩════════╩═══════════╝

Please, test it, use it.
Regards
Pavel Stehule

Experimental psql

2009-03-10T01:40:00.000-07:00

Hello

I spent some on psql's face lifting. Some new features are really experimental. I don't expect early adopting it for core. It's more game for now. epsql will be available with release 8.4. What epsql can do?

UTF8 boxes

one new border style, five new border lines styles

Stored function's listing

should help with debuging


postgres=# select test(10);
ERROR:  division by zero
CONTEXT:  PL/pgSQL function "test" line 3 at assignment

postgres=# \lf test
*** CREATE OR REPLACE FUNCTION public.test(a integer)
***  RETURNS integer
***  LANGUAGE plpgsql
*** AS $function$
1 declare b int;
2 begin
3   b := a/0;
4   return a;
5 end;
*** $function$

Cursor's support

allows using data from database for statement parameters

\fetch

postgres=# BEGIN;
BEGIN
postgres=# DECLARE c CURSOR FOR SELECT * FROM pg_database;
DECLARE CURSOR
postgres=# \fetch c \echo :datname :datcollate
template1 cs_CZ.UTF-8
postgres=# \fetch c \echo :datname :datcollate
template0 cs_CZ.UTF-8
postgres=# \fetch c \echo :datname :datcollate
postgres cs_CZ.UTF-8
postgres=# \fetch c \echo :datname :datcollate
ERROR:  (not available)
postgres=# COMMIT;
COMMIT

\fetchall

postgres=# CREATE TABLE test1(a int); CREATE TABLE test2(a int);
CREATE TABLE
CREATE TABLE
postgres=# BEGIN;
BEGIN
postgres=# DECLARE c CURSOR FOR SELECT tablename FROM pg_tables WHERE tablename LIKE 'test%';
DECLARE CURSOR
postgres=# \fetchall c DROP TABLE :tablename;
DROP TABLE
DROP TABLE
postgres=# COMMIT;
COMMIT
postgres=#

I invite any ideas, comments.

Pavel

8.3 migration helper

2009-02-20T22:45:00.000-08:00

8.3 dropped some implicit casts. I didn't watch discus about it, but I am thinking so there are two reasons. First - it shows some programmer's mistakes, second - it warns against wrong written predicates. I can speak, so all it does well. When we migrated older application, we found lot of nonsense: typically numeric data are stored in varchar column or deprecated style (every constant is string literal) like:


any_numeric_column = 'any_number' ... -- deprecated
any_numeric_column = any_number ... -- good style

Peter Eisentraut wrote module for 8.3, that enable all disabled implicit casts. This helps with migration, but doesn't help with question, where disabled casts are used in application. I wrote function, that adds warning to theses casts:


CREATE OR REPLACE FUNCTION generator_81_casts()
RETURNS void AS $$
DECLARE
  src varchar[] := '{integer,smallint,oid,date,double precision,real,time with time zone, time without time zone, timestamp with time zone, interval,bigint,numeric,timestamp without time zone}';
  fn varchar[] := '{int4out,int2out,oidout,date_out,float8out,float4out,timetz_out,time_out,timestamptz_out,interval_out,int8out,numeric_out,timestamp_out}';
  fn_name varchar;
  fn_msg varchar; fn_body varchar;
BEGIN
  FOR i IN array_lower(src,1)..array_upper(src,1) LOOP
    fn_name := 'aux_cast_func_' || replace(src[i],' ','_') ||'_to_text';
    fn_msg := '''using obsolete implicit casting from ' || src[i] || ' to text''';
    fn_body := 'CREATE OR REPLACE FUNCTION '|| fn_name || '(' || src[i] ||') RETURNS text AS $_$ BEGIN RAISE WARNING '
                  || fn_msg || ';RETURN textin(' || fn[i] || '($1)); END; $_$ LANGUAGE plpgsql IMMUTABLE';
    EXECUTE fn_body;
    -- for 8.1                                                                                                                                                                                 
    --EXECUTE 'UPDATE pg_cast SET castfunc = ''' || fn_name || '''::regproc WHERE castsource = ''' || src[i] || '''::regtype AND casttarget = ''text''::regtype';                              
   DROP CAST 'CREATE CAST (' || src[i] || ' AS text)';
   EXECUTE 'CREATE CAST (' || src[i] || ' AS text) WITH FUNCTION ' || fn_name || '(' || src[i] || ') AS IMPLICIT';
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT generator_81_casts();

DB2 SQL Procedures on Postgres? Yes, why not?

2008-12-27T12:14:00.000-08:00

I ported PL/pgPSM for PostgreSQL 8.4 this week. PL/pgPSM is implementation of SQL/PSM language to PostgreSQL space. SQL/PSM is procedural language from ANSI SQL standard. DB2 or MySQL use it. PL/pgPSM has some features from IBM implementation, but it isn't fully compatible. It isn't possible - PostgreSQL is little bit different than DB2. Relation between PL/pgPSM and SQL PL is like relation between PL/pgSQL and PL/SQL.

Installation

download source code,
extract it in PL directory in PostgreSQL 8.4 source code tree

compile and install it

[pavel@localhost Desktop]$ mv plpgpsm.tgz ../src/pgsql/src/pl/
[pavel@localhost Desktop]$ cd  ../src/pgsql/src/pl/
[pavel@localhost pl]$ tar xvfz plpgpsm.tgz
plpgpsm/Makefile
plpgpsm/src/
plpgpsm/src/pl_gram.c
plpgpsm/src/pl_exec.c
plpgpsm/src/gram.y
plpgpsm/src/pl_funcs.c
plpgpsm/src/plpgpsm.h
plpgpsm/src/y.tab.h
plpgpsm/src/sql/
plpgpsm/src/sql/plpgpsm.sql
plpgpsm/src/INSTALL.plpgpsm
plpgpsm/src/Makefile
plpgpsm/src/pl_handler.c
plpgpsm/src/plerrcodes.h
plpgpsm/src/scan.l
plpgpsm/src/y.tab.c
plpgpsm/src/pl_comp.c
plpgpsm/src/expected/
plpgpsm/src/expected/plpgpsm.out
plpgpsm/src/pl_gram.h
plpgpsm/src/pl_scan.c
[pavel@localhost pl]$ cd plpgpsm/src/
[pavel@localhost src]$ make all
...
[pavel@localhost src]$ su
Heslo:
[root@localhost src]# make install
/bin/sh ../../../../config/mkinstalldirs '/usr/local/pgsql/lib'
/bin/sh ../../../../config/install-sh -c -m 755  plpgpsm.so '/usr/local/pgsql/lib/plpgpsm.so'

[pavel@localhost src]$ psql template1
psql (8.4devel)
Type "help" for help.

template1=# insert into pg_pltemplate
select 'plpgpsm','t'::boolean, 't'::boolean, 'plpgpsm_call_handler','plpgpsm_validator','$libdir/plpgpsm',NULL
from (
select 'plpgpsm'
except all
select tmplname
from pg_pltemplate
) a;
INSERT 0 0

Check it:


[pavel@localhost src]$ pwd
/home/pavel/src/pgsql/src/pl/plpgpsm/src
[pavel@localhost src]$ make installcheck
make -C ../../../../src/test/regress pg_regress
make[1]: Entering directory `/home/pavel/src/pgsql/src/test/regress'
make[1]: `pg_regress' is up to date.
make[1]: Leaving directory `/home/pavel/src/pgsql/src/test/regress'
../../../../src/test/regress/pg_regress --inputdir=. --psqldir= --dbname=pl_regression --load-language=plpgpsm plpgpsm
(using postmaster on Unix socket, default port)
============== dropping database "pl_regression"      ==============
DROP DATABASE
============== creating database "pl_regression"      ==============
CREATE DATABASE
ALTER DATABASE
============== installing plpgpsm                     ==============
CREATE LANGUAGE
============== running regression test queries        ==============
test plpgpsm              ... ok

=====================
All 1 tests passed.
=====================

Usage

Read some documentation http://www.sqlpl-guide.com/ and http://www.pgsql.cz/index.php/SQL/PSM_Manual, http://www.pgsql.cz/index.php/MySQL5:_Gentle_introduction_to_stored_procedures_of_MySQL5

Play with it and use it :)

postgres=# CREATE OR REPLACE FUNCTION foo2(a integer)
postgres-# RETURNS void AS
postgres-# $$
postgres$#   BEGIN
postgres$#     DECLARE i integer DEFAULT 1;
postgres$#     WHILE i <= a
postgres$#     DO
postgres$#       PRINT i;
postgres$#       SET i = i + 1;
postgres$#     END WHILE;
postgres$#   END
postgres$# $$ LANGUAGE plpgpsm;
CREATE FUNCTION
postgres=# select foo2(3);
NOTICE:  1
NOTICE:  2
NOTICE:  3
foo2 ------

(1 row)

plpgsql and temp. tables

2008-11-18T06:21:00.000-08:00

I tested speed of three possible styles of work with temp tables inside stored procedures. Usually I preferred checking of existence table to exception's trapping. I didn't expect it, but exception's trapping is the best of all.


create or replace function test1() 
returns void as $$
begin 
  drop table if exists omega; 
  create temp table omega(a integer); 
  insert into omega values(10); 
  if exists(select * from omega) then end if; 
end;
$$ language plpgsql;

create or replace function test2() 
returns void as $$
begin 
  if exists(select * from pg_class where relname='omega' and pg_table_is_visible(oid)) then 
    delete from omega; 
  else 
    create temp table omega(a integer); 
  end if; 
  insert into omega values(10); 
  if exists(select * from omega) then end if; 
end;
$$ language plpgsql;

create or replace function test3() 
returns void as $$
begin 
  begin 
    delete from omega; 
  exception 
    when others then 
      create temp table omega(a integer); 
  end; 
  insert into omega values(10); 
  if exists(select * from omega) then end if; 
end;
$$ language plpgsql;

test via pgbench


Test1:
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 339.780441 (including connections establishing)
tps = 340.172513 (excluding connections establishing)

Test2:
scaling factor: 1
query mode: simple
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 1891.021562 (including connections establishing)
tps = 1907.533096 (excluding connections establishing)

Test3:
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 2664.756569 (including connections establishing)
tps = 2698.289177 (excluding connections establishing

updatable cursor's test

2008-08-29T01:37:00.000-07:00

Hello

I had to update every row of table with an result of external function. It was possibility for testing of some patterns:

postgres=# create table testcursor(i integer primary key, v integer);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "testcursor_pkey" for table "testcursor"
CREATE TABLE
postgres=# insert into testcursor select i, 0 from generate_series(1,100000) g(i);
INSERT 0 100000
postgres=# analyze testcursor;
ANALYZE

/* external function sample */
create or replace function ext_fce(a integer, b integer)
returns int as $
declare r int;
begin
/* protect section */
begin
  r := b;
exception when others then
  r := null;
end;
return r;
end;
$$ language plpgsql;

1. test - standard update statement

postgres=# update testcursor set v = ext_fce(i, 30);
UPDATE 100000
Time: 4369,246 ms

2. test - update with updateable cursors

create or replace function testc2(_v integer)
returns void as $$
declare
c cursor for select i from testcursor;
_i integer;
begin
open c;
fetch c into _i;
while found loop
  update testcursor set v = ext_fce(_i, _v)
    where current of c;
  fetch c into _i;
end loop;
close c;
end;
$$ language plpgsql;
postgres=# select testc2(20);
testc2
--------

(1 row)

Time: 8434,985 ms

3. test - update with PK

create or replace function testc3(_v integer)
returns void as $$
declare _i integer;
begin
  for _i in select i from testcursor loop
    update testcursor set v = ext_fce(_i,_v) where i = _i;
  end loop;
end;
$$ language plpgsql;
postgres=# select testc3(30);
testc3
--------

(1 row)

Time: 9959,209 ms

Result: Using updateable cursor is about 90% slower than one statement. Usig pk is about 17% slower than updateable cursor. - So updateable cursors has sense and it is well to use it. But every iteration is much slower than one statement's update.

default parameters for PL functions

2008-08-27T06:32:00.000-07:00

Hello

I completed one task - defaults for PL functions. Using is simple - it is same as Firebird's 2.x defaults.

postgres=# create or replace function x1(int = 1,int = 2,int= 3)
        returns int as $$
          select $1+$2+$3;
        $$ language sql;
CREATE FUNCTION
postgres=# select x1();
x1
----
6
(1 row)

postgres=# select x1(10);;
x1
----
15
(1 row)

postgres=# select x1(10,20);
x1
----
33
(1 row)

postgres=# select x1(10,20,30);
x1
----
60
(1 row)

This is first step before named parameters feature - and less controversy. Second step will be difficult - there are two opinions about named parameter's syntax: variant a) using Oracle's syntax name => expression and variant b) use own syntax based on keyword "AS" expression AS name. I prefer variant @a - I thing so it's more readable (SQL use AS for labeling). Variant @b is safe from compatibility views. There was discussion on pg_hackers - without any conclusion. So I hope so at least defaults will be committed.

bye
Pavel

Using cursors for generating cross tables

2008-08-25T02:54:00.000-07:00

I am working on procedure support in PostgreSQL more than one year. Missing procedures is one disadvantage of PostgreSQL. Because procedures are not called from SELECT statement, then it couldn't respect some rules like functions. Procedures are mainly used for explicit transaction controlling and for generating mutable results (like dynamic record set or stacked record set). Dynamic record set is interesting feature when we nothing know about result set's columns. It's typical for cross tables. Because PostgreSQL doesn't support procedures, we cannot return dynamic (mutable number of columns) queries directly, but we can return dynamic cursor. This method of generating cross tables is inspired by Roland Bauman's http://rpbouman.blogspot.com blog.
Cross table is based on query's pattern:

SELECT shop,
SUM(CASE gender WHEN 'f' THEN salary ELSE 0 END) AS f,
SUM(CASE gender WHEN 'm' THEN salary ELSE 0 END) AS m,
SUM(salary) AS total
FROM employees INNER JOIN shops USING (shop_id)
GROUP BY shop

for data:

CREATE TABLE employees (
id serial PRIMARY KEY,
shop_id int,
gender char(1),
name varchar(32),
salary int
);

CREATE TABLE shops (
id serial PRIMARY KEY,
shop varchar(32)
);

INSERT INTO shops(shop) VALUES('Zurich'),('New York'),('London');

INSERT INTO employees (shop_id, gender, name, salary)
VALUES
(1, 'm', 'Jon Simpson', 4500),
(1, 'f', 'Barbara Breitenmoser',4700),
(2, 'f', 'KirstenRuegg',5600),
(3, 'm', 'Ralp Teller',5100),
(3, 'm', 'Peter Jonson',4700);

I write function, that generate necessary SELECT statement and open dynamic cursor.

CREATE OR REPLACE FUNCTION do_cross_cursor(dimx_name varchar,
    dimx_source varchar, dimy_name varchar,
    dimy_source varchar, expr varchar)
RETURNS refcursor AS $$
DECLARE
col_list text[] := '{}';
query text;
r RECORD;
result refcursor := 'result';
BEGIN
FOR r IN EXECUTE 'SELECT DISTINCT '
  || dimx_name || '::text AS val ' || dimx_source
LOOP
col_list := array_append(col_list, 'SUM(CASE ' || dimx_name
 || ' WHEN ' || quote_literal(r.val) || ' THEN ' || expr
 || ' ELSE 0 END) AS ' || quote_ident(r.val) || '');
END LOOP;
query := 'SELECT ' || dimy_name || ', '
 || array_to_string(col_list, ',')
 || ', SUM(' || expr || ') AS Total '
 || dimy_source || ' GROUP BY ' || dimy_name;
OPEN result NO SCROLL FOR EXECUTE query;
RETURN result;
END;
$$ LANGUAGE plpgsql STRICT;

Because cursors should be used only in transaction, I have to use explicit transaction:

BEGIN;
SELECT do_cross_cursor('gender', 'FROM employees','shop',
     'FROM employees e JOIN shops s ON s.id = e.shop_id',
     'salary');
FETCH ALL FROM result;
END;

BEGIN;
SELECT do_cross_cursor('shop', 'FROM shops','gender',
      'FROM employees e JOIN shops s ON s.id = e.shop_id',
      'salary');
FETCH ALL FROM result;
END;

There is result:

postgres=# BEGIN;
BEGIN
postgres=#   SELECT do_cross_cursor('gender', 'FROM employees',
  'shop',
  'FROM employees e JOIN shops s ON s.id = e.shop_id', 'salary');
do_cross_cursor
-----------------
result
(1 row)

postgres=#   FETCH ALL FROM result;
 shop   |  m   |  f   | total
----------+------+------+-------
New York |    0 | 5600 |  5600
Zurich   | 4500 | 4700 |  9200
London   | 9800 |    0 |  9800
(3 rows)

postgres=# END;BEGIN;
COMMIT
BEGIN
postgres=#   SELECT do_cross_cursor('shop', 'FROM shops','gender',
 'FROM employees e JOIN shops s ON s.id = e.shop_id', 'salary');
do_cross_cursor
-----------------
result
(1 row)

postgres=#   FETCH ALL FROM result;
gender | New York | Zurich | London | total
--------+----------+--------+--------+-------
m      |        0 |   4500 |   9800 | 14300
f      |     5600 |   4700 |      0 | 10300
(2 rows)

postgres=# END;
COMMIT

recursive queries only with SQL procedures

2008-06-18T05:32:00.000-07:00

I tested possibility of recursive queries only with SQL language (and SQL procedures)

postgres=# select * from rec_src;
id | parent |   v
----+--------+--------
 2 |      1 | rodic
 3 |      1 | rodic
 4 |      2 | dite
 5 |      2 | dite
 6 |      3 | dite
 7 |      6 | vnouce
 1 |      0 | root
(7 rows)

It's ugly, but it works :)

create or replace function empty2null(anyarray)
returns anyarray as $$
 select case
            when array_upper($1,1) is null
            then null
            else $1 end;
$$ language sql strict immutable;

create or replace function search_path(int)
returns varchar as $$
 select case
            when $1 is null
            then ''
            else search_path((select parent
                                 from rec_src
                                where id = $1)) || '.'|| $1 end;
$$ language sql;

create or replace function ls(int[], int,
                             out id int, out parent int, out path varchar, out v varchar )
returns setof record as $$
 select id, parent, search_path(id), repeat('  ',$2)|| v
    from rec_src
   where parent = any($1)
 union all
 select id, parent, path, v from ls(empty2null(array(select id
                                                        from rec_src
                                                       where parent = any($1))),
                                    $2 + 1);
$$ language sql volatile strict;
postgres=# select * from ls(array[0], 0) order by path;
id | parent |    path    |      v
----+--------+------------+--------------
 1 |      0 | .0.1       | root
 2 |      1 | .0.1.2     |   rodic
 4 |      2 | .0.1.2.4   |     dite
 5 |      2 | .0.1.2.5   |     dite
 3 |      1 | .0.1.3     |   rodic
 6 |      3 | .0.1.3.6   |     dite
 7 |      6 | .0.1.3.6.7 |       vnouce
(7 rows)

EXECUTE USING feature in PostgreSQL 8.4

2008-06-03T01:12:00.000-07:00

Hello,

My motivation for implementation USING feature in EXECUTE was simplifycation and securisation of dynamic SQL. I didn't thing about execution plans. I am speaking on plpgsql's lectures about risks related to dynamic SQL (mainly security risks). Prepared statements are safe, but has some gotcha http://www.depesz.com/index.php/2008/05/10/prepared-statements-gotcha/ , so there are some situations, where prepared statements are suboptimal. In plpgsql we have to use dynamic SQL

DECLARE
a integer;
r integer;
BEGIN
-- prepared statement
SELECT INTO r * FROM test WHERE c = a;
-- classic dynamic SQL witg risk of SQL injection
EXECUTE 'SELECT * FROM test WHERE c = ' || a INTO r;
-- dynamic SQL with USING (secure and with well execution plan)
EXECUTE 'SELECT * FROM test WHERE c = $1' INTO r USING a;

When we use USING clause, then dynamic SQL are secure (and readable too) . Every evalaution generate new execution plan with knowleage actual params. It's disadvantage (it's need some time - so don't use it in cycles), and advantage (generated plan will be propably better - not allways (sometimes we must play statistic alchemy game)).

I unlike SELECT composition. It's bad style:

CREATE OR REPLACE FUNCTION get_names(age integer, class integer)
RETURNS SETOF persons AS $$
DECLARE sql varchar = 'SELECT * FROM persons ';
BEGIN
IF age IS NOT NULL THEN
 sql := sql || 'WHERE age = ' || age;
END IF;
IF class IS NOT NULL THEN
 IF strpos(sql, 'WHERE') <> 0 THEN
   sql := sql || ' AND ';
 ELSE
   sql := sql || 'WHERE ';
 END IF;
 sql := sql || ' class = ' || class;
END IF;
RETURN QUERY EXECUTE sql;
END $$ LANGUAGE plpgsql;

With USING clause I should to write:

..
BEGIN
RETURN QUERY EXECUTE
  'SELECT * FROM persons
     WHERE ($1 IS NULL or age = $1)
            AND ($2 IS NULL or class = $2)
USING age, class;
END; $$ LANGUAGE plpgsql;

Optimaliser do predicat's reduction self.

Tom Lane demonstarated other example of EXECUTE USING:

create or replace function foo() returns trigger as $$
declare
t text;
begin
for i in 1 .. tg_argv[0] loop
 execute 'select cast ( $1 . ' || tg_argv[i] || ' as text)'
   into t using new;
 raise notice '% = %', tg_argv[i], t;
end loop;
return new;
end $$ language plpgsql;

This code shows values of all field of NEW record in trigger's body. It wasn't possible before EXECUTE USING in plpgsql.

Any EXECUTE in plpgsql is door to you system for SQL injection. With EXECUTE USING your procedures can be 100% safe.

-- very unsafe function
CREATE OR REPLACE FUNCTION foo(_tablename varchar, _parameter varchar)
RETURNS int AS $$
DECLARE _r int;
BEGIN
 EXECUTE 'SELECT c FROM ' || _tablename || ' WHERE x=''' || _parameter || '''' INTO _r;
 RETURN _r;
END;
$$ LANGUAGE plpgsql STRICT;

-- safe function
CREATE OR REPLACE FUNCTION foo(_tablename varchar, _parameter varchar)
RETURNS int AS $$
DECLARE _r int;
BEGIN
 EXECUTE 'SELECT c FROM ' || quote_ident(_tablename) || ' WHERE x='
                                || quote_literal(_parameter) || INTO _r;
 RETURN _r;
END;
$$ LANGUAGE plpgsql STRICT;

-- bullet proof function
CREATE OR REPLACE FUNCTION foo(_tablename varchar, _parameter varchar)
RETURNS int AS $$
DECLARE _r int;
BEGIN
 EXECUTE 'SELECT c FROM ' || quote_ident(_tablename)::regclass || ' WHERE x=$1' 
                                USING _parameter || INTO _r;
 RETURN _r;
END;
$$ LANGUAGE plpgsql STRICT;

Using $_SHARED as table cache in plperl

2007-12-01T09:29:00.000-08:00

Oracle has new function result cache. It's nice idea. There are some use cases, mostly in www applications. There are nothing similar in PostgreSQL. I found simple similar solution in plperl (with important disadvantage to Oracle solution). It works, but without any pooling mechanisms its not really effective.

I created some tables:

CREATE TABLE Books(
id serial PRIMARY KEY,
name VARCHAR(20));

CREATE TABLE Sale(
book_id integer REFERENCES Books(id),
inserted timestamp DEFAULT(CURRENT_TIMESTAMP)
);

INSERT INTO Books VALUES(1,'Dracula');
INSERT INTO Books VALUES(2,'Nosferatu');
INSERT INTO Books VALUES(3,'Bacula');

INSERT INTO Sale VALUES(1, '2007-10-11');
INSERT INTO Sale VALUES(2, '2007-10-12');
INSERT INTO Sale VALUES(2, '2007-10-13');
INSERT INTO Sale VALUES(3, '2007-10-10');

and I created function Top10Books that returns top ten books.

-- Top10
CREATE OR REPLACE FUNCTION Top10Books(IN date, OUT ordr integer, OUT name varchar(20))
RETURNS SETOF RECORD
AS $$
BEGIN
ordr := 0;
FOR name IN SELECT b.name
              FROM Books b
                   JOIN
                   Sale s
                   ON b.id = s.book_id
             WHERE s.inserted BETWEEN date_trunc('month', $1)
                                  AND date_trunc('month', $1)
           + interval '1month' - interval '1day'
             GROUP BY b.name
             ORDER BY count(*) DESC
             LIMIT 10
LOOP
ordr := ordr + 1;
RETURN NEXT;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;

One my customer put similar function on every page on some site (with really destructive impact to load on db server. Correct solution is to use some php cache or some similar tool. With plperl I am able cache result in Postgres too:

CREATE OR REPLACE FUNCTION Top10BooksCached(IN date, IN bool,
       OUT ordr integer, OUT name varchar(20))
RETURNS SETOF RECORD
AS $$
return $_SHARED{tableof_top10book}
    if (defined ($_SHARED{tableof_top10book}) and not (defined($_[1]) and $_[1] eq "t"));
if (not defined($_SHARED{plan_for_top10books}))
{
   $_SHARED{plan_for_top10books} = spi_prepare(
           'SELECT b.name                                                                                                                                                                                                
              FROM Books b                                                                                                                                                                                               
                   JOIN                                                                                                                                                                                                  
                   Sale s                                                                                                                                                                                                
                   ON b.id = s.book_id                                                                                                                                                                                   
             WHERE s.inserted BETWEEN date_trunc(\'month\', $1)                                                                                                                                                          
                                  AND date_trunc(\'month\', $1)
    + interval \'1month\' - interval \'1day\'                                                                                                                
             GROUP BY b.name                                                                                                                                                                                             
             ORDER BY count(*) DESC                                                                                                                                                                                      
             LIMIT 10' , 'DATE');
}
my $row;
my $i = 0;
my $heap;
my $sth = spi_query_prepared($_SHARED{plan_for_top10books}, $_[0]);
while (defined ($row = spi_fetchrow($sth))) {
    push @$heap, {ordr => ++$i, name => $row->{name}}
}
$_SHARED{tableof_top10book} =  $heap ;
return $_SHARED{tableof_top10book};
$$ LANGUAGE plperlu;

or with cache expiration

CREATE OR REPLACE FUNCTION Top10BooksCached(IN date, IN integer,
     OUT ordr integer, OUT name varchar(20))
RETURNS SETOF RECORD
AS $$
return $_SHARED{tableof_top10book}
    if (defined ($_SHARED{tableof_top10book})
            and defined($_SHARED{actualised_top10book})
            and ($_SHARED{actualised_top10book} + $_[1] > time));
if (not defined($_SHARED{plan_for_top10books}))
{
   $_SHARED{plan_for_top10books} = spi_prepare(
           'SELECT b.name                                                                                                                                                                                                
              FROM Books b                                                                                                                                                                                               
                   JOIN                                                                                                                                                                                                  
                   Sale s                                                                                                                                                                                                
                   ON b.id = s.book_id                                                                                                                                                                                   
             WHERE s.inserted BETWEEN date_trunc(\'month\', $1)                                                                                                                                                          
                                  AND date_trunc(\'month\', $1)
    + interval \'1month\' - interval \'1day\'                                                                                                                
             GROUP BY b.name                                                                                                                                                                                             
             ORDER BY count(*) DESC                                                                                                                                                                                      
             LIMIT 10' , 'DATE');
}
my $row;
my $i = 0;
my $heap;
my $sth = spi_query_prepared($_SHARED{plan_for_top10books}, $_[0]);
while (defined ($row = spi_fetchrow($sth))) {
    push @$heap, {ordr => ++$i, name => $row->{name}}
}
$_SHARED{tableof_top10book} =  $heap ;
$_SHARED{actualised_top10book} = time;
return $_SHARED{tableof_top10book};
$$ LANGUAGE plperlu;

An usage is simple:

-- first call is slow
postgres=# select * from Top10BooksCached(current_date, 300);
ordr |   name
------+-----------
1 | Nosferatu
2 | Bacula
3 | Dracula
(3 rows)

Time: 128,965 ms
-- second call is fast
postgres=# select * from Top10BooksCached(current_date, 300);
ordr |   name
------+-----------
1 | Nosferatu
2 | Bacula
3 | Dracula
(3 rows)

Time: 11,911 ms

Use it carefully! It's not good for big tables, and what more, this function can returns out-of-date values.

Stacked recordset (multirecordset)

2007-11-20T20:15:00.000-08:00

So I am little bit far now. Multirecorsets are available with current protocol, but some changes in libpq are necessary :(. Procedure's output can be much more dynamic than from SRF functions. There are not limits. I wrote small regress test. Its based on Roland Bauman's sample (that was be used in http://www.pgsql.cz/index.php/MySQL5:_Gentle_introduction_to_stored_procedures_of_MySQL5).

CREATE TYPE gender AS ENUM('m', 'f');

CREATE TABLE employees (
id serial PRIMARY KEY,
shop_id int,
gender gender,
name varchar(32),
salary int
);

CREATE TABLE shops (
id serial PRIMARY KEY,
shop varchar(32)
);

INSERT INTO shops (shop)
VALUES ('Zurich'), ('New York'), ('London');

INSERT INTO employees (shop_id, gender, name, salary)
VALUES
(1, 'm', 'Jon Simpson', 4500),
(1, 'f', 'Barbara Breitenmoser', 4700),
(2, 'f', 'Kirsten Ruegg', 5600),
(3, 'm', 'Ralph Teller', 5100),
(3, 'm', 'Peter Jonson', 5200);

Procedure crosstab generate and execute SQL like

SELECT shop,
SUM(CASE gender WHEN 'f' THEN salary ELSE 0 END) AS f,
SUM(CASE gender WHEN 'm' THEN salary ELSE 0 END) AS m,
SUM(salary) AS total
FROM employees e INNER JOIN shops s ON e.shop_id = s.id
GROUP BY shop

Source code:

CREATE OR REPLACE procedure crosstab(dimx_name varchar(32), dimx_source varchar(32),
                                  dimy_name varchar(32), dimy_source varchar(32),
                                  expr varchar(32)) AS
$$
DECLARE
cols_expr varchar;
xtab_expr varchar;
BEGIN
 EXECUTE  'SELECT array_to_string(ARRAY(SELECT ''SUM(CASE ' || dimx_name || ' WHEN '''''' || x.'
        || dimx_name || ' || '''''' THEN ' || expr || ' ELSE 0 END) AS "'' || x.'|| dimx_name
        || ' || ''"'' FROM (SELECT DISTINCT '
        || dimx_name || ' ' || dimx_source || ') x),'', '')' INTO cols_expr;
 xtab_expr := 'SELECT '|| dimy_name ||', ' || cols_expr || ', SUM(' || expr || ') AS Total '
      || dimy_source || ' GROUP BY '|| dimy_name;
EXECUTE xtab_expr;
END;
$$ LANGUAGE plpgsql;

Test output:

postgres=# CALL crosstab('gender', 'FROM employees','shop','FROM employees e INNER JOIN shops s ON e.shop_id = s.id','salary');
shop   |   m   |  f   | total
---------+-------+------+-------
New York |     0 | 5600 |  5600
Zurich   |  4500 | 4700 |  9200
London   | 10300 |    0 | 10300
(3 rows)

CALL 0
postgres=# CALL crosstab('shop', 'FROM shops', 'gender','FROM employees e INNER JOIN shops s ON e.shop_id = s.id','salary');
gender | London | New York | Zurich | total
-------+--------+----------+--------+-------
m      |  10300 |        0 |   4500 | 14800
f      |      0 |     5600 |   4700 | 10300
(2 rows)

CALL 0

And one really multirecordset's sample:

create or replace procedure free(int) as $$
declare s varchar = 'SELECT 1';
begin
 for i in 2..$1 loop
   s := s || ',' || to_char(i,'999');
   execute s;
 end loop; 
end$$ language plpgsql;

Output:

postgres=# call free(6);
?column? | ?column?
----------+----------
       1 |        2
(1 row)

?column? | ?column? | ?column?
----------+----------+----------
       1 |        2 |        3
(1 row)

?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------
       1 |        2 |        3 |        4
(1 row)

?column? | ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------+----------
       1 |        2 |        3 |        4 |        5
(1 row)

?column? | ?column? | ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------+----------+----------
       1 |        2 |        3 |        4 |        5 |        6
(1 row)

CALL 0

First real procedures on PostgreSQL

2007-11-07T09:25:00.000-08:00

I am working on an enhancing of plpgsql. I would to add to plpgsql procedures and methods. Methods are flagged functions with different calling convention. Procedures are total different beast. Now I am able to run procedures without parameters. I would to support parameter passing by reference. It allows to take reference for any variable and potentially some interesting features (like string builder or array builder etc). What is best, I need not modify system tables. Procedure is function (from pg_proc perspective) that returns PROCEDURE type (it will be used for exit status).


postgres=#
create or replace procedure print()
as $$
begin
raise notice 'ahoj';
end;
$$ language plpgsql;
CREATE PROCEDURE

postgres=#
create or replace function test()
returns void as $$
begin
call print();
return;
end;
$$ language plpgsql;
CREATE FUNCTION

postgres=# select test();
NOTICE:  Found ()
CONTEXT:  PL/pgSQL function "test" line 4456553 at CALL
NOTICE:  OID: 16821
CONTEXT:  PL/pgSQL function "test" line 4456553 at CALL
NOTICE:  ahoj
CONTEXT:  PL/pgSQL function "test" line 4456553 at CALL
test
------

(1 row)

postgres=# \df print
                  List of functions
Schema | Name  | Result data type | Argument data types
--------+-------+------------------+---------------------
public | print | procedure        |
(1 row)

There is lot of work still, but first step is done.