Thread: 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
Bill, > 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? Because that's not the syntax for a function that returns SETOF RECORD. The syntax is: SELECT * FROM set_returning_function(var1, var2) AS alias (col1 TYPE, col2 TYPE); That is, if the function definition does not contain a clear row structure, the query has to contain one. This does not apply to functions that are based on a table or composite type: CREATE FUNCTION .... RETURNS SETOF table1 ... CREATE FUNCTION .... RETURNS SETOF comp_type Can be called with: SELECT * FROM some_function(var1, var2) as alias; What this means is that you have to know the structure of the result set, either at function creation time or at function execution time. > > 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 ;) Flattery will get you everywhere. -- Josh Berkus Aglio Database Solutions San Francisco
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
Josh Berkus wrote: > Bill, > >>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? > > Because that's not the syntax for a function that returns SETOF RECORD. > > The syntax is: > > SELECT * > FROM set_returning_function(var1, var2) AS alias (col1 TYPE, col2 TYPE); > > That is, if the function definition does not contain a clear row structure, > the query has to contain one. > > This does not apply to functions that are based on a table or composite type: > > CREATE FUNCTION .... RETURNS SETOF table1 ... > CREATE FUNCTION .... RETURNS SETOF comp_type > > Can be called with: > > SELECT * FROM some_function(var1, var2) as alias; > > What this means is that you have to know the structure of the result set, > either at function creation time or at function execution time. Yep. You're right, I hadn't looked at that, but I'm probably better off creating types and returning setof those types as much as possible. >>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 ;) > > Flattery will get you everywhere. Really? I'll have to use it more often. -- Bill Moran Potential Technologies http://www.potentialtech.com