Générer dynamiquement des colonnes pour crosstab dans PostgreSQL

je suis en train de créer crosstab requêtes dans PostgreSQL tel qu'il génère automatiquement le crosstab colonnes au lieu du codage dur. J'ai écrit une fonction qui génère dynamiquement la liste des colonnes que j'ai besoin pour mon crosstab requête. L'idée est de remplacer le résultat de cette fonction dans l' crosstab requête utilisant le sql dynamique.

je sais comment faire cela facilement dans SQL Server, mais ma connaissance limitée de PostgreSQL entrave Mon progrès ici. Je pensais stocker le résultat de la fonction qui génère la liste dynamique des colonnes dans une variable et l'utiliser pour construire dynamiquement la requête sql. Ce serait bien si quelqu'un pouvait me guider à ce sujet.


-- 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 ?
12
demandé sur invinc4u 2012-10-14 09:56:49

3 réponses

vous pouvez utiliser la fonction C fournie crosstab_hash pour cela.

Le manuel n'est pas très clair à cet égard. Il est mentionné dans les à la fin du chapitre sur l' crosstab() avec deux paramètres:

Vous pouvez créer des fonctions prédéfinies pour éviter d'avoir à écrire le résultat noms de colonne et types dans chaque requête. Voir les exemples dans la section précédente. La fonction C sous-jacente pour cette forme de crosstab est nommé crosstab_hash.

Pour ton exemple:

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;

Appel:

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

notez que vous devez créer un crosstab_hash fonction pour chaque crosstab fonction avec un type de retour différent.

Voici une autre réponse étroitement liée.


fonction pour générer la liste des colonnes est plutôt alambiqué, le résultat est incorrect ( int manquant après kernel_id), il peut être remplacé par cette requête SQL:

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

et il ne peut pas être utilisé dynamiquement de toute façon.

9
répondu Erwin Brandstetter 2017-05-23 12:26:30

@erwin-brandstetter: le type de retour de la fonction n'est pas un problème si vous retournez toujours un type JSON avec les résultats convertis.

Voici la fonction que j'ai trouvé:

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

ainsi, quand vous l'exécutez, vous obtenez les résultats dynamiques dans JSON, et vous n'avez pas besoin de savoir combien de valeurs ont été pivotées:

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)

Modifier: si vous avez mélangé des types de données dans votre crosstab, vous pouvez ajouter de la logique pour le rechercher pour chaque colonne avec quelque chose comme ceci:

  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;"
2
répondu Caullyn 2017-06-16 10:02:34

L'approche décrite ici http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/ a bien fonctionné pour moi. Au lieu de récupérer le tableau croisé dynamique directement. L'approche la plus simple est de laisser la fonction générer une chaîne de requête SQL. Exécutez dynamiquement la chaîne de requête SQL résultante sur demande.

1
répondu Ben 2017-05-23 08:51:07