plpgsql은 처음쓰는겁니다 우선 데이터값들이 들어오는 상태에서
CREATE TABLE event
(
sid int4 NOT NULL,
cid int8 NOT NULL,
signature int4 NOT NULL,
"timestamp" timestamptz NOT NULL,
CONSTRAINT event_pkey PRIMARY KEY (sid, cid)
)
CREATE TABLE ips_count_week
(
date timestamp NOT NULL,
sig_id int4 NOT NULL,
count_rule int4 NOT NULL default 0,
CONSTRAINT ips_count_date_pkey PRIMARY KEY (date),
CONSTRAINT meddle_rule_fkey FOREIGN KEY (sig_id) REFERENCES signature(sig_id)
)
이 둘 테이블간에 trigger를 걸었고
create trigger count_week_trgger
after insert on event
for each row
execute procedure count_func();
실행 시켰을때
WARNING: changing return type of function count_func from "opaque" to "trigger"
이 부분은 나와서 좀 찜찜하고
이것에 대한 function은
create or replace function count_func() RETURNS opaque AS '
begin
if TG_OP = ''INSERT'' then
update ips_count_week SET count_rule = count_rule + 1 where sig_id = NEW.signature
and date_trunc(hour, date) = date_trunc(hour, NEW.timestamp );
return NEW;
end if;
end;
'language 'plpgsql';
이것을 실행했을때의 결과는 로그상으로
database: postgresql_error: ERROR: column "hour" does not exist
CONTEXT: PL/pgSQL function "count_func" line 4 at SQL statement
database: postgresql_error: ERROR: column "hour" does not exist
CONTEXT: PL/pgSQL function "count_func" line 4 at SQL statement
database: postgresql_error: ERROR: column "hour" does not exist
CONTEXT: PL/pgSQL function "count_func" line 4 at SQL statement
Sep 15 11:20:05 test postgres[4339]: [6-1] ERROR: column "hour" does not exist
Sep 15 11:20:05 test postgres[4339]: [6-2] CONTEXT: PL/pgSQL function "count_func" line 4 at SQL statement
Sep 15 11:20:05 test postgres[4339]: [7-1] ERROR: column "hour" does not exist
Sep 15 11:20:05 test postgres[4339]: [7-2] CONTEXT: PL/pgSQL function "count_func" line 4 at SQL statement
Sep 15 11:20:05 test postgres[4339]: [8-1] ERROR: column "hour" does not exist
Sep 15 11:20:05 test postgres[4339]: [8-2] CONTEXT: PL/pgSQL function "count_func" line 4 at SQL statement
database: postgresql_error: ERROR: column "hour" does not exist
CONTEXT: PL/pgSQL function "count_func" line 4 at SQL statement
Sep 15 11:20:05 test postgres[4339]: [9-1] ERROR: column "hour" does not exist
Sep 15 11:20:05 test postgres[4339]: [9-2] CONTEXT: PL/pgSQL function "count_func" line 4 at SQL statement
이렇게 나오고 있습니다
plpgsql은 처음 사용하였고 new와 old개념또한 조금 애매모호한 상태입니다.
책은 열심히 뒤지고 있으나 뽀족한 방법도 없고
어떻게 해결에 실마리라도 제공해 주실 수 없는지요..... 부탁드리겠습니다.
|