From: | ERIC Lawson - x52010 <eric(at)bioeng(dot)washington(dot)edu> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pg-novice list <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Functional dysfunction |
Date: | 2000-07-28 21:43:18 |
Message-ID: | Pine.GSO.4.10.10007281439380.11195-100000@gandalf.bioeng.washington.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | cpliu | 2000-07-29 00:49:53 | pg_class file |
Previous Message | Tom Lane | 2000-07-28 21:03:07 | Re: Functional dysfunction |