Re: SQL-Invoked Procedures for 8.1

Gavin,

> My real question, however, is do we want default values at all. Josh has
> been fairly keen on them but I haven't seen much outright support for the
> idea other than Joe and Joshua (perhaps a few others) putting the argument
> that anything which eases the burden of migration from SQL Server is
> (potentially) a good thing.

Let me advance the reason *I* want them.    I do a lot of applications with 
extensive, procedure-driven business logic.   One of the things I constantly 
run up against is when a widely used procedure needs a new parameter.   With 
functions as they stand now, I have to create a "shell" function that 
encompasses the new parameter -- which starts to get hard to track when it's 
happened 3 or 4 times.   (woe is the lot of those with spec drift)

It also allows code neatness by not forcing you to constantly look up the 
order of parameters in the function catalog.  For example, this:

sf_cases ( user     = 124223, session     = 114643343, casename    = 'VIKTOR',client        = 'KELLEY',managedby =
NULL,status   = 1,fuzzysearch = TRUE,filedafter    = NULL,format    = 'long',page        = 1,resultsper    = 15 );
 

Is easier to both read an maintain than:

sf_cases ( 124223, 114643343, 'VIKTOR', 'KELLEY', NULL, 1, TRUE, NULL, 'long', 1, 15);

Of course, this is as true of functions as it will be of procedures.   So half 
the functionality that I'm angling for to support with calling named params 
could be accomplished within the context of overloading just by extending the 
named param patch in 8.0 to cover calling functions/SPs in the format above.

Therefore: the arguments you raise about the difficulty of implementing a 
seperate catalog are strong ones, and you are probably correct in the 
tradeoff being a bad one.

And, not that I think about it, I have a way to support DEFAULT params within 
the context of overloading.  Let me muse it over and I'll get back to you.

> I think we can distinguish between functions and procedures based on
> context -- there is one case which will affect people, however.

So, do we still need to distinguish if we're not supporting default params?

> iii) Support functions and procedures through SQL
>
> Instead of adding EXECUTE FUNCTION, we could have:
>
> FOR EACH { ROW | STATEMENT } { SELECT <funcname> | CALL <procedure> }
>
> This gives us the option, I believe, of moving to full SQL comformance in
> the future as well as giving people (and pg_dump) and upgrade path.

I like this because of the SQL conformance, completely aside from issues of 
determinism.

> Is foo() a function or procedure? I think it is reasonable to say that
> only procedures can be called in this fashion, and that function need be
> invoked with PERFORM or in a query, as we have in 7.4, etc.

Frankly, I agree here.   For one thing, any Function being called in that 
fashion is effectively being treated as a procedure -- the value it returns 
is being thrown away.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco