Thread: Functional dysfunction

I'm trying to create a function to return a set of tuples showing the
contents of an attribute (last name, ln) from a relation (adr) if the
contents of any of another set of attributes (nsrrelat01-4) match a text
string given as the argument of the function.  The SQL statements shown
here,

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';

generate this error message:

ERROR:  function declared to return type adr does not retrieve (adr.*)

Can anybody here tell me either 1) how to write the function so it works,
or 2) a simple way of using SQL commands to achieve the desired result,
i.e., I don't want to use

    select ln from ADR where nsrrelat01 ~* '[text to match]' or
        nsrrelat02 ~* '[text to match]' or nsrrelat03 ~*
        '[text to match]' or nsrrelat04 ~* '[text to match]';

I've tried to iterate over attributes using wild card notation, e.g.,

    select ln from ADR where * ~* '[text to match]';

but of course that doesn't work; however, perhaps I'm overlooking some
simple mechanism to use SQL to achieve my aim.

If you can help, TIA.

best,
Eric

James Eric Lawson
Research Publications Editor III
National Simulation Resource

eric@bioeng.washington.edu

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Everyday language is a part of the human organism and is no less
complicated than it. - Ludwig Wittgenstein (1889-1951) [Tractatus
Logico-Philosophicus, 1921]

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

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