Advice on stored proc error handling versus Sybase?
От | Ken Corey |
---|---|
Тема | Advice on stored proc error handling versus Sybase? |
Дата | |
Msg-id | 3A5A45A8.A01FE9CB@kencorey.com обсуждение исходный текст |
Ответы |
Re: Advice on stored proc error handling versus Sybase?
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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
В списке pgsql-novice по дате отправления:
Предыдущее
От: "Robert B. Easter"Дата:
Сообщение: Re: Re: I think I know what I'm doing wrong, but....