Wie kann ich eine PL/pgSQL-Funktion ändern, um die Ergebnisse einer dynamischen SQL-Abfrage zurückzugeben?

Ändern Sie die PL/pgSQL-Funktion, um Abfrageergebnisse zurückzugeben

Hintergrund

Zunächst erstellen Sie eine Funktion, die eine wohlgeformte PostgreSQL-SELECT-Abfragezeichenfolge ausgibt. Jetzt möchten Sie die generierte SELECT-Anweisung direkt in der Datenbank ausführen und deren Ergebnisse abrufen.

Methoden: Dynamisches SQL und Rückgabetypen

Dynamisches SQL

Um dynamisches SQL auszuführen, verwenden wir den Befehl EXECUTE. Da Sie keinen Cursor verwenden, können Sie die Syntax RETURN QUERY EXECUTE verwenden.

Rückgabetyp

Die Herausforderung besteht darin, Datensätze eines undefinierten Typs zurückzugeben, da die Funktion den Rückgabetyp deklarieren muss. Abhängig von den spezifischen Eigenschaften der Daten werden wir verschiedene Ansätze in Betracht ziehen.

Einfache Lösung mit festem Rückgabetyp

Unter der Annahme fester Rückgabetypen für Zeitstempel, Text und Textspalten können wir die Funktion wie folgt definieren:

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

Variable Anzahl von Spalten desselben Typs

Wenn Sie eine variable Anzahl von Spalten haben und alle Spalten vom gleichen Typ sind (z. B. Double), können wir den Typ ARRAY verwenden, um die Werte zu verschachteln:

<code class="language-sql">CREATE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, names text[], values float8[])
AS $$
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
$$ LANGUAGE plpgsql;</code>

Eine Vielzahl kompletter Tischtypen

Um alle Spalten der Tabelle zurückzugeben, können wir polymorphe Typen verwenden:

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

Hinweis: Im zweiten Beispiel erfordert die Funktion string_to_array ein Trennzeichen, und , wird hier als Trennzeichen hinzugefügt. Das dritte Beispiel verwendet den Formatierungsbezeichner %I, um Tabellennamen sicherer zu verarbeiten und eine SQL-Injection zu verhindern. Welche Methode Sie wählen, hängt von Ihren spezifischen Anforderungen und Ihrer Datenstruktur ab. Wenn möglich, kann die Bereitstellung weiterer Informationen zu Ihrer Tabellenstruktur und den gewünschten Ergebnissen mir dabei helfen, eine präzisere und effizientere Lösung zu finden.

Das obige ist der detaillierte Inhalt vonWie kann ich eine PL/pgSQL-Funktion ändern, um die Ergebnisse einer dynamischen SQL-Abfrage zurückzugeben?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!