動的に生成したカラムを使った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"
となります。。。
カラムの定義が必要だと言われているんだと思いますが、カラムは
動的になっているので、どうすれば良いのでしょうか？？
お礼
ありがとうございます。 ご指摘のとおり、予約語にひっかかっていました。 それなりに数があるようなので、カラム名をつけるときに気をつけることにします。