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

Поиск
Список
Период
Сортировка
От Albe Laurenz
Тема Playing with set returning functions in SELECT list - behaviour intended?
Дата
Msg-id D960CB61B694CF459DCFB4B0128514C202FF664F@exadv11.host.magwien.gv.at
обсуждение исходный текст
Ответы Re: Playing with set returning functions in SELECT list - behaviour intended?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Sam Mason
Дата:
Сообщение: Re: Dynamic table
Следующее
От: Nishkarsh
Дата:
Сообщение: Re: Trigger Function and backup