Genera dynamicmente colonne per campi incrociati in PostgreSQL

Sto cercando di creare query a crosstab in PostgreSQL in modo tale che generi automaticamente le colonne della crosstab croce invece di codificarle. Ho scritto una funzione che genera dynamicmente l’elenco di colonne di cui ho bisogno per la mia query a crosstab . L’idea è di sostituire il risultato di questa funzione nella query a crosstab utilizzando sql dinamico.

So come farlo facilmente in SQL Server, ma la mia conoscenza limitata di PostgreSQL sta ostacolando i miei progressi qui. Stavo pensando di memorizzare il risultato della funzione che genera l’elenco dinamico di colonne in una variabile e di utilizzarlo per creare dynamicmente la query sql. Sarebbe bello se qualcuno potesse guidarmi riguardo lo stesso.

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

Per questo puoi usare la funzione C fornita da crosstab_hash .

Il manuale non è molto chiaro a questo riguardo. È citato alla fine del capitolo su crosstab() con due parametri:

È ansible creare funzioni predefinite per evitare di dover scrivere i nomi e i tipi di colonna dei risultati in ogni query. Vedi gli esempi nella sezione precedente. La funzione C sottostante per questa forma di crosstab è denominata crosstab_hash .

Per il tuo esempio:

 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; 

Chiamata:

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

Si noti che è necessario creare una distinta funzione crosstab_hash per ogni funzione della crosstab con un tipo di ritorno diverso.

Ecco un’altra risposta strettamente correlata.


La tua funzione per generare l’elenco di colonne è piuttosto complessa, il risultato è errato ( int mancante dopo kernel_id ), può essere sostituito con questa query SQL:

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

E non può essere comunque utilizzato dynamicmente.

@ erwin-brandstetter: il tipo di ritorno della funzione non è un problema se si restituisce sempre un tipo JSON con i risultati convertiti.

Ecco la funzione che ho trovato:

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

Quindi, quando lo esegui, ottieni i risultati dinamici in JSON e non hai bisogno di sapere quanti valori sono stati ruotati:

 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) 

Modifica : se si dispone di tipi di dati misti nel campo incrociato, è ansible aggiungere la logica per cercarlo per ogni colonna con qualcosa di simile a questo:

  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’approccio descritto qui http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/ funzionato bene per me. Invece di recuperare direttamente la tabella pivot. L’approccio più semplice consiste nel consentire alla funzione di generare una stringa di query SQL. Esegui dynamicmente la stringa di query SQL risultante su richiesta.