Message-ID: <25998338.post@talk.nabble.com>
Date: Wed, 21 Oct 2009 12:04:32 -0700 (PDT)
From: maboyz <thabani.moyo@distributel.ca>
To: pgsql-sql@postgresql.org
Subject: Re: @@Error equivalent in Postgresql
In-Reply-To: <162867790910211027k3daa588bwbd31d4ce8de23eb8@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
References: <25995788.post@talk.nabble.com>
	<162867790910211027k3daa588bwbd31d4ce8de23eb8@mail.gmail.com>


Thanks for the response Pavel. So does this mean i will have to make the
@ReturnValue an OUT parameter too??? am a bit confused here, i guess what i
am driving at is, i see where you are going with the altered function you
suggeted but its fitting the exception handling into the grand scheme of
things so i can be able to manipulate it in the code just like you wd use
the @returnValue =3D @@Error. Thanks

Pavel Stehule wrote:
>=20
> 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]
>>
>> =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_Account=
No 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 =
null;
>>
>> =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
>> function
>> to report back successful execution or failure just like the @@Error
>> function does in T-SQL?
>> --
>=20
> Hello
>=20
> 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
>=20
> http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html=
#PLPGSQL-ERROR-TRAPPING
>=20
> Then you can use SQLSTATE and SQLERRM variables.
>=20
> p.s. For similar function like your function use sql language. It
> could be more effective:
>=20
> 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;
>=20
> You don't need set flags because planner see inside sql functions.
>=20
> Regards
> Pavel Stehule
>=20
>> View this message in context:
>> http://www.nabble.com/%40%40Error-equivalent-in-Postgresql-tp25995788p25=
995788.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
>>
>=20
> --=20
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>=20
>=20

--=20
View this message in context: http://www.nabble.com/%40%40Error-equivalent-=
in-Postgresql-tp25995788p25998338.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.