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/