Добро пожаловать в третью часть серии из трех статей о табличных функциях PostgreSQL. Эти функции можно легко использовать в распределенной базе данных SQL, такой как YugabyteDB, которая совместима с PostgreSQL.
В этом заключительном посте я рассмотрю четыре реалистичных варианта использования, которые вызывают пользовательские табличные функции, и объясню, как выполняется каждый случай, используя полные, автономные примеры кода.
Если вы пропустили два предыдущих поста, обязательно ознакомьтесь с:
— Введение в табличные функции PostgreSQL в YugabyteDB
— Реализация пользовательских табличных функций PostgreSQL в YugabyteDB
Вариант использования №1: параметризованное представление
В своем сообщении в блоге Использование хранимых процедур в распределенных базах данных SQL я описал эксперимент по измерению скорости транзакций с переменным количеством операторов вставки на транзакцию, когда они реализованы соответственно как последовательность вызовов базы данных верхнего уровня или как один вызов хранимой процедуры, которая имеет тот же эффект. Я рассчитал отношение скорости вызова процедуры к вызову верхнего уровня в зависимости от количества вставок на транзакцию. Я сделал это, записав результаты каждого теста в таблицу, а затем запустив код, как описано в этом разделе.
Эта таблица представляет собой стилизованную версию таблицы, которую я использовал:
create table results(
test_no int,
method varchar(20),
duration dec
constraint results_duration_nn not null,
constraint results_duration_chk check(duration > 0),
constraint results_pk primary key (test_no, method));
Используйте это, чтобы вставить некоторые данные, чтобы проиллюстрировать поведение кода, который я описываю в этом разделе:
insert into results(test_no, method, duration) values (1, 'Method A', 10.1), (1, 'Method B', 8.6), (1, 'Method C', 5.4), (2, 'Method A', 20.6), (2, 'Method B', 17.3), (2, 'Method C', 9.9), (3, 'Method A', 30.4), (3, 'Method B', 23.8), (3, 'Method C', 16.2);
Эта табличная функция вычисляет коэффициенты скорости для двух указанных методов для всех значений test_no:
create function speed_ratios(
method_1 in results.method%type,
method_2 in results.method%type)
returns table(test_no results.test_no%type, ratio dec)
language sql
as $body$
select
test_no,
method_a_duration/method_b_duration as ratio from (
select
test_no,
t1.duration as method_a_duration,
t2.duration as method_b_duration
from results t1 inner join results t2
using(test_no)
where t1.method = method_1
and t2.method = method_2) as a;
$body$;
Протестируйте это так:
select
test_no as "Test No.",
to_char(ratio, '990.99')||'x' as "Speed Ratio"
from speed_ratios('Method A', 'Method B')
order by 1;
Он производит этот вывод:
Test no. | Speed Ratio
----------+-------------
1 | 1.17x
2 | 1.19x
3 | 1.28x
Конечно, вы можете создать такой отчет с помощью клиентской программы. Но это приведет к потере основного преимущества. Подход с табличными функциями позволяет запускать произвольные запросы для получения ответов на произвольные вопросы, например:
select to_char(
(select max(ratio) from speed_ratios('Method A', 'Method B'))/
(select max(ratio) from speed_ratios('Method B', 'Method C')),
'990.99')
as x;
Пример использования № 2. Красивые печатные специальные отчеты
для администраторов
В «Случай использования №1» есть три различных значения для results.test_no в примере данных, который я использую. Это означает, что необходимо рассмотреть три отдельные таблицы сравнения: «Метод A» и «Метод B», «Метод B» и «Метод C» и «Метод A» против «Метода C». Вместо того, чтобы создавать их путем выполнения сценария .sql с тремя явными операторами "select… from speed_ratios(...)", перемежающимися соответствующими метакомандами \echo, он лучше просто выполнить одну хранимую процедуру. Однако единственный способ получить вывод из обычной процедуры PL/pgSQL — использовать поднять информацию (или поднять уведомление). Когда вы запускаете такую программу из скрипта, каждая строка вывода выглядит так:
ysqlsh:raise_info_demo.sql:9: INFO: Some output
Более того, номера строк, как правило, у всех будут разные, так что вы не сможете просто удалить этот ужасный беспорядок с помощью простого «найти и заменить все» в обычном текстовом редакторе. Это особенно неприятно для разработчиков и администраторов, которые, используя другие базы данных, собирают набор сценариев и программ для создания полезных и удобных для чтения отчетов, которые, как и в данном случае использования, собирают выходные данные из нескольких отдельных запросов. Здесь на помощь приходит табличная функция. Это основная идея:
create function pretty_report_speed_ratios()
returns table(t varchar)
language plpgsql
as $body$
begin
for j in 1..5 loop
t := '';
return next;
end loop;
t := '==================================================';
return next;
t := 'Speed Ratios Report';
return next;
t := '==================================================';
return next;
t := '';
return next;
<<"loop over the method combinations">>
for j in 1..3 loop
...
end loop "loop over the method combinations";
end;
$body$;
Запустите отчет следующим образом:
select t from pretty_report_speed_ratios();
Цикл по комбинациям методов начинается так:
<<"loop over the method combinations">>
for j in 1..3 loop
declare
method_1 results.method%type;
method_2 results.method%type;
begin
case j
when 1 then
method_1 := 'Method A';
method_2 := 'Method B';
when 2 then
method_1 := 'Method B';
method_2 := 'Method C';
when 3 then
method_1 := 'Method A';
method_2 := 'Method C';
end case;
<<"do each method-pair combination">>
...
end "do each method-pair combination";
end;
end loop "loop over the method combinations";
Вот последняя недостающая часть:
<<"do each method-pair combination">>
declare
tt varchar(20);
rr varchar(20);
n int := 0;
begin
for tt, rr in (
select
to_char(test_no, '9999999'),
to_char(ratio, '99990.99')||'x'
from speed_ratios(method_1, method_2)
order by 1)
loop
n := n + 1;
t := tt||' '||rr;
if n = 1 then
t := t||' << '||quot||method_2||quot||' to '||
quot||method_1||quot;
end if;
return next;
end loop;
end "do each method-pair combination";
А вот результат, полученный с помощью pretty_report_speed_ratios():
==================================================
Speed Ratios Report
==================================================
Test No. Speed Ratio
-------- -----------
1 1.17x << 'Method B' to 'Method A'
2 1.19x
3 1.28x
1 1.59x << 'Method C' to 'Method B'
2 1.75x
3 1.47x
1 1.87x << 'Method C' to 'Method A'
2 2.08x
3 1.88x
==================================================
С первого взгляда видно, что «Метод B» примерно в 1,2 раза быстрее, чем «Метод A», а «Метод C» примерно В 1,6 раза быстрее, чем «Метод B», и, конечно же, «Метод C», следовательно, примерно в 1,9 раза быстрее, чем «Метод A» >.
Полный исходный текст табличной функции pretty_report_speed_ratios() представлен в приложении, поэтому его можно просто скопировать и вставить в ysqlsh, а затем запустить.
Пример использования № 3: Динамический список IN
Предположим, что у нас есть таблица t с таким содержимым:
k | v -----+------ 100 | t_01 200 | t_04 300 | t_42 400 | t_04 500 | t_10 600 | t_42 700 | t_07 800 | t_16 900 | t_42
Код для его создания показан в разделе Встроенные табличные функции SQL в разделе «generate_series()». Разработчики и администраторы обычно вводят явный списокin в запрос ad hoc следующим образом:
select k, v from t where k in (200, 300, 500, 900) order by 1;
Но что, если это должно быть выдано из процедуры PL/pgSQL, а члены списка in неизвестны до времени выполнения? Наивный программист склонен думать, что для этого требуется динамический SQL, в котором список in строится программно. Но есть гораздо лучший и простой в написании способ:
select k, v from t where k in (
select unnest as k
from unnest(
array[200, 300, 500, 900]::int[]))
order by 1;
Функциональность встроенной табличной функции SQL unnest() может быть реализована как определяемая пользователем табличная функция, например:
create function my_unnest(ks int[])
returns table(k int)
language plpgsql
as $body$
declare
element int;
begin
foreach element in array ks loop
k := element;
return next;
end loop;
end;
$body$;
Используйте это так:
select k, v from t where k in (
select k
from my_unnest(
array[200, 300, 500, 900]::int[]))
order by 1;
Конечно, бессмысленно просто заново реализовывать встроенную функцию unnest(). Более того, встроенный может принимать фактический аргумент любого типа данных. Я проиллюстрировал несколько возможностей в разделе, где это описывалось. Вам пришлось бы явно реализовывать каждую необходимую перегрузку, если бы вы программировали ее с помощью PL/pgSQL.
Моя цель показать вам my_unnest() заключается в следующем:
- во-первых, чтобы показать вам, как обычно перебирать элементы в массиве в PL/pgSQL;
- и, во-вторых, чтобы показать вам общую структуру табличной функции, которая вычисляет значения, которые затем можно использовать в качестве списка in в подзапросе.
Кто знает, какие произвольные вычисления вы можете захотеть использовать для создания значений списка in, и, конечно же, встроенная функция unnest() просто имеет фиксированную функциональность.
Вариант использования 4: Компактный синтаксис для массовой вставки
Рассмотрим процедуру, созданную таким образом:
create procedure insert_rows(vs in varchar[])
language plpgsql
as $body$
declare
element t.v%type;
begin
foreach element in array vs loop
insert into t(v) values(element);
end loop;
end
$body$;
Если у нас есть таблица, созданная таким образом:
create table t( k uuid default gen_random_uuid() constraint t_pk primary key, v varchar(100));
то мы можем заполнить его таким образом:
call insert_rows( array['one', 'two', 'three']);
Я использовал тот же подход "default gen_random_uuid()" для заполнения столбца первичного ключа таблицы в моем примере кода в моем посте "Использование хранимых процедур в распределенных базах данных SQL". Я указал там, что вы должны сделать gen_random_uuid() доступным, установив расширение PostgreSQL следующим образом:
create extension pgcrypto
как описано в документации YugabyteDB здесь.
Обратите внимание, что процедура просто перебирает элементы входного массива и выполняет явную однострочную операцию insert для каждого значения. Это выглядит неэффективно; но PL/pgSQL не поддерживает методы массовой привязки к операторам insert, update или delete.
Вот альтернативная реализация insert_rows(), использующая unnest():
create or replace procedure insert_rows(vs in varchar[]) language plpgsql as $body$ begin insert into t(v) select unnest from unnest(vs::varchar[]); end; $body$;
Конечно, использование этой второй реализации insert_rows() такое же, как и для первой реализации, которую я показал. Это, безусловно, короче, чем реализация, которая использует это:
foreach element in array vs loop insert into t(v) values(element); end loop;
Поэтому его легче понять и просмотреть. Однако мои тесты показали, что производительность двух реализаций и третьей, использующей my_unnest(), была одинаковой в доверительных пределах моих измерений.
Вывод
Когда у разработчиков есть богатый набор инструментов, у них гораздо больше возможностей для реализации оптимального подхода к новому варианту использования, чем когда они ограничены скудным набором инструментов. В этой серии статей из трех частей представлено несколько вариантов использования, в которых табличные функции показали свою ценность. Определяемые пользователем табличные функции являются частью более широкой картины хранимых процедур, поэтому для их использования вы должны использовать распределенную базу данных SQL, которая их поддерживает! YugabyteDB наследует поддержку PostgreSQL, и в этой серии постов я описал примеры, реализованные как в SQL, так и в PL/pgSQL. В примерах использовался очень широкий спектр функций PostgreSQL SQL и хранимых процедур. Тот факт, что весь код, который я показал, работает одинаково в YugabyteDB и vanilla PostgreSQL, является свидетельством успеха нашей стратегии использования верхней половины уровня запросов PostgreSQL просто «как есть» поверх нашего собственного уникального уровня хранения. . Эта архитектура делает YugabyteDB уникальной среди распределенных баз данных SQL.
Что дальше?
- Подробно сравните YugabyteDB с такими базами данных, как CockroachDB, Google Cloud Spanner и MongoDB.
- Начните с YugabyteDB на macOS, Linux, Docker и Kubernetes.
- Свяжитесь с нами, чтобы узнать больше о лицензировании, ценах или запланировать технический обзор.
Приложение
Вот полный исходный код варианта использования «Красиво напечатанные специальные отчеты для администраторов»:
create function pretty_report_speed_ratios()
returns table(t varchar)
language plpgsql
as $body$
declare
quot constant varchar(1) not null := '''';
begin
for j in 1..5 loop
t := '';
return next;
end loop;
t := '==================================================';
return next;
t := 'Speed Ratios Report';
return next;
t := '==================================================';
return next;
t := '';
return next;
t := 'Test No. Speed Ratio';
return next;
t := '-------- -----------';
return next;
<<"loop over the method combinations">>
for j in 1..3 loop
declare
method_1 results.method%type;
method_2 results.method%type;
begin
case j
when 1 then
method_1 := 'Method A';
method_2 := 'Method B';
when 2 then
method_1 := 'Method B';
method_2 := 'Method C';
when 3 then
method_1 := 'Method A';
method_2 := 'Method C';
end case;
t := '';
return next;
<<"do each method-pair combination">>
declare
tt varchar(20);
rr varchar(20);
n int := 0;
begin
for tt, rr in (
select
to_char(test_no, '9999999'),
to_char(ratio, '99990.99')||'x'
from speed_ratios(method_1, method_2)
order by test_no)
loop
n := n + 1;
t := tt||' '||rr;
if n = 1 then
t := t||' << '||
quot||method_2||quot||' to '||
quot||method_1||quot;
end if;
return next;
end loop;
end "do each method-pair combination";
end;
end loop "loop over the method combinations";
t := '==================================================';
return next;
for j in 1..5 loop
t := '';
return next;
end loop;
end;
$body$;
Запустите это так:
\t on select t from pretty_report_speed_ratios(); \t off
Метакоманда \t on просто отключает беспорядок в заголовке столбца (имя столбца t), его подчеркивание и нижний колонтитул с количеством строк.
Первоначально опубликовано на https://blog.yugabyte.com 16 декабря 2019 г.
