Re: Functional dysfunction

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