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@bioeng.washington.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