X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 733E3474E44
	for <pgsql-sql@postgresql.org>; Wed,  9 Apr 2003 14:23:27 -0400 (EDT)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id 81ADE474E42
	for <pgsql-sql@postgresql.org>; Wed,  9 Apr 2003 14:23:26 -0400 (EDT)
Received: from [66.219.92.2] (HELO temoku)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2953297; Wed, 09 Apr 2003 11:23:20 -0700
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: <mallah@trade-india.com>
Subject: Re: Getting NEW and OLD in ordinary functions.
Date: Wed, 9 Apr 2003 11:23:16 -0700
User-Agent: KMail/1.4.3
Cc: <pgsql-sql@postgresql.org>
References: <200304091551.02277.mallah@trade-india.com>
	<200304090945.45334.josh@agliodbs.com>
	<1321.219.65.233.94.1049911417.squirrel@mail.trade-india.com>
In-Reply-To: <1321.219.65.233.94.1049911417.squirrel@mail.trade-india.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200304091123.16123.josh@agliodbs.com>
X-Spam-Status: No, hits=-31.6 required=5.0
	tests=BAYES_10,IN_REP_TO,QUOTED_EMAIL_TEXT,REFERENCES,
	REPLY_WITH_QUOTES,USER_AGENT_KMAIL autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200304/145
X-Sequence-Number: 12846

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=20
help with particular errors.  And your general approach is fine.

Two suggestions for improvement:

1)=20

> 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 eve=
r=20
get my website up ...

        IF listing_status NOT IN (''ACTIVE'', ''DELETED'',=20
''EXPIRED_BY_SYSTEM'') THEN
                RAISE EXCEPTION '' listing_status has to be ACTIVE,DELETED =
or=20
EXPIRED_BY_SYSTEM for profile_id=3D%'' , profile_id;
        END IF ;

        IF hosting_status NOT IN (''TEMPORARY'', ''COMPLETE'') THEN
                RAISE EXCEPTION '' hosting_status has to be TEMPORARY or=20
COMPLETE for profile_id=3D%'' , profile_id;
        END IF ;

These two constraints might be better handled by Foriegn Keys to an=20
appropriate reference list (personally, I don't use check constraints at al=
l=20
... I find FKs vastly easier to manage.)

Second, it would be more user-friendly if you concatinated all error messag=
es=20
and delivered a list of *all* problems to the user in the exception message=
,=20
rather than one at a time.   Imagine a user who fixed his listing status,=
=20
only to get an error message about hosting status, and then to get an error=
=20
message about online sales.


--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco