Thread: Functions returning sets
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.
(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.
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?
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?
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
Serviceand other goodies at http://donb.photo.net.
> 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
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
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
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 Serviceand other goodies at http://donb.photo.net.
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.
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
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
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
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 Serviceand other goodies at http://donb.photo.net.
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.
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.
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?
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
Serviceand other goodies at http://donb.photo.net.
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 whereblah.val1 in (select count(*) from blah2 where blah2.val2=blah.val2 group by blah2.val3);
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
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