From: mallah@trade-india.com
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Getting NEW and OLD in ordinary functions.
Date: Wed, 9 Apr 2003 23:33:37 +0530 (IST)
Message-ID: <1321.219.65.233.94.1049911417.squirrel@mail.trade-india.com> (raw)
In-Reply-To: <200304090945.45334.josh@agliodbs.com>
References: <200304091551.02277.mallah@trade-india.com>
<200304090945.45334.josh@agliodbs.com>
> 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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mallah@trade-india.com, josh@agliodbs.com
Subject: Re: Getting NEW and OLD in ordinary functions.
In-Reply-To: <1321.219.65.233.94.1049911417.squirrel@mail.trade-india.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox