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

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.)

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 Berkus
Aglio Database Solutions
San Francisco