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]
