質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

Q&A

解決済

1回答

8214閲覧

functionの戻り値を複数取得する方法

退会済みユーザー

退会済みユーザー

総合スコア0

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

0グッド

0クリップ

投稿2021/08/01 03:25

編集2021/08/01 06:21

functionの戻り値を複数取得する方法をご教示ください。

メインのfunctionからサブのfunctionを呼び出しています。
サブ内でテーブルをselectした結果をメインに返却します。

調べたところ、戻り値の型はrecord型、テーブル型が使えそうでしたので、テーブル型で返却しています。
返却値をメインで取得することはできましたが、返却値を単一項目に分解して取得できません。
サブには主キーを全て渡すため、単一レコードが返却されます。

<質問事項>
1:メインで「テーブル名.項目名」の形で取得することは可能でしょうか。
2:サブで単一行にもかかわらずloopをかけることに違和感を感じます。
3:サブの戻り値のrecord型、テーブル型の違いがよくわかりません。

作成中のモジュールは以下の通りです。

//データを取得するテーブル CREATE TABLE manager ( id text, first_name text, age numeric, tel text ); INSERT INTO manager (id, first_name, age, tel) VALUES ('1', '名字', 30, '090-9999-9999'); //データを登録するテーブル CREATE TABLE test ( test1 text, test2 text, test_num numeric, test4 text );
  • メインのファンクション

PL/pgSQL

1CREATE OR REPLACE FUNCTION fnc_main(id text) 2 RETURNS character varying 3 LANGUAGE plpgsql 4AS $function$ 5 6declare 7 8 manager record; 9 tmp_manager manager%rowtype; 10 11begin 12 13-- 1:getmanagerの戻り値全てを取得できるが、1カラムにまとまってしまう 14-- 取得値:("(1,名字,30)") 15select getmanager(id) into manager; 16insert into test values(manager,null,null,null); 17 18-- 2:項目ごとに値を取得できるが、項目数分selectを書く必要がある? 19select (getmanager(id)).out_id into manager; 20insert into test values(manager.out_id,null,null,null); 21 22-- 3:1と同じ 23select getmanager(id) into tmp_manager; 24insert into test values(tmp_manager,null,null,null); 25 26-- 4:エラー「record "tmp_manager" has no field "out_name"」 27--select (getmanager(id)).out_name into tmp_manager; 28--insert into test values(tmp_manager.out_name,null,null,null); 29 30return ''; 31end; 32 33$function$ 34
  • サブのファンクション

PL/pgsql

1CREATE OR REPLACE FUNCTION getmanager(p_id text) 2 RETURNS TABLE(out_id character varying, out_name character varying, out_age numeric) 3 LANGUAGE plpgsql 4 STRICT 5AS $function$ 6 7declare 8 rec record; 9 10BEGIN 11 12-- テスト用に簡略しています。joinやwhereを複数使用予定です 13 For rec IN SELECT id, first_name, age FROM manager 14 Loop 15 out_id = rec.id; 16 out_name = rec.first_name; 17 out_age = rec.age; 18 19 return next; 20 End Loop; 21 return; 22 23END; 24 25$function$ 26

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

ベストアンサー

1:メインで「テーブル名.項目名」の形で取得することは可能でしょうか。

select項目で指定できるのは単項目です。
なので、修飾を付けて個別の項目を指定しない場合は、項目が構造体として扱われます。
纏めて扱いたい場合には*で指定します。selectで指定する以外にFrom句で指定も出来ます。

2:サブで単一行にもかかわらずloopをかけることに違和感を感じます。

loopしない記述も出来ます。

3:サブの戻り値のrecord型、テーブル型の違いがよくわかりません。

record型は実行時に型が決まり、テーブル型はコンパイル時に決まります。

SQL

1CREATE OR REPLACE FUNCTION getmanager(p_id text) 2 RETURNS TABLE(out_id text, out_name text, out_age numeric) 3 LANGUAGE plpgsql STRICT 4AS $function$ 5declare 6BEGIN 7 return query( 8 SELECT id, first_name, age FROM manager 9 ); 10END; 11$function$

SQL

1CREATE OR REPLACE FUNCTION fnc_main(id text) 2 RETURNS void -- 戻り値無しはvoid 3 LANGUAGE plpgsql 4AS $function$ 5declare 6begin 7 insert into test(test1, test2, test_num) 8 select (getmanager(id)).* 9 ; 10 /* 11 -- または 12 insert into test(test1, test2, test_num) 13 select * from getmanager(id) 14 ; 15 */ 16return; 17end; 18$function$

character varyingtextが混在していたので、textに統一しています。
※createで"で括るのは英数字の大文字/小文字を識別したい時に使用するものですので、外した方が良いですよ。

投稿2021/08/01 05:10

編集2021/08/01 05:20
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2021/08/01 05:25

回答ありがとうございます。 「1:」につきまして、追加で質問させてください。 構造体とは一塊の項目として扱われる認識で合っていますか。 返却された値を個別に取り扱い、メインにて別の処理にも使用したいと考えています。また、上記返却値以外にinsertする項目もあるため、分解したい意図もあります。 上記モジュールに記載がなく申し訳ありませんが、ご教示ください。
sazi

2021/08/01 05:41 編集

>1:メインで「テーブル名.項目名」の形で取得することは可能でしょうか。 select out_id, out_name, out_age from getmanager(id) のように記述できますので、from句でfanctionを指定するのが良いでしょう。 > 返却された値を個別に取り扱い、メインにて別の処理にも使用したいと考えています。 使いまわすなら、テーブル型ではなくCREATE TYPEでの定義も検討された方が良いでしょう。 > また、上記返却値以外にinsertする項目もあるため、分解したい意図もあります。 insert into select~の場合、気にしないと駄目なのはselect項目の並びとinsert先の項目の並びを一致させる事です。 selectで*指定を優先するならinto項目の並びを合わせれば良いですし、into項目を指定しない場合は、selectで分解するなどして並びを合わせます。
退会済みユーザー

退会済みユーザー

2021/08/01 06:10

>1:select out_id, out_name, out_age from getmanager(id) のように記述できますので、from句でfanctionを指定するのが良いでしょう。 おっしゃる通り1項目ずつ取得でき、実現したかったことができました。本当にありがとうございます。 >使いまわすなら、テーブル型ではなくCREATE TYPEでの定義も検討された方が良いでしょう。 CREATE TYPEを使用する場合、サブファンクションの返却の型に使用する認識であっていますでしょうか。 TYPEを使用したことがなく、的外れでしたら申し訳ありません。 >insert into select~の場合、気にしないと駄目なのはselect項目の並びとinsert先の項目の並びを一致させる事です。 insert into後の項目名はテーブル定義と同様と誤解していました。 select句との関係を意識したいと思います。ありがとうございます。
sazi

2021/08/01 06:28

> CREATE TYPEを使用する場合、サブファンクションの返却の型に使用する認識であっていますでしょうか。 合っています。
退会済みユーザー

退会済みユーザー

2021/08/01 06:32

承知しました。本当にありがとうございました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問