Reputation: 21
I'm trying to create a dynamic system that allows the users to import lists of data from Excel so I need to have dynamic columns, ex:
custom_columns_table
id list_id data_type column_name data ....
1 1 VARCHAR(255) email [email protected] ....
2 1 VARCHAR(255) name Jhon ....
list_table
id
1
I need a result like this:
id email name ....
1 [email protected] Jhon ....
I have found some examples using crosstab but I don`t know if it will work in this case.
Does anyone know how can I do this?
Upvotes: 2
Views: 14681
Reputation: 659197
First off, the crosstab()
family of functions is not installed in standard PostgreSQL. You need to install the extension tablefunc
for this. In PostgreSQL 9.1 you would simply:
CREATE EXTENSION tablefunc;
For older versions have a look at this related answer.
The query could 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 form of crosstab()
with two parameters, because that allows for missing attributes. Like, when a person has no email. Then this form will return NULL
for the email
column. See:
But the column definition list must be in sync with the second parameter anyway. So it only makes limited sense to make one dynamic and the other one static. It's only justified to catch varying column_name
- that still sort to fit the same output column definition list.
Or create a function so you don't have to supply a column definition list for every call:
CREATE OR REPLACE FUNCTION f_mycross(text, text)
RETURNS TABLE (id integer, email text, name text)
LANGUAGE c STABLE STRICT AS
'$libdir/tablefunc','crosstab_hash';
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');
Upvotes: 10