syntax to return value for error while preforming postgresql function

Question

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.

Gordon Linoff · Accepted Answer · 2015-12-30 15:04:55Z

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.

Collectives™ on Stack Overflow

syntax to return value for error while preforming postgresql function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related