From: maboyz <thabani.moyo@distributel.ca>
To: pgsql-sql@postgresql.org
Subject: Re: @@Error equivalent in Postgresql
Date: Wed, 21 Oct 2009 12:04:32 -0700 (PDT)
Message-ID: <25998338.post@talk.nabble.com> (raw)
In-Reply-To: <162867790910211027k3daa588bwbd31d4ce8de23eb8@mail.gmail.com>
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 = @@Error. Thanks
Pavel Stehule wrote:
>
> 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
>>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
>
--
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.
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: thabani.moyo@distributel.ca
Subject: Re: @@Error equivalent in Postgresql
In-Reply-To: <25998338.post@talk.nabble.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