• ベストアンサー

postgres ストアドファンクションないのレコードの取得について

質問させていただきます pg/plsqlのストアドファンクション内で 受け取った引数を sql := select * from hoge; FOR rec IN EXECUTE sql LOOP return rec.col;　　　　　＜＜＜この部分で END LOOP; return; rec.引数名のようにして　引数から取得した 列を指定したいのですが、そのまま入れると そのような列はありませんと言うような返事を 返されてしまいます。 うまくやる方法はないものでしょうか？ よろしくお願いします。

質問者が選んだベストアンサー

  • ベストアンサー
回答No.2

こんにちは。 なるほど・・・です。 残念ながら動的SQLじゃないので、コンパイル時にカラムの整合性をチェックしに行きます。 なので、本当は実行時にだけ参照して欲しいというささやかな願いは、聞き入れてくれません・・・。 動的SQLを使用するように変更するか、レコード型を宣言し、明示カーソルループ（こんな言い方でいいのかな？？？）でレコード型に代入、型のカラムを返す。 などという方向転換が必要かと・・・。 (^^ゞ

VIPFIRE
質問者

お礼

こんにちは なるほど了解しました。 カーソルループのやりかたでやってみようと思います。 お返事ありがとうございました！

その他の回答 (1)

回答No.1

こんばんは。 >>引数から取得した列を指定したいのですが ？？？ 全体がつかめません・・・。 もう少しお願いします。 (^^ゞ

VIPFIRE
質問者

補足

わかりにくい文章で申し訳ありません。 「引数から取得した列を指定したいのですが」とは 引数で取得した文字列を rec.列名　 の列名に代入して任意の列を取得したかったと言う意味なのです。 rec.$1　とか rec. || $1 のようにしては（$1は引数名）うまくいかなかったのでこのような質問をさせていただきました。 よろしければまたお願いいたします。

  1. カテゴリ
  2. [技術者向] コンピューター
  3. データベース
  4. PostgreSQL

関連するQ&A

  • 動的に生成したカラムを使った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" となります。。。 カラムの定義が必要だと言われているんだと思いますが、カラムは 動的になっているので、どうすれば良いのでしょうか？？

  • postgresのストアドファンクションでエラーの

    postgresのストアドファンクションでエラーの拾い方を教えてください。 insertして重複したらアップデートに切り替えるというサンプルをよく見かけます。 DECLARE BEGIN insert文 RETURN TRUE; EXCEPTION WHEN unique_violation THEN update文 END; それと同じノリで、何かしらのエラーがあった時に、 『RETURN FALSE;』するか、もしくはエラーコードをそのまま拾って返すというようなサンプルが欲しいです。 DECLARE BEGIN 何かしらのSQL RETURN TRUE; EXCEPTION WHEN すべてのエラー THEN RETURN FALSE; END; DECLARE BEGIN 何かしらのSQL RETURN TRUE; EXCEPTION WHEN すべてのエラー THEN RETURN エラーコード; END;

  • ストアドファンクションの戻り値をテーブルに・・

    SQL/PLUSにて作成したファンクションの戻り値で返ってきた文字列をテーブル名として使用するにはどうすればよいのでしょうか？？ まず以下のようにSQL/PLUSにてFUNCTIONを作成しました。 create or replace function FuncXXXXX (inA varchar2) return varchar2 is OutA varchar2; begin select MAX(table_name) into outA from all_tables where table_name like inA || '%'; return outA end; / それを以下省略ですが、テーブルでコールしようとするとエラー（ORA-00905：キーワードがありません）が発生します。このような使い方はできないのでしょうか？ select 　　・ 　　・ from AAAA inner join FuncXXXXX(inA)　※execute FuncXXXXX(inA)でも同様 on 　　・ 　　・ 申し訳ありませんが宜しくお願いします。

  • PLPGSQL レコードセットを列数で取得したい

    Postgresで関数（ストアドプロシージャ）を作成するのに PLPGSQLを使用しています。 レコードセットを取得し、1列目～40列目までを順次値を判定し、特定の値がある場合は、41列～80列のそれぞれ対応する（1列目なら41列目、13列目なら53列目…）列から数値を取得する動きを実現したいと考えています。 上記条件は下記プログラムで実現してますが、再帰処理で同じことが実現できないでしょうか？ ※商品マスタを作成して、クエリを作成するのが当たり前と思いますが、よんどころのない事情でこのテーブル形式をを崩せません。（外部システムの汎用データから取り込むためです） ------------------- 以下現状のプログラム　------------------- declare rec record; int1 integer; begin int1:=0; for rec in select * from kudamonouriage where syainid=$1 and kppw=$2 loop if substr(rec.kate1,1,3)='りんご' then int1:=int1+rec.kingaku1; end if; if substr(rec.kate2,1,3)='りんごthen int1:=int1+rec.kingaku2; end if; if substr(rec.kate3,1,3)='りんご' then int1:=int1+rec.kingaku3; end if; … … if substr(rec.kate39,1,3)='りんご' then int1:=int1+rec.kingaku39; end if; if substr(rec.kate40,1,3)='りんご' then int1:=int1+rec.kingaku40; end if; end loop; return int1; end; ---------------- 以上現状のプログラム　--------------------- これを ------------------- 希望するプログラム　------------------- declare rec record; int1 integer; begin int1:=0; for rec in select * from kudamonouriage where syainid=$1 and kppw=$2 loop for i 1..40 loop if substr(rec.(i),1,3)='りんご' then int1:=int1+rec.(40+i); end if; end loop; end loop; return int1; end; ---------------- 以上希望のプログラム　--------------------- このようにしたいと思っています。 サイト上で色々と検索していますが、解決に至っていません。お力を貸していただけると大変助かります。宜しくお願いいたします。

  • Mysql ストアドファンクション　意図しない結果

    以下の通りストアドファンクションを作成しました。 関数名：fgetLineNew インパラメータ：shop　Varchar 8 ※1つだけ。。。 戻り値： Int 11 ＜ソース＞ BEGIN DECLARE pline INTEGER(11) DEFAULT NULL; SELECT COUNT(1) INTO pline FROM `hoge` WHERE `SHOP` = shop; IF pline IS NULL THEN SET pline = 10000; END IF; RETURN pline; END 上記のように、作成したストアドファンクションですが、 どんな引数を入れても、戻り値が全ての行(817行)になってしまいます。 以下を参照。 Where区がまったく働いていないのですが、何が原因なのでしょうか。 教えてください。 ＜ストアドファンクション実行＞ mysql> select fgetLineNew(1); +----------------+ | fgetLineNew(1) | +----------------+ | 817 | +----------------+ 1 row in set (0.01 sec) mysql> select fgetLineNew(525); +------------------+ | fgetLineNew(525) | +------------------+ | 817 | +------------------+ 1 row in set (0.01 sec) mysql> select fgetLineNew(101); +------------------+ | fgetLineNew(101) | +------------------+ | 817 | +------------------+ 1 row in set (0.00 sec) mysql> select fgetLineNew(525); +------------------+ | fgetLineNew(525) | +------------------+ | 817 | +------------------+ 1 row in set (0.00 sec) mysql> select fgetLineNew('525'); +--------------------+ | fgetLineNew('525') | +--------------------+ | 817 | +--------------------+ 1 row in set (0.01 sec)

    • ベストアンサー
    • MySQL
  • ストアドファンクションの再帰呼び出しについて

    「ストアドファンクションの再帰呼び出し」はできないのでしょうか。 hoge関数をhoge関数の中で呼び出しています。 hoge関数では、条件に当てはまらないと第三引数を、 編集して再帰呼出しをしています。 再帰呼出しする条件を引数に設定して実行すると、以下の実行結果(ERROR)が表示されます。 ＜実行結果＞ mysql> select hoge('7','1234','1234567891012'); ERROR 1424 (HY000): Recursive stored functions and triggers are not allowed. よろしくお願いします。

    • ベストアンサー
    • MySQL
  • AccessのSQLで、レコード数の取得方法を教えてください。

    すみませんが、教えてください。 AccessをＡＤＯ＋ＳＱＬで操作しています。 cnn.Open　＊＊＊＊ sql="SELECT hoge FROM tabeleHoge;" set rec=cnn.Execute(sql) で、recオブジェクトから、レコード数を取得する方法ってあるのでしょか？ sql="SELECT COUNT(*) FROM tableHoge；" を使わずに、できれば、嬉しいのですが。 以上、よろしくお願いいたします。

  • ストアドファンクション⇒プロシージャ呼出し失敗する

    参考の通りに、ストアドプロシージャ作成SQL文を発行し、 コマンドライン上からは、以下の通りの結果1となり、 レコードがinsertされます。 ＜結果1＞ mysql> call logger('ほげ', 'ホゲ'); Query OK, 1 row affected (0.01 sec) mysql> select * from PROCEDURE_LOG; +--------+--------+---------------------+ | NAME | QUERY | EXECUTE_DATE | +--------+--------+---------------------+ | ほげ | ホゲ | 2014-03-26 10:41:26 | +--------+--------+---------------------+ 1 row in set (0.00 sec) ですが、testというストアドファンクションから呼び出してみましたが、 以下の結果2の通り、失敗します。 ＜結果2＞ mysql> select test(); ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger ＜test＞ BEGIN CALL logger('test','1'); RETURN CONCAT('END'); END 現在動作している環境では、どこでエラーが発生したかがわからず、 ロガーの処理をなんとしても実装したいのですが、 いろいろ試してみても、解決できませんでした。 ご教示のほど、よろしくお願いします。 ＜参考＞ http://treeapps.hatenablog.com/entry/20120106/p1 ＜ストアドプロシージャ作成SQL文(logger)＞ SET NAMES UTF8; delimiter // -- ログ出力 DROP PROCEDURE IF EXISTS logger// CREATE PROCEDURE logger( IN PROCEDURE_NAME TEXT, IN SQL_TEXT TEXT ) BEGIN DECLARE CNT INT; CREATE TABLE IF NOT EXISTS PROCEDURE_LOG ( NAME VARCHAR(64), QUERY TEXT, EXECUTE_DATE TIMESTAMP, KEY IDX1 (NAME, QUERY(64), EXECUTE_DATE) ) ENGINE=MYISAM DEFAULT CHARSET=UTF8; -- 古いログを削除 SELECT COUNT(*) INTO CNT FROM PROCEDURE_LOG; IF CNT >= 1000 THEN DELETE FROM PROCEDURE_LOG LIMIT 1; END IF; -- テーブルにログを記録 SET @sql = CONCAT('INSERT INTO PROCEDURE_LOG VALUES (', QUOTE(PROCEDURE_NAME), ',', QUOTE(SQL_TEXT), ', null)'); PREPARE stmt FROM @sql; EXECUTE stmt; END // delimiter ;

    • ベストアンサー
    • MySQL
  • ストアドファンクションについて

    最近SQL sarvarの勉強を始めた初心者です。 今、参考書を読みながらストアドファンクションを勉強してるんですが、 create function dbo.Fn_aaaaa (@dat datetime) returns char(14) as begin return cast(datepart(yyyy, @dat) as char(4))　+ '年' + cast(datepart(mm, @dat) as char(2)) + '月' + cast(datepart(mm, @dat) as char(2)) + '日' + end という例題にある「returns char(14)」とはどういう意味なのかがよくわかりません。正直、戻り値という概念をしっかりと理解していない部分があり困っています。 本当に初歩的でもしかしたらSQLに関係のない質問かもしれませんがよろしければお教えください。

  • ストアド内で実行したSQLの出力結果について

    現在、引数の値を元に下記のようなSQL文を生成しています。 SELECT code, name FROM T_CGY WHERE code = '1111' UNION SELECT code, name FROM T_CGY WHERE code = '1110' UNION SELECT code, name FROM T_CGY WHERE code = '1100' UNION SELECT code, name FROM T_CGY WHERE code = '1000' ORDER BY code; ※出力されたSQL文に誤りがないかを確認する為にコピーしてターミナル上で実行したら該当するレコードを取得する事ができました。 それをストアドプロシージャ内で生成したSQL文を「QUERY EXECUTE」を実行して該当するレコードが取得できるように改造してみました。 下記が出力された結果です。 getCgyData -------------------- (1000,洋服) (1100,子供服) (1110,ズボン) (1111,長ズボン) 出力されたデータは私が望んだ内容なんですが・・・ 私的には下記のように出力したいと考えています。 col1　　　　｜　col2 ------------------------- 1000　　｜　洋服 1100　　｜　子供服 1110　　｜　ズボン 1111　　｜　長ズボン 何とか上記のように出力できるようにするにはどうしたらいいでしょうしょうか。 そもそも上記のように出力する事は可能なのでしょうか。 CREATE FUNCTION getCgyData (VARCHAR) RETURNS TABLE(col1 VARCHAR, col2 VARCHAR) AS ' DECLARE key ALIAS FOR $1; sql TEXT; BEGIN 　　　　　　　　　　　　　　　　・ 　　　　　　　　　　　　　　　　・ ※ここでSQL文を生成して、変数(sql)に格納しています。 　　　　　　　　　　　　　　　　・ 　　　　　　　　　　　　　　　　・ RETURN QUERY EXECUTE sql; END; ' language 'plpgsql' ; データベースのバージョンはpostgreSQL8.4.9です。 再度、申し訳ありませんがアドバイス、宜しくお願いします。 では、失礼します。

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する