PL/SQLとPL/pgSQLでの複数行を返す関数の例

整数型の id 列と文字列型の name 列を持つ hoge テーブルについて、引数の数値に id 列が一致する行をすべて返却する関数を作成します。

PL/SQL

-- hoge テーブルの作成
CREATE TABLE hoge (id INTEGER, name VARCHAR2(64));
INSERT INTO hoge VALUES (1, 'one');
INSERT INTO hoge VALUES (1, 'first');
INSERT INTO hoge VALUES (2, 'two');
INSERT INTO hoge VALUES (3, 'three');
INSERT INTO hoge VALUES (4, 'four');

-- hoge テーブルのレコード型の作成
CREATE TYPE HogeRowType IS OBJECT (id INTEGER, name VARCHAR2(64));
/

-- hoge テーブルのレコード型を要素とするネストした表を作成
CREATE TYPE HogeRowArrayType IS TABLE OF HogeRowType;
/

-- 関数例その1 (ネストした表に結果を溜め込んで一度に返却)
CREATE OR REPLACE FUNCTION hogetbl (x INTEGER)
  RETURN HogeRowArrayType
IS
  ret HogeRowArrayType := HogeRowArrayType();
  i INTEGER;
BEGIN
  i := 1;
  FOR row IN (SELECT * FROM hoge WHERE id = x) LOOP
    ret.EXTEND;
    ret(i) := HogeRowType(row.id, row.name);
    i := i + 1;
  END LOOP;
  RETURN ret;
END;
/

-- 関数例その1を実行
SELECT * FROM TABLE(hogetbl(1));

-- 関数例その2 (PIPELINED で結果を少しずつ返却)
CREATE OR REPLACE FUNCTION hogetbl_pipelined (x INTEGER)
  RETURN HogeRowArrayType PIPELINED
IS
BEGIN
  FOR row IN (SELECT * FROM hoge WHERE id = x) LOOP
    PIPE ROW(HogeRowType(row.id, row.name));
  END LOOP;
END;
/

-- 関数例その2を実行
SELECT * FROM TABLE(hogetbl_pipelined(1));

PL/pgSQL

-- hoge テーブルの作成
CREATE TABLE hoge (id INTEGER, name VARCHAR(64));
INSERT INTO hoge VALUES (1, 'one');
INSERT INTO hoge VALUES (1, 'first');
INSERT INTO hoge VALUES (2, 'two');
INSERT INTO hoge VALUES (3, 'three');
INSERT INTO hoge VALUES (4, 'four');

-- 関数例その1 (RETURN QUERY で結果を返却)
CREATE OR REPLACE FUNCTION hogetbl (x INTEGER)
  RETURNS SETOF hoge AS $$
BEGIN
  RETURN QUERY SELECT * FROM hoge WHERE id = x;
  RETURN;
END;
$$ LANGUAGE plpgsql;

-- 関数例その1を実行
SELECT * FROM hogetbl(1);

-- hoge テーブルのレコード型の作成
CREATE TYPE HogeRowType AS (id INTEGER, name VARCHAR(64));

-- 関数例その2 (RETURN NEXT で結果を返却)
CREATE OR REPLACE FUNCTION hogetbl2 (x INTEGER)
  RETURNS SETOF HogeRowType AS $$
DECLARE
  row HogeRowType;
BEGIN
  FOR row IN (SELECT * FROM hoge WHERE id = x) LOOP
    RETURN NEXT row;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

-- 関数例その2を実行
SELECT * FROM hogetbl2(1);