function execution problem - plpgsql - Mailing list pgsql-general

From Zoltan Bartko
Subject function execution problem - plpgsql
Date
Msg-id 20050721081806.19136.qmail@ems003.blast.sk
Whole thread Raw
Responses Re: function execution problem - plpgsql
Can't connect after restart
List pgsql-general

Hello folks

I would like to ask the following thing:

I have two functions: funcA() drops a row from table A, funcB() drops a
row from table B that references table A. funcA() calls funcB() and
issues a delete command after returning from funcB(). I get an error,
stating that it can not be done, because there is a row in B that can
not be deleted with deleting the particular row from A.

So now: Why does this happen? Is it because a function is a transaction?
why do series of update/delete operations work fine in a single
function, but fail as soon as one of these operations is performed by
function? Is there any way to avoid this behavior?

My functions return values, error codes infact, so I need the return
values, because I do not allow the users to directly insert, update or
delete records in the tables, only via functions.

I could define a trigger that fires before deleting the row from table A
and calls funcB(), raises an exception if anything happened, catch the
exception if funcA(), if the return value was not correct, but with this
approach I loose the error value, because in PgSQL 8.0 there is no
SQLERRM I could analyse.

Please enlighten me

Thanks

Zoltan

__________________________________________________
http://www.email.azet.sk - 2 000 MB na Vase e-maily!

pgsql-general by date:

Previous
From: "Andrus"
Date:
Subject: Postgres terminates on first connecton
Next
From: Marco Colombo
Date:
Subject: Re: RAMFS with Postgres