Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Rajesh Kumar Mallah <mallah@trade-india.com>,
	pgsql-sql@postgresql.org
Subject: Re: Getting NEW and OLD in ordinary functions.
Date: Wed, 9 Apr 2003 09:45:45 -0700
User-Agent: KMail/1.4.3
References: <200304091551.02277.mallah@trade-india.com>
In-Reply-To: <200304091551.02277.mallah@trade-india.com>
MIME-Version: 1.0
Message-Id: <200304090945.45334.josh@agliodbs.com>
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 parameter=
s.
2) Instead of doing an UPDATE or INSERT into the table, the client calls th=
is=20
function.
3) I check all the data fed to the function.  If I need to compare it to th=
e=20
data on disk, I SELECT the "old" data into a RECORD and compare.
4) If everything's ok, I do an UPDATE or INSERT.

I have about 10,000 lines of PL/PgSQL doing this for various applications=
=20
where the data integrity logic is too complex for a trigger or check=20
constraint.  It also allows me to implement a custom locking scheme and=20
return custom error messages.  It works very well.

--=20
Josh Berkus
Aglio Database Solutions
San Francisco