Message-ID: <4AE03EED.105@rp-online.de>
Date: Thu, 22 Oct 2009 13:15:57 +0200
From: Thomas Pundt <mlists@rp-online.de>
Reply-To: thomas.pundt@rp-online.de
Organization: RP Online GmbH
User-Agent: Thunderbird 2.0.0.23 (Macintosh/20090812)
MIME-Version: 1.0
To: maboyz <thabani.moyo@distributel.ca>
CC: pgsql-sql@postgresql.org
Subject: Re: @@Error equivalent in Postgresql
References: <25995788.post@talk.nabble.com>
In-Reply-To: <25995788.post@talk.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi

maboyz schrieb:
> I am in the process of migrating our database from MS Server 2000 to
> Postgres. I have a bunch of stored procs which i have to modify the syntax
> so they work in postgresql. My ? is is there an equivalent for the @@Error
> function in T-SQL for postgres: The stored proc i am converting is:
> 
> ALTER PROCEDURE [dbo].[AuditAccounts]
> 
> 	@ReturnValue		int output
> AS
> 
> SET NOCOUNT ON
> 
> select * from 
> 	AdminAccts full join AmAccts
> 	on adm_acc_AccountNo = am_acc_AccountNo
> 	where 
> 		adm_acc_AccountNo is null 
> 		or am_acc_AccountNo is null
> 
> Set @ReturnValue = @@Error
> 
> I have wriiten the postgres function as follows :
> 
> CREATE TYPE AuditAccount AS (adm_acc_AccountNo character varying,
> am_acc_AccountNo character varying);
> CREATE FUNCTION dint_AuditAccounts( )
>    RETURNS SETOF AuditAccount AS
>    $BODY$
>       BEGIN
>          RETURN QUERY
>             select * from "AdminAccounts" 
>     full join "AmAccounts" 
>     on "adm_acc_AccountNo" = "am_acc_AccountNo"
>     where "adm_acc_AccountNo" is null or "am_acc_AccountNo" is null;
>     
>          END;
>    $BODY$
>    LANGUAGE 'plpgsql' VOLATILE
>    COST 100
>    ROWS 10;
> 
> How do i implement exception handling in this case, if i want the function
> to report back successful execution or failure just like the @@Error
> function does in T-SQL?

I have no clue about T-SQL, but I think you can easily extend your
function(s) to use PL/pgSQL exception handling described here:

http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

     BEGIN
         statements
     EXCEPTION
         WHEN condition [ OR condition ... ] THEN
             handler_statements
         [ WHEN condition [ OR condition ... ] THEN
               handler_statements
           ... ]
     END;


Ciao,
Thomas