> Rajesh,
>
>> is it possible to access NEW , OLD rows in an ordinary function
>> (function which are not TRIGGER PROCEDURES)
>
> I do this all the time in a roundabout way.
> 1) I create a function that takes all the columns of the table as parameters.
> 2) Instead of
> doing an UPDATE or INSERT into the table, the client calls this function.
> 3) I check all the data fed to the function. If I need to compare it to the data on disk, I
> SELECT the "old" data into a RECORD and compare.
> 4) If everything's ok, I do an UPDATE or INSERT.
Thanks for the response Josh ,
This approach is also good. But i have a question
A table can get updated in many contexts , sometimes only few
columns and some times many columns are updated then how can a
generic function handle all the situations ?
So is a check (validation_function(arg1,arg2,.... argn) is TRUE ) approach
not more generic ?
>
> I have about 10,000 lines of PL/PgSQL doing this for various applications where the data
> integrity logic is too complex for a trigger or check constraint.
Hmm 10,000 LOC sounds impressive!!
could u pleeeeeez check my 50 LOC when u have some free time and comment
on my questions I will be really grateful.
It also allows me to
> implement a custom locking scheme and return custom error messages. It works very well.
This is also very interesting , when u time can u discuss it a bit more.
what does the locking scheme achieve?
and how does "custom error messages " really help ?
(maybe in a pvt mail if its not appropriate here)
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
-----------------------------------------
Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/