Re: [GENERAL] User defined function

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(dot)mcconnachie(at)computing-services(dot)oxford(dot)ac(dot)uk>
À : pgsql-general(at)postgreSQL(dot)org <pgsql-general(at)postgreSQL(dot)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(dot)McConnachie(at)computing-services(dot)oxford(dot)ac(dot)uk
>
>
>************
>