Re: Postgres trigger issue with update statement in it. - Mailing list pgsql-sql
| From | Wolfe Whalen |
|---|---|
| Subject | Re: Postgres trigger issue with update statement in it. |
| Date | |
| Msg-id | 1365060506.15519.140661213164229.7F345F5D@webmail.messagingengine.com Whole thread Raw |
| In response to | Postgres trigger issue with update statement in it. (Kaleeswaran Velu <v_kalees@yahoo.com>) |
| Responses |
Re: Postgres trigger issue with update statement in it.
|
| List | pgsql-sql |
Hi Kaleeswaran,
We're glad to have you on the mailing list. I don't know enough about your trigger function to know exactly where it's going wrong, but I threw together a quick example that has an insert trigger on a child table that updates a row on the parent table. I'm hoping this might help. If it doesn't help, maybe you could give us a little more information about your function or tables. I'd be happy to help in any way that I can.
CREATE TABLE survey_records (
name varchar(100),
obsoleted timestamp DEFAULT NULL
);
CREATE TABLE geo_surveys (
measurement integer
) INHERITS (survey_records);
CREATE OR REPLACE FUNCTION obsolete_old_surveys() RETURNS trigger AS $$
BEGIN
UPDATE survey_records SET obsoleted = clock_timestamp()
WHERE survey_records.name = NEW.name AND survey_records.obsoleted IS NULL;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER obsolete_old_surveys_tr
BEFORE INSERT ON geo_surveys
FOR EACH ROW EXECUTE PROCEDURE obsolete_old_surveys();
INSERT INTO geo_surveys (name, measurement) VALUES ('Carbon Dioxide', 5);
INSERT INTO geo_surveys (name, measurement) VALUES ('Carbon Dioxide', 10);
INSERT INTO geo_surveys (name, measurement) VALUES ('Carbon Dioxide', 93);
You'd wind up with something like this:
SELECT * FROM survey_records;
name | obsoleted
----------------+----------------------------
Carbon Dioxide | 2013-04-03 23:59:44.228225
Carbon Dioxide | 2013-04-03 23:59:53.66243
Carbon Dioxide |
(3 rows)
SELECT * FROM geo_surveys;
name | obsoleted | measurement
----------------+----------------------------+-------------
Carbon Dioxide | 2013-04-03 23:59:44.228225 | 5
Carbon Dioxide | 2013-04-03 23:59:53.66243 | 10
Carbon Dioxide | | 93
(3 rows)
The parent survey_records is actually updating the child table rows when you do an update. Parent tables can almost seem like a view in that respect. You would have to be a bit careful if you're going to have an update trigger on a child that updated the parent table. It's easy to wind up with a loop like this:
Child: Update row 1 -> Trigger function -> Update Row 1 on parent
->Parent: Let's see... Row 1 is contained in this child table, so let's update it there.
->Child: Update row 1 -> Trigger function -> Update Row 1 on parent
->Parent: Let's see... Row 1 is contained in this child table, so let's update it there.
... etc etc.
Best Regards,
Wolfe
--
Wolfe Whalen
wolfe@quios.net
On Wed, Apr 3, 2013, at 09:08 PM, Kaleeswaran Velu wrote:
Hello Friends,I am new to Postgres DB. Recently installed Postgres 9.2.Facing an issue with very simple trigger, tried to resolve myself by reading documents or google search but no luck.I have a table A(parent) and table B (child). There is a BEFORE INSERT OR UPDATE trigger attached in table B. This trigger has a update statement in it. This update statement should update a respective record in table A when ever there is any insert/update happen in table B. The issue here is where ever I insert/update record in table B, getting an error as below :********** Error **********ERROR: cannot begin/end transactions in PL/pgSQLSQL state: 0A000Hint: Use a BEGIN block with an EXCEPTION clause instead.Context: PL/pgSQL function func_update_payment() line 53 at SQL statementLine no 53 in the above error message is an update statement. If I comment out the update statement, trigger works fine.Can anyone shed some lights on this? Your help is appreciated.Thanks and RegardsKaleeswaran Velu