Pages

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

1 comment: