PL/pgSQL 関数をリファクタリングして動的 SQL 戻り値の型を処理する方法

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

動的 SQL と戻り値の型

現在、関数は生成された SELECT ステートメントを表すテキスト文字列を返します。実行を自動化し結果を返すには、動的 SQL を使用できます。ただし、戻り値の型が未定義であることが問題です。関数は RETURNS 句で戻り値の型を定義する必要があります。

固定戻り型ソリューション

datahora という名前のタイムスタンプ列と、名前と型が異なる 2 つの追加列を含む固定戻り型を想定すると、次のように実装できます。

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

列の数は可変ですが、型は同じです

戻り値の型の列数が可変でも、すべての列が同じ型 (たとえば、double) の場合は、配列型を使用します。

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

完全なテーブルタイプ

テーブルのすべての列を返すには、多態性型を使用します。

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

使用方法:

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

以上がPL/pgSQL 関数をリファクタリングして動的 SQL 戻り値の型を処理する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。