Thread: Advice on stored proc error handling versus Sybase?

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

Ken Corey <ken@kencorey.com> writes:
> Questions:
> 1) Can I default arguments to the stored proc when the proc is defined?

We don't have default arguments for functions --- that wouldn't interact
too well with function-name overloading (which is the feature whereby
you can have multiple functions of the same name, so long as they have
different argument lists).  You could work around this by defining some
convenience functions, eg

create function myfunc(a,b,c) as 'do the full job'

create function myfunc(a,b) as 'select myfunc(a,b,default-for-c)'

create function myfunc(a) as 'select myfunc(a,default-for-b,default-for-c)'

> 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')"?

Not at the moment.  This has been suggested before, and I suppose
someone might get around to it someday...

> 3) What if the insert fails?  How can I tell?

You don't have to, because the function won't get to execute any further
if there's an error.  AFAIK there's not yet any provision for trapping
errors in plpgsql.  You might want to try the select first, and only
do the insert if the select doesn't find a match.

> 4) is there an 'isnull(value,substitute)' function predefined?

I think what you are looking for is COALESCE().  If that's not quite
right, build what you want out of spare parts using CASE.  See
http://www.postgresql.org/devel-corner/docs/postgres/functions-conditional.htm

            regards, tom lane

Thanks for the response, Tom.

Tom Lane wrote:
> We don't have default arguments for functions --- that wouldn't interact
> too well with function-name overloading (which is the feature whereby

Right.  Not what I'm used to, but I'll get over it.  *smile*.  So that
means that when calling a function using nulls, I have to cast the nulls
to an appropriate type so that plpgsql can figure out which function I
mean...messy.

> > 3) What if the insert fails?  How can I tell?
>
> You don't have to, because the function won't get to execute any further
> if there's an error.  AFAIK there's not yet any provision for trapping
> errors in plpgsql.  You might want to try the select first, and only
> do the insert if the select doesn't find a match.

Hrm...I must be able to tell *somewhere* that an error happened,
otherwise how would you ever know if something is wrong or not?  I mean,
okay, the referential validity may have been maintained, but that's
scant consolation when the data just can't be inserted and I can't see
why.

Can you tell in the sql/C/whatever that called the plpgsql function?  Do
you get a return code back indicating '*some* error happened'?

-Ken

Ken Corey <ken@kencorey.com> writes:
> Right.  Not what I'm used to, but I'll get over it.  *smile*.  So that
> means that when calling a function using nulls, I have to cast the nulls
> to an appropriate type so that plpgsql can figure out which function I
> mean...messy.

Only if there's not enough information in the other arguments to
uniquely determine which function you mean.

>>>> 3) What if the insert fails?  How can I tell?
>>
>> You don't have to, because the function won't get to execute any further
>> if there's an error.  AFAIK there's not yet any provision for trapping
>> errors in plpgsql.  You might want to try the select first, and only
>> do the insert if the select doesn't find a match.

> Hrm...I must be able to tell *somewhere* that an error happened,
> otherwise how would you ever know if something is wrong or not?

Well, your application gets back an error message, but my point was that
the code of the function itself doesn't get to trap the error.  (Yes,
this could stand to be improved.)

            regards, tom lane

How do I get the values of parameters in the query?

e.g.

  where x = 'op';


i.e. How do I get ?

field: x
value : op

any example, pointers will help.

thanks,
Sandeep Joshi