PostgreSQL - use date constant in c function

Question

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?

What makes you think this is the hotspot / slowpoint in your query execution? Date/time maths isn't cheap. — Craig Ringer
– Craig Ringer, Commented Mar 11, 2014 at 8:43
@CraigRinger we use this function a lot in our queries and I wanted to test if c function will ve any faster. This is supposed to be generic function for the whole data warehouse. Currently, I do not have problems with any specific query. — Tomas Greif
– Tomas Greif, Commented Mar 11, 2014 at 9:06
Probably worth profiling to see whether it's actually having any meaningful impact on performance first, then. — Craig Ringer
– Craig Ringer, Commented Mar 11, 2014 at 9:19
@CraigRinger Just to let you know - c function dwd is three-times faster then SQL function dat2, see my answer. — Tomas Greif
– Tomas Greif, Commented Mar 11, 2014 at 12:21
Sure, but if dat2 is (say) 2% of execution cost, you haven't gained much. That's what I was saying: Worth profiling first, to see whether what you're looking at optimizing is actually worth improving. — Craig Ringer
– Craig Ringer, Commented Mar 11, 2014 at 12:33

Tomas Greif · Accepted Answer · 2014-03-11 12:19:36Z

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.

Collectives™ on Stack Overflow

PostgreSQL - use date constant in c function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related