PostgreSQLのストアドファンクションからSELECT結果を返す方法

この記事は公開から3年以上経過しています。

PostgreSQLのストアドファンクション内でSELECTした結果のレコードを返す方法の備忘録。

サンプルソースコード

テストテーブル構成

CREATE TABLE user_master (
    id bigint NOT NULL,
    name text
);

INSERT INTO user_master (id, name)
    VALUES (100, 'Foo');

INSERT INTO user_master (id, name)
    VALUES (101, 'Bar');

INSERT INTO user_master (id, name)
    VALUES (102, 'Baz')

ストアドファンクション定義

CREATE FUNCTION get_users ()
    RETURNS TABLE (
        id bigint,
        name text
    )
    AS $$
BEGIN
    RETURN query
    SELECT
        *
    FROM
        user_master;
END;
$$
LANGUAGE plpgsql;

※select文で*ではなくカラム名を指定する場合は、returns tableのカラム名定義と重複しないように列名にテーブル名を付けるなどの対応が必要。

結果

SELECT
    *
FROM
    get_users ();

ちなみに、テーブルではなく集合を使い

CREATE FUNCTION get_users ()
    RETURNS SETOF record
    AS $$
BEGIN
    RETURN query
    SELECT
        *
    FROM
        user_master;
END;
$$
LANGUAGE plpgsql;

SELECT
    *
FROM
    get_users ()
    AS (id bigint, name text);

のようにsetof recordと列定義リストを利用しても、同様の結果を得られます。

参考ウェブサイトなど

• PostgreSQL Docs
  34.4.8. TABLEを返すSQL関数

• PostgreSQL Docs
  34.4.7. 集合を返すSQL関数

以上です。