| From: | Jakub Ouhrabka <jouh8664(at)ss1000(dot)ms(dot)mff(dot)cuni(dot)cz> | 
|---|---|
| To: | <pgsql-general(at)postgresql(dot)org> | 
| Subject: | triggers and plpgsql | 
| Date: | 2001-08-03 08:35:11 | 
| Message-ID: | Pine.LNX.4.33.0108031012120.22404-100000@u-pl0 | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | 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)
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Fran Fabrizio | 2001-08-03 14:22:19 | Re: encrypting a password field in the database | 
| Previous Message | John Clark Naldoza y Lopez | 2001-08-03 00:40:52 | Re: Pgadmin |