From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: pgsql-sql@postgresql.org
Subject: Re: Getting NEW and OLD in ordinary functions.
Date: Wed, 9 Apr 2003 16:42:34 +0530
Message-ID: <200304091642.34776.mallah@trade-india.com> (raw)
In-Reply-To: <200304091551.02277.mallah@trade-india.com>
References: <200304091551.02277.mallah@trade-india.com>
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 true not 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) ,
FOREIGN KEY (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) ,
FOREIGN KEY (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 to be 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.
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: <200304091642.34776.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