Thursday, October 29, 2009

Named function parameters - a feature of PostgreSQL 8.5

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

Thursday, October 1, 2009

Dynamic access to record fields in PL/pgSQL

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