Re: Advice on stored proc error handling versus Sybase?

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