Re: Mechanics of Select

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!