Pivotal Knowledge Base

Follow

How to import tablefunc module into Greenplum Database for testing

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.x

Symptom

The tablefunc functions that come with postgresql may be required to use the crosstab function. However, tablefunc is not officially supported in HAWQ or GPDB.

PLEASE NOTE: As this procedure is not officially supported by Pivotal, any defects / wrong results or any issues related to the utility has to be addressed via public community.

Resolution

As a test reference, you may follow the below procedure to import tablefunc ( after compiling from postgresql database software source code ) into Greenplum database.

cd /data/david/greenplum-db-4.2.1.0/include/postgresql/server
mkdir cdb
cd /data/david/greenplum-db-4.2.1.0/include/postgresql/internal/cdb
cp * /data/david/greenplum-db-4.2.1.0/include/postgresql/server/cdb
cp /data/david/software/postgresql-8.3.23/src/include/pgtime.h /data/david/greenplum-db-4.2.1.0/include/postgresql/server
cd /data/david/software/postgresql-8.3.23/contrib/tablefunc
gcc -fpic -I /data/david/greenplum-db-4.2.1.0/include/postgresql/server -c tablefunc.c
gcc -fpic -shared -o tablefunc.so tablefunc.o
psql -f tablefunc.sql.gp

Verify:

initdb=# SELECT * FROM crosstab( 'select rowid, attribute, value from ct where attribute = ''att2'' or attribute = ''att3'' order by 1,2') AS ct(row_name text, category_1 text, category_2 text, category_3 text);

row_name | category_1 | category_2 | category_3
------------------------------------------------
test1 | val2 | val3 |
test2 | val6 | val7 |

Internal reference: 

  • ZD-13466
  • MPP-23567

Comments

Powered by Zendesk