DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=syoLq0OZ5YOIWh26at+eWmjyTOPZJ5oacXlVBRva/s92bZht+1Zxz5eMhBIeGgeZnZ
	Q/5n0dQ+xJvu5bsFHSYCOkMNoUMspeQ30VCmv+ZK+S4JQCIbz/H9VDfCBCF9M5YiDh9O
	UnTv7b/iiZ7I4ercQHr/RTh7f9F+JVYe+fAs0=
MIME-Version: 1.0
In-Reply-To: <25995788.post@talk.nabble.com>
References: <25995788.post@talk.nabble.com>
Date: Wed, 21 Oct 2009 19:27:21 +0200
Message-ID: <162867790910211027k3daa588bwbd31d4ce8de23eb8@mail.gmail.com>
Subject: Re: @@Error equivalent in Postgresql
From: Pavel Stehule <pavel.stehule@gmail.com>
To: maboyz <thabani.moyo@distributel.ca>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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 synta=
x
> so they work in postgresql. My ? is is there an equivalent for the @@Erro=
r
> function in T-SQL for postgres: The stored proc i am converting is:
>
> ALTER PROCEDURE [dbo].[AuditAccounts]
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0@ReturnValue =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0int output
> AS
>
> SET NOCOUNT ON
>
> select * from
> =C2=A0 =C2=A0 =C2=A0 =C2=A0AdminAccts full join AmAccts
> =C2=A0 =C2=A0 =C2=A0 =C2=A0on adm_acc_AccountNo =3D am_acc_AccountNo
> =C2=A0 =C2=A0 =C2=A0 =C2=A0where
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0adm_acc_AccountNo =
is null
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0or am_acc_AccountN=
o is null
>
> Set @ReturnValue =3D @@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( )
> =C2=A0 RETURNS SETOF AuditAccount AS
> =C2=A0 $BODY$
> =C2=A0 =C2=A0 =C2=A0BEGIN
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 RETURN QUERY
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0select * from "AdminAccounts"
> =C2=A0 =C2=A0full join "AmAccounts"
> =C2=A0 =C2=A0on "adm_acc_AccountNo" =3D "am_acc_AccountNo"
> =C2=A0 =C2=A0where "adm_acc_AccountNo" is null or "am_acc_AccountNo" is n=
ull;
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 END;
> =C2=A0 $BODY$
> =C2=A0 LANGUAGE 'plpgsql' VOLATILE
> =C2=A0 COST 100
> =C2=A0 ROWS 10;
>
> How do i implement exception handling in this case, if i want the functio=
n
> 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#P=
LPGSQL-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" =3D "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-equivalen=
t-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
>