Как избежать множественных ошибок функций с помощью синтаксиса (func()).* В запросе SQL?
контекст
Когда функция возвращает TABLE или SETOF composite-type, как этот пример функции:
CREATE FUNCTION func(n int) returns table(i int, j bigint) as $$
BEGIN
RETURN QUERY select 1,n::bigint
union all select 2,n*n::bigint
union all select 3,n*n*n::bigint;
END
$$ language plpgsql;
К результатам можно получить доступ различными способами:
1) select * from func(3) произведет эти выходные столбцы:
я | J ---+--- 1 | 3 2 | 9 3 | 27
2) select func(3) будет производить только один выходной столбец типа ROW.
FUNC ------- (1,3) (2,9) (3,27)
3) select (func(3)).* будет производить как #1:
я | J ---+--- 1 | 3 2 | 9 3 | 27
Когда аргумент функции происходит из таблицы или подзапроса, синтаксис #3 является единственным возможным, как в:
select N, (func(N)).* from (select 2 as N union select 3 as N) s;
или как в этом связанном ответе. Если бы мы имели LATERAL JOIN мы могли бы использовать это, но до выхода PostgreSQL 9.3 он не поддерживается, а предыдущие версии все равно будут использоваться годами.
проблема
Теперь проблема с синтаксисом #3 заключается в том, что функция вызывается столько раз, сколько столбцов в результате. Там нет очевидной причины для этого, но это происходит. Мы можем увидеть это в версии 9.2, добавив RAISE NOTICE 'called for %', n в функции. С запросом выше, это выводит:
ВНИМАНИЕ: призыв к 2 ВНИМАНИЕ: призыв к 2 ВНИМАНИЕ: призыв к 3 ВНИМАНИЕ: призыв к 3
Теперь, если функция изменена так, чтобы она возвращала 4 столбца, вот так:
CREATE FUNCTION func(n int) returns table(i int, j bigint,k int, l int) as $$
BEGIN
raise notice 'called for %', n;
RETURN QUERY select 1,n::bigint,1,1
union all select 2,n*n::bigint,1,1
union all select 3,n*n*n::bigint,1,1;
END
$$ language plpgsql stable;
затем тот же запрос выводит:
ВНИМАНИЕ: призыв к 2 ВНИМАНИЕ: призыв к 2 ВНИМАНИЕ: призыв к 2 ВНИМАНИЕ: призыв к 2 ВНИМАНИЕ: призыв к 3 ВНИМАНИЕ: призыв к 3 ВНИМАНИЕ: призыв к 3 ВНИМАНИЕ: призыв к 3
Потребовалось 2 функциональных вызова, фактически было сделано 8. Соотношение - это количество выходных столбцов.
С синтаксисом № 2, который дает тот же результат, за исключением разметки выходных столбцов, эти множественные вызовы не выполняются:
select N,func(N) from (select 2 as N union select 3 as N) s;
дает:
ВНИМАНИЕ: призыв к 2 ВНИМАНИЕ: призыв к 3
затем следуют 6 строк:
п | FUNC ---+------------ 2 | (1,2,1,1) 2 | (2,4,1,1) 2 | (3,8,1,1) 3 | (1,3,1,1) 3 | (2,9,1,1) 3 | (3,27,1,1)
Вопросы
Существует ли синтаксис или конструкция с 9.2, которая могла бы достичь ожидаемого результата, выполняя только минимальные требуемые вызовы функций?
Бонусный вопрос: почему множественные оценки вообще происходят?
1 ответ
Вы можете заключить его в подзапрос, но это не гарантирует безопасность без OFFSET 0 хак. В 9.3 используйте LATERAL, Проблема вызвана тем, что синтаксический анализатор эффективно расширяет макрос * в список столбцов.
Временное решение
Куда:
SELECT (my_func(x)).* FROM some_table;
будет оценивать my_funcn времена для n В результате столбцы из функции, эта формулировка:
SELECT (mf).* FROM (
SELECT my_func(x) AS mf FROM some_table
) sub;
как правило, не будет, и имеет тенденцию не добавлять дополнительное сканирование во время выполнения. Чтобы гарантировать, что множественная оценка не будет выполнена, вы можете использовать OFFSET 0 взлом или злоупотребление неспособностью PostgreSQL оптимизировать через границы CTE:
SELECT (mf).* FROM (
SELECT my_func(x) AS mf FROM some_table OFFSET 0
) sub;
или же:
WITH tmp(mf) AS (
SELECT my_func(x) FROM some_table
)
SELECT (mf).* FROM tmp;
В PostgreSQL 9.3 вы можете использовать LATERAL чтобы получить более разумное поведение:
SELECT mf.*
FROM some_table
LEFT JOIN LATERAL my_func(some_table.x) AS mf ON true;
LEFT JOIN LATERAL ... ON true сохраняет все строки как исходный запрос, даже если вызов функции не возвращает строки.
демонстрация
Создайте функцию, которая не является встроенной в качестве демонстрации:
CREATE OR REPLACE FUNCTION my_func(integer)
RETURNS TABLE(a integer, b integer, c integer) AS $$
BEGIN
RAISE NOTICE 'my_func(%)',$1;
RETURN QUERY SELECT $1, $1, $1;
END;
$$ LANGUAGE plpgsql;
и таблица фиктивных данных:
CREATE TABLE some_table AS SELECT x FROM generate_series(1,10) x;
тогда попробуйте вышеуказанные версии. Вы увидите, что первое вызывает три уведомления за вызов; последний только поднять один.
Зачем?
Хороший вопрос. Это ужасно.
Это выглядит как:
(func(x)).*
расширяется как:
(my_func(x)).i, (func(x)).j, (func(x)).k, (func(x)).l
в разборе, согласно взгляду на debug_print_parse, debug_print_rewritten а также debug_print_plan, (Обрезанное) дерево разбора выглядит так:
:targetList (
{TARGETENTRY
:expr
{FIELDSELECT
:arg
{FUNCEXPR
:funcid 57168
...
}
:fieldnum 1
:resulttype 23
:resulttypmod -1
:resultcollid 0
}
:resno 1
:resname i
...
}
{TARGETENTRY
:expr
{FIELDSELECT
:arg
{FUNCEXPR
:funcid 57168
...
}
:fieldnum 2
:resulttype 20
:resulttypmod -1
:resultcollid 0
}
:resno 2
:resname j
...
}
{TARGETENTRY
:expr
{FIELDSELECT
:arg
{FUNCEXPR
:funcid 57168
...
}
:fieldnum 3
:...
}
:resno 3
:resname k
...
}
{TARGETENTRY
:expr
{FIELDSELECT
:arg
{FUNCEXPR
:funcid 57168
...
}
:fieldnum 4
...
}
:resno 4
:resname l
...
}
)
Таким образом, в основном, мы используем тупой взлом парсера для расширения подстановочных знаков путем клонирования узлов.