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
