Genere dinámicamente columnas para tablas cruzadas en PostgreSQL

Intento crear consultas de crosstab en PostgreSQL de modo que genere automáticamente las columnas de la crosstab lugar de codificarlas en forma rígida. He escrito una función que genera dinámicamente la lista de columnas que necesito para mi consulta de crosstab . La idea es sustituir el resultado de esta función en la consulta de crosstab utilizando sql dynamic.

Sé cómo hacer esto fácilmente en SQL Server, pero mi conocimiento limitado de PostgreSQL está obstaculizando mi progreso aquí. Estaba pensando en almacenar el resultado de la función que genera la lista dinámica de columnas en una variable y usar eso para construir dinámicamente la consulta sql. Sería genial si alguien pudiera guiarme con respecto a lo mismo.

 -- 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 ? 

Puede usar la función C proporcionada crosstab_hash para esto.

El manual no es muy claro a este respecto. Se menciona al final del capítulo sobre la crosstab() con dos parámetros:

Puede crear funciones predefinidas para evitar tener que escribir los nombres y tipos de columnas de resultados en cada consulta. Vea los ejemplos en la sección anterior. La función C subyacente para esta forma de crosstab se denomina crosstab_hash .

Para tu ejemplo:

 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; 

Llamada:

 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'); 

Tenga en cuenta que debe crear una función distinta crosstab_hash para cada función de crosstab con un tipo de devolución diferente.

Aquí hay otra respuesta estrechamente relacionada.


Su función para generar la lista de columnas es bastante intrincada, el resultado es incorrecto (faltando int después de kernel_id ), puede ser reemplazado por esta consulta SQL:

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

Y no se puede usar dinámicamente de todos modos.

@ erwin-brandstetter: El tipo de devolución de la función no es un problema si siempre devuelve un tipo JSON con los resultados convertidos.

Aquí está la función que se me ocurrió:

 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'; 

Entonces, cuando lo ejecuta, obtiene los resultados dynamics en JSON, y no necesita saber cuántos valores se pivotaron:

 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) 

Editar : si ha mezclado tipos de datos en su tabla cruzada, puede agregar lógica para buscarla en cada columna con algo como esto:

  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;" 

El enfoque descrito aquí http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/ funcionó bien para mí. En lugar de recuperar la tabla dinámica directamente. El enfoque más fácil es dejar que la función genere una cadena de consulta SQL. Ejecute dinámicamente la cadena de consulta SQL resultante a pedido.