Hi,
To avoid string concatenation using dates, I figured I could write a C function:
#include "postgres.h"
#include "fmgr.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 = PG_GETARG_INT32(0);
int32 p_month = PG_GETARG_INT32(1);
int32 p_day = PG_GETARG_INT32(2);
DateADT d = date2j (p_year, p_month, p_day) - POSTGRES_EPOCH_JDATE;
PG_RETURN_DATEADT(d);
}
Compiles without errors or warnings. The function is integrated as follows:
CREATE OR REPLACE FUNCTION dateserial(integer, integer, integer)
RETURNS text AS
'ymd.so', 'dateserial'
LANGUAGE 'c' IMMUTABLE STRICT
COST 1;
However, when I try to use it, the database segfaults:
select dateserial( 2007, 1, 3 )
Any ideas why?
Thank you!
Dave
P.S.
I have successfully written a function that creates a YYYYmmDD formatted string (using
sprintf) when given three integers. It returns as expected; I ran it as follows:
dateserial( extract(YEAR FROM m.taken)::int, 1, 1 )::dateThis had a best-of-three time of 3.7s compared with 4.3s using string concatenation. If I can eliminate all the typecasts, and pass in m.taken directly (rather than calling
extract), I think the speed will be closer to 2.5s.
Any hints would be greatly appreciated.