Thread: Playing with set returning functions in SELECT list - behaviour intended?

While playing around with the new features in 8.4rc1, I observe the following:

I create a simple set returning function:

CREATE OR REPLACE FUNCTION n_plicate(x anyelement, i integer) RETURNS SETOF anyelement LANGUAGE plpgsql AS
$$BEGIN
   FOR n IN 1..i LOOP
      RETURN NEXT x;
   END LOOP;
   RETURN;
END;$$;

test=> select n_plicate(42, 3), 41;
 n_plicate | ?column? 
-----------+----------
        42 |       41
        42 |       41
        42 |       41
(3 rows)

test=> select n_plicate(42, 4), n_plicate(41, 6);
 n_plicate | n_plicate 
-----------+-----------
        42 |        41
        42 |        41
        42 |        41
        42 |        41
        42 |        41
        42 |        41
        42 |        41
        42 |        41
        42 |        41
        42 |        41
        42 |        41
        42 |        41
(12 rows)

So it looks like the number of result rows is the least common multiple
of the cardinalities of all columns in the select list.

Is this an artifact or is this intentional?
Should it be documented?

Then I try this:

test=> WITH dummy(a, b) AS
test->    (VALUES(42, 12), (11, 41))
test-> SELECT n_plicate(a, 2), n_plicate(b, 2)
test-> FROM dummy;
 n_plicate | n_plicate 
-----------+-----------
        42 |        12
        42 |        12
        11 |        41
        11 |        41
(4 rows)

Looks reasonable.

But this doesn't:

test=> WITH dummy(a, b) AS
test->    (VALUES(42, 12), (11, 41))
test-> SELECT n_plicate(max(a), 2), n_plicate(max(b), 2)
test-> FROM dummy;
 n_plicate | n_plicate 
-----------+-----------
        42 |        41
(1 row)

I had expected two result rows.
I guess it is the implicit grouping kicking in, but in an unintuitive way.


Should it be that way?

Yours,
Laurenz Albe

"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
> So it looks like the number of result rows is the least common multiple
> of the cardinalities of all columns in the select list.

It's always been that way.  The lack of any obviously-sane way to
handle multiple SRFs in a targetlist is exactly why the feature is
looked on with disfavor.

            regards, tom lane

On Tue, Jun 16, 2009 at 10:06:54AM -0400, Tom Lane wrote:
> "Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
> > So it looks like the number of result rows is the least common
> > multiple of the cardinalities of all columns in the select list.
>
> It's always been that way.  The lack of any obviously-sane way to
> handle multiple SRFs in a targetlist is exactly why the feature is
> looked on with disfavor.

I must be missing something obvious.  Isn't the nested loop thing that
happens with generate_series() pretty sane?

SELECT generate_series(1,2) AS i, generate_series(1,3) AS j;
 i | j
---+---
 1 | 1
 2 | 2
 1 | 3
 2 | 1
 1 | 2
 2 | 3
(6 rows)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david@fetter.org> writes:
> On Tue, Jun 16, 2009 at 10:06:54AM -0400, Tom Lane wrote:
>> It's always been that way.  The lack of any obviously-sane way to
>> handle multiple SRFs in a targetlist is exactly why the feature is
>> looked on with disfavor.

> I must be missing something obvious.  Isn't the nested loop thing that
> happens with generate_series() pretty sane?

You've carefully chosen a case in which the LCM is also the product.
Try some other combinations of periods and see if you still think
it's sane.

            regards, tom lane

Tom Lane wrote:
> It's always been that way.  The lack of any obviously-sane way to
> handle multiple SRFs in a targetlist is exactly why the feature is
> looked on with disfavor.

It is clear that there is no really good way to handle this.

How about my last example that involved aggregate functions, where
I surprisingly got only one result row?

Yours,
Laurenz Albe

"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
> How about my last example that involved aggregate functions, where
> I surprisingly got only one result row?

Oh, you're right, now that I look closer that one is a bug.  Fixed.

            regards, tom lane