Hi
maboyz schrieb:
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?
I have no clue about T-SQL, but I think you can easily extend your
function(s) to use PL/pgSQL exception handling described here:
http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
BEGIN
statements
EXCEPTION
WHEN condition [ OR condition ... ] THEN
handler_statements
[ WHEN condition [ OR condition ... ] THEN
handler_statements
... ]
END;
Ciao,
Thomas
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
