Re: @@Error equivalent in Postgresql

2009/10/21 maboyz <thabani(dot)moyo(at)distributel(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>