Message-ID: <4BAC9EC1.7010708@wp.pl>
Date: Fri, 26 Mar 2010 12:47:13 +0100
From: Ireneusz Pluta <ipluta@wp.pl>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: Sergey Konoplev <gray.ru@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Week to date function
References: <4BAB2C15.8090406@wp.pl>
	<c3a7de1f1003250244p440b8244t57319b742fa680f0@mail.gmail.com>
	<4BAC9107.5070701@wp.pl>
	<c3a7de1f1003260402y42a61ca4j657983343760d4d4@mail.gmail.com>
In-Reply-To: <c3a7de1f1003260402y42a61ca4j657983343760d4d4@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Sergey Konoplev pisze:
>> CREATE OR REPLACE FUNCTION your_week2date(double precision, double
>> precision) RETURNS SETOF date
>> AS
>> $_$
>> SELECT  day
>> FROM (
>>   SELECT  (
>>               date_trunc('week', ($1::text||'-01-01')::date)
>>               + ($2::text||' week')::interval
>>               + ( d::text||'  day')::interval
>>           )::date  AS  day
>>   FROM    generate_series(0, 6) AS d
>> )   alias
>> WHERE   to_char(day, 'IYYY')::integer = $1
>> ORDER
>> BY      1
>> $_$
>> LANGUAGE SQL
>> IMMUTABLE
>>     
>
> I think it is better to use date_part('year', day) instead of
> to_char(...).
this might cut first or last week in the year, like this:


SELECT your_week2date(2009, 52) ;
 your_week2date
----------------
 2009-12-28
 2009-12-29
 2009-12-30
 2009-12-31
 2010-01-01   cut
 2010-01-02   cut
 2010-01-03   cut
(7 rows)

which is not what I want when playing with isoweeks.
>  And may be it is worth to do raise exception when
> incorrect week specified.
>   
but, maybe, controlled by an additonal parameter saying if one prefers 
to have exception or rather an empty resultset