Wie kann ich eine PL/pgSQL-Funktion umgestalten, um Ergebnisse aus dynamischen SELECT-Abfragen mit variablen Spaltennummern und -typen zurückzugeben?

Refaktorisieren Sie PL/pgSQL-Funktionen, um die Ausgabe verschiedener SELECT-Abfragen zurückzugeben

Einführung

Sie haben eine PL/pgSQL-Funktion, die eine SELECT-Abfrage als Textzeichenfolge generiert. Jetzt möchten Sie diese Funktion weiter verbessern, um die generierte Abfrage auszuführen und tatsächliche Ergebnisse zurückzugeben, ähnlich dem Verhalten einer eigenständigen Abfrage.

Variabler Rückgabetyp

Die Herausforderung besteht darin, dass die Struktur und Art der zurückgegebenen Daten je nach der zugrunde liegenden abgefragten Tabelle variieren können. Eine einfache Lösung besteht darin, einen festen Rückgabetyp mit einem gemeinsamen Spaltennamen zu definieren und alle Werte in Text umzuwandeln, wie in der modifizierten Funktion wie folgt:

<code class="language-sql">CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, col2 text, col3 text)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sensors text := 'col1::text, col2::text';
   _type    text := 'foo';
BEGIN
   RETURN QUERY EXECUTE '
      SELECT datahora, ' || _sensors || '
      FROM   ' || quote_ident(_type) || '
      WHERE  id = 
      ORDER  BY datahora'
   USING  _id;
END
$func$;</code>

Umgang mit variabler Spaltenanzahl

Wenn sich die Anzahl der zurückgegebenen Spalten ändert, die Typen jedoch gleich bleiben (z. B. sind alle float8), können Sie ein Array verschachtelter Werte verwenden:

<code class="language-sql">CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, names text[], values float8[])
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sensors text := 'col1, col2, col3';
   _type    text := 'foo';
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT datahora
           , string_to_array()  -- AS names
           , ARRAY[%s]            -- AS values
      FROM   %s
      WHERE  id = 
      ORDER  BY datahora'
    , _sensors, _type)
   USING  _sensors, _id;
END
$func$;</code>

Jeden Tabellentyp dynamisch zurückgeben

Um verschiedene vollständige Tabellentypen mit unterschiedlichen Strukturen zu verarbeiten, können Sie polymorphe Typen nutzen:

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

Diese Funktion erfordert, dass Sie einen NULL-Wert übergeben, der in den Zieltabellentyp umgewandelt wird (z. B. NULL::pcdmet). Anschließend ermittelt es automatisch den passenden Zeilentyp und gibt bei Verwendung mit SELECT * FROM data_of() die einzelnen Spalten zurück.

Das obige ist der detaillierte Inhalt vonWie kann ich eine PL/pgSQL-Funktion umgestalten, um Ergebnisse aus dynamischen SELECT-Abfragen mit variablen Spaltennummern und -typen zurückzugeben?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!