triggers and plpgsql
От | Jakub Ouhrabka |
---|---|
Тема | triggers and plpgsql |
Дата | |
Msg-id | Pine.LNX.4.33.0108031012120.22404-100000@u-pl0 обсуждение исходный текст |
Ответы |
Re: triggers and plpgsql
Re: triggers and plpgsql |
Список | pgsql-general |
hi, i'm getting strange results when executing the code below. i would expect that li_count in function foo and the select after calling this function should return same values. can anyone explain me why i'm getting these results, please? thanks kuba example (using 7.1.2): CREATE TABLE TC01 ( TC01PK___ SERIAL PRIMARY KEY, TC01CNT1_ INTEGER, TC01CNT2_ INTEGER ); CREATE TABLE TC02 ( TC02PK___ SERIAL PRIMARY KEY, TC01PK___ INTEGER, FOREIGN KEY (TC01PK___) REFERENCES TC01(TC01PK___) ); CREATE FUNCTION on_delete_tc02() RETURNS OPAQUE AS ' DECLARE BEGIN UPDATE TC01 SET TC01CNT2_ = TC01CNT2_ + 1 WHERE TC01PK___ = OLD.TC01PK___; RETURN NULL; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER on_delete_tc02 AFTER DELETE ON TC02 FOR EACH ROW EXECUTE PROCEDURE on_delete_tc02(); INSERT INTO TC01 ( TC01PK___ ,TC01CNT1_ ,TC01CNT2_ ) VALUES ( 1 ,2 ,0 ); INSERT INTO TC02 ( TC01PK___ ) VALUES ( 1 ); INSERT INTO TC02 ( TC01PK___ ) VALUES ( 1 ); CREATE FUNCTION foo() RETURNS INTEGER AS ' DECLARE li_count INTEGER; BEGIN DELETE FROM TC02; SELECT INTO li_count COUNT(*) FROM TC01 WHERE TC01CNT1_ = TC01CNT2_; RAISE NOTICE ''li_count = %'', li_count; RETURN 0; END; ' LANGUAGE 'plpgsql'; tom=# begin; BEGIN tom=# select foo(); NOTICE: li_count = 0 foo ----- 0 (1 row) tom=# SELECT COUNT(*) FROM TC01 WHERE TC01CNT1_ = TC01CNT2_; count ------- 1 (1 row)
В списке pgsql-general по дате отправления: