Saltar al contenido

Genere columnas dinámicamente para la tabla de referencias cruzadas en PostgreSQL

Lucía, miembro de este equipo de trabajo, nos ha hecho el favor de redactar este post ya que conoce muy bien este tema.

Solución:

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

El manual no es muy claro al respecto. Se menciona al final del capítulo sobre 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
es nombrado crosstab_hash.

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

Llama:

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 necesita crear una crosstab_hash función para cada crosstab función con un tipo de retorno diferente.

Relacionado:

  • Fila de PostgreSQL a columnas

Tu función para generar la lista de columnas es bastante complicado, el resultado es incorrecto (int desaparecido después kernel_id), se puede reemplazar con 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 utilizar de forma dinámica de todos modos.

@ erwin-brandstetter: el tipo de retorno 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 dinámicos 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 usted tiene mixed tipos de datos en su tabla de referencias cruzadas, puede agregar lógica para buscarla para 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;"

Me doy cuenta de que esta es una publicación más antigua, pero luché un poco por el mismo problema.

Mi declaración de problema:
Tenía una tabla con múltiples valores en un campo y quería crear una consulta de tabla cruzada con más de 40 encabezados de columna por fila.

Mi solución fue crear una función que recorriera la columna de la tabla para tomar los valores que quería usar como encabezados de columna dentro de la consulta de tabla de referencias cruzadas.

Dentro de esta función, podría crear la consulta de tabla de referencias cruzadas. En mi caso de uso, agregué este resultado de tabla de referencias cruzadas en una tabla separada.

P.ej

CREATE OR REPLACE FUNCTION field_values_ct ()
 RETURNS VOID AS $$
DECLARE rec RECORD;
DECLARE str text;
BEGIN
str := '"Issue ID" text,';
   -- looping to get column heading string
   FOR rec IN SELECT DISTINCT field_name
        FROM issue_fields
        ORDER BY field_name
    LOOP
    str :=  str || '"' || rec.field_name || '" text' ||',';
    END LOOP;
    str:= substring(str, 0, length(str));

    EXECUTE 'CREATE EXTENSION IF NOT EXISTS tablefunc;
    DROP TABLE IF EXISTS temp_issue_fields;
    CREATE TABLE temp_issue_fields AS
    SELECT *
    FROM crosstab(''select issue_id, field_name, field_value from issue_fields order by 1'',
                 ''SELECT DISTINCT field_name FROM issue_fields ORDER BY 1'')
         AS final_result ('|| str ||')';
END;
$$ LANGUAGE plpgsql;

Te invitamos a defender nuestra publicación exponiendo un comentario o valorándolo te lo agradecemos.

¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)



Utiliza Nuestro Buscador

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *