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