Greenplum is_date C Language Interface

In greenplum/PostgreSQL, it is convenient to convert a string to a time. In many formats, GP can automatically identify whether the time character is used. If the time is not correct or the time is incorrect, the SQL statement reports an error.

aligputf8=# select'2011-13-10 10:10:10'::date;ERROR:  date/time field value out of range:"2011-13-10 10:10:10"LINE 1: select'2011-13-10 10:10:10'::date;               ^HINT:  Perhaps you need a different"datestyle" setting. aligputf8=# select'df'::date;                   ERROR:  invalid input syntax for type date:"df"LINE 1: select'df'::date;

One requirement is to identify whether a string is valid at a time. Many PostgreSQL documents have not found such a function. However, during the type conversion, the database did perform this verification, but it did not implement this function call. So let's take a look at the source code of postgresql, extract this function, and recreate a function to implement this function.

In the src/backend/utils/ADT/date. C code, this function is available:

/* date_in() * Given date text string, convert to internaldate format. */Datumdate_in(PG_FUNCTION_ARGS){        char       *str = PG_GETARG_CSTRING(0);        DateADT         date;        fsec_t          fsec;        struct pg_tm tt,                          *tm = &tt;        int                     tzp;        int                     dtype;        int                     nf;        int                     dterr;        char       *field[MAXDATEFIELDS];        int                     ftype[MAXDATEFIELDS];        char            workbuf[MAXDATELEN + 1];         dterr = ParseDateTime(str, workbuf,sizeof(workbuf),                                                 field, ftype, MAXDATEFIELDS, &nf);        if (dterr == 0)                dterr = DecodeDateTime(field,ftype, nf, &dtype, tm, &fsec, &tzp);        if (dterr != 0)                DateTimeParseError(dterr, str,"date");

 

This function calls the parsedatetime and decodedatetime functions to identify whether the string is a time character. h, so we need to include this header file. So I wrote the following C language function to implement this function:

 

//is_date.c#include"postgres.h"#include"funcapi.h"#include"utils/datetime.h"#include"fmgr.h"#ifdefPG_MODULE_MAGICPG_MODULE_MAGIC;#endifDatumis_date(PG_FUNCTION_ARGS);PG_FUNCTION_INFO_V1(is_date);Datumis_date(PG_FUNCTION_ARGS){      VarChar *arg1 = PG_GETARG_VARCHAR_P(0);      struct pg_tm tt,                           *tm = &tt;      fsec_t          fsec;      char      *str = (char *)palloc(VARSIZE(arg1) - VARHDRSZ +1);      memcpy(str, VARDATA(arg1), VARSIZE(arg1)- VARHDRSZ);      str[VARSIZE(arg1) - VARHDRSZ]=0;      int                     nf;      int                     dtype;      int                     dterr;      int                     tzp;      char            workbuf[MAXDATELEN + 1];      char      *field[MAXDATEFIELDS];      int                     ftype[MAXDATEFIELDS];      elog(INFO, str);      //elog(INFO, arg1->vl_len_);      dterr = ParseDateTime(str, workbuf,sizeof(workbuf),field, ftype, MAXDATEFIELDS, &nf);      if (dterr == 0)                dterr = DecodeDateTime(field,ftype, nf, &dtype, tm, &fsec, &tzp);      if (dterr == 0){      //   elog(INFO, "true");          PG_RETURN_BOOL(true);       }      else      {       //  elog(INFO, "false");          PG_RETURN_BOOL(false);      }}

 

 

Compile:

gcc -m64-I$GPHOME/include -I$GPHOME/include/postgresql -I$GPHOME/include/libpq-I$GPHOME/include/postgresql/server -I$GPHOME/include/postgresql/internal -O2-Wall -Wmissing-prototypes -Wpointer-arith -Winline-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -fPIC-I. -c -o is_date.o is_date.c gcc -m64 -sharedis_date.o  -L$GPHOME/lib-L$GPHOME/lib/postgresql -o is_date.so

Put the is_date.so library file SCP in the/home/gpadmin1/cxf/is_date directory of each GP machine:

Create a function:

CREATE or replaceFUNCTION is_date(varchar)RETURNS bool AS'/home/gpadmin1/cxf/is_date'LANGUAGE C IMMUTABLE  ;

Verification:

aligputf8=# selectis_date('2011-12-10 10:10:10'); is_date --------- t(1 row) aligputf8=# selectis_date('2011-13-10 10:10:10');  is_date --------- f(1 row) aligputf8=# selectis_date('sfd'); is_date --------- f(1 row)

PS: If the string is in time format and the time is correct, true is returned; otherwise, false is returned.