RE: Re: Functions returning sets

I see Tom Lane implemented the SQL92 feature of using subselects in
FROM clauses:

CREATE TABLE foo (
key integer not null,
value text);

SELECT * FROM (SELECT * FROM foo) AS bar
WHERE bar.key = 1;

Perhaps this is how functions returning sets should operate:

SELECT titles.* FROM titles, (SELECT funct_set('blah blah')) AS bar
WHERE titles.title = bar.title;

FWIW,

Mike Mascari
mascarm(at)mascari(dot)com

-----Original Message-----
From: mlw [SMTP:markw(at)mohawksoft(dot)com]
Sent: Saturday, May 19, 2001 5:42 PM
To: Stephan Szabo
Cc: Hackers List
Subject: [HACKERS] Re: Functions returning sets

Stephan Szabo wrote:
>
> (Machine couldn't find mx record for mohawksoft, replying only
> to list)
>
> On Sat, 19 May 2001, mlw wrote:
>
> > Sorry to gripe here. Don't get me wrong, I think Postgres is
amazing, and I
> > think all you guys do an amazing job.
> >
> > Is it just me, or do others agree, functions returning sets need
to be able to
> > be used in a select where equal clause.
> >
> > select * from table where field = funct_set('bla bla');

I don't understand your reasoning. Look at the syntax:

select * from foo where bar = function(...);

If function() returns one value, then only one will be returned and
the
relation features of postgres can be used, as in "select * from foo,
this where
foo.bar = function() and foo.bar = this.that"

If function() can return multiple values, should it not follow that
multiple
values should be selected?

In the example where one result is returned, that makes sense. Why
does the
example of multiple results being returned no longer make sense?

It is a point of extreme frustration to me that I can't do this
easily. Lacking
this ability makes Postgres almost impossible to implement a search
engine
correctly. I know it is selfish to feel this way, but I am positive
my
frustration is indicative of others out there trying to use Postgres
for
certain applications. I bet a huge number of developers feel the
same way,
but try a few quick tests and give up on Postgres all together,
without saying
a word. What good are multiple results in a relational environment if
one can
not use them as relations?