Перевод Waiting for 8.4 - pl/* srf functions in selects с select * from depesz;28 октября Tom Lane применил свой патч изменяющий внутреннее устройство функций, что дало несколько интересных возможностей.
Комментарий к патчу:
Расширяет ExecMakeFunctionResult() поддержкой set-returning
функций, возвращающих значения с использованием tuplestore вместо механизма
значение-за-вызов. Проведён рефакторинг некоторых вещей, устраняющий дублирование
кода с помощью nodeFunctionscan.c. Это не обсуждаемая часть моего патча для перевода
SQL функций на возврат tuplestore. На данный момент SQL функции всё ещё ведут себя
по старому. Однако, теперь возможно использовать PL SRF функции в целевом списке
полей.
Что это даёт. Как вы возможно знаете, нельзя сделать join таблицы с функцией. Если функция возвращает >1 строки (или >1 колонки), не возможно её вызвать, передав ей в качестве аргумента поле из таблицы, используемой в запросе.
Я думаю описание немного сложновато, так что сразу перейдём к примеру.
Есть функция, которая, принимая 2 целых значения, возвращает все значения между ними, плюс некоторые текстовые поля:
CREATE OR REPLACE FUNCTION test(
IN from_i INT4,
IN to_i INT4,
OUT numerical INT4,
OUT textual TEXT
) RETURNS setof record as $$
DECLARE
i INT4;
BEGIN
for i in from_i .. to_i LOOP
numerical := i;
textual := 'i = ' || i;
RETURN next;
END loop;
RETURN;
END;
$$ language plpgsql;
Эта простая функция делает следующее:
# select * from test(2,5);
numerical | textual
-----------+---------
2 | i = 2
3 | i = 3
4 | i = 4
5 | i = 5
(4 rows)
Теперь, допустим, мы хотим (по какой-то причине) получить такие строки для каждой пары следующего выражения:
# select 1 as from_i, i as to_i from generate_series(1,3) i;
from_i | to_i
--------+------
1 | 1
1 | 2
1 | 3
(3 rows)
По большому счёту это затруднительно. Я не могу сделать join generate_series и моей функции. Если бы она была на SQL, то можно было бы сделать так
select i, test(...) from
но она на pl/pgsql. Конечно, я бы мог написать для неё SQL обёртку, но это совсем не здорово. К счастью, с новым патчем, pl/pgsql (и другие pl/*) функции смогут вызываться так же как и просто SQL функции:
# select i, test(1, i) from generate_series(1,3) i;
i | test
---+-------------
1 | (1,"i = 1")
2 | (1,"i = 1")
2 | (2,"i = 2")
3 | (1,"i = 1")
3 | (2,"i = 2")
3 | (3,"i = 3")
(6 rows)
Но что, если надо получить колонки отдельно?
Это можно сделать так:
select i, (test(1, i)).numerical, (test(1,i)).textual from generate_series(1,3) i;
Но в этом случае test() будет вызываться дважды для каждой строки, что будет проблематично, если там будет что-нибудь посложнее, чем просто цикл.
К счастью это можно также сделать простым подзапросом:
# select i, (test).numerical, (test).textual
from (select i, test(1, i) from generate_series(1,3) i) x;
i | numerical | textual
---+-----------+---------
1 | 1 | i = 1
2 | 1 | i = 1
2 | 2 | i = 2
3 | 1 | i = 1
3 | 2 | i = 2
3 | 3 | i = 3
(6 rows)
Превосходно. Ещё одна проблема позади.
*ДОПОЛНЕНИЕ*
(после напоминания от David Fetter)
CTE (основные табличные выражения) настолько новы, что я просто о них забыл, вот они:
with source as (
select i, test(1, i) from generate_series(1,3) i
)
select i, (test).numerical, (test).textual from source;
explain analyze
для CTE:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
CTE Scan on source (cost=262.50..282.50 rows=1000 width=36) (actual time=2.331..2.534 rows=6 loops=1)
InitPlan
-> Function Scan on generate_series i (cost=0.00..262.50 rows=1000 width=4) (actual time=2.315..2.488 rows=6 loops=1)
Total runtime: 2.646 ms
(4 rows)
и для подзапроса:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Subquery Scan x (cost=0.00..272.50 rows=1000 width=36) (actual time=0.236..0.423 rows=6 loops=1)
-> Function Scan on generate_series i (cost=0.00..262.50 rows=1000 width=4) (actual time=0.229..0.395 rows=6 loops=1)
Total runtime: 0.477 ms
(3 rows)
От автора перевода:
Немного не понятно, почему в первом случае Function Scan занял больше времени. Возможно это было связано с разной нагрузкой на сервер во время обоих тестов. И я думаю, в данном случае смотреть надо на estimation, а не на actual time. Буду рад почитать ваши мысли по этому поводу в комментариях.
Кстати, я это не тестировал, но мне кажется, что достичь желаемого результата и без двойного вызова можно такselect i, (test(1, i)).* from generate_series(1,3) i;
Или я ошибаюсь?
*ДОПОЛНЕНИЕ*
Да, я оказался прав# select i, (test(1, i)).* from generate_series(1,3) i;
i | numerical | textual
—+———–+———
1 | 1 | i = 1
2 | 1 | i = 1
2 | 2 | i = 2
3 | 1 | i = 1
3 | 2 | i = 2
3 | 3 | i = 3
(6 rows)
Posts
