PL/pgSQL 関数をリファクタリングして、可変の列番号と型を含む動的 SELECT クエリから結果を返すにはどうすればよいですか?

PL/pgSQL 関数をリファクタリングして、さまざまな SELECT クエリの出力を返します

はじめに

テキスト文字列として SELECT クエリを生成する PL/pgSQL 関数があります。ここで、この関数をさらに拡張して、スタンドアロン クエリの動作と同様に、生成されたクエリを実行して実際の結果を返すようにしたいと考えています。

変数の戻り値の型

課題は、返されるデータの構造とタイプが、クエリ対象の基になるテーブルに応じて異なる可能性があることです。簡単な解決策は、次のような変更された関数のように、共通の列名で固定戻り値の型を定義し、すべての値をテキストにキャストすることです。

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

可変数の列の処理

返される列の数は変更されるが、型が同じままの場合 (例: すべてが float8 である場合)、ネストされた値の配列を使用できます。

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

任意のテーブルタイプを動的に返します

異なる構造を持つさまざまな完全なテーブル型を処理するには、多態性型を利用できます。

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

この関数では、ターゲット テーブル タイプにキャストされる NULL 値 (NULL::pcdmet など) を渡す必要があります。次に、適切な行タイプを自動的に決定し、SELECT * FROM data_of() とともに使用すると、個々の列を返します。

以上がPL/pgSQL 関数をリファクタリングして、可変の列番号と型を含む動的 SELECT クエリから結果を返すにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。