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 ?
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
crosstabest 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.
@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;"
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.