Thread: triggers and plpgsql

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)

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.

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