動的に生成したカラムを使ったFUNCTION

レコード型の使用方法がわからないので、悪戦苦闘しております。。。

【やりたいこと】
・あるテーブルのある項目でGROUP BYし、別の項目事に集計した結果を
　カラムとして出力
クロス集計と呼ぶのでしょうか？

上記の結果表を取得出来るFUNCTIONを作成したいのです。

【サンプルデータ】
CREATE TABLE t1 (
col1 integer
,col2 integer
,val integer
);

INSERT INTO t1 VALUES( 1, 1, 1, );
INSERT INTO t1 VALUES( 1, 1, 1, );
INSERT INTO t1 VALUES( 2, 1, 1, );
INSERT INTO t1 VALUES( 2, 1, 1, );
INSERT INTO t1 VALUES( 3, 2, 1, );
INSERT INTO t1 VALUES( 3, 2, 1, );
INSERT INTO t1 VALUES( 4, 2, 1, );
INSERT INTO t1 VALUES( 4, 2, 1, );

※ここでcol1、col2ともどんなデータが入っているか、わからないとします。

なので、まずcol2をDISTINCTしたものを求めました。

SELECT DISTINCT col2 FROM t1;

この結果をカーソルを使ってループし、動的にカラムを作成するＳＱＬを
作りました。
DECLARE
cu refcursor;
rec record;
str text := 'SELECT col1 ';
str2 text := ' FROM t1 GROUP BY col1 ORDER BY col1';
BEGIN
OPEN cu FOR EXECUTE SELECT DISTINCT col2 FROM t1;
LOOP
FETCH cu INTO rec;
IF NOT FOUND THEN EXIT;
END IF;

str := str || ', SUM( CASE WHEN col2 = rec.col2 THEN val END )
AS col_name' || rec.col2;
END LOOP;
CLOSE cu;

RAISE INFO 'key = %', str || str2;

　動的に生成されたSQLを実行したい

-------------------------------------------------------
※ちなみに動的に生成されたＳＱＬはこんな感じになります。
SELECT col1
,SUM( CASE WHEN col2 = 1 THEN val END ) AS col_name1
,SUM( CASE WHEN col2 = 2 THEN val END ) AS col_name2
FROM t1
GROUP BY col1
ORDER BY col1;
-------------------------------------------------------

あとがわかりません。。動的なカラムなので、どうやって取得すれば
良いのでしょうか？
レコード型を使うと上手くいくのでしょうか？

試しにFUNCTION( func1 )の戻り値をRETURNS SETOF record として
内部で
rec1 record;
:
:
　FOR rec1 IN EXECUTE str || str2
LOOP
RETURN NEXT rec1;
END LOOP;
RETURN;

SELECT * FROM func1();
とやってみたら、
ERROR: a column definition list is required for functions returning "record"
となります。。。

カラムの定義が必要だと言われているんだと思いますが、カラムは
動的になっているので、どうすれば良いのでしょうか？？

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

>※ちなみに動的に生成されたＳＱＬはこんな感じになります。

>SELECT col1
>,SUM( CASE WHEN col2 = 1 THEN val END ) AS col_name1
>,SUM( CASE WHEN col2 = 2 THEN val END ) AS col_name2
>FROM t1
>GROUP BY col1
>ORDER BY col1;

int,int,intと3個を列として受け渡したいのだから、次のようなユーザデータ型を定義します。

CREATE TYPE test_type -- (INT,INT,INT)の3項目の受け渡し用
AS　
(a INT,
b INT,
c INT);

そして、このユーザ定義型の変数で、受け渡しができると思います。

この回答への補足

ありがとうございます。

今回は、受け取りたい列数が可変なのです。

SELECT DISTINCT col2 FROM t1;
上記のＳＱＬで得られる結果を列にしたいのです。

例）
1000
2100
3500
---
3 rows

ならば、列は
col_name1000, col_name2100, col_name3500としたいのです。

※クロス集計する列を可変で行いたいと考えています
・・・やっぱりムリなんでしょうか？

補足日時：2008/06/19 23:25

この回答へのお礼

いまさらですが、ありがとうございました。
お礼をするのを忘れていました。。。

お礼日時：2010/09/19 23:38