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

Josh Berkus wrote:
> Bill,
>
> First off: discussion moved to the SQL list, where it really belongs.

True, it started out as [PERFORM], but is no longer.

>>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.

Well, they're already handling what MSSQL gives them in their prototype, so
that's not my problem.

>>>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.

I don't understand at all. If I do "SELECT * FROM set_returning_function()"
and all I'm going to do is iterate through the columns and rows, adding them
to a two dimensional array that will be marshalled as a SOAP message, what
about not knowing the nature of the return set can cause me to get no data?

> All of this is very hackneyed, as I'm sure you realize.

Well, the way this project is being done tends to cause that. It was written
in VB, it's being converted to VB.NET ... the original backend was MSSQL, now
it's being converted to PostgreSQL with C glue to make PostgreSQL talk SOAP ...
and all on the lowest budget possible.

> 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.

Quite possibly. It's amazing to me how well I've apparently self-taught
myself relational databases. I've spoken with a lot of people who have had
formal schooling in RDBMS who don't really understand it. And I've seen
LOTs of applications that are written so badly that it's scarey. I mean,
check out http://www.editavenue.com ... they wanted me to optimize their
database to get rid of the deadlocks. I've been totally unable to make
them understand that deadlocks are not caused by poor optimization, but
by poor database programmers who don't really know how to code for
multi-user. As a result, I've probably lost the work, but I'm probably
better off without it.

One of the things I love about working with open source databases is I
don't see a lot of that. The people on these lists are almost always
smarter than me, and I find that comforting ;)

--
Bill Moran
Potential Technologies
http://www.potentialtech.com