sql - How to generate calendar of movable feasts? -

there table: feasts dates stored in smallint fields (not elegant way, know..). looks that:

id - serial, pk day - smallint not null, month - smallint not null, year - smallint 

it seems simple - if there record date, there kind of feast on date.

there "only" 1 thing makes whole idea complicated... field 'year' nullable. if 'year' null, means there movable feast on day - independent year. (of course can create 1 field date, , boolean flag 'is_movable', wouldn't change in problem).

i want create procedure list feasts specified range of dates.

get_all_feasts(date_from date, date_to date) 

..which returns table: id, date, is_movable.

for example, data:

id / day / month / year 1 / 12 / 05 / 2013 2 / 15 / 05 / 2013 3 / 16 / 02 / 2012 4 / 25 / 12 / null 5 / 26 / 12 / null 6 / 2 / 04 / 2014 

..function call: get_all_feasts('2012-03-01'::date, '2014-05-01'::date) should return:

id / date 4 / 2012-12-25 5 / 2012-12-26 1 / 2013-05-12 2 / 2013-05-15 4 / 2013-12-25 5 / 2013-12-26 6 / 2014-04-02 

there no problem selecting feasts year, month , day specified.

but how generate feast dates movable feasts in specific range?

i use postgresql v. 9.2.

you can use recursive queries - reference

try -

with recursive year_table(n) (     select 1000   union     select n+1 year_table ) select id, to_date(day || ' ' || month || ' ' || year, 'dd mm yyyy')   feasts  to_date(day || ' ' || month || ' ' || year, 'dd mm yyyy') >=        date_from    , to_date(day || ' ' || month || ' ' || year, 'dd mm yyyy') <= date_to    , year not null union select feasts.id,        to_date(day || ' ' || month || ' ' || year_table.n, 'dd mm yyyy')   feasts, year_table  n >= to_char(date_from, 'yyyy')    , n <= to_char(date_to, 'yyyy')    , feasts.year null;