ChatGPTを使ってPostgreSQLのPL/pgSQL関数を単純化してみる

最近の流行りに乗って、ChatGPTを使ってPostgreSQLのPL/pgSQLユーザ定義関数を単純化してみます。

単純化する対象のユーザ定義関数について

PostgreSQLには、ページレベルの情報を取得するためのpageinspectというcontribモジュールがあります。このモジュールには、get_raw_page()とheap_page_items()という2つの関数が含まれています。get_raw_page()は、指定したページの生データを確認でき、heap_page_items()は、指定したページに含まれる行の情報を確認できます。これらの関数を組み合わせることで、テーブル内のページの情報を簡単に確認できます。

例えば、テーブルtestの2ページ目の情報を確認するには、以下のクエリを使用します。

SELECT * FROM heap_page_items(get_raw_page('test', 2));

テーブル全体のページの情報を確認するには、ページ番号を毎回与えながらクエリを実行する必要がありますが、面倒です。そこで、ページ番号でループしながらテーブル内の全てのページを確認するユーザ定義関数を以下のとおり作成します。

CREATE OR REPLACE FUNCTION heap_page_items_all(IN tbl text,
    OUT blkno int4,
    OUT lp smallint,
    OUT lp_off smallint,
    OUT lp_flags smallint,
    OUT lp_len smallint,
    OUT t_xmin xid,
    OUT t_xmax xid,
    OUT t_field3 int4,
    OUT t_ctid tid,
    OUT t_infomask2 integer,
    OUT t_infomask integer,
    OUT t_hoff smallint,
    OUT t_bits text,
    OUT t_oid oid,
    OUT t_data bytea)
RETURNS SETOF record
AS $$
DECLARE
    blk int4;
    upto int4;
BEGIN
    upto = pg_relation_size(tbl) / current_setting('block_size')::int4 - 1;
    FOR blk IN 0 .. upto LOOP
        RETURN QUERY SELECT blk, * FROM heap_page_items(get_raw_page(tbl, blk));
    END LOOP;
END;
$$ LANGUAGE plpgsql;

例えば、テーブルtest全体のページの情報を確認するには、以下のクエリを使用します。

SELECT * FROM heap_page_items_all('test');

今回は、このユーザ定義関数をChatGPTを使って単純化してみます。

ChatGPTでユーザ定義関数を単純化してみる

heap_page_items_all()の関数定義を入力に、 「以下のPL/pgSQLユーザ定義関数を改善して単純化してください。」 とChatGPTに関数の単純化を依頼します。ChatGPTからは、28行あった関数定義を14行に単純化する以下の回答がありました。ChatGPTは、単純化の際にどのような変更を加えたかも説明してくれました。

以下は、heap_page_items_all関数をシンプルにする方法の一例です。この関数では、テーブル名を引数として受け取り、各ブロック内のすべてのヒープページアイテムを返します。

CREATE OR REPLACE FUNCTION heap_page_items_all(tbl regclass)
RETURNS SETOF record
LANGUAGE plpgsql
AS $$
DECLARE
    blkno int4;
    raw_page bytea;
BEGIN
    FOR blkno IN 0 .. pg_relation_size(tbl) / current_setting('block_size')::int4 - 1 LOOP
        raw_page := get_raw_page(tbl, blkno);
        RETURN QUERY SELECT blkno, * FROM heap_page_items(raw_page);
    END LOOP;
END;
$$;

この新しい関数では、OUTパラメータを使用する代わりに、SELECTステートメントによって戻り値を指定しています。また、一時変数の数を減らし、ループ内で変数を宣言する必要がなくなりました。さらに、regclass型を使用することで、テーブル名をより簡潔に指定できます。

さらに単純化してみる

heap_page_items_all()の単純化された関数定義を入力に、 「以下のPL/pgSQLユーザ定義関数をさらに改善して単純化してください。」 とChatGPTに関数のさらなる単純化を依頼します。ChatGPTからは、14行あった関数定義を8行にさらに単純化する以下の回答がありました！

以下は、heap_page_items_all関数をさらに単純化する方法の一例です。この関数では、ループ内でSELECTステートメントを使用する必要がなくなり、1つのクエリですべてのヒープページアイテムを取得できます。

CREATE OR REPLACE FUNCTION heap_page_items_all(tbl regclass)
RETURNS SETOF record
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY SELECT blkno, * FROM generate_series(0, pg_relation_size(tbl) / current_setting('block_size')::int4 - 1) blkno JOIN LATERAL heap_page_items(get_raw_page(tbl, blkno)) ON true;
END;
$$;

この新しい関数では、generate_series関数を使用して、ブロック番号を生成しています。JOIN LATERALを使用して、各ブロックに対してheap_page_items関数を呼び出します。 ON trueを使用することで、クエリ内で条件を指定する必要がなくなりました。これにより、より簡潔な関数が作成されました。

関数をやめてみる

前回の回答によると、heap_page_items_all()はSQL文1つに単純化できる可能性があることが分かります。そこで、前回回答の関数定義を入力に、 「以下を関数ではなく1つのSQLにしてください。」 とChatGPTに依頼します。ChatGPTからは、1つのSQL文に変換する以下の回答がありました。

以下は、heap_page_items_all関数を1つのSQLクエリに変換したものです。

SELECT blkno, *
  FROM generate_series(0, pg_relation_size('tbl_name') / current_setting('block_size')::int4 - 1) blkno
  JOIN LATERAL heap_page_items(get_raw_page('tbl_name', blkno)) ON true;

tbl_nameには、テーブル名が入ります。このクエリでは、generate_series関数を使用して、0から最大ブロック数までの連続する整数を生成しています。次に、LATERAL JOINを使用して、各ブロックのページデータをheap_page_items関数に渡し、戻り値としてブロック番号と各ページアイテムを取得します。

SQLももう少し単純化してみる

前回ChatGPTが提案したSQLにはLATERAL JOINが含まれていました。しかし、これはおそらく必要ないため、「以下のSQLをLATERALを使わないように、より単純化してください。」 ChatGPTに依頼します。ChatGPTからは、LATERALを使わないSQL文に変換する以下の回答がありました。

以下は、LATERALを使用せずに、より単純なSQLに変換したものです。

SELECT blkno, *
FROM generate_series(0, pg_relation_size('tbl_name') / current_setting('block_size')::int4 - 1) blkno, 
     heap_page_items(get_raw_page('tbl_name', blkno));

さいごに

テーブル全体のページの情報を確認する28行のユーザ定義関数を、ChatGPTを使って1つのシンプルなSQL文に単純化することができました。ChatGPTが生成するSQLやユーザ定義関数には誤りが含まれる可能性もあるため、その利用には注意が必要ですが、ChatGPTの活用によって、自分で考えるよりも効率的にSQLやユーザ定義関数を単純化できるケースがあることを確認できました。