Обсуждение: plpgsql function

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

Andreas Kendlinger <andreas.kendlinger@bestsolution.at> writes:
> I wrote a little stored function to simulate the EXTRACT(YEAR_MONTH ...)
> from mySQL.
> ...
> One Method call requires 53ms.

Really?  Near as I can tell, it takes about 130 microsec on my ancient
HPPA machine, which is surely as slow as anything anyone's still using.
What PG version are you using?  Are you sure you're only measuring the
function call and not some other overhead?  I tested like this:

regression=# \timing
Timing is on.
regression=# select count(extractyearmonth('2008-02-04')) from generate_series(1,100000);
 count
--------
 100000
(1 row)

Time: 14431.591 ms
regression=# select count(1) from generate_series(1,100000);
 count
--------
 100000
(1 row)

Time: 1130.305 ms
regression=# select (14431.591-1130.305)/100000;
        ?column?
------------------------
 0.13301286000000000000
(1 row)

Time: 7.262 ms

(This is with the IMMUTABLE marker removed from the function, else it'd
be called only once and we couldn't measure anything.)

However, I certainly think it can be done more easily --- use to_char.
It looks to me like to_char(some_timestamp, 'YYYYMM') does what you want,
and that runs in about 18 microsec.

            regards, tom lane