From: | Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> |
---|---|
To: | brian <brian(at)zijn-digital(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Mechanics of Select |
Date: | 2008-02-11 00:23:33 |
Message-ID: | CA714497-D513-44FD-9C50-C355BD6106E0@solfertje.student.utwente.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 :)
Another thing I've taught myself is to prefix local variables and
parameters in functions, so that they can NEVER accidentally match a
column name that you use in a query (I usually use '_'). Otherwise
you can get silly queries like "SELECT * FROM table WHERE x = x" that
look perfectly fine while you're writing them down, being perfectly
able to make the distinction between *variable x* and *column x* in
your mind.
Regards,
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,47af8f8e167321323610058!
From | Date | Subject | |
---|---|---|---|
Next Message | Willem Buitendyk | 2008-02-11 01:23:39 | Re: Mechanics of Select |
Previous Message | Tom Lane | 2008-02-11 00:21:00 | Re: 8.3: where's the replacement tsearch2 module? |