Dynamically generate columns for crosstab in PostgreSQL

by

You can use the provided C function crosstab_hash for this.

The manual is not very clear in this respect. It’s mentioned at the end of the chapter on crosstab() with two parameters:

You can create predefined functions to avoid having to write out the
result column names and types in each query. See the examples in the
previous section. The underlying C function for this form of crosstab
is named crosstab_hash.

For 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 distinct crosstab_hash function for every crosstab function with a different return type.

Related:

Your function to generate the column list is rather convoluted, the result is incorrect (int missing after kernel_id), it can be replaced 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 anyway.

More Related Contents:

  1. Execute a dynamic crosstab query
  2. Test for null in function with varying parameters
  3. INSERT with dynamic table name in trigger function
  4. Truncating all tables in a Postgres database
  5. Dynamic SQL (EXECUTE) as condition for IF statement
  6. Return SETOF rows from PostgreSQL function
  7. Error when setting n_distinct using a plpgsql variable
  8. Function to loop through and select data from multiple tables
  9. Format specifier for integer variables in format() for EXECUTE?
  10. Refactor a PL/pgSQL function to return the output of various SELECT queries
  11. The forgotten assignment operator “=” and the commonplace “:=”
  12. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  13. I forgot the password I entered during postgres installation
  14. How to set value of composite variable field using dynamic SQL
  15. PostgreSQL parameterized Order By / Limit in table function
  16. How do I do large non-blocking updates in PostgreSQL?
  17. Is there a way to disable function overloading in Postgres
  18. Generate series of dates – using date type as input
  19. Can I make a plpgsql function return an integer without using a variable?
  20. Returning from a function with OUT parameter
  21. Postgres function returning table not returning data in columns
  22. COPY with dynamic file name
  23. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  24. Postgresql insert trigger to set value
  25. ERROR: unterminated quoted string at or near
  26. How can I send some http request from postgresql function or trigger
  27. Select from a table variable
  28. Hashing a String to a Numeric Value in PostgreSQL
  29. Loop on tables with PL/pgSQL in Postgres 9.0+
  30. Loop through columns of RECORD

Leave a Comment