Skip site navigation (1) Skip section navigation (2)

Re: Analysis Function

From: David Jarvis <thangalin(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Analysis Function
Date: 2010-06-11 18:17:18
Message-ID: AANLkTim6TrfgLiXM3sTSx6rgXQ3zNLTWBTD_MbU0OPsv@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

Here is code to convert dates from integers without string concatenation:

Edit dateserial.c:

#include "postgres.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 = (int32)PG_GETARG_FLOAT8(0);
  int32 p_month = PG_GETARG_INT32(1);
  int32 p_day = PG_GETARG_INT32(2);

  PG_RETURN_DATEADT( date2j( p_year, p_month, p_day ) - POSTGRES_EPOCH_JDATE );
}

Edit Makefile:

MODULES = dateserial
PGXS := $(shell pg_config --pgxs)
include $(PGXS)

Edit inst.sh (optional):

#!/bin/bash

make clean && make && strip *.so && make install &&
/etc/init.d/postgresql-8.4 restart

Run bash inst.sh.

Create a SQL function dateserial:

CREATE OR REPLACE FUNCTION dateserial(double precision, integer, integer)
  RETURNS date AS
'$libdir/dateserial', 'dateserial'
  LANGUAGE 'c' IMMUTABLE STRICT
  COST 1;
ALTER FUNCTION dateserial(double precision, integer, integer) OWNER TO postgres;

Test the function:

SELECT dateserial( 2007, 5, 5 )

Using this function, performance increases from 4.4s to 2.8s..

Dave

In response to

Responses

pgsql-performance by date

Next:From: Bob LunneyDate: 2010-06-11 18:50:10
Subject: Re: Query about index usage
Previous:From: Magnus HaganderDate: 2010-06-11 17:19:50
Subject: Re: Error with GIT Repository

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group