Re: Analysis Function

[David Jarvis <thangalin@xxxxxxxxx>]

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.