Mutating table (urgent)

From: "Cristian Custodio" <crstian(at)terra(dot)com(dot)br>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Mutating table (urgent)
Date: 2003-02-21 18:19:43
Message-ID: 002201c2d9d5$d085db50$fb01a8c0@ttcristian
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Hi,

I'm having a big throuble with postgreSQL, called "mutating table"

This problem also happen with Oracle,
but we found a soluction creating packagers.

In first.
I would like to explain that this exemple is just a exemple.
Don't try to understand its utility.

Step to emulation the error:

1) Creating a principal table and a child table
CREATE TABLE PAI(CODPAI INTEGER PRIMARY KEY, NOMPAI VARCHAR(30));
CREATE TABLE FILHO(CODPAI INTEGER, CODFIL SERIAL, NOMFIL VARCHAR(30), PRIMARY KEY (CODPAI, CODFIL));

2) Creating constraint between principal and child table
ALTER TABLE FILHO ADD CONSTRAINT FK_PAI_FILHO FOREIGN KEY (CODPAI) REFERENCES PAI (CODPAI);

3) Creating trigger on principal table
CREATE OR REPLACE FUNCTION TR_AIPAI() RETURNS OPAQUE AS '
BEGIN
INSERT INTO FILHO (CODPAI, NOMFIL) VALUES (NEW.CODPAI, ''FILHO DO PAI''||NEW.CODPAI);
RETURN NULL;
END;
' language 'plpgsql';

CREATE TRIGGER AIPAI AFTER INSERT ON PAI FOR EACH ROW EXECUTE PROCEDURE TR_AIPAI();

If we taking a insert on principal table it will insert on child table,
until here, thats all right...

4) Create a trigger on child table that make a select on principal table
CREATE OR REPLACE FUNCTION TR_SELECT_PAI() RETURNS OPAQUE AS'
DECLARE I INTEGER;
BEGIN
SELECT COUNT(*) INTO I FROM PAI;
RETURN NULL;
END;
'language 'plpgsql';

CREATE TRIGGER BIFILHO BEFORE INSERT ON FILHO FOR EACH ROW EXECUTE PROCEDURE TR_SELECT_PAI();

In this moment when we insert a register on principal table
it don't insert the register on child table, neither send any error message.

If anybody can help me, I'll be thanks for ever.

Cristian Custodio

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Doug McNaught 2003-02-21 18:26:20 Re: transactions
Previous Message Jan Wieck 2003-02-21 18:15:50 Re: Foreign Key with Constant