In response to Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>:
> On Feb 11, 2008, at 12:43 AM, brian wrote:
> > Try:
> >
> > CREATE OR REPLACE FUNCTION first_day_next_month(inputdate date)
> > RETURNS date AS
> > $BODY$
> > DECLARE
> > resultdate date;
> > BEGIN
> > SELECT INTO resultdate to_date(to_char((inputdate + interval \
> > '1 month'), 'yyyy-MM') || '-01', 'yyyy-mm-dd');
> > RETURN resultdate;
> > END;
> > $BODY$
> > LANGUAGE 'plpgsql';
>
>
> No need for the variable or the SELECT, and it's an immutable
> function, so better define that. Besides that it's probably better to
> use the date_trunc function here.
>
> Try:
>
> CREATE OR REPLACE FUNCTION first_day_next_month(inputdate date)
> RETURNS date AS
> $BODY$
> BEGIN
> RETURN date_trunc('month', inputdate + interval '1 month');
> END;
> $BODY$
> LANGUAGE 'plpgsql' IMMUTABLE;
>
> And with that I wonder why you'd even need a function :)
Because it's clear what the function does by the name. It becomes
self-documenting, and ginormous queries will be easier to grok with
a function called first_day_next_month().
--
Bill Moran
http://www.potentialtech.com
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
