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

テーブル ファンクションでの PostgreSQL パラメータ化された Order By / Limit

PostgreSQL でテーブル ファンクションを利用すると、効率的なデータ操作と取得が可能になります。ただし、大規模なデータセットを扱う場合は、結果を効率的に処理するために並べ替えとページネーションを適用することが重要になります。

次のシナリオを考えてみましょう。テーブル関数 getStuff があり、このテーブル関数は、という名前のテーブルに対して単純な SELECT クエリを実行します。もの:

CREATE OR REPLACE FUNCTION getStuff(param character varying)
  RETURNS SETOF stuff AS
$BODY$
    select *
    from stuff
    where col = 
$BODY$
  LANGUAGE sql;

最初に、関数を次のように呼び出します。

select * from getStuff('hello');

ただし、パフォーマンスを最適化するには、ORDER BY 句と LIMIT 句を適用する必要があります。残念ながら、次のようなクエリを実行すると、

select * from getStuff('hello') order by col2 limit 100;

スタッフ テーブルからすべての行が取得され、それらが並べ替えられて制限されるため、処理が非効率になります。

さらに、PostgreSQL では、値のみを転送できるため、ORDER BY 引数を SQL 言語関数に渡す固有のメソッド。 plpgsql 関数内でクエリを構築し、EXECUTE を介して実行するという代替方法もありますが、これは最も洗練された解決策ではありません。

解決策:

幸いなことに、plpgsql 関数は、次のような場合に効果的な解決策を提供します。このシナリオ。 getStuff 関数を変更して、ORDER BY と LIMIT の追加パラメータを受け入れることができます:

CREATE OR REPLACE FUNCTION getStuff(param character varying, orderby character varying, _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;

これで、必要なパラメータを指定して関数を呼び出すことができます:

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

RETURN QUERY EXECUTEステートメントは、関数内のクエリの結果を効率的に取得します。

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