Hi,
Here is code to convert dates from integers without string concatenation:
Edit
dateserial.c:
#include "postgres.h"
#include "utils/date.h"
#include "utils/nabstime.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
Datum dateserial(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1 (dateserial);
Datum
dateserial(PG_FUNCTION_ARGS) {
int32 p_year = (int32)PG_GETARG_FLOAT8(0);
int32 p_month = PG_GETARG_INT32(1);
int32 p_day = PG_GETARG_INT32(2);
PG_RETURN_DATEADT( date2j( p_year, p_month, p_day ) - POSTGRES_EPOCH_JDATE );
}
Edit Makefile:
MODULES = dateserial
PGXS := $(shell pg_config --pgxs)
include $(PGXS)
Edit inst.sh (optional):
#!/bin/bash
make clean && make && strip *.so && make install && /etc/init.d/postgresql-8.4 restart
Run bash inst.sh.
Create a SQL function dateserial:
CREATE OR REPLACE FUNCTION dateserial(double precision, integer, integer)
RETURNS date AS
'$libdir/dateserial', 'dateserial'
LANGUAGE 'c' IMMUTABLE STRICT
COST 1;
ALTER FUNCTION dateserial(double precision, integer, integer) OWNER TO postgres;
Test the function:
SELECT dateserial( 2007, 5, 5 )
Using this function, performance increases from 4.4s to 2.8s..
Dave