PL/pgSQLでページングされた一覧と全件数を取得する

目的

アプリケーションサーバーとDBサーバーの通信は少なければ少ないほどパフォーマンスはよくなります。ここではPL/pgSQLを使って通信数を減らすテクニックを紹介します。

ページングありの一覧のページを考えます。当然1ページのデータを取得しますが、ページングのためには全件数が必要になります。普通なら1ページのデータを取得するのと、全件数を取得するSQLは戻り値がまったく違うので2回呼び出すことになります。

で、ちょっといんちきして、1回のPL/pgSQLの呼び出しで完了するようにします。

コード

テーブル

bookテーブルを考えます。一覧取得は作成日順で取得します。

DROP TABLE IF EXISTS books;
CREATE TABLE books (
    uuid UUID NOT NULL DEFAULT gen_random_uuid()
    ,title TEXT NOT NULL
    ,created_at TIMESTAMPTZ
    ,PRIMARY KEY (uuid)
);

PL/pgSQL

取得するコードは以下のようになります。
p_titleでタイトルをLIKEで検索します。p_limitが1ページあたりのサイズ、p_offsetがずらす数です。
p_with_count_flagがTRUEの時に一覧と一緒に数を返します。
全件数は無理やりタイトルにつっこんで返します。取得したアプリケーションサーバーで分解して利用します。

DROP TYPE IF EXISTS type_my_get_booke_for_paging CASCADE;
CREATE TYPE type_my_get_booke_for_paging AS (
  uuid UUID
  ,title TEXT
);
CREATE OR REPLACE FUNCTION my_get_booke_for_paging (
  p_title TEXT DEFAULT NULL
  ,p_limit BIGINT DEFAULT NULL
  ,p_offset BIGINT DEFAULT 0
  ,p_with_count_flag BOOLEAN DEFAULT FALSE
) RETURNS SETOF type_my_get_booke_for_paging AS $FUNCTION$
DECLARE
BEGIN
  RETURN QUERY SELECT
    t1.uuid
    ,t1.title
  FROM
    public.books AS t1
  WHERE
    p_title IS NULL OR title LIKE p_title
  ORDER BY
    t1.created_at
  LIMIT
    p_limit
  OFFSET
    p_offset
  ;

  IF p_with_count_flag IS TRUE THEN
    RETURN QUERY SELECT
      NULL::UUID
      ,COUNT(*)::TEXT
    FROM
      public.books
    WHERE
      p_title IS NULL OR title LIKE p_title
    ;
  END IF;
END;
$FUNCTION$ LANGUAGE plpgsql;

まとめ

型を無視して適当なカラムに全件数を返すいんちき技です。あまり良くないコードなのでこのあたりはPL/pgSQLもアプリケーション側のコードも自動生成して人が触らないような工夫をしています。