User Defined Function returning a row of columns.

Hi, I'm trying to write a server function which will return a row of more than one column. The function will be passed a SELECT statement which it will execute, it will then sort the rows according to a scoring system and then returned the rows. The SELECT statement will return multiple columns and those columns should also be returned by the function once the sorting has been done. I've been going through lots of pages of documentation but can't seem to find the right info. Can somebody tell me (point me in the right direction) how I'd go about returning a row like this? I have a working function will takes a SELECT statement which returns a single row with one column which is a large chunk of text, my function then sorts the text (which has delimiters for each row/line embedded in it) and then returns a text type which is the sorted text. This approach is too slow as WebExplode is used to get the initial block of text (and there's quite a lot of it). For Informix 9.14. BFN, John -- "coffee" may be a binary file. See it anyway?

John Wright (john@REMOVE.infernet.com) wrote: : Hi, : : I'm trying to write a server function which will return a row of more than : one column. The function will be passed a SELECT statement which it will : execute, it will then sort the rows according to a scoring system and then : returned the rows. The SELECT statement will return multiple columns and : those columns should also be returned by the function once the sorting has : been done. : : I've been going through lots of pages of documentation but can't seem to : find the right info. : : Can somebody tell me (point me in the right direction) how I'd go about : returning a row like this? I have a working function will takes a SELECT : statement which returns a single row with one column which is a large : chunk of text, my function then sorts the text (which has delimiters for : each row/line embedded in it) and then returns a text type which is the : sorted text. This approach is too slow as WebExplode is used to get the : initial block of text (and there's quite a lot of it). OK. 1. I suggest that what you want is a user-defined aggregate that produces a COLLECTION result. This is going to be more flexible, and probably scaleable, that any alternative. SELECT OrderedScores( Scores ) fROM Class WHERE Class.Id = 'CS 101'; 2. The aggregate function is OrderesScores(). Think of this as kind of like a SUM() function, only instead of adding all the columns up, it puts them into a COLLECTION. Then before returning the COLLECTION, it does a pass through to order them. i. For stuff on how to do COLLECTIONS, check out the SAPI manual. i.e. mi_collection_create() etc. ii. For an excellent write-up on User-defined aggregates, check out Jacques Roy's Tech Notes piece, or the "Best Practices" book about IDS/UD edited by Angela Sanchez (where Jacque's piece is re-printed). iii. The IDN web site (http://www.informix.com/idn) has write-ups on both of these. 3. Why the aggregate? Cos' then you can do queies like this: SELECT Class.Id, OrderedScores ( Scores ) FROM Class GROUP BY Class.Id; KR Pb

Paul Brown wrote: > : I'm trying to write a server function which will return a row of more than > : one column. The function will be passed a SELECT statement which it will > : execute, it will then sort the rows according to a scoring system and then > : returned the rows. The SELECT statement will return multiple columns and > : those columns should also be returned by the function once the sorting has > : been done. > : [...] > 1. I suggest that what you want is a user-defined aggregate > that produces a COLLECTION result. This is going to be more > flexible, and probably scaleable, that any alternative. > > SELECT OrderedScores( Scores ) > fROM Class > WHERE Class.Id = 'CS 101'; > [...] Will this be able to return a row made up of mixed types though? Maybe initialise the COLLECTION with the row type? BFN, John -- "coffee" may be a binary file. See it anyway?

John Wright (john@REMOVE.infernet.com) wrote: : Paul Brown wrote: : > SELECT OrderedScores( Scores ) : > fROM Class : > WHERE Class.Id = 'CS 101'; : > [...] : Will this be able to return a row made up of mixed types though? Maybe : initialise the COLLECTION with the row type? Well, in this case the COLLECTION will only contain instances of a single type (the type used to define the Scores column). And if you're ordering the instances somehow, then it follows that all of the objects in the COLLECTION must be "of the same type" (even if this is just a string). Basically, you can't have a COLLECTION that contains a mix of types, in the same way that you can't have a column that contains a mix of types. Does that answer the question? Or am I missing something? KR Pb

Paul Brown wrote: > Basically, you can't have a COLLECTION that contains a mix of types, > in the same way that you can't have a column that contains a mix of > types. > > Does that answer the question? Or am I missing something? Weeellll. What I want to do is pass a SELECT statement to the function which executes the statement, buffers the rows, sorts them and then returns the rows in sorted order. The SELECT statement will have lots of columns listed and I want to return them all. The function will be told which column numbers can be used to make the score in order to sort them. I'm not even sure this is possible without defining a row type so that Informix knows how many columns are in the row being returned -- that sort of thing. I've got technical support looking for an answer too... It'd be really nice to get this to work. BFN, John -- "coffee" may be a binary file. See it anyway?

John Wright wrote: > Weeellll. What I want to do is pass a SELECT statement to the function > which executes the statement, buffers the rows, sorts them and then > returns the rows in sorted order. The SELECT statement will have lots of > columns listed and I want to return them all. The function will be told > which column numbers can be used to make the score in order to sort them. Gah, it's not possible. It's possible to return a row type but their columns can't be access by the webblade (as $1 $2 $3, etc) so we're looking at alternatives. Sigh. BFN, John -- "coffee" may be a binary file. See it anyway?