[pgsql-jp: 39075] RE: RE: Re: 複数テーブルからの検索結果を、複数行列返す関数

[chuuken kenkou]

忠犬KEN公です。

関数として呼び出すストアド例を考えてみました。

１．表の定義
(1)必要なら表を削除
drop table header;
drop table target;

(2)表の定義
create table header
(id       int primary key,
 targetid int);
create table target
(id       int primary key,
 name     varchar(10),
 descript varchar(10));

２．ストアドの定義
(1)必要なら削除
drop function functest(id integer);

(2)ストアドの定義
create function functest
 (id integer)
 returns varchar(21)
 as $$
declare
 w_rslt  varchar(21);
begin
 select
   target.name ||' '||target.descript as key
  into w_rslt
  from header,target
  where header.id=$1
    and header.targetid=target.id;
 return w_rslt;
end;
$$ language plpgsql;

３．テスト
(1)テストデータの格納
insert into header values(1,1);
insert into header values(2,1);
insert into header values(3,2);
insert into header values(4,2);
insert into header values(5,3);

insert into target values(1,'aaaaaa','bbbbbbbb');
insert into target values(2,'cccccc','dddddddd');
insert into target values(3,'eeeeee','ffffffff');

(2)検索
-- 検索例1
select functest(1);

-- 検索例2
select
  header.id,
  functest(header.id) as key
 from header
 where header.id between 2 and 3
;

-- 検索例3
select
  header.id,
  functest(header.id) as key
 from header,target
 where header.targetid=target.id
   and header.id between 2 and 3
;

>> 忠犬KEN公です。
>
> 質問のやり取りをざくっと眺めただけなので、ポイントを外した意見かも
> 知れませんが。。。
>
> ストアド・ファンクションとストアド・プロシジャの違いが、PostgreSQLでは
> 分かりにくいために混同しているように感じます。
>
> 標準SQLや主要なRDBMSでは、ストアド・ファンクションはcreate
> function、ストアド・プロシジャはcreate procedureで定義します。
> ストアド・ファンクションは、その名の通り関数であり、一回の呼び出しで
> 返却する値は、1個です。
> それに対して、ストアド・プロシジャは、複数行の処理が可能です。
>
> ストアド・ファンクションは、「select 関数名(引数） from 表名　〜」と
> いった呼び出し方をし、ストアド・プロシジャは、「call プロシジャ名（引数)」
> といった呼び出し方をします。
>
> PostgreSQLでは、ストアド・ファンクション、ストアド・プロシジャの明確な
> 区別がなく、create functionで定義し、select文で呼び出す形になって
> います。
>
> select ストアド・ファンクション名（引数） from 〜
> という指定は、関数としての使い方になるため、1回の関数呼び出しでは
> 1個の値を返却するように関数を定義しておく必要があります。
> 「（列値1、列値2)」のように返却されたのは、値をrecord（行）形式で返却
> するように関数が定義されているためです。
>
> ストアド・ファンクション中で、PL/pgSQLの手続文が書けないといったことは
> ありません。返却値を1個にするように定義すれば、関数として動作させら
> れます。
>
>
>> Date: Fri, 25 Jan 2008 17:55:44 +0900
>> From: itagaki.takahiro ＠ oss.ntt.co.jp
>> To: pgsql-jp ＠ ml.postgresql.jp
>> Subject: [pgsql-jp: 39072] Re: 複数テーブルからの検索結果を、複数行列返す関数
>>
>>
>> NASUNO Isao wrote:
>>
>>> これからも関数を作る場面が出てきそうなのですが、
>>> PL/pgSQL と SQL の違いが、私にはよく飲み込めません。
>>>
>>> PL/pgSQL＝SQL手続き言語というのは、LOOPやFORなどを使って、
>>> 手続き式にSQLを処理するような記述をしたものでしょうか。
>>>
>>> 今回、FOR、LOOP、IN EXECUTE などを使った例も参考にしたのですが（難しくて挫折した）、
>>> これらには、教えていただいた方法は使えないということでしょうか？
>>
>> 例示された testfunc() ならばシンプルなので、SQL のみで実現できると思います。
>> もし、testfunc() でもっと複雑なことをしようと思うと、SQL だけでは足りず、
>> フロー制御などがある手続き言語 (PL/pgSQL など) を使うことになると思います。
>>
>> しかし、ストアド関数に SQL 以外の言語を使った場合、今回の書き方では
>> エラーになるようです。エラーを避けるには、「複数テーブルからの検索結果を
>> マージするストアド関数」も自作する必要がありそうです。
>> （二重のFORループで実現できそうですが、効率が悪い場合も若干あります。）
>>
>> SQL 関数の場合はそのまま処理できる理由は、関数のインライン化が効き、
>> 単純な JOIN にまでクエリを変形できているからだと思われます。
>>
>>
>> -- PL/pgSQL で同じ機能を実現（ただしエラーになる）
>> CREATE FUNCTION testfunc2(integer,
>> OUT id integer,
>> OUT key text)
>> RETURNS SETOF record AS
>> $$
>> DECLARE
>> r record;
>> BEGIN
>> FOR id, key IN SELECT
>> header.id, target.name||' '||target.descript
>> FROM
>> header,
>> target
>> WHERE
>> header.id=$1 and header.targetid=target.id
>> LOOP
>> RETURN NEXT;
>> END LOOP;
>> END;
>> $$ LANGUAGE plpgsql;
>>
>
> _________________________________________________________________
> 「ペットの悩み、みんなにきいたら解決した。」安心のペットライフにMSN相談箱
> http://questionbox.msn.co.jp/c531.html

_________________________________________________________________
Hotmailがお届けする、幸せになるためのメールマガジン「ビジネス幸福論」実施中
http://go.windowslive.jp/