[pgsql-jp: 38113] Re: pl/pgsqlのinsertファンクションについて

[Tomoaki Sato]

佐藤です。

> 初めまして。繁宮と申します。
> PostgreSQLを勉強し始めた初心者です。よろしくお願いします。
> 
> pl/pgsqlで単一テーブルに対して複数レコードをinsertするファンクションを
> 作ろうとしていますが、上手くいきません。

何がうまくいかないのか、どのバージョンを使っているのか、そういったこと
を書きましょう。とりあえずそれは置いておくとして、

> ファンクションの引数としてanyarray？もしくは配列のtype(create type)を
> 使って処理をしようと検討してますが・・・。
> 
> ================================================================
> #テーブル定義
> CREATE TABLE test(
>   id character varying(5),
>   name character varying(60)
> )
> ================================================================
> #タイプ定義　(不要でしょうか？)
> CREATE TYPE test_type AS(
>     id text,
>     name text
> );
> ================================================================
> #ファンクション　※ここで複数レコードの登録をしたい。
> create or replace function test_insert(test_type) RETURNS void AS '
> DECLARE
>     result ALIAS FOR $1;
> BEGIN
>    execute ''insert into test(id,name) values(result.id, result.name)'';
> END;
>  'LANGUAGE 'plpgsql';
> ================================================================

ここで関数を定義した際にはエラーにならなかったと思いますが、

> #使用方法
> select test_insert(('1','tarou'));
> ================================================================

おそらく関数を実行するとエラーになったと思います。

> どのようにファンクション定義をすれば上手くいくかご教授お願いします。

それは EXECUTE 文に問題があるからです。以下のように EXECUTE 文を記述し
ていますが、

  execute ''insert into test(id,name) values(result.id, result.name)'';

変数を参照する際には、上記のように文字列リテラルに変数を直接埋め込まず、
以下のように文字列リテラルと変数を || 演算子で結合して組み立てる必要が
あります。

  execute ''insert into test(id,name) values(''
      || quote_literal(result.id)
      || '', ''
      || quote_literal(result.name)
      || '')'';

なお、quote_literal 関数は変数に特殊文字が含まれていた場合に文字列リテ
ラルとして適切なエスケープを行う関数です。

マニュアルには PL/pgSQL について詳しい説明が載っているので、使っている
バージョンが 8.2 であれば以下の URL に書いてあることに一度目を通してお
いたほうがいいでしょう。

  第 37 章 PL/pgSQL - SQL 手続き言語:
    http://www.postgresql.jp/document/pg823doc/html/plpgsql.html

  37.6.5. 動的コマンドの実行:
    http://www.postgresql.jp/document/pg823doc/html/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

> 過去ログの検索をしたのですが、欲しい情報に上手くたどり着けませんでした。
> 重複した質問になってしまっていれば、申し訳ありません。
> 
> ご教授の程、よろしくお願いします。


----
Tomoaki Sato <sato ＠ sraoss.co.jp>
SRA OSS, Inc. Japan