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