CODEWITHSUNDEEP
sqlpostgresqlfunctionexceptionerror-handling
Cjhosu
Cjhosu

Reputation: 1

syntax to return value for error while preforming postgresql function

I am brand new to Postgresql and am trying to write a function that calls another function, and then returns -1 if that function had any errors. I am coming from a T-SQL background and essentially want the equivalent of 

CREATE PROCEDURE [dbo].[p_newproc] 
AS 
BEGIN
EXEC p_seed_table 
IF @@ERROR <> 0   
RETURN -1
... 
END

So far I have found that I am likely going to use something like the following:

CREATE OR REPLACE FUNCTION public.fn_newfunc() 
RETURNS void 
AS 
$$ 
BEGIN  
PERFORM fn_seed_table();  
EXCEPTION 
WHEN 
SQLSTATE <> '00000' 
RAISE EXCEPTION '-1'; 
End; 
$$ 
LANGUAGE 'plpgsql';

Any advice would be appreciated. Thanks.

Upvotes: 0

Views: 1235

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

This is too long for a comment.

Both SQL Server and Postgres support exception handling. You chose not to use exception handling in the SQL Server code. You should make a conscious and informed decision about using exceptions (in either database).

SQL Server stored procedures return integers by default, and these can be used for status. Postgres doesn't have stored procedures, "only" stored functions, so you need to be a bit more explicit about the return values. The closest equivalent in Postgres to a stored procedure is a function that returns an integer.

There are pros and cons to switching over to exceptions rather than handling exceptions locally using return values (and be extra careful if you have transactions that span code blocks). I think exception handling is more robust in Postgres: SQL Server misses some types of exceptions.

However, you can implement either method in either scripting language. If you switch to using exceptions, think about the issues before doing so. That is, don't just reflexively make the switch. If you do switch to exception handling, also change the SQL Server code -- assuming you are maintaining both code bases.

Upvotes: 1

Related Questions