Re: Illustra Server functions that return sets
kkumar@cs.ucsd.edu wrote:
>
> Hello,
>
> I am working on ILLUSTRA 3.3.0.14 for WindowsNT developing some data
> types and functions.I have a function which returns a set. I need
only
> those members of this set that satisfy some criterion.i.e. I need to
e
> able to prune the set (returned by the function) further by means ofSQL
> predicates.
>
> For example,
>
> 'create table MyTable ( MyKey integer, MyText text);'
>
> 'create function Words(text) returns setof(text) as ....'
> - returns all the words in the text column as a set
>
> I want to write an adhoc query to return all the words in a particular
> record if - the length of the word exceeds 10 OR
> - if the word starts with an 'X'
>
> How can I write such a query using only the functions already available
> (Words/length/substring) ?
>
Try the following; I just tried something similar and it worked
create function mywords(setof(text)) returns setof(text)
as select a from $1 a where substring(a from 1 for 1) = 'X' or
length(a) > 10;
select * from mywords(Words('word1 word2 word3 word4...'));
Or even simpler:
select a from Words('word1 word2 word3 word4...') a
where substring(a from 1 for 1) = 'X' or
length(a) > 10;
Mike Dunham-Wilkie
mike@barrodale.com