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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
