Wie kann ich eine PL/pgSQL-Funktion umgestalten, um dynamische SQL-Rückgabetypen zu verarbeiten?

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

Dynamisches SQL und Rückgabetypen

Derzeit gibt Ihre Funktion eine Textzeichenfolge zurück, die die generierte SELECT-Anweisung darstellt. Um die Ausführung zu automatisieren und Ergebnisse zurückzugeben, können Sie dynamisches SQL verwenden. Die Schwierigkeit liegt jedoch im undefinierten Rückgabetyp. Funktionen müssen in der RETURNS-Klausel einen Rückgabetyp definieren.

Lösung mit festem Rückgabetyp

Unter der Annahme eines festen Rückgabetyps, der eine Zeitstempelspalte mit dem Namen datahora und zwei zusätzliche Spalten mit unterschiedlichen Namen und Typen enthält, können Sie Folgendes implementieren:

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

Die Anzahl der Spalten ist variabel, aber der Typ ist derselbe

Wenn der Rückgabetyp eine variable Anzahl von Spalten hat, aber alle Spalten vom gleichen Typ sind (z. B. Double), verwenden Sie den Array-Typ:

<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()  -- 作为名称
           , ARRAY[%s]            -- 作为值
      FROM   %s
      WHERE  id = 
      ORDER  BY datahora'
    , _sensors, _type)
   USING  _sensors, _id;
END
$func$;</code>

Vollständiger Tabellentyp

Um alle Spalten einer Tabelle zurückzugeben, verwenden Sie einen polymorphen Typ:

<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 返回 regtype，自动引用
      WHERE  id = 
      ORDER  BY datahora'
    , pg_typeof(_tbl_type))
   USING  _id;
END
$func$;</code>

Anwendung:

<code class="language-sql">SELECT * FROM data_of(NULL::pcdmet, 17);</code>

Das obige ist der detaillierte Inhalt vonWie kann ich eine PL/pgSQL-Funktion umgestalten, um dynamische SQL-Rückgabetypen zu verarbeiten?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!