plpgsql function

From: Andreas Kendlinger <andreas(dot)kendlinger(at)bestsolution(dot)at>
To: pgsql-general(at)postgresql(dot)org
Subject: plpgsql function
Date: 2008-02-22 15:05:02
Message-ID: 1203692702.5004.5.camel@maat.bestsolution.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello.

I wrote a little stored function to simulate the EXTRACT(YEAR_MONTH ...)
from mySQL.

//---------------------------------
CREATE OR REPLACE FUNCTION "BiSCAT_combined".extractyearmonth(date
timestamp without time zone)
RETURNS character varying AS
$BODY$

DECLARE i INTEGER;

BEGIN
i := EXTRACT(MONTH FROM $1 ::timestamp);
if i > 9 THEN
RETURN EXTRACT(YEAR FROM $1 :: timestamp) || EXTRACT(MONTH FROM $1 ::
timestamp);
else
RETURN EXTRACT(YEAR FROM $1 ::timestamp) || 0 || EXTRACT(MONTH FROM
$1 :: timestamp);
end if;

END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE STRICT;

//--------------------

One Method call requires 53ms.
I'm sure that this function is absolutely unoptimezed but I think
53ms is too long.

Are there any suggestions to improve the execution time of the function.

Best regards

Andi Kendlinger

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2008-02-22 15:16:04 Re: Mac ordering with locales
Previous Message Tom Lane 2008-02-22 14:58:56 Re: Unique indicies