PostgreSQL row for columns

Question

I am trying to create a dynamic system that allows users to import lists of data from Excel, so I need to have dynamic columns, for example:

custom_columns_table id list_id data_type column_name data .... 1 1 VARCHAR(255) email [e-mail] .... 2 1 VARCHAR(255) name Jhon .... list_table id 1 

I need the result:

 id email name .... 1 [e-mail] Jhon .... 

I found some examples using a crosstab, but I don't know if this will work in this case.

Does anyone know how I can do this?

Answer 1

First, the crosstab() family of functions is not installed in standard PostgreSQL. To do this, you need to install the tablefunc extension. In PostgreSQL 9.1, you simply:

 CREATE EXTENSION tablefunc; 

In older versions, see this related answer .

Query

The request may look like this:

 SELECT * FROM crosstab ( 'SELECT l.id ,c.column_name ,c.data FROM custom_columns_table c JOIN list_table l ON l.id = c.list_id ORDER BY 1', 'SELECT DISTINCT column_name FROM custom_columns_table ORDER BY 1') AS tbl ( id integer ,email text ,name text ); 

I use the crosstab() form with two parameters because it allows to skip attributes. For example, when a person does not have email. This form will then return NULL for the email column. Detailed explanation:

• PostgreSQL crosstab query

Function

Or create a function, so you do not need to specify a list of column definitions for each call:

 CREATE OR REPLACE FUNCTION f_mycross(text, text) RETURNS TABLE ( id integer ,email text ,name text) AS '$libdir/tablefunc','crosstab_hash' LANGUAGE C STABLE STRICT; 

Call:

 SELECT * FROM f_mycross( 'SELECT l.id ,c.column_name ,c.data FROM custom_columns_table c JOIN list_table l ON l.id = c.list_id ORDER BY 1', 'SELECT DISTINCT column_name FROM custom_columns_table ORDER BY 1')