Instalar tablefunc extension en PostgreSQL 9.6 para utilizar crosstab

El módulo "tablefunc" incluye varias funciones que devuelven tablas, es decir, obtendremos varias filas. En el siguiente enlace os dejo disponible la documentación para su correcto uso.

https://www.postgresql.org/docs/9.6/static/tablefunc.html

Tengo una entrada en este blog sobre como instalar PostgreSQL en un linux Debian partiendo del código fuente (tengo pendiente hacer una actualización de esta entrada) y en lugar de utilizar el repositorio de paquetes del sistema.

En esta entrada veremos como partiendo de esa instalación compilando el código fuente de PostgreSQL, incorporaremos la extensión tablefunc. Si buscas un poco encontrarás que para hacer uso de las funciones contenidas en tablefunc será necesario ejecutar como usuario administrador la sentencia "create extension tablefunc". Y posiblemente si has llegado hasta aquí, obtuviste como resultado algo parecido a "ERROR:  could not access file "$libdir/tablefunc": No existe el fichero o el directorio".

echo 'create extension tablefunc' | psql -U usuario base_de_datos
ERROR:  could not access file "$libdir/tablefunc": No existe el fichero o el directorio

Si es así está es tu solución. ¿Recuerdas cuando compilaste PostgreSql? Pues mira en aquel directorio donde encontrarás dentro de contrib al directorio contrib/tablefunc. Dirígete a ese directorio y ejecuta "make" y "make install".

root@myhost:~/descargas/postgresql-9.6.3/contrib/tablefunc# make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o tablefunc.o tablefunc.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/opt/postgresql-9.6.3/lib',--enable-new-dtags -lm  -shared -o tablefunc.so tablefunc.o

root@myhost:~/descargas/postgresql-9.6.3/contrib/tablefunc# make install
/bin/mkdir -p '/opt/postgresql-9.6.3/share/extension'
/bin/mkdir -p '/opt/postgresql-9.6.3/share/extension'
/bin/mkdir -p '/opt/postgresql-9.6.3/lib'
/usr/bin/install -c -m 644 ./tablefunc.control '/opt/postgresql-9.6.3/share/extension/'
/usr/bin/install -c -m 644 ./tablefunc--1.0.sql ./tablefunc--unpackaged--1.0.sql  '/opt/postgresql-9.6.3/share/extension/'
/usr/bin/install -c -m 755  tablefunc.so '/opt/postgresql-9.6.3/lib/'

Ahora sí al ejecutar "CREATE EXTENSION tablefunc;" se instalará sin problemas y ya tendrás disponibles las funciones incluidas en tablefunc.

Ejemplo de utilización de crosstab

Una de las funciones más utilizadas de tablefunc es crosstab. Aquí dejo un simple ejemplo de ventas donde pivotaremos los costes de cada uno de los meses a columnas, de forma que nos quedará un registro (fila) por ejercicio.

Crearemos unos datos de prueba para trabajar

CREATE TABLE sales(year int, month int, qty int);
INSERT INTO sales VALUES(2017, 1, 1000);
INSERT INTO sales VALUES(2017, 2, 1500);
INSERT INTO sales VALUES(2017, 7, 500);
INSERT INTO sales VALUES(2017, 11, 1500);
INSERT INTO sales VALUES(2017, 12, 2000);
INSERT INTO sales VALUES(2018, 1, 1000);
INSERT INTO sales VALUES(2019, 5, 2500);
INSERT INTO sales VALUES(2019, 9, 800);

SELECT * FROM sales;
 year | month | qty 
------+-------+------
 2017 |     1 | 1000
 2017 |     2 | 1500
 2017 |     7 |  500
 2017 |    11 | 1500
 2017 |    12 | 2000
 2018 |     1 | 1000
 2019 |     5 | 2500
 2019 |     9 |  800
(8 rows)

Uso de crosstab

SELECT * FROM crosstab(
  $$ SELECT year, month, qty FROM sales ORDER BY 1 $$,
  $$ SELECT m FROM generate_series(1,12) m $$
) AS (
  year int, "Jan" int, "Feb" int, "Mar" int, "Apr" int, "May" int, "Jun" int, "Jul" int, "Aug" int, "Sep" int, "Oct" int, "Nov" int, "Dec" int
);

 year | Jan  | Feb  | Mar | Apr | May  | Jun | Jul | Aug | Sep | Oct | Nov  | Dec 
------+------+------+-----+-----+------+-----+-----+-----+-----+-----+------+------
 2017 | 1000 | 1500 |     |     |      |     | 500 |     |     |     | 1500 | 2000
 2018 | 1000 |      |     |     |      |     |     |     |     |     |      |   
 2019 |          |      |     |     | 2500 |     |     |     | 800 |     |      |   
(3 rows)