Обсуждение: @@Error equivalent in Postgresql
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 AmAcctson adm_acc_AccountNo = am_acc_AccountNowhere 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? -- 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.
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 >
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.
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 >
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
Pavel Stehule wrote: > 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. I'd like to second and emphasise this. Attempting to use return codes will cause you unnecessary grief. It might be worth it to maintain compatibility in other code between Pg and MS-SQL, but I'm not convinced - there are enough other differences that you'll probably need to maintain fairly different versions of the functions anyway. In many cases (mainly where no flow control is required) your T-SQL functions can become simple SQL functions anyway. I suspect in the long run you'll be better off handling errors by letting exceptions bubble up so that the caller can handle it or let it bubble up in turn. The exception will include an informative error code. You can then get that error code via exception information if you trap the exception in PL/PgSQL or via your application driver interface (JDBC/ODBC/etc) as the SQLSTATE if it bubbles up to a top-level statement. I've found PostgreSQL's exception-based error handling a real life-saver, especially when working with programming languages that also use exceptions. When I'm working in Java, for example, a sanity check deep in some PL/PgSQL function may raise an exception that propagates through to the top-level SQL statement, causing the JDBC driver to throw a Java SQLException that in turn propagates up to code that's in a position to do something about the problem. -- Craig Ringer