Re: @@Error equivalent in Postgresql - Mailing list pgsql-sql

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 <[email protected]>:
>>
>> 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 ([email protected])
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> 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.