PostgreSQL テーブル関数でパラメータ化された ORDER BY と LIMIT を効率的に実装するにはどうすればよいですか?

PostgreSQL テーブル関数のパラメータ化された Order By と Limit

基本的な SELECT ステートメントを実行するために、getStuff という名前のカスタム SQL 関数が作成されました。この関数を select * from getStuff('hello') の形式で使用すると、指定されたパラメーター値に基づいてスタッフ テーブルからすべての行がフェッチされます。

ただし、順序付けをどのように実装できるかという疑問が生じます。効率を向上させ、必要な行のみを取得するためにこの関数を制限しますか? getStuff 関数内で order by 句とlimit 句を直接使用すると、フィルタを適用する前にすべての行をフェッチする必要があるため、非効率的になります。

推奨される解決策は、PostgreSQL の手続き型言語である PL/pgSQL を利用して、関数。 PL/pgSQL を使用すると、動的 SQL クエリの構築と実行が可能になり、パラメータ値を柔軟に処理できます。

PL/pgSQL の関数の例:

CREATE OR REPLACE FUNCTION get_stuff(_param text, _orderby text, _limit int)
RETURNS SETOF stuff AS
$func$
BEGIN
   RETURN QUERY EXECUTE '
      SELECT *
      FROM   stuff
      WHERE  col = 
      ORDER  BY ' || quote_ident(_orderby) || ' ASC
      LIMIT  '
   USING _param, _limit;
END
$func$  LANGUAGE plpgsql;

この関数は 3 つのパラメータを取ります: _param (フィルターの基準となる値)、_orderby (並べ替えの基準となる列)、および _limit (並べ替える最大行数) return).

関数を呼び出すには:

SELECT * FROM get_stuff('hello', 'col2', 100);

RETURN QUERY EXECUTE を使用すると、構築された SQL クエリの結果が一度に返されます。 quote_ident は、識別子をエスケープすることで SQL インジェクションを防ぎます。 SQL インジェクションと潜在的なキャスト/引用の問題を回避するために、パラメーターは USING 経由で渡されます。

より複雑なクエリの場合は、format() 関数の使用を検討してください。

SQL 関数は単純な操作に適している場合がありますが、 PL/pgSQL は、テーブル関数でパラメータ化された ORDER BY 句と LIMIT 句を処理する際に、より優れた柔軟性と最適化機能を提供します。

以上がPostgreSQL テーブル関数でパラメータ化された ORDER BY と LIMIT を効率的に実装するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。