Добро пожаловать во вторую часть из трех статей о табличных функциях PostgreSQL. Эти функции можно легко использовать в распределенной базе данных SQL, такой как YugabyteDB, которая совместима с PostgreSQL.
В первой части я дал краткое введение в табличные функции PostgreSQL. В третьей части будут рассмотрены некоторые реалистичные варианты использования. Я начну этот второй пост, процитировав этот абзац:
Определяемая пользователем функция plpgsql на обычном языке реализуется с помощью простого оператора return. Его аргумент может быть только одним значением. Пользовательская табличная функция, напротив, реализуется с помощью либо return next, чтобы вернуть следующую явно вычисленную строку, либо return query, чтобы вернуть весь набор результатов оператора select. Вы можете определить оператор, используя либо статический SQL, либо динамический SQL. Вы также можете реализовать табличную функцию языка sql, полностью определив тело с помощью одного статического оператора выбора SQL.
язык sql
Представление определяется одним оператором выбора, а определяемая пользователем табличная функция язык sql также определяется одним оператором выбора. Критическое отличие заключается в том, что табличная функция, как и все виды функций, обычно определяется формальными параметрами. И вы можете использовать один из них в месте синтаксиса в определяющем операторе select, где требуется значение. Другими словами, у вас есть параметризованное представление. Вот пример:
create or replace function t_view(the_k in int) returns table(k int, v varchar) language sql as $body$ select t.k, t.v from t where t.k = t_view.the_k; $body$;
Протестируйте это так:
select k, v from t_view(2);
Пока вы знаете идентификатор таблицы (или таблиц), для которой вы хотите параметризованное представление, этот тип табличной функции идеально подходит. Значение кода не может быть более прозрачным. Я показываю полезность такой табличной функции язык sql в случае использования Параметризованное представление в моем третьем посте.
язык plpgsql с обратным запросом
В самом простом примере есть только один запрос возврата, за которым следует оператор select. Но если вам достаточно этой функциональности, вы можете использовать табличную функцию language sql. Вот более интересный пример. Он опирается на две таблицы: таблицу t, использованную выше, и таблицуs с той же формой строки. Вот удобный способ его создания:
create table s as
select k, ('not '||v)::varchar(20) as v
from t;
Примечание. Обратите внимание на использование явного приведения типов. Без него столбец s.v имел бы тип данных text.
create function t_view(table_name in varchar, the_k in int)
returns table(k int, v varchar)
language plpgsql
as $body$
begin
case table_name
when 't' then
return query
select t.k, t.v from t where t.k = t_view.the_k;
when 's' then
return query
select s.k, s.v from s where s.k = t_view.the_k;
end case;
end;
$body$;
Проверьте это с помощью этих двух запросов:
select k, v from t_view('t', 2);
select k, v from t_view('s', 3);
Примечание. Без приведения типа в операторе "create table as select" для tables запрос во второй части case вызовет эту ошибку:
structure of query does not match function result type Returned type text does not match expected type character varying in column 2.
Примечание. Хотя varchar и text имеют одинаковую функциональность, формально они являются разными типами данных. Это особенность PostgreSQL и, следовательно, YugabyteDB. Это дает возможности для ошибок программиста. Но у вас нет другого выбора, кроме как понять это и соответственно запрограммировать.
Функция t_view() демонстрирует две различные полезные функции. Во-первых, он действует как параметризованное представление, как уже обсуждалось. И, во-вторых, он инкапсулирует выбор между различными исходными таблицами, но при этом использует статический SQL для обеспечения защиты от SQL-инъекций. Если исходных таблиц слишком много, чтобы выбирать между использованием оператора case, или если вы не знаете их имен до момента выполнения, вы можете использовать этот вариант динамического SQL:
create or replace function t_view(table_name in varchar, the_k in int) returns table(k int, v varchar) language plpgsql as $body$ begin return query execute 'select t.k, t.v from "'||table_name||'" as t where t.k = $1' using t_view.the_k; end; $body$;
Протестируйте его с помощью тех же запросов, которые вы использовали для тестирования статического варианта SQL. Вы увидите, что оба варианта дают одинаковые результаты.
Обратите внимание, что одним из формальных параметров функции является имя исходной таблицы. Я преобразовываю его, следуя общепринятой практике, в защищенный от инъекций идентификатор SQL, заключая его в двойные кавычки. Я полагаюсь на тот факт, что этот оператор create table:
create table t(k int primary key, v varchar(20));
и этот:
create table "t"(k int primary key, v varchar(20));
имеют тот же эффект. Любая ссылка на таблицу с именем нижний регистр-t может быть сделана либо с помощью идентификатора без кавычек t, взятого без учета регистра для обозначения имени в нижнем регистре, либо идентификатор в двойных кавычках "t" с учетом регистра.
Примечание. это правило полностью противоположно правилу базы данных Oracle. Там идентификаторы без кавычек тоже берутся без учета регистра; но там они приняты для обозначения имени в верхнем регистре!
язык plpgsql с возвратом следующего
Эта разновидность табличной функции является наиболее общей. В пределе вы можете явно вычислить набор значений для каждой возвращаемой строки. Вот тривиальный пример, показывающий синтаксис:
create function some_computed_rows() returns table(k t.k%type, v t.v%type) language plpgsql as $body$ begin k := 7; v := 'seven'; return next; k := 8; v := 'eight'; return next; k := 9; v := 'nine'; return next; end; $body$;
Конечно, вы можете использовать его, как я показал в других примерах:
insert into t(k, v) select k, v from f();
В более типичных случаях вы не можете выразить то, что хотите, используя только один оператор select, а скорее собираете возвращаемые результаты из одного или нескольких запросов, используя некоторую процедурную логику для их объединения. В качестве упражнения вы можете воспроизвести поведение одного оператора select, использующего функцию окна, путем реализации табличной функции, которая явно устанавливает интересующее окно в теле цикла for. Конечно, вы всегда должны использовать чистый SQL, когда это возможно. Но есть класс требований, где вы не можете этого сделать. Каноническим примером в этом классе является таблица истории цен акций — другими словами, график зависимости цены от времени, — где вы хотите найти так называемый паттерн «W» (локальный минимум, за которым следует локальный максимум, за которым следует локальный максимум). другой локальный минимум, все в течение определенного интервала времени).
Я использую language plpgsql с функцией return next table, чья таблица возврата содержит только один столбец varchar в “ Красиво напечатанные специальные отчеты для администраторов» вариант использования, описанный в моем третьем посте. Там многие строки (например, заголовки и исключения) создаются, каждая с собственным выделенным оператором return next. Но другие вычисляются в цикле курсора for для встраивания результатов запроса в отчет.
Табличные функции PostgreSQL не конвейеризированы
В настоящее время YugabyteDB использует исходный код PostgreSQL 11.2. Найдите в документации этой версии этот раздел: «43.6.1.2. RETURN NEXT и RETURN QUERY”. И найдите примечание, которое начинается так:
Текущая реализация RETURN NEXT и RETURN QUERY сохраняет весь набор результатов перед возвратом из функции…
То же самое примечание можно найти в документации PostgreSQL для версии 12.1.
Это означает, что табличная функция не ведет себя как источник строк, как обычная постоянная, временная таблица или представление, доставляя строки только по мере необходимости. Скорее, весь результирующий набор, определяемый табличной функцией, материализуется до того, как любая из его строк может быть использована. Если результирующий набор огромен, это может означать сброс на диск, вызывающий соответствующие проблемы с производительностью.
Это контрастирует с поведением так называемых конвейерных табличных функций базы данных Oracle, где результаты доставляются по запросу в стиле источника строк.
Следствием такого текущего поведения является то, что табличные функции PostgreSQL наиболее полезны, когда они выдают небольшие ограниченные наборы результатов. Этот критерий соблюдается во всех вариантах использования, которые я описываю и реализую в своем третьем посте — а таких вариантов использования очень много.
Вывод
В этом посте объясняется, как реализовать пользовательские табличные функции во всех вариантах. Но он использовал только простые примеры, которые указывают на обобщенные классы вариантов использования.
В моем третьем посте, Четыре убедительных варианта использования табличных функций PostgreSQL, я обсуждаю четыре конкретных, реалистичных варианта использования, которые вызывают пользовательские табличные функции, и объясняю, как каждый случай выполняется с помощью полного , автономные примеры кода.
Что дальше?
- Подробно сравните YugabyteDB с такими базами данных, как CockroachDB, Google Cloud Spanner и MongoDB.
- Начните с YugabyteDB на macOS, Linux, Docker и Kubernetes.
- Свяжитесь с нами, чтобы узнать больше о лицензировании, ценах или запланировать технический обзор.
Первоначально опубликовано на https://blog.yugabyte.com 16 декабря 2019 г.
