From: Thomas Pundt <mlists@rp-online.de>
To: maboyz <thabani.moyo@distributel.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: @@Error equivalent in Postgresql
Date: Thu, 22 Oct 2009 13:15:57 +0200
Message-ID: <4AE03EED.105@rp-online.de> (raw)
In-Reply-To: <25995788.post@talk.nabble.com>
References: <25995788.post@talk.nabble.com>
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-TRAPPIN...
BEGIN
statements
EXCEPTION
WHEN condition [ OR condition ... ] THEN
handler_statements
[ WHEN condition [ OR condition ... ] THEN
handler_statements
... ]
END;
Ciao,
Thomas
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mlists@rp-online.de, thabani.moyo@distributel.ca
Subject: Re: @@Error equivalent in Postgresql
In-Reply-To: <4AE03EED.105@rp-online.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox