Re: Analysis Function

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 )::date

This 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.