From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: pgsql-sql@postgresql.org
Subject: Getting NEW and OLD in ordinary functions.
Date: Wed, 9 Apr 2003 15:51:02 +0530
Message-ID: <200304091551.02277.mallah@trade-india.com> (raw)
Hi,
is it possible to access NEW , OLD rows in an ordinary function
(function which are not TRIGGER PROCEDURES)
motivation:
We need to ensure data integrity of every row in a table.
1. one way is to one or many CHECK constraint on columns (or domains in 7.4).
2. To ADD a trigger procedure that checks for updates and new inserts.
Problem(i feel) with 1 is that as my logic keeps getting more and more
complicated it may not be possible to put everything in a CHECK / set of CHECK
contraints. the logic may even require querying other tables which is anyway
not possible in a CHECK () at the moment.
Problem with 2 is whenever the logic changes we need to check that all the
rows of the existing table satisfies it that logic. for this we either have to
reload the data OR run a dummy update on table so that procedure is called
for all the existing rows.
3. since ADDing of a CHECK constriaint checks all the rows of a table.
It looks like a better approach to me. whenever the logic changes
we just DROP the CHECK constraint and Add it again so that all the rows are
rechecked.
the problem i am facing with 3 is that i dont seem to find an easy way
of accessing the current record .
two possible workarounds are
1) pass the primary key to the function and query the table again
for the record.
2) pass all the columns as arguments to the function that has to check
the consistency.
in first case a primary key may not always exists for a relation .
in sencond case too many arguments requried to be passed as ur
logic grows.
so i was wondering abt automagically getting NEW/OLD or a current record
in a function that can be used in check constraint .
Regds
Mallah.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
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
Subject: Re: Getting NEW and OLD in ordinary functions.
In-Reply-To: <200304091551.02277.mallah@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