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@mascari.com

-----Original Message-----
From:	mlw [SMTP:markw@mohawksoft.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?


Sat, 19 May 2001 18:41:45 -0400
Mike Mascari <mascarm@mascari.com>
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;
>

This is exactly what I was thinking. To take it one step further, then you
could define a view as:

CREATE VIEW vw_funct_set as (SELECT funct_set('blah blah'));

Then the statement above might look like:

SELECT titles.* FROM titles, vw_funct_set AS bar WHERE titles.title =
bar.title;

IMHO this seems the most natural way to do what you have proposed.

-- Joe


Sat, 19 May 2001 16:38:16 -0700
"Joe Conway" <joe@conway-family.com>
Re: Re: Functions returning sets
Why not like Interbase ?

when you define a procedure in Interbase, you a the 'suspend' instruction, 
it suspend execution of the stored procedure and returns variables, then 
come back to the procedure.

select * from myfunc('ba ba');
select mycol from myfunc('dada');

escuse my poor english :)

....
Mike Mascari wrote:

> 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@mascari.com



Sun, 20 May 2001 09:32:33 +0200
mordicus <mordicus@free.fr>
RE: Re: Functions returning sets
Why not like Interbase ?

when you define a procedure in Interbase, you have the 'suspend' 
instruction, 
it suspend execution of the stored procedure and returns variables, then 
come back to the procedure.

select * from myfunc('ba ba');
select mycol from myfunc('dada');

escuse my poor english :)


Mike Mascari wrote:

> 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@mascari.com


Sun, 20 May 2001 09:47:29 +0200
mordicus <mordicus@free.fr>
RE: Re: Functions returning sets