Overview

The tablefunc extension provides pivot (row-to-column) capabilities in PostgreSQL-based systems like Greenplum. Note that this extension has only been validated in test environments—use in production at your own risk.

Step 1: Download Matching PostgreSQL Source

Identify the exact PostgreSQL kernel vertion used by your Greenplum installation. Then download the corresponding source code from the official PostgreSQL archive:

$ mkdir -p /home/demo/tablefunc
$ cd /home/demo/tablefunc
$ wget https://ftp.postgresql.org/pub/source/v8.2.15/postgresql-8.2.15.tar.gz

Step 2: Build and Install the Extension

Compile and install the tablefunc module using the PGXS build infrastructure. This must be done on all segment hosts to ensure consistent availability:

$ cd postgresql-8.2.15/contrib/tablefunc
$ make USE_PGXS=1 install

Step 3: Distribute Shared Library Across Cluster

Copy the compiled shared object to all non-master segment hosts and set appropriate permissions:

gpscp -f all_nomaster /usr/local/greenplum-db-6.20.5/lib/postgresql/tablefunc.so =:/usr/local/greenplum-db-6.20.5/lib/postgresql/
gpssh -f all_nomaster "chmod 755 /usr/local/greenplum-db-6.20.5/lib/postgresql/tablefunc.so"

Step 4: Register Extension in Target Database

Load the SQL definitions into your target database:

psql -f /usr/local/greenplum-db-6.20.5/share/postgresql/contrib/tablefunc.sql datawarehouse_test

Step 5: Verify Installtaion

Confirm successful registration by listing available functions:

testdb=# \df tablefunc.*

This should display functions including crosstab, connectby, and others.

Ussage Example

Sample Data Setup

CREATE TABLE score (
    name VARCHAR,
    subject VARCHAR,
    score BIGINT
);

INSERT INTO score VALUES
('Lucy','English',100), ('Lucy','Physics',90), ('Lucy','Math',85),
('Lily','English',76), ('Lily','Physics',57), ('Lily','Math',86),
('David','English',57), ('David','Physics',86), ('David','Math',100),
('Simon','English',88), ('Simon','Physics',99), ('Simon','Math',65);

Standard SQL Pivot

SELECT name,
       SUM(CASE WHEN subject='English' THEN score ELSE 0 END) AS "English",
       SUM(CASE WHEN subject='Physics' THEN score ELSE 0 END) AS "Physics",
       SUM(CASE WHEN subject='Math' THEN score ELSE 0 END) AS "Math"
FROM score
GROUP BY name
ORDER BY name DESC;

Using tablefunc.crosstab

SELECT * FROM crosstab(
    'SELECT name, subject, score FROM score ORDER BY name DESC',
    $$VALUES ('English'::TEXT), ('Physics'::TEXT), ('Math'::TEXT)$$
) AS result(name TEXT, english BIGINT, physics BIGINT, math BIGINT);

Both approaches yield identical results, with tablefunc offering a more declarative syntax for dynamic pivoting scenarios.