Message-ID: <1321.219.65.233.94.1049911417.squirrel@mail.trade-india.com>
Date: Wed, 9 Apr 2003 23:33:37 +0530 (IST)
Subject: Re: Getting NEW and OLD in ordinary functions.
From: <mallah@trade-india.com>
To: <josh@agliodbs.com>
In-Reply-To: <200304090945.45334.josh@agliodbs.com>
References: <200304091551.02277.mallah@trade-india.com>
	<200304090945.45334.josh@agliodbs.com>
Importance: Normal
Cc: <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

> 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 paramet=
ers.
> 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/