Hi Moray,
Try the following:
create function test(date)
returns date
as '
select (text_datetime(\'01/12/\' || float8_text(date_part(\'year\', $1) -
1)))::date
as answer;
' language'sql';
Test: (my DATESTYLE is set to European)
------------------------------------------------------------
select test('30/01/1972'::date);
result -> '01-12-1971'
-----Message d'origine-----
De : Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
À : pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org>
Date : samedi 16 octobre 1999 01:06
Objet : [GENERAL] User defined function
>I need to write a function to return the first of december of the year
>previous to the current year. Part of my problem is I'm not sure how
>to escape quotes in function definitions. I am looking at something
>along the lines of
>
>CREATE FUNCTION startofregyear(date) RETURNS date AS '
>SELECT text_datetime('01/12/' ¦¦ float8_text(datepart('year',$1)-1))
>AS answer;
>' LANGUAGE 'SQL';
>
>but clearly this won't work because of the large number of single
>quotes. I've tried escaping the quotes in the SELECT line with
>backslashes, but that doesn't seem to do it.
>
>Can anyone tell me if this function should work, once I sort out the
>escaping?
>
>Thanks,
>Moray
>
>----------------------------------------------------------------------
>----------------
>Moray.McConnachie@computing-services.oxford.ac.uk
>
>
>************
>