PLPGSQLでの複数行複数列

はじめまして。

２つ質問があります。
(1)
SELECT col1, col2 FROM tblA （データは複数行）
の結果表をFUCNTIONとして作成したいのです。

(2)
また、そのFUNCTION( func1とします )から得られる結果を利用して
以下のような使い方がしたいのですが、可能でしょうか？

SELECT * FROM tblB b
LEFT JOIN ( SELECT * FROM func1() ) a USING( col1 )

(1)で行き詰まっているので(2)を試すことも出来ません。。。
せめて(1)だけでも出来たら助かります。

よろしくお願いします

No.3 ベストアンサー 回答者： chukenkenkou 回答日時： 2008/06/19 19:18

>SELECT SUM( c2 ) AS sum_c2,SUM( c3 ) AS sum_c3

> FROM t1
> WHERE c2 IS NOT NULL
> GROUP BY c1

selectの選択列にc1列も指定しないと、どのc1のc2集計、c3集計かが分からないですよ？

方法は二つです。

一つは、表の検索しない列に対応する値を、selectの選択列でダミーの定数で指定する方法です。
つまり、次のように指定します。
select
null, -- c1列に対応
SUM( c2 ),
SUM( c3 ),
null -- c4列に対応
from ～

なお、ストアドファンクション中で付けた別名は、呼び出し側には渡らないようです。
そのため、別名で参照したい場合は、
select
null as dmy_c1,
sum(c2) as sum_c2,
sum(c3) as sum_c3,
null as dmy_c4
from func1()
のように呼び出します。

もう一つの方法は、
select c1,sum(c2),sum(c3) from ～
と、今回の場合、int,int,intの3個の値を列として得たいなら、
create type test_type -- (INT,INT,INT)の3項目の受け渡し用
as 　
(a INT,
b INT,
c INT);
というユーザ定義型を作成し、そのユーザ定義型で受け渡しするストアド・プロシジャを作るというやり方です。

この場合、呼び出しは、
select
a as c1,
b as sum_c2,
c as sum_c3
from func1();
のように行います。

この回答へのお礼

ありがとうございます。
＞selectの選択列にc1列も指定しないと、どのc1のc2集計、c3集計かが分からないですよ？
そうでした。。。すみません。

ダミーの方法とユーザ定義型の方法と教えていただきありがとうございました。
どちらを使うかは、用途によって使い分けようと思います。

お礼日時：2008/06/19 23:22

No.2 回答者： chukenkenkou 回答日時： 2008/06/18 22:47

>ちなみに、私がやりたかったのは、テーブル全件ではなく、WHEREで指定した条件の複数行取得でした。

>この場合は、上記の方法では上手くいかなかった

「うまく行かなかった」とは、具体的に何がどううまく行かなかったのでしょうか？
検索条件がある例を、以下に示します。
ストアドプロシジャでは、「表の全列を、定義順に操作する」ので、データ型としても表名そのもの（今回の場合、t1）を使用しています。

表の全件操作か検索条件で絞り込むかどうかは、ストアドプロシジャ中のSELECT文にWHERE句で検索条件を指定していること以外、前回の提示例と違いはありません。

＜表定義例＞
CREATE TABLE t1
(c1 SERIAL PRIMARY KEY,
c2 INT,
c3 TEXT);

＜格納データ例＞
insert into t1 values(1,null,null);
insert into t1 values(2,null,'data#2');
insert into t1 values(3,31,null);
insert into t1 values(4,41,'data#4');
insert into t1 values(5,null,null);
insert into t1 values(6,null,-61);
insert into t1 values(7,71,null);
insert into t1 values(8,81,'data#8');

＜ストアドプロシジャ例＞
CREATE FUNCTION func1() RETURNS SETOF t1 AS $$
-- ---------------------------------------------------
-- 検索結果を集合として返す関数
-- 表名そのもの（この例では、t1）をデータ型として使用
-- ---------------------------------------------------
DECLARE
rec t1; -- t1表の1行の全列値を受け取る変数
BEGIN
RAISE INFO 'func1 started';
FOR rec IN
SELECT * FROM t1
WHERE c2 IS NOT NULL
AND c3 IS NOT NULL
ORDER BY c1
LOOP
RETURN NEXT rec; -- 検索結果を溜め込む
END LOOP;
RAISE INFO 'func1 ended';
RETURN; -- 呼び先へ戻る
END;
$$ LANGUAGE plpgsql
;


ちなみに、ビューで操作する場合の例です。
＜ビューの定義例＞
CREATE VIEW v1
AS
SELECT * FROM t1
WHERE c2 IS NOT NULL
AND c3 IS NOT NULL
ORDER BY c1
;
＜ビュー経由で操作例＞
例１
SELECT * FROM v1;

例２　さらに絞り込むことも可能。
SELECT * FROM v1
WHERE C1 BETWEEN 1 AND 4
;

この回答への補足

大変失礼しました。
WHERE句を指定したらエラーになったと思っておりましたが、
上記の例題では、問題なく動作しました。

どうやら実際のＳＱＬでのエラー原因は、カラムを指定していることでした。
以下のような感じです。

＜表定義例＞
CREATE TABLE t1
(c1 INT,
c2 INT,
c3 INT,
c4 TEXT);

＜格納データ例＞
insert into t1 values(1,2,null,null);
insert into t1 values(1,3,null,'data#2');
insert into t1 values(2,1,31,null);
insert into t1 values(2,4,41,'data#4');
insert into t1 values(3,55,null,null);
insert into t1 values(3,2,null,-61);
insert into t1 values(4,3,71,null);
insert into t1 values(4,1,81,'data#8');

DECLARE
rec t1; -- t1表の1行の全列値を受け取る変数
BEGIN
RAISE INFO 'func1 started';
FOR rec IN
SELECT
SUM( c2 ) AS sum_c2
,SUM( c3 ) AS sum_c3
FROM t1
WHERE c2 IS NOT NULL
GROUP BY c1
LOOP
RETURN NEXT rec; -- 検索結果を溜め込む
END LOOP;
RAISE INFO 'func1 ended';
RETURN; -- 呼び先へ戻る
END;
$$ LANGUAGE plpgsql
;

FUNCTIONの生成は正常に終了されます。
SELECT * FROM func1();
ここで以下のエラーになります。
　ERROR: invalid input syntax for integer: "29.00";

補足日時：2008/06/19 09:41

この回答へのお礼

エラーの原因がWHERE句ではなかったことに気づきました。
ありがとうございます。

お礼日時：2008/06/19 10:27

No.1 回答者： chukenkenkou 回答日時： 2008/06/18 18:05

関数にするメリットというか、理由が分からないですが？

SQLの記述の簡略化などが理由なら、ビュー表にするといった手段でいいのでは？

一応、関数を作成する方法も示しておきます。

関数で集合を返すには、

(1)RETURNS SETOF ～
(2)RETURN NEXT文

を使うことになりますが、その辺までは把握できているのでしょうか？

また、表の定義通りに行の全列を返す場合、表名そのものをデータ型として指定します。

＜定義例＞
CREATE FUNCTION func1() RETURNS SETOF t1 AS $$
-- --------------------------------------
-- 検索結果を集合として返す関数
-- --------------------------------------
DECLARE
rec t1; -- t1表の1行の全列値を受け取る変数
BEGIN
RAISE INFO 'func1 started';
FOR rec IN
SELECT * FROM t1 ORDER BY c1
LOOP
RETURN NEXT rec; -- 検索結果を溜め込む
END LOOP;
RAISE INFO 'func1 ended';
RETURN; -- 呼び先へ戻る
END;
$$ LANGUAGE plpgsql
;

＜関数呼び出し例＞
select * from func1();

この回答への補足

ありがとうございます。
上記の方法でデータ型として取得できました。

ちなみに、私がやりたかったのは、
テーブル全件ではなく、WHEREで指定した条件の複数行取得でした。

この場合は、上記の方法では上手くいかなかったので、以下のような
方法を使ってみました。どこかのサイトのを参考にしただけなので、
本当にこれで良いのか、わかりません。

CREATE TYPE test_type AS ( a text, b integer );

CREATE OR REPLACE FUNCTION func1() RETURNS SETOF test_type AS
$$
DECLARE
rec test_type;
BEGIN

FOR rec IN
SELECT * FROM t1 WHERE a IS NOT NULL
LOOP
RETURN NEXT rec;
END LOOP;

RETURN;
END;
$$ LANGUAGE plpgsql

実際は、カラムが１５個くらいあるので、この方法だと少し面倒でした。
どうでしょうか？

補足日時：2008/06/18 18:30

この回答へのお礼

サンプルまで示していただき、ありがとうございます。

お礼日時：2008/06/19 10:25