triggers and plpgsql

Started by Jakub Ouhrabkaover 24 years ago3 messagesgeneral
Jump to latest
#1Jakub Ouhrabka
jouh8664@ss1000.ms.mff.cuni.cz

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)

#2Stephan Szabo
sszabo@megazone23.bigpanda.com
In reply to: Jakub Ouhrabka (#1)
Re: triggers and plpgsql

On Fri, 3 Aug 2001, Jakub Ouhrabka wrote:

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?

AFAICS the after triggers are going to be delayed until the outer
statement is completing. That would means when the select
foo() is finishing up, the after triggers run, so between the delete and
select in the function, the trigger hasn't been called yet.

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Jakub Ouhrabka (#1)
Re: triggers and plpgsql

Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz> writes:

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.

An AFTER trigger runs at the end of the transaction, so it hasn't fired
yet at the time your function foo looks at the table. Perhaps you want
a BEFORE trigger instead.

regards, tom lane