Есть ли какая-либо формальная разница между функциями PostgreSQL с параметрами OUT и результатами TABLE?
Рассмотрим эти две функции PostgreSQL:
CREATE OR REPLACE FUNCTION f_1 (v1 INTEGER, v2 OUT INTEGER)
AS $$
BEGIN
v2 := v1;
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION f_2 (v1 INTEGER)
RETURNS TABLE(v2 INTEGER)
AS $$
BEGIN
v2 := v1;
END
$$ LANGUAGE plpgsql;
В любом "обычном" процедурном языке SQL (например, Transact-SQL) эти два типа функций будут совершенно разными. f_1 будет на самом деле процедура, тогда как f_2 будет табличной функцией. В SQL Server последний возвращается из INFORMATION_SCHEMA.ROUTINES вот так:
SELECT r.routine_schema, r.routine_name
FROM information_schema.routines r
WHERE r.routine_type = 'FUNCTION'
AND r.data_type = 'TABLE'
Однако в PostgreSQL это не работает. Следующий запрос показывает, что по существу нет разницы между сигнатурами f_1 а также f_2:
SELECT r.routine_name, r.data_type, p.parameter_name, p.data_type
FROM information_schema.routines r
JOIN information_schema.parameters p
USING (specific_catalog, specific_schema, specific_name);
Вышеуказанные выходы:
routine_name | data_type | parameter_name | data_type
-------------+-----------+----------------+----------
f_1 | integer | v1 | integer
f_1 | integer | v2 | integer
f_2 | integer | v1 | integer
f_2 | integer | v2 | integer
Вещи не становятся намного лучше, когда у меня есть несколько столбцов, возвращаемых из функций, в случае которых у меня даже нет "формального" возвращаемого типа. Просто record:
CREATE OR REPLACE FUNCTION f_3 (v1 INTEGER, v2 OUT INTEGER, v3 OUT INTEGER)
AS $$
BEGIN
v2 := v1;
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION f_4 (v1 INTEGER)
RETURNS TABLE(v2 INTEGER, v3 INTEGER)
AS $$
BEGIN
v2 := v1;
END
$$ LANGUAGE plpgsql;
... Я получу:
routine_name | data_type | parameter_name | data_type
-------------+-----------+----------------+----------
f_3 | record | v1 | integer
f_3 | record | v2 | integer
f_3 | record | v3 | integer
f_4 | record | v1 | integer
f_4 | record | v2 | integer
f_4 | record | v3 | integer
Если исходить из других баз данных, ясно, что цель лексической подписи совершенно иная. Как человек Oracle, я ожидаю PROCEDURES иметь побочные эффекты, тогда как FUNCTIONS не имеет побочных эффектов (кроме случаев автономной транзакции) и может быть безопасно встроен в SQL. Я знаю, что PostgreSQL ловко рассматривает все функции как таблицы, но я не думаю, что это хорошая идея для разработки OUT параметры в виде столбцов таблицы в любом запросе...
Мой вопрос:
Есть ли какая-либо формальная разница между двумя способами объявления функций? Если есть, как я могу обнаружить это из INFORMATION_SCHEMA или из PG_CATALOG?
3 ответа
\df public.f_* Является ли это
select
n.nspname as "Schema",
p.proname as "Name",
pg_catalog.pg_get_function_result(p.oid) as "Result data type",
pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
case
when p.proisagg then 'agg'
when p.proiswindow then 'window'
when p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype then 'trigger'
else 'normal'
end as "Type"
from
pg_catalog.pg_proc p
left join
pg_catalog.pg_namespace n on n.oid = p.pronamespace
where
p.proname ~ '^(f_.*)$'
and n.nspname ~ '^(public)$'
order by 1, 2, 4;
который возвращает это
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+-------------------------------+--------------------------------------------+--------
public | f_1 | integer | v1 integer, OUT v2 integer | normal
public | f_2 | TABLE(v2 integer) | v1 integer | normal
public | f_3 | record | v1 integer, OUT v2 integer, OUT v3 integer | normal
public | f_4 | TABLE(v2 integer, v3 integer) | v1 integer | normal
(4 rows)
Чтобы удалить функцию, необходимо передать ее ввод (IN а также INOUT) аргументы типов данных. Тогда я предполагаю, что имя функции и ее типы данных входных аргументов формируют свою подпись. И чтобы изменить возвращаемый тип данных, необходимо сначала удалить его и создать заново.
RETURNS TABLE() фактически так же, как OUT параметры в сочетании с RETURNS SETOF ...Без дополнительного SETOF ключевое слово, функция с OUT Параметры всегда возвращает одну строку, а функция с RETURNS TABLE() может вернуть 0-н строк.
Ваш пример просто не демонстрирует разницу из-за того, как он написан.
Это отражено во флаге proretset системного каталога pg_proc, Руководство:
Функция возвращает набор (т. Е. Несколько значений указанного типа данных)
Представление информационной схемы information_schema.routines не особенно полезен в этом отношении. Это размытый стандартизированный компромисс для представления информации в независимой от платформы форме, которая вряд ли подходит для описания особенностей Postgres.
Подпись функции
Две функции считаются одинаковыми, если они имеют одинаковые имена и типы входных аргументов, игнорируя любые
OUTпараметры
Где "входные" типы аргументов inlcude IN а также INOUT параметры.
Эти две функции играют важную роль в работе с определениями функций. Руководство:
pg_get_function_arguments(func_oid) ... get argument list of function's definition (with default values)
pg_get_function_identity_arguments(func_oid) ... get argument list to identify a function (without default values)
Больше в этих связанных ответах:
Похоже, что pg_catalog.pg_proc.proretset Флаг содержит подсказку о том, возвращает ли функция набор (т. е. таблицу):
SELECT r.routine_name, r.data_type, p.parameter_name,
p.data_type, pg_p.proretset
FROM information_schema.routines r
JOIN information_schema.parameters p
USING (specific_catalog, specific_schema, specific_name)
JOIN pg_namespace pg_n
ON r.specific_schema = pg_n.nspname
JOIN pg_proc pg_p
ON pg_p.pronamespace = pg_n.oid
AND pg_p.proname = r.routine_name
WHERE r.routine_schema = 'public'
AND r.routine_name IN ('f_1', 'f_2', 'f_3', 'f_4')
ORDER BY routine_name, parameter_name;
Вышеуказанное даст:
routine_name | data_type | parameter_name | data_type | proretset
-------------+-----------+----------------+-----------+----------
f_1 | record | v1 | integer | f
f_1 | record | v2 | integer | f
f_2 | record | v1 | integer | t
f_2 | record | v2 | integer | t
f_3 | record | v1 | integer | f
f_3 | record | v2 | integer | f
f_3 | record | v3 | integer | f
f_4 | record | v1 | integer | t
f_4 | record | v2 | integer | t
f_4 | record | v3 | integer | t
INFORMATION_SCHEMA.COLUMNS эмуляция
Для чего это стоит и на случай, если кому-то понадобится эта сумасшедшая вещь, вот прекрасный запрос, который я придумал, чтобы эмулировать SQL Server. INFORMATION_SCHEMA.COLUMNS Реализация, которая возвращает столбцы табличных функций (что нам действительно нужно при поддержке табличных функций в генераторе кода jOOQ):
SELECT
p.proname AS TABLE_NAME,
columns.proargname AS COLUMN_NAME,
ROW_NUMBER() OVER(PARTITION BY p.oid ORDER BY o.ordinal) AS ORDINAL_POSITION,
format_type(t.oid, t.typtypmod) AS DATA_TYPE,
information_schema._pg_char_max_length(t.oid, t.typtypmod) AS CHARACTER_MAXIMUM_LENGTH,
information_schema._pg_numeric_precision(t.oid, t.typtypmod) AS NUMERIC_PRECISION,
information_schema._pg_numeric_scale(t.oid,t.typtypmod) AS NUMERIC_SCALE,
not(t.typnotnull) AS IS_NULLABLE
FROM pg_proc p,
LATERAL generate_series(1, array_length(p.proargmodes, 1)) o(ordinal),
LATERAL (
SELECT
p.proargnames[o.ordinal],
p.proargmodes[o.ordinal],
p.proallargtypes[o.ordinal]
) columns(proargname, proargmode, proargtype),
LATERAL (
SELECT pg_type.oid oid, pg_type.*
FROM pg_type
WHERE pg_type.oid = columns.proargtype
) t
WHERE p.proretset
AND proargmode = 't'
AND p.proname LIKE 'f%';
Вышеуказанное приятно возвращает (имена столбцов сокращены для SO):
table_name | column_name | ordinal | data_type | length | precision | scale | nullable
f_2 | v2 | 1 | integer | | 32 | 0 | t
f_4 | v2 | 1 | integer | | 32 | 0 | t
f_4 | v3 | 2 | integer | | 32 | 0 | t