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

From: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Playing with set returning functions in SELECT list - behaviour intended?
Date: 2009-06-16 09:56:33
Message-ID: D960CB61B694CF459DCFB4B0128514C202FF664F@exadv11.host.magwien.gv.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Nishkarsh 2009-06-16 10:48:58 Re: Trigger Function and backup
Previous Message Sam Mason 2009-06-16 09:47:41 Re: Dynamic table