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