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

Where funct_set returns multiple results. This currently does not work. The
only other option with sets is usage like:

select * from table where field in (select funct_set('bla bla')) ;

Which would be OK if it were able to use an index and preserve the order
returned.

Without the ability to use a set to select multiple objects in a join, it is
dramatically limited in use for a relational database. In fact, I can't think
of many uses for it as is.

Having used Postgres since 1996/1997 I have only a very few gripes, and I think
7.1 is a fantastic effort, but, I think a HUGE amount of applications can not
be done because of this limitation.

What can I do to help get this ability in 7.2? I is VERY important to a project
on which I am working.

Sat, 19 May 2001 16:35:56 -0400
mlw <markw@mohawksoft.com>
Functions returning sets

(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 think what we should probably do is make IN better and use that or then
support =ANY(=SOME)/=ALL on such things.  I think =ANY would be easy
since IN is defined in terms of it in the spec.

I personally don't like the idea of value = set being value in set any
more than value = array being value in array.


Sat, 19 May 2001 14:08:06 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
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 17:42:13 -0400
mlw <markw@mohawksoft.com>
Re: Functions returning sets
At 02:08 PM 5/19/01 -0700, 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 think what we should probably do is make IN better...

IN is the right operator.  "field = [set of rows]" isn't SQL92 and doesn't
really make sense.   It's the equivalent of:

select * from table where field = (select some_column from some_other_table)

If the subselect returns a single row it will work, if it returns multiple
rows you need to use IN.  That's just how SQL92 is defined.

I'd assume that a function returning a single column and single row will
work just as the subselect does, but there's no reason expect it to work
if more than one row is returned.

What's so hard about writing "IN" rather than "=" ???



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Sat, 19 May 2001 16:08:37 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Functions returning sets
Don Baccus <dhogaza@pacifier.com> writes:
> What's so hard about writing "IN" rather than "=" ???

Even more to the point, if we did adopt such a (crazy IMHO)
interpretation of '=', what makes anyone think that it'd be
any more efficient than IN?

AFAICT, mlw is hoping that redefining '=' would magically avoid the
performance problems with IN, but my bet is it'd be just the same.

What we need to do is teach the system how to handle WHERE ... IN ...
as a form of join.  Changing semantics of operators isn't necessary
nor helpful.

			regards, tom lane

Sat, 19 May 2001 20:44:09 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Functions returning sets 
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> I think what we should probably do is make IN better and use that or then
> support =ANY(=SOME)/=ALL on such things.  I think =ANY would be easy
> since IN is defined in terms of it in the spec.

And in our code too ;-).  ANY/ALL have been there for awhile.

			regards, tom lane

Sat, 19 May 2001 20:45:20 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Functions returning sets 
At 08:44 PM 5/19/01 -0400, Tom Lane wrote:
>Don Baccus <dhogaza@pacifier.com> writes:
>> What's so hard about writing "IN" rather than "=" ???
>
>Even more to the point, if we did adopt such a (crazy IMHO)
>interpretation of '=', what makes anyone think that it'd be
>any more efficient than IN?

I was going to mention this, but stuck to the old letter-to-the-editor
rule of one point per note. :)

>AFAICT, mlw is hoping that redefining '=' would magically avoid the
>performance problems with IN, but my bet is it'd be just the same.

Or that it would actually be a join operator???  Wasn't clear to me
exactly what he was expecting.

>What we need to do is teach the system how to handle WHERE ... IN ...
>as a form of join.

Yes.

BTW Oracle has a 1000-element limit on the number of values in an
"IN" set.  This limits its generality as it applies to subselects
as well as lists of constants.  It seems that PG isn't the only
RDMBS that has problems with getting "IN" implemented perfectly.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Sat, 19 May 2001 17:56:06 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Functions returning sets 
Tom Lane wrote:
> 
> Don Baccus <dhogaza@pacifier.com> writes:
> > What's so hard about writing "IN" rather than "=" ???
> 
> Even more to the point, if we did adopt such a (crazy IMHO)
> interpretation of '=', what makes anyone think that it'd be
> any more efficient than IN?
> 
> AFAICT, mlw is hoping that redefining '=' would magically avoid the
> performance problems with IN, but my bet is it'd be just the same.
> 
> What we need to do is teach the system how to handle WHERE ... IN ...
> as a form of join.  Changing semantics of operators isn't necessary
> nor helpful.

I will defer, of course, to your interpretation of '=', but I still think it
(if implemented efficiently) could be cool. However, I hang my head in shame
that I didn't see this syntax:

select table.* from table, (select function() as field) as result where
table.field = result.field;

It seems to be pretty efficient, and satisfies the main criteria that I needed,
which was a full text search could be used on select with no external
programming language.

Currently my system can't be used without an external programming language, and
this is a huge, if awkward solution. Thanks all.

Sat, 19 May 2001 21:34:27 -0400
mlw <markw@mohawksoft.com>
Re: Functions returning sets
Don Baccus <dhogaza@pacifier.com> writes:
> BTW Oracle has a 1000-element limit on the number of values in an
> "IN" set.

Yeah?  What happens when you get past that?

			regards, tom lane

Sun, 20 May 2001 00:31:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Functions returning sets 
I think you probably could use contrib/intarray if you redesign
your scheme.

	Oleg

On Sat, 19 May 2001, mlw wrote:

> Tom Lane wrote:
> >
> > Don Baccus <dhogaza@pacifier.com> writes:
> > > What's so hard about writing "IN" rather than "=" ???
> >
> > Even more to the point, if we did adopt such a (crazy IMHO)
> > interpretation of '=', what makes anyone think that it'd be
> > any more efficient than IN?
> >
> > AFAICT, mlw is hoping that redefining '=' would magically avoid the
> > performance problems with IN, but my bet is it'd be just the same.
> >
> > What we need to do is teach the system how to handle WHERE ... IN ...
> > as a form of join.  Changing semantics of operators isn't necessary
> > nor helpful.
>
> I will defer, of course, to your interpretation of '=', but I still think it
> (if implemented efficiently) could be cool. However, I hang my head in shame
> that I didn't see this syntax:
>
> select table.* from table, (select function() as field) as result where
> table.field = result.field;
>
> It seems to be pretty efficient, and satisfies the main criteria that I needed,
> which was a full text search could be used on select with no external
> programming language.
>
> Currently my system can't be used without an external programming language, and
> this is a huge, if awkward solution. Thanks all.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>

	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Sun, 20 May 2001 07:58:28 +0300 (GMT)
Oleg Bartunov <oleg@sai.msu.su>
Re: Re: Functions returning sets
mlw wrote:
> 
> 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?

of course not! if function() can return (i.e. returns) as set then bar
must be of 
type SET too and only rows that are = (in whatever sense currently
defined) should
be returned

---------------
Hannu

Sun, 20 May 2001 12:34:31 +0500
Hannu Krosing <hannu@tm.ee>
Re: Re: Functions returning sets
At 12:31 AM 5/20/01 -0400, Tom Lane wrote:
>Don Baccus <dhogaza@pacifier.com> writes:
>> BTW Oracle has a 1000-element limit on the number of values in an
>> "IN" set.
>
>Yeah?  What happens when you get past that?

My understanding is that it gives you an error, though I've never tried
it myself.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Sun, 20 May 2001 09:27:30 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Functions returning sets 
On Sun, 20 May 2001, Don Baccus wrote:

> >> BTW Oracle has a 1000-element limit on the number of values in an
> >> "IN" set.
> >
> >Yeah?  What happens when you get past that?
>
> My understanding is that it gives you an error, though I've never
> tried it myself.

It does.

Matthew.


Sun, 20 May 2001 18:09:42 +0100 (BST)
Matthew Kirkwood <matthew@hairy.beasts.org>
Re: Functions returning sets 
On Sat, 19 May 2001, mlw wrote:

> 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? 

What does select * from foo where bar=(select value from foo2) mean when
the second returns more than one row.  IIRC, sql says that's an error.
I don't see why functions returning sets would be different than a
subquery.  That's what =ANY, =ALL, and IN are for.  I don't see what the
difference between doing this with =s and making IN work better really is.


Sun, 20 May 2001 10:53:55 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Functions returning sets
On Sat, 19 May 2001, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > I think what we should probably do is make IN better and use that or then
> > support =ANY(=SOME)/=ALL on such things.  I think =ANY would be easy
> > since IN is defined in terms of it in the spec.
> 
> And in our code too ;-).  ANY/ALL have been there for awhile.

:) Well that makes it easier anyway.  I do agree with the point that at
some point IN needs to be smarter.  Can the IN always get written as a
join and is it always better to do so?



Sun, 20 May 2001 10:55:25 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Functions returning sets 
At 10:55 AM 5/20/01 -0700, Stephan Szabo wrote:
> Can the IN always get written as a
>join and is it always better to do so?

Nope:

openacs4=# select 1 in (1,2,3);
 ?column? 
----------
 t
(1 row)

You might also do something like

"select count(*) from foo where foo.state in ('rejected', 'banned');"

A better question, I guess, is if it is always better to write
it as a join if the left hand operand is a table column and
the right hand operand a rowset.





- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Sun, 20 May 2001 11:29:00 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Functions returning sets 

On Sun, 20 May 2001, Don Baccus wrote:

> At 10:55 AM 5/20/01 -0700, Stephan Szabo wrote:
> > Can the IN always get written as a
> >join and is it always better to do so?
> 
> Nope:
> ...
> A better question, I guess, is if it is always better to write
> it as a join if the left hand operand is a table column and
> the right hand operand a rowset.

Well I was assuming we were talking about the subquery case
in general :)

It might be a problem with subqueries with set value functions
and parameters passed down from the outer tables:
select * from blah where
 blah.val1 in 
  (select count(*) from blah2 where blah2.val2=blah.val2
    group by blah2.val3);


Sun, 20 May 2001 11:55:02 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Functions returning sets 