Thread: Getting NEW and OLD in ordinary functions.
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 toreload the data OR run a dummy update on tableso 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.
Below is what i have actually implemented
It would hopefully make my previous
question clearer.
Btw can a PL/PGSQL guru pleeese examine the function
comment on its efficiency or programming style?
I have relatively less experience in pl/pgsql and trying to shift
business logic from the perl to database layer.
particulary is it ok to never return false from
a function declared as returning boolean ?
i am returning true if everything is ok else
i am raising EXCEPTION instead of returning
false.
Regds
Mallah.
CREATE TABLE general.eyp_listing
( profile_id integer not null , sno integer , branch_id integer , edition integer , size
varchar(20) not null, amount integer not null, category_id integer not null, show_ad boolean default
truenot null , listing_status varchar(25) default 'ACTIVE' not null, hosting_status varchar(25) default
'COMPLETE'not null, keywords text , user_keywords text, hosting_date integer not null,
expires_on integer CHECK ( general.check_eyp_listing (profile_id,sno,branch_id,edition,size,amount,show_ad,lis
ting_status,hosting_status) is true),FOREIGN KEY (profile_id) REFERENCES general.profile_master (profile_id) ,
FOREIGNKEY (branch_id) REFERENCES general.branch_master (branch_id) , FOREIGN KEY (size) REFERENCES
general.eyp_size_master(size) , FOREIGN KEY (hosting_date) REFERENCES general.date_dimension (date_id) ,
FOREIGNKEY (expires_on) REFERENCES general.date_dimension (date_id)
);
/*Args:profile_id(int) ,sno(int) , branch_id(int) , edition(int) ,size(varchar) , amount(int) , show_ad (boolean),
listing_status(varchar),hosting_status(varchar)*/
CREATE OR REPLACE FUNCTION general.check_eyp_listing(
integer, integer,integer,integer, varchar, integer,boolean,varchar, varchar
) RETURNS boolean AS '
DECLARE profile_id alias for $1; sno alias for $2; branch_id alias for $3; edition alias for
$4; size alias for $5; amount alias for $6; show_ad alias for $7; listing_status alias for $8;
hosting_status alias for $9;
BEGIN IF listing_status NOT IN (''ACTIVE'', ''DELETED'', ''EXPIRED_BY_SYSTEM'') THEN RAISE
EXCEPTION'' listing_status has to be ACTIVE,DELETED or EXPIRED_BY_SYSTEM for profile_id=%'' , profile_id; END IF
;
IF hosting_status NOT IN (''TEMPORARY'', ''COMPLETE'') THEN RAISE EXCEPTION '' hosting_status has
tobe TEMPORARY or COMPLETE for profile_id=%'' , profile_id; END IF ;
IF size ilike ''%ONLINE%'' THEN -- online case IF branch_id <> 35 THEN
RAISE EXCEPTION '' branch has to be ONLINESALES for size=% profile_id=%'' , size , profile_id; END IF;
ELSE -- print(offline) ad case IF NOT (sno > 0 AND edition > 0 AND branch_id > 0) THEN
RAISE EXCEPTION '' sno , edition , branch_id has to be > 0 for size=% , profile_id=%'' , size , profile_id;
END IF;
END IF; RETURN true;
END;
' LANGUAGE 'plpgsql';
On Wednesday 09 Apr 2003 3:51 pm, Rajesh Kumar Mallah wrote:
> 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.
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. 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. It also allows me to implement a custom locking scheme and return custom error messages. It works very well. -- Josh Berkus Aglio Database Solutions San Francisco
> 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/
Rajesh,
> could u pleeeeeez check my 50 LOC when u have some free time and comment
> on my questions I will be really grateful.
SInce you asked so nicely ...
I don't see anything wrong with your code, though I will of course be happy to
help with particular errors. And your general approach is fine.
Two suggestions for improvement:
1)
> 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)
This will be the subject for an "Adventures in PostgreSQL" article if I ever
get my website up ...
IF listing_status NOT IN (''ACTIVE'', ''DELETED'',
''EXPIRED_BY_SYSTEM'') THEN RAISE EXCEPTION '' listing_status has to be ACTIVE,DELETED or
EXPIRED_BY_SYSTEM for profile_id=%'' , profile_id; END IF ;
IF hosting_status NOT IN (''TEMPORARY'', ''COMPLETE'') THEN RAISE EXCEPTION '' hosting_status has
tobe TEMPORARY or
COMPLETE for profile_id=%'' , profile_id; END IF ;
These two constraints might be better handled by Foriegn Keys to an
appropriate reference list (personally, I don't use check constraints at all
... I find FKs vastly easier to manage.)
Second, it would be more user-friendly if you concatinated all error messages
and delivered a list of *all* problems to the user in the exception message,
rather than one at a time. Imagine a user who fixed his listing status,
only to get an error message about hosting status, and then to get an error
message about online sales.
--
-Josh BerkusAglio Database SolutionsSan Francisco
> Rajesh,
>
>> could u pleeeeeez check my 50 LOC when u have some free time and comment on my questions I
>> will be really grateful.
>
> SInce you asked so nicely ...
Glad to hear again!
>
> I don't see anything wrong with your code, though I will of course be happy to help with
> particular errors. And your general approach is fine.
>
> Two suggestions for improvement:
>
> 1)
>
>> 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)
>
> This will be the subject for an "Adventures in PostgreSQL" article if I ever get my website up
> ...
>
> IF listing_status NOT IN (''ACTIVE'', ''DELETED'',
> ''EXPIRED_BY_SYSTEM'') THEN
> RAISE EXCEPTION '' listing_status has to be ACTIVE,DELETED or
> EXPIRED_BY_SYSTEM for profile_id=%'' , profile_id;
> END IF ;
>
> IF hosting_status NOT IN (''TEMPORARY'', ''COMPLETE'') THEN
> RAISE EXCEPTION '' hosting_status has to be TEMPORARY or
> COMPLETE for profile_id=%'' , profile_id;
> END IF ;
>
> These two constraints might be better handled by Foriegn Keys to an appropriate reference list
> (personally, I don't use check constraints at all ... I find FKs vastly easier to manage.)
This was for simulating CHECK (column in ('A','B' , 'C')) ;
I already have 5 FKEYS on that table dont' u feel fkeys for this
purpose is an overkill?
eg here the valid values can range from say 3 to 10 (small number)
whereas FKEYS are capable to check in a very large set residing in
another table.
also fkeys makes updates and inserts slower (i feel)
another question i have seen CHECK traslating IN to a set of ORs
does the same happen in pl/pgsql internally? and does it really matter
for here for small target sets?
>
> Second, it would be more user-friendly if you concatinated all error messages and delivered a
> list of *all* problems to the user in the exception message, rather than one at a time.
Thanks for this ,
> Imagine a user who fixed his listing status, only to get an error message about hosting
> status, and then to get an error message about online sales.
true this is particularly valid in www environments.
Regds
MAllah.
>
>
> --
> -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/
Rajesh,
> This was for simulating CHECK (column in ('A','B' , 'C')) ;
>
> I already have 5 FKEYS on that table dont' u feel fkeys for this
> purpose is an overkill?
> eg here the valid values can range from say 3 to 10 (small number)
> whereas FKEYS are capable to check in a very large set residing in
> another table.
> also fkeys makes updates and inserts slower (i feel)
The problem with CHECK constraints is:
1) If you forget what the range of valid values for the column is, you have to
dump the constraint definition to find out;
2) If you want to add a new valid value, you have to:a. Freeze all data entry on the table b. delete the
constraint c. replace the constraint d. test that the new constraint does not bug out e. restart
databaseaccess
adding a new value to a reference list is *much* easier.
> another question i have seen CHECK traslating IN to a set of ORs
> does the same happen in pl/pgsql internally? and does it really matter
> for here for small target sets?
No idea, and probably not.
--
-Josh BerkusAglio Database SolutionsSan Francisco