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

[ISHIDA Akio]

こんにちは。石田@苫小牧市と申します。

2009年11月4日17:27 Yusuke Yamasaki <tm9233yy ＠ gmail.com>:
> 山崎（ゆ）といいます。
>
> 環境: 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を渡すことはできるようですが、他の変数も並べるとエラーになってしまいました。
>
> 文字連結で見にくくなるのを避けるのに使おうと思っただけなんですが、
> そういう使い方はできないんでしょうか？
>

試してませんが、おそらくリテラルにしか使えないということだと
思います。なので、


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

ではどうでしょうか。

>
> 下記(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>
>
>



-- 
ISHIDA Akio <iakio ＠ mono-space.net/ishida ＠ cycleof5th.com>