Advice on stored proc error handling versus Sybase?

From: Ken Corey <ken(at)kencorey(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Advice on stored proc error handling versus Sybase?
Date: 2001-01-08 22:56:40
Message-ID: 3A5A45A8.A01FE9CB@kencorey.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-novice

Hi All,

My first post. Wow. This is a bit long because I listed two
procs...sorry.

I am converting a C application plus data definitions plus stored procs
from Sybase to PostgreSQL. It must be said that Postgres' C interface
is *much* cleaner than Sybase, while the pgplsql interface seems a
little more cluttered...unless I'm doing something completely
wrong...that's where I need some advice...

The PLAYER table contains a field called 'PLAYER_ID'. In Sybase it's
defined to be an IDENTITY field. In Postgres, I've defined it to be a
sequence called 'player_id_sequence' with a default of
nextval('player_id_sequence').

I have a small stored proc like this:

-----------SYBASE stored proc-------------------
create procedure I_PLAYER @PLAYER_NAME varchar(255) = null,
@TEAM_SUPPORTED char(2) = null, @EMAIL varchar(255) = null
as

if @PLAYER_NAME is null
begin
print "Must supply a PLAYER_NAME"
return -100
end
if @TEAM_SUPPORTED is null
begin
print "Must supply a TEAM_SUPPORTED"
return -100
end

insert PLAYER (PLAYER_NAME, PLAYING_FOR, EMAIL) values (@PLAYER_NAME,
@TEAM_SUPPORTED, @EMAIL)

select isnull(PLAYER_ID, 0) 'PlayerID' from PLAYER where PLAYER_NAME =
@PLAYER_NAME
go
-------------------------------------------------

What I turned it into in pgplsql:

===============PostgreSQL's pgplsql==============
create FUNCTION I_PLAYER(varchar(255),varchar(255),varchar(255))
RETURNS INT4
AS '
DECLARE
player_name alias for $1;
team_supported alias for $2;
email alias for $3;
player_id as INT4;
idflag as INT4;
BEGIN

if player_name is null
then
print "Must supply a player_name";
return -100;
end if;

if team_supported is null
then
print "Must supply a team_supported";
return -100;
end if;

insert PLAYER (PLAYER_NAME, PLAYING_FOR, EMAIL)
values (player_name, team_supported, email);

-- Did this statement fail?
select into idflag PLAYER_ID where PLAYER_NAME=player_name
if NOT FOUND
then
RETURN 0;
else
RETURN idflag;
end if;

END;'
LANGUAGE 'plpgsql';
=================================================

Questions:
1) Can I default arguments to the stored proc when the proc is defined?
2) Can I explicitly name the args when the function is called, so that I
could
call this function as "select into ret I_PLAYER(@team_supported = 'My
Team')"?
3) What if the insert fails? How can I tell? The document
'c40874113.htm' mentions that Exception handling is weak, and in fact,
it cannot be determined *why* something failed. Is this still true, or
is the doc out of date?
4) is there an 'isnull(value,substitute)' function predefined? (the only
trouble with defining this for myself is that it seems I'd need to do it
once for each datatype, no? I'm *too* lazy!)
5) any 'gotcha' comments to watch out for from you experienced guys?

Pointers to the docs and 'RTFMs' or source examples are much appreciated
as long as you tell me where to look...I've gotten all the docs I could
find from www.postgres.org, printed out the PostgreSQL HOW-TO for Linux,
and bought Mr. Momjian's book 'PostgreSQL Introduction and Concepts'.

Thanks!

-Ken

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Brett W. McCoy 2001-01-08 23:03:26 Re: Dangling large objects
Previous Message Thomas T. Thai 2001-01-08 22:47:51 database names are all numbers now

Browse pgsql-novice by date

  From Date Subject
Next Message Anthony E . Greene 2001-01-08 23:12:40 Re: backup
Previous Message Robert B. Easter 2001-01-08 17:05:54 Re: Re: I think I know what I'm doing wrong, but....