CODEWITHSUNDEEP
cpostgresqlpostgresql-9.3
Tomas Greif
Tomas Greif

Reputation: 22661

PostgreSQL - use date constant in c function

I am trying to rewrite the following SQL function into c equivalent (trying to make it a little bit faster):

CREATE OR REPLACE FUNCTION dat2(time_key integer)
  RETURNS date AS
$BODY$
BEGIN
        RETURN case when time_key > 0 then '2006-12-31'::date + time_key end as result;
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE STRICT
  COST 100;

I thought I can modify existing date+int4 operator and do something like:

#include "postgres.h"
#include "fmgr.h"
#include "utils/date.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(dwd);

Datum
dwd(PG_FUNCTION_ARGS)
{
     /* DateADT     dateVal = PG_GETARG_DATEADT(0); */
     DateADT     dateVal = PG_GETARG_DATEADT(2006-12-31);
     int32       days = PG_GETARG_INT32(0);

     PG_RETURN_DATEADT(dateVal + days);
}

If I compile my function and turn it into .so I can create dwd function in PostgreSQL:

create or replace function dwd(int) returns date as 
'/usr/lib/postgresql/9.3/lib/dwd', 'dwd'
  language c
  cost 1;

I get 2000-01-01 for select dwd(0);, but I expected 2006-12-31. Apparently there is problem in DateADT dateVal = PG_GETARG_DATEADT(2006-12-31);.

How do I define date constant in this c function?

Upvotes: 3

Views: 382

Answers (1)

Tomas Greif
Tomas Greif

Reputation: 22661

Now it works. It turns out that DateADT is number of days (integer) since 2000-01-01.

c function:

#include "postgres.h"
#include "fmgr.h"
#include "utils/date.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif    

PG_FUNCTION_INFO_V1(dwd);

Datum
dwd(PG_FUNCTION_ARGS)
{

  int32       days = PG_GETARG_INT32(0);  

  if (days > 0) {
     DateADT     dateVal = 2556;
     PG_RETURN_DATEADT(dateVal + days);     
  } 
  else {
     PG_RETURN_NULL();         
  }  

}

Performance test:

  drop table if exists tmp;
  create table tmp as select dat2(gs) from generate_series(1,1000000) gs;
  -- Query returned successfully: 1000000 rows affected, 4101 ms execution time.

  drop table if exists tmp;
  create table tmp as select dwd(gs) from generate_series(1,1000000) gs;
  -- Query returned successfully: 1000000 rows affected, 1527 ms execution time.

During my search I have found this to be quite useful reference for c functions in PostgreSQL.

Upvotes: 2

Related Questions