Re: [PERFORM] Set-Returning Functions WAS: On the performance of views

Bill,

First off: discussion moved to the SQL list, where it really belongs.

> Well, I would have agreed with the uselessness, until this project. The
> "source of endless debugging" frightens me!

Well, the last time I tried to use this capability was SQL Server 7. On that
model, the problems I found were:
1) There was no good way to differentiate the recordsets returned; you had to
keep careful track of what order they were in and put in "fillers" for
recordsets that didn't get returned.
2) Most of the MS client technology (ODBC, ADO) was not prepared to handle
multiple recordsets. I ended up hiring a C-based COM hacker to write me a
custom replacement for ADO so that we could handle procedure results
reliably.

All in all, it wasn't worth it and if I had the project to do over again, I
would have chosen a different approach.

> > This can be done with Set Returning Functions. The issue is that the
> > call to the function requires special syntax, and the program calling the
> > function must know what columns are going to be returned at the time of
> > the call. Hmmm, is that clear or confusing?
>
> Clear as mud. In my case, my application simply doesn't care what row of
> what kind are returned. See, I'm writing the server end, and all said and
> done, it's really just glue (frighteningly thick glue, but glue
> nonetheless)

To be clearer: You can create a Set Returning Function (SRF) without a
clearly defined set of return columns, and just have it return "SETOF
RECORD". However, when you *use* that function, the query you use to call
it needs to have a clear idea of what columns will be returned, or you get no
data.

All of this is very hackneyed, as I'm sure you realize. Overall, I'd say
that the programming team you've been inflicted with don't like relational
databases, or at least have no understanding of them.

--
Josh Berkus
Aglio Database Solutions
San Francisco