Re: Postgres trigger issue with update statement in it.

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(at)quios(dot)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/pgSQL
SQL state: 0A000
Hint: Use a BEGIN block with an EXCEPTION clause instead.
Context: PL/pgSQL function func_update_payment() line 53 at SQL
statement

Line 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 Regards
Kaleeswaran Velu