Re: Getting NEW and OLD in ordinary functions.

> 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/