Re: [GENERAL] User defined function - Mailing list pgsql-general

From Stéphane FILLON
Subject Re: [GENERAL] User defined function
Date
Msg-id 040b01bf1794$63a946e0$a5373ad1@portable
Whole thread Raw
List pgsql-general
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
>
>
>************
>


pgsql-general by date:

Previous
From: Sebastian Heath
Date:
Subject: Problem with Pg.pm
Next
From: "Stéphane FILLON"
Date:
Subject: Functions documentations