From: Pavel Stehule <pavel.stehule@gmail.com>
To: maboyz <thabani.moyo@distributel.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: @@Error equivalent in Postgresql
Date: Wed, 21 Oct 2009 19:27:21 +0200
Message-ID: <162867790910211027k3daa588bwbd31d4ce8de23eb8@mail.gmail.com> (raw)
In-Reply-To: <25995788.post@talk.nabble.com>
References: <25995788.post@talk.nabble.com>
2009/10/21 maboyz <thabani.moyo@distributel.ca>:
>
> 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?
> --
Hello
PostgreSQL has different model of error processing than MSSQL. When
any exception is raised, then simply is raised and not silently
ignored like in T-SQL. You can catch exception. See
http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
Then you can use SQLSTATE and SQLERRM variables.
p.s. For similar function like your function use sql language. It
could be more effective:
CREATE FUNCTION dint_AuditAccounts(OUT adm_acc_AccountNo character varying,
OUT
am_acc_AccountNo character varying)
RETURNS SETOF record AS
$BODY$
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;
$BODY$
LANGUAGE sql;
You don't need set flags because planner see inside sql functions.
Regards
Pavel Stehule
> 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.
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
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: pavel.stehule@gmail.com, thabani.moyo@distributel.ca
Subject: Re: @@Error equivalent in Postgresql
In-Reply-To: <162867790910211027k3daa588bwbd31d4ce8de23eb8@mail.gmail.com>
* 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