Re: [GENERAL] User defined function

hi...

>
> > CREATE FUNCTION startofregyear(date) RETURNS date AS '
> > SELECT text_datetime(''01/12/''
> float8_text(datepart(''year'',$1)-1))
> > AS answer;
> > ' LANGUAGE 'SQL';
>
> absolutely not, I tried that already.
> I get "attribute not found" error wherever the opening double-quotes
> are during the create of the function.

try this (it works quite nicely):
select (''12-1-'' || date_part(''year'',$1) - 1)::date;

your problem is 2 fold. first, you need double quotes. second, it isn't
datepart, its date_part. that's the "attribute not found" error.

as a suggestion, try doing the select statement from the command line outside
of a function first to make sure it works. i.e:
select ('12-1-' || date_part('year','now'::date) - 1)::date;

once you know that your select works, pop it into the function. functions don't
return the most helpful error messages =) the command line in psql is much
better for debugging selects/inserts/etc/etc...

--
Aaron J. Seigo
Sys Admin