From: Rajesh Kumar Mallah <mallah@trade-india.com>
Organization: Infocom Network Limited
To: pgsql-sql@postgresql.org
Subject: Re: Getting NEW and OLD in ordinary functions.
Date: Wed, 9 Apr 2003 16:42:34 +0530
User-Agent: KMail/1.5
References: <200304091551.02277.mallah@trade-india.com>
In-Reply-To: <200304091551.02277.mallah@trade-india.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200304091642.34776.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.