PL/pgSQL 関数を変更して動的 SQL クエリの結果を返すにはどうすればよいですか?

クエリ結果を返すように PL/pgSQL 関数を変更します

背景

最初に、整形式の PostgreSQL SELECT クエリ文字列を出力する関数を作成します。ここで、生成された SELECT ステートメントをデータベースに対して直接実行し、その結果を取得したいとします。

メソッド: 動的 SQL と戻り値の型

動的 SQL

動的 SQL を実行するには、EXECUTE コマンドを使用します。カーソルを使用していないため、RETURN QUERY EXECUTE 構文を使用できます。

戻り値の型

関数は戻り値の型を宣言する必要があるため、未定義の型のレコードを返すことが課題です。データの特性に応じてさまざまなアプローチを検討します。

固定戻り型を使用した簡単な解決策

タイムスタンプ、テキスト、テキスト列の戻り値の型が固定であると仮定すると、関数を次のように定義できます。

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;

同じ型の列の可変数

可変数の列があり、すべての列が同じ型 (例: double) である場合、ARRAY 型を使用して値をネストできます。

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;

さまざまな完全なテーブル タイプ

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

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;

注: 2 番目の例では、string_to_array 関数には区切り文字が必要で、ここでは , が区切り文字として追加されています。 3 番目の例では、%I フォーマット識別子を使用してテーブル名をより安全な方法で処理し、SQL インジェクションを防ぎます。 どの方法を選択するかは、特定のニーズとデータ構造によって異なります。 可能であれば、テーブル構造と目的の結果に関する詳細情報を提供していただくと、より正確で効率的なソリューションを提供できる可能性があります。

以上がPL/pgSQL 関数を変更して動的 SQL クエリの結果を返すにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。