Refactor una funzione PL / pgSQL per restituire l’output di varie query SELECT

Ho scritto una funzione che genera una query SELECT PostgreSQL ben formata in forma di testo. Ora non voglio più stampare un testo, ma effettivamente eseguire l’istruzione SELECT generata sul database e restituire il risultato, proprio come farebbe la query stessa.

Quello che ho finora:

 CREATE OR REPLACE FUNCTION data_of(integer) RETURNS text AS $BODY$ DECLARE sensors varchar(100); -- holds list of column names type varchar(100); -- holds name of table result text; -- holds SQL query -- declare more variables BEGIN -- do some crazy stuff result := 'SELECT\r\nDatahora,' || sensors || '\r\n\r\nFROM\r\n' || type || '\r\n\r\nWHERE\r\id=' || $1 ||'\r\n\r\nORDER BY Datahora;'; RETURN result; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION data_of(integer) OWNER TO postgres; 

sensors contiene l’elenco dei nomi delle colonne per il type tabella. Quelli sono dichiarati e compilati nel corso della funzione. Alla fine, hanno valori come:

  • sensors : 'column1, column2, column3'
    Ad eccezione di Datahora ( timestamp ) tutte le colonne sono di tipo a double precision .

  • type : 'myTable'
    Può essere il nome di uno dei quattro tavoli. Ognuno ha colonne diverse, fatta eccezione per la colonna comune Datahora .

Definizione delle tabelle sottostanti .

I sensors variabili terranno tutte le colonne visualizzate qui per la tabella corrispondente nel type . Per esempio: se type è pcdmet sensors saranno 'datahora,dirvento,precipitacao,pressaoatm,radsolacum,tempar,umidrel,velvento'

Le variabili vengono utilizzate per creare un’istruzione SELECT memorizzata nel result . Piace:

 SELECT Datahora, column1, column2, column3 FROM myTable WHERE id=20 ORDER BY Datahora; 

In questo momento, la mia funzione restituisce questa dichiarazione come text . Copia-incolla ed eseguo in pgAdmin o via psql. Voglio automatizzarlo, eseguire la query automaticamente e restituire il risultato. Come lo posso fare?

SQL dinamico e tipo RETURN

(Ho salvato il meglio per ultimo, continua a leggere!)
Vuoi eseguire SQL dinamico . In linea di principio, è semplice in plpgsql con l’aiuto di EXECUTE . Non hai bisogno di un cursore: infatti, la maggior parte delle volte stai meglio senza cursori espliciti.
Trova esempi su SO con una ricerca .

Il problema che stai incontrando: vuoi restituire i record di un tipo ancora non definito . Una funzione deve dichiarare il tipo restituito con la clausola RETURNS (o con i parametri OUT o INOUT ). Nel tuo caso dovresti ricorrere ai record anonimi, perché il numero , i nomi e i tipi di colonne restituite variano. Piace:

 CREATE FUNCTION data_of(integer) RETURNS SETOF record AS ... 

Tuttavia, questo non è particolarmente utile. In questo modo dovresti fornire un elenco di definizioni di colonne per ogni chiamata della funzione. Piace:

 SELECT * FROM data_of(17) AS foo (colum_name1 integer , colum_name2 text , colum_name3 real); 

Ma come faresti a farlo anche quando non conosci le colonne in anticipo?
È ansible ricorrere a tipi di dati di documenti meno strutturati come json , jsonb , hstore o xml :

  • Come memorizzare una tabella di dati (o Elenco > o Dictionary) nel database?

Ma ai fini di questa domanda, supponiamo che tu voglia restituire il più ansible colonne singole, digitate correttamente e con nome.

Soluzione semplice con tipo a rendimento fisso

La colonna datahora sembra essere un dato, datahora timestamp tipo di dati e ci sono sempre altre due colonne con nome e tipo di dati diversi.

Nomi abbandoneremo a favore di nomi generici nel tipo di ritorno.
Anche i tipi verranno abbandonati e convertiti tutti in text poiché ogni tipo di dati può essere convertito in text .

 CREATE OR REPLACE FUNCTION data_of(_id integer) RETURNS TABLE (datahora timestamp, col2 text, col3 text) AS $func$ DECLARE _sensors text := 'col1::text, col2::text'; -- cast each col to text _type text := 'foo'; BEGIN RETURN QUERY EXECUTE ' SELECT datahora, ' || _sensors || ' FROM ' || quote_ident(_type) || ' WHERE id = $1 ORDER BY datahora' USING _id; END $func$ LANGUAGE plpgsql; 

Come funziona?

  • Le variabili _sensors e _type potrebbero invece essere parametri di input.

  • Prendere nota della clausola RETURNS TABLE .

  • Nota l’uso di RETURN QUERY EXECUTE . Questo è uno dei modi più eleganti per restituire righe da una query dynamic.

  • Uso un nome per il parametro function, solo per rendere meno confusa la clausola USING di RETURN QUERY EXECUTE . $1 nella stringa SQL non si riferisce al parametro della funzione ma al valore passato con la clausola USING . (Entrambi sono $1 nel rispettivo ambito in questo semplice esempio).

  • Nota il valore di esempio per _sensors : ogni colonna è cast per scrivere text .

  • Questo tipo di codice è molto vulnerabile all’iniezione SQL . Io uso quote_ident() per proteggerlo. Il raggruppamento di un paio di nomi di colonne nella variabile _sensors impedisce l’uso di quote_ident() (ed è tipicamente una ctriggers idea!). Assicurati che nessuna roba ctriggers possa esserci in qualche altro modo, per esempio eseguendo individualmente i nomi delle colonne attraverso quote_ident() . VARIADIC viene in mente un parametro VARIADIC

Più semplice con PostgreSQL 9.1+

Con la versione 9.1 o successiva è ansible utilizzare format() per semplificare ulteriormente:

 RETURN QUERY EXECUTE format(' SELECT datahora, %s -- identifier passed as unescaped string FROM %I -- assuming the name is provided by user WHERE id = $1 ORDER BY datahora' ,_sensors, _type) USING _id; 

Ancora una volta, i singoli nomi di colonne potrebbero essere opportunamente scappati e sarebbero il modo pulito.

Numero variabile di colonne che condividono lo stesso tipo

Dopo gli aggiornamenti delle domande, sembra che il tuo tipo di reso sia stato restituito

  • un numero variabile di colonne
  • ma tutte le colonne dello stesso tipo a double precision (alias float8 )

Dato che dobbiamo definire il tipo RETURN di una funzione, in questo caso ARRAY ad un tipo ARRAY , che può contenere un numero variabile di valori. Inoltre, restituisco un array con i nomi delle colonne, in modo da poter analizzare anche i nomi del risultato:

 CREATE OR REPLACE FUNCTION data_of(_id integer) RETURNS TABLE (datahora timestamp, names text[], values float8[] ) AS $func$ DECLARE _sensors text := 'col1, col2, col3'; -- plain list of column names _type text := 'foo'; BEGIN RETURN QUERY EXECUTE format(' SELECT datahora , string_to_array($1) -- AS names , ARRAY[%s] -- AS values FROM %s WHERE id = $2 ORDER BY datahora' , _sensors, _type) USING _sensors, _id; END $func$ LANGUAGE plpgsql; 

Vari tipi di tabelle complete

Se in effetti stai tentando di restituire tutte le colonne di una tabella (ad esempio una delle tabelle nella pagina collegata , utilizza questa soluzione semplice e molto potente con un tipo polimorfico :

 CREATE OR REPLACE FUNCTION data_of(_tbl_type anyelement, _id int) RETURNS SETOF anyelement AS $func$ BEGIN RETURN QUERY EXECUTE format(' SELECT * FROM %s -- pg_typeof returns regtype, quoted automatically WHERE id = $1 ORDER BY datahora' , pg_typeof(_tbl_type)) USING _id; END $func$ LANGUAGE plpgsql; 

Chiamata:

 SELECT * FROM data_of(NULL::pcdmet, 17); 

Sostituisci pcdmet nella chiamata con qualsiasi altro nome di tabella.

Come funziona?

  • anyelement è un tipo di dati pseudo, un tipo polimorfico, un segnaposto per qualsiasi tipo di dati non array. Tutte le occorrenze di anyelement nella funzione valutano lo stesso tipo fornito in fase di esecuzione. Fornendo un valore di un tipo definito come argomento alla funzione, definiamo implicitamente il tipo di ritorno.

  • PostgreSQL definisce automaticamente un tipo di riga (un tipo di dati composito) per ogni tabella creata, quindi esiste un tipo ben definito per ogni tabella. Questo include tabelle temporanee, che è conveniente per l’uso ad-hoc.

  • Qualsiasi tipo può essere NULL . Quindi NULL un valore NULL , lanciato sul tipo di tabella.

  • Ora la funzione restituisce un tipo di riga ben definito e possiamo usare SELECT * FROM data_of(...) per scomporre la riga e ottenere singole colonne.

  • pg_typeof(_tbl_type) restituisce il nome della tabella come tipo di identificatore di object regtype . Quando vengono convertiti automaticamente in text , gli identificatori vengono automaticamente citati in doppia virgola e qualificati per schema se necessario. Pertanto, l’iniezione SQL non è ansible. Questo può anche gestire nomi di tabelle qualificati per schema dove quote_ident() fallirebbe .

Probabilmente vorrai restituire un cursore . Prova qualcosa di simile (non l’ho provato):

 CREATE OR REPLACE FUNCTION data_of(integer) RETURNS refcursor AS $BODY$ DECLARE --Declaring variables ref refcursor; BEGIN -- make sure `sensors`, `type`, $1 variable has valid value OPEN ref FOR 'SELECT Datahora,' || sensors || ' FROM ' || type || ' WHERE nomepcd=' || $1 ||' ORDER BY Datahora;'; RETURN ref; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION data_of(integer) OWNER TO postgres; 

Mi dispiace dirlo ma la tua domanda non è chiara. Tuttavia di seguito troverete un esempio autonomo su come creare e utilizzare una funzione che restituisce una variabile del cursore. Spero che sia d’aiuto !

 begin; create table test (id serial, data1 text, data2 text); insert into test(data1, data2) values('one', 'un'); insert into test(data1, data2) values('two', 'deux'); insert into test(data1, data2) values('three', 'trois'); create function generate_query(query_name refcursor, columns text[]) returns refcursor as $$ begin open query_name for execute 'select id, ' || array_to_string(columns, ',') || ' from test order by id'; return query_name; end; $$ language plpgsql; select generate_query('english', array['data1']); fetch all in english; select generate_query('french', array['data2']); fetch all in french; move absolute 0 from french; -- do it again ! fetch all in french; select generate_query('all_langs', array['data1','data2']); fetch all in all_langs; -- this will raise in runtime as there is no data3 column in the test table select generate_query('broken', array['data3']); rollback;