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

Question

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

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

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

＜質問事項＞
１：メインで「テーブル名.項目名」の形で取得することは可能でしょうか。
２：サブで単一行にもかかわらずloopをかけることに違和感を感じます。
３：サブの戻り値の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
);

• メインのファンクション

CREATE OR REPLACE FUNCTION fnc_main(id text)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$

declare

 manager  		record;
 tmp_manager	manager%rowtype;

begin

-- 1:getmanagerの戻り値全てを取得できるが、1カラムにまとまってしまう
-- 取得値：("(1,名字,30)")
select getmanager(id) into manager;
insert into test values(manager,null,null,null);

-- 2:項目ごとに値を取得できるが、項目数分selectを書く必要がある？
select (getmanager(id)).out_id into manager;
insert into test values(manager.out_id,null,null,null);

-- 3:1と同じ
select getmanager(id) into tmp_manager;
insert into test values(tmp_manager,null,null,null);

-- 4:エラー「record "tmp_manager" has no field "out_name"」
--select (getmanager(id)).out_name into tmp_manager;
--insert into test values(tmp_manager.out_name,null,null,null);

return '';
end;

$function$

• サブのファンクション

CREATE OR REPLACE FUNCTION getmanager(p_id text)
 RETURNS TABLE(out_id character varying, out_name character varying, out_age numeric)
 LANGUAGE plpgsql
 STRICT
AS $function$

declare
	rec record;

BEGIN

-- テスト用に簡略しています。joinやwhereを複数使用予定です
	For rec IN SELECT id, first_name, age FROM manager
	Loop
		out_id		= rec.id;
		out_name	= rec.first_name;
		out_age		= rec.age;
	
		return next;
	End Loop;
	return;

END;

$function$

Answer 1

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

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

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

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

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

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

CREATE OR REPLACE FUNCTION getmanager(p_id text)
 RETURNS TABLE(out_id text, out_name text, out_age numeric)
 LANGUAGE plpgsql STRICT
AS $function$
declare
BEGIN
    return query(
      SELECT id, first_name, age FROM manager
    );
END;
$function$

CREATE OR REPLACE FUNCTION fnc_main(id text)
 RETURNS void -- 戻り値無しはvoid
 LANGUAGE plpgsql
AS $function$
declare
begin
  insert into test(test1, test2, test_num)
  select (getmanager(id)).* 
  ;
  /*
  -- または
  insert into test(test1, test2, test_num)
  select * from getmanager(id) 
  ;
  */
return;
end;
$function$

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

Comments

[退会済みユーザー]

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

[sazi]

>１：メインで「テーブル名.項目名」の形で取得することは可能でしょうか。
select out_id, out_name, out_age from getmanager(id)
のように記述できますので、from句でfanctionを指定するのが良いでしょう。

> 返却された値を個別に取り扱い、メインにて別の処理にも使用したいと考えています。
使いまわすなら、テーブル型ではなくCREATE TYPEでの定義も検討された方が良いでしょう。

> また、上記返却値以外にinsertする項目もあるため、分解したい意図もあります。
insert into select～の場合、気にしないと駄目なのはselect項目の並びとinsert先の項目の並びを一致させる事です。
selectで*指定を優先するならinto項目の並びを合わせれば良いですし、into項目を指定しない場合は、selectで分解するなどして並びを合わせます。

[退会済みユーザー]

>１：select out_id, out_name, out_age from getmanager(id)
のように記述できますので、from句でfanctionを指定するのが良いでしょう。
おっしゃる通り１項目ずつ取得でき、実現したかったことができました。本当にありがとうございます。

＞使いまわすなら、テーブル型ではなくCREATE TYPEでの定義も検討された方が良いでしょう。
CREATE TYPEを使用する場合、サブファンクションの返却の型に使用する認識であっていますでしょうか。
TYPEを使用したことがなく、的外れでしたら申し訳ありません。

＞insert into select～の場合、気にしないと駄目なのはselect項目の並びとinsert先の項目の並びを一致させる事です。
insert into後の項目名はテーブル定義と同様と誤解していました。
select句との関係を意識したいと思います。ありがとうございます。

[sazi]

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

合っています。

[退会済みユーザー]

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