Message-ID: <25995788.post@talk.nabble.com>
Date: Wed, 21 Oct 2009 09:25:48 -0700 (PDT)
From: maboyz <thabani.moyo@distributel.ca>
To: pgsql-sql@postgresql.org
Subject: @@Error equivalent in Postgresql
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


Hi,

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?
-- 
View this message in context: http://www.nabble.com/%40%40Error-equivalent-in-Postgresql-tp25995788p25995788.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.