Dynamically create columns for a crosstab in PostgreSQL

Question

I am trying to create crosstab queries in PostgreSQL so that it automatically generates crosstab columns instead of hardcoding. I wrote a function that dynamically generates a list of columns that I need for my crosstab query. The idea is to replace the result of this function in a crosstab query using dynamic sql.

I know how to do this easily in SQL Server, but my limited knowledge of PostgreSQL is hindering my progress. I was thinking about saving the result of a function that generates a dynamic list of columns into a variable and uses this to dynamically build an SQL query. It would be great if someone could lead me to the same thing.

 -- Table which has be pivoted CREATE TABLE test_db ( kernel_id int, key int, value int ); INSERT INTO test_db VALUES (1,1,99), (1,2,78), (2,1,66), (3,1,44), (3,2,55), (3,3,89); -- This function dynamically returns the list of columns for crosstab CREATE FUNCTION test() RETURNS TEXT AS ' DECLARE key_id int; text_op TEXT = '' kernel_id int, ''; BEGIN FOR key_id IN SELECT DISTINCT key FROM test_db ORDER BY key LOOP text_op := text_op || key_id || '' int , '' ; END LOOP; text_op := text_op || '' DUMMY text''; RETURN text_op; END; ' LANGUAGE 'plpgsql'; -- This query works. I just need to convert the static list -- of crosstab columns to be generated dynamically. SELECT * FROM crosstab ( 'SELECT kernel_id, key, value FROM test_db ORDER BY 1,2', 'SELECT DISTINCT key FROM test_db ORDER BY 1' ) AS x (kernel_id int, key1 int, key2 int, key3 int); -- How can I replace .. -- .. this static list with a dynamically generated list of columns ? 

Answer 1

For this you can use the provided C function crosstab_hash .

In this regard, the manual is not very clear. He mentioned at the end of the chapter on crosstab() with two parameters:

You can create predefined functions to avoid having to write the names and types of result columns in each query. See Examples in the previous section. The main C function for this form of crosstab is called crosstab_hash .

In your example:

 CREATE OR REPLACE FUNCTION f_cross_test_db(text, text) RETURNS TABLE (kernel_id int, key1 int, key2 int, key3 int) AS '$libdir/tablefunc','crosstab_hash' LANGUAGE C STABLE STRICT; 

Call:

 SELECT * FROM f_cross_test_db( 'SELECT kernel_id, key, value FROM test_db ORDER BY 1,2' ,'SELECT DISTINCT key FROM test_db ORDER BY 1'); 

Note that you need to create a separate crosstab_hash function for each crosstab function with a different return type.

Here is another close answer.

---

Your function to generate a list of columns is rather confusing, the result is incorrect ( int missing after kernel_id ), you can replace it with this SQL query:

 SELECT 'kernel_id int, ' || string_agg(DISTINCT key::text, ' int, ' ORDER BY key::text) || ' int, DUMMY text' FROM test_db; 

And it cannot be used dynamically in any way.

Answer 2

@ erwin-brandstetter: The return type of the function is not a problem if you always return the JSON type with the converted results.

Here is the function I came with:

 CREATE OR REPLACE FUNCTION report.test( i_start_date TIMESTAMPTZ, i_end_date TIMESTAMPTZ, i_interval INT ) RETURNS TABLE ( tab JSON ) AS $ab$ DECLARE _key_id TEXT; _text_op TEXT = ''; _ret JSON; BEGIN -- SELECT DISTINCT for query results FOR _key_id IN SELECT DISTINCT at_name FROM report.company_data_date cd JOIN report.company_data_amount cda ON cd.id = cda.company_data_date_id JOIN report.amount_types at ON cda.amount_type_id = at.id WHERE date_start BETWEEN i_start_date AND i_end_date AND interval_type_id = i_interval LOOP -- build function_call with datatype of column IF char_length(_text_op) > 1 THEN _text_op := _text_op || ', ' || _key_id || ' NUMERIC(20,2)'; ELSE _text_op := _text_op || _key_id || ' NUMERIC(20,2)'; END IF; END LOOP; -- build query with parameter filters RETURN QUERY EXECUTE ' SELECT array_to_json(array_agg(row_to_json(t))) FROM ( SELECT * FROM crosstab(''SELECT date_start, at.at_name, cda.amount ct FROM report.company_data_date cd JOIN report.company_data_amount cda ON cd.id = cda.company_data_date_id JOIN report.amount_types at ON cda.amount_type_id = at.id WHERE date_start between $$' || i_start_date::TEXT || '$$ AND $$' || i_end_date::TEXT || '$$ AND interval_type_id = ' || i_interval::TEXT || ' ORDER BY date_start'') AS ct (date_start timestamptz, ' || _text_op || ') ) t;'; END; $ab$ LANGUAGE 'plpgsql'; 

So, when you run it, you get dynamic results in JSON, and you don't need to know how many values ​​have been rotated:

 select * from report.test(now()- '1 week'::interval, now(), 1); tab ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [{"date_start":"2015-07-27T08:40:01.277556-04:00","burn_rate":0.00,"monthly_revenue":5800.00,"cash_balance":0.00},{"date_start":"2015-07-27T08:50:02.458868-04:00","burn_rate":34000.00,"monthly_revenue":15800.00,"cash_balance":24000.00}] (1 row) 

Change If you have mixed data types in your crosstab, you can add logic to view it for each column with something like this:

  SELECT a.attname as column_name, format_type(a.atttypid, a.atttypmod) AS data_type FROM pg_attribute a JOIN pg_class b ON (a.attrelid = b.relfilenode) JOIN pg_catalog.pg_namespace n ON n.oid = b.relnamespace WHERE n.nspname = $$schema_name$$ AND b.relname = $$table_name$$ and a.attstattarget = -1;" 

Answer 3

The approach described here http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/ worked well for me. Instead of getting a pivot table directly. A simpler approach is to allow the function to generate an SQL query string. Dynamically execute the resulting SQL query string on demand.