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

2009/10/21 maboyz <thabani.moyo@distributel.ca>:
>
> 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

yes, if you like to return state, then you have to mark it as OUT.

It's better if you are drop your knowledge from T-SQL and start from
zero. PL/pgSQL is modern language based on Ada language. Mainly - it
is too different than T-SQL stored procedures programming - but is
near to Oracle's programming. It is exception based. So the
programming based on returning state codes is very obsolete, and
little bit difficult. You can emulate, but any protected block creates
inner transaction and this should negative effect on speed - and it
are some lines more.

http://www.postgres.cz/index.php/PL/pgSQL_%28en%29

Pavel

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