Re: Getting NEW and OLD in ordinary functions. - Mailing list pgsql-sql

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.