Re: Functional dysfunction

Thanks, Tom. "setof text" worked (with minor mods of the "create
function..."), and your suggestion that I might use a view is quite
useful.

best,
Eric

On Fri, 28 Jul 2000, Tom Lane wrote:

> ERIC Lawson - x52010 <eric(at)bioeng(dot)washington(dot)edu> writes:
> > create function matRelat(text)
> > returns setof ADR as
> > 'select ln from ADR where
> > nsrrelat01 ~* \'$1\'::text or
> > nsrrelat02 ~* \'$1\'::text or
> > nsrrelat03 ~* \'$1\'::text or
> > nsrrelat04 ~* \'$1\'::text;'
> > language 'sql';
>
> Seems to me you want "returns setof TEXT" or whatever the datatype of ln
> is. "setof ADR" implies it returns the whole tuple (that would be
> appropriate if you wanted "select * from ADR where ...").
>
> Beware that functions returning sets are not all that well supported;
> they work in simple examples like "select function(...)" but you can't
> really combine them in expressions.
>
> Have you thought about a view? Perhaps
>
> create view v1 as select ln, nsrrelat01 || ' ' || nsrrelat02 || ' ' ||
> nsrrelat03 || ' ' || nsrrelat04 as nsrrelat;
>
> and then
>
> select ln from v1 where nsrrelat ~* 'foo';
>
> regards, tom lane