Re: Getting NEW and OLD in ordinary functions.

> 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/