-- ISO-8601 assigns a number to each week of the year.
-- A week that lies partly in one year and partly in another is 
-- assigned a number in the year in which most of its days lie.
-- This means that:
--      Week 1 of any year is the week that contains 4 January.
-- or equivalently
--      Week 1 of any year is the week that contains the first Thrusday
--      in January .
-- If the week is 0 that means that first week is less than 4 days.

-- returns the week number of the year (1 - 53)...

drop function week(date);
create function week(date) returns int2 as
'declare
	p	int2;
	i	int2;
	week	int4;
	year	text;
	start	date;
	diff	timespan;
	mais	int2;
	txt	text;
begin
	year:= date_part(''year'',$1);
        if textlen(year) = 1 then
		year:= ''000'' || year;
	end if;
        if textlen(year) = 2 then
		year:= ''00'' || year;
	end if;
	start:= year || ''-01-01'';
	week:= date_part(''dow'',start);
	if week > 3 or week = 0 then
		mais:= 0;
	else
		mais:= 1;
	end if;
	week:= date_part(''dow'',start);
	diff:= date_part(''epoch'',$1) - date_part(''epoch'',start);
	txt:= diff;
 	p:= textpos(txt,'' '');
	if p = 0 then
	    i:= 0;
	else
	    i:= substr(txt,1,p - 1);
        end if;
	return (i + week) / 7 + mais;
 end;
' language 'plpgsql';

select week('1997-01-01'), week('1997-12-31');
select week('1998-01-01'), week('1998-12-31');
select week('1999-01-01'), week('1999-12-31');
select week(current_date);