[pgsql-jp: 40047] EXECUTE USINGについて

[Yusuke Yamasaki]

山崎（ゆ）といいます。

環境: CentOS5.4, PostgreSQL 8.4.1

ログデータを格納するテーブルをパーティションに分け、
自動管理するようなBEFORE INSERTトリガを書いてみました。

下記§4のサンプルで、テーブルがない場合は作成する機能を追加したものです。

http://lets.postgresql.jp/documents/technical/partitioning/2#detail
4. 動的SQLによる INSERT の分配
6. CREATE TABLE LINE 構文によるインデックス定義の自動化

動作したコードが、下記(1)になります。

そこで質問ですが、PostgreSQL8.4ではEXECUTE USINGというのが使えるとのことですが、
この機能の使い方がよくわかりません。

newを渡すことはできるようですが、他の変数も並べるとエラーになってしまいました。

文字連結で見にくくなるのを避けるのに使おうと思っただけなんですが、
そういう使い方はできないんでしょうか？


下記(2)のコードでは、

=# insert into events select * from events;
ERROR:  relation "$2" does not exist
行 1: CREATE TABLE "$1" (LIKE "$2" INCLUDING INDEXES INCLUDING DEF...
                              ^
QUERY:  CREATE TABLE "$1" (LIKE "$2" INCLUDING INDEXES INCLUDING
DEFAULTS INCLUDING CONSTRAINTS, CHECK ('$3' <= '$4' AND '$4' < '$5'))
INHERITS ("$2")
CONTEXT:  PL/pgSQL function "event_insert_trigger" line 17 at EXECUTE statement

というエラーになります。


(1) 動作するSQL

CREATE OR REPLACE FUNCTION event_insert_trigger() RETURNS TRIGGER AS
$$
  DECLARE
   parent text := 'events';
   part   text; -- パーティション・テーブルの名前
   t_cur  timestamp;
   t_beg  timestamp;
   t_end  timestamp;
  BEGIN
   -- パーティション名 : parent_YYMM
   part := parent || '_' || to_char(new.created_at, 'YYMM');

   IF NOT EXISTS(select * from information_schema.tables where
table_name = part) THEN
     -- 振り分けの上限・下限
     t_cur := new.created_at;
     t_beg := to_timestamp(to_char(t_cur, 'YYYY-MM-01 00:00:00'),
'YYYY-MM-DD HH24:MI:SS');
     t_end := t_beg + '1 month';

     EXECUTE 'CREATE TABLE ' ||part|| ' (LIKE ' ||parent|| ' INCLUDING
INDEXES INCLUDING DEFAULTS INCLUDING CONSTRAINTS, '
     || 'CHECK (' ||quote_literal(t_beg)|| ' <= '
||quote_literal(t_cur)|| ' AND ' ||quote_literal(t_cur)|| ' < '
||quote_literal(t_end)|| ')'
     || ') INHERITS (' ||parent|| ')';
   END IF;

   -- new を渡す
   EXECUTE 'INSERT INTO ' || part || ' VALUES(($1).*)' USING new;
   RETURN NULL;
  END;
$$ LANGUAGE plpgsql;

-- トリガを作成
CREATE TRIGGER event_insert_trigger_instance
	BEFORE INSERT ON events
	FOR EACH ROW EXECUTE PROCEDURE event_insert_trigger();

(2) エラーになるSQL

CREATE OR REPLACE FUNCTION event_insert_trigger() RETURNS TRIGGER AS
$$
  DECLARE
   parent text := 'events';
   part   text; -- パーティション・テーブルの名前
   t_cur  timestamp;
   t_beg  timestamp;
   t_end  timestamp;
  BEGIN
   -- パーティション名 : parent_YYMM
   part := parent || '_' || to_char(new.created_at, 'YYMM');

   IF NOT EXISTS(select * from information_schema.tables where
table_name = part) THEN
     -- 振り分けの上限・下限
     t_cur := new.created_at;
     t_beg := to_timestamp(to_char(t_cur, 'YYYY-MM-01 00:00:00'),
'YYYY-MM-DD HH24:MI:SS');
     t_end := t_beg + '1 month';

     EXECUTE 'CREATE TABLE "$1" ('
     || 'LIKE "$2" INCLUDING INDEXES INCLUDING DEFAULTS INCLUDING CONSTRAINTS, '
     || 'CHECK (''$3'' <= ''$4'' AND ''$4'' < ''$5'')'
     || ') INHERITS ("$2")'
     USING part, parent, t_beg, t_cur, t_end;
   END IF;

   EXECUTE 'INSERT INTO "$1" VALUES(($2).*)' USING part, new;

   RETURN NULL;
  END;
$$ LANGUAGE 'plpgsql';

-- トリガを作成
CREATE TRIGGER event_insert_trigger_instance
	BEFORE INSERT ON events
	FOR EACH ROW EXECUTE PROCEDURE event_insert_trigger();

-- 
Yusuke Yamasaki <tm9233yy ＠ gmail.com>