X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 8E1899DCC07 for ; Thu, 16 Mar 2006 15:32:21 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 88207-03 for ; Thu, 16 Mar 2006 15:32:21 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from perseverance.encs.concordia.ca (perseverance-96.encs.concordia.ca [132.205.96.94]) by postgresql.org (Postfix) with ESMTP id 6F8D49DCC64 for ; Thu, 16 Mar 2006 15:32:19 -0400 (AST) Received: from [132.205.47.205] (IDENT:emilu@curacao.encs.concordia.ca [132.205.47.205]) by perseverance.encs.concordia.ca (8.12.11/8.12.11) with ESMTP id k2GJWBUf024495; Thu, 16 Mar 2006 14:32:12 -0500 Message-ID: <4419BD3A.1050903@encs.concordia.ca> Date: Thu, 16 Mar 2006 14:32:10 -0500 From: Emi Lu Reply-To: emilu@encs.concordia.ca User-Agent: Mozilla Thunderbird 1.0.7 (X11/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Pedro B." CC: pgsql-sql@postgresql.org Subject: Re: About how to use "exception when ??? then " References: <4419AB1A.3070805@encs.concordia.ca> <200603161827.19462.pedro.borracha@netcabo.pt> In-Reply-To: <200603161827.19462.pedro.borracha@netcabo.pt> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Scanned-By: MIMEDefang 2.43 on perseverance.encs.concordia.ca at 2006/03/16 14:32:12 EST X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.137 required=5 tests=[AWL=0.137] X-Spam-Score: 0.137 X-Spam-Level: X-Archive-Number: 200603/240 X-Sequence-Number: 24586 Hi Pedro, >|> The place where I have ???, what I should put there please? >|> >|> e.g., >|> 1. WHEN sqlcode = '02000' THEN >|> 2. WHEN no_data then >|> 3. other ways? >|> >|> From the 8.0 docs, I am not be able to find Constant values of all >|> error codes. >|> http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html >|> >|> Please enlighten me. > >Maybe this link will help you better, as it has the Constants: > >http://developer.postgresql.org/docs/postgres/errcodes-appendix.html > >Then, some logic real life examples would be something like: > >(snip) >EXCEPTION > WHEN NOT_NULL_VIOLATION THEN > RAISE WARNING 'Not null...'; > WHEN OTHERS THEN > RAISE NOTICE 'Hmmmm.... [%,%]', SQLSTATE, SQLERRM; > >or > > > I am using postgresql 8.0.1. The keyword "SQLSTATE" & "SQLERRM" did not work for me. But, I think I do need the two outputs "sql error code", and "sql error code statement". Errors I got are: syntax error at or near "SQLSTATE" at character 2613 LINE 58: RAISE NOTICE 'Hmmmm.... [%,%]', SQLSTATE, SQL... Should I install any patches or do anything elese to have SQLSTATE and SQLERRM work for me? Ying