Добро пожаловать в первую из трех статей о табличных функциях PostgreSQL. Эти функции можно легко использовать в распределенной базе данных SQL, такой как YugabyteDB, которая совместима с PostgreSQL.
Эта серия является продолжением моего поста Использование хранимых процедур в распределенных базах данных SQL. В этой серии постов мы рассмотрим:
- Что такое табличные функции и почему они полезны
- Продемонстрировать использование некоторых встроенных табличных функций SQL.
- Представьте, как вы можете реализовать пользовательскую табличную функцию, если ни одна встроенная не соответствует вашим потребностям, так же, как вы реализуете обычные хранимые процедуры и функции.
Что такое YugabyteDB? Это высокопроизводительная распределенная база данных SQL с открытым исходным кодом, созданная на основе масштабируемой и отказоустойчивой архитектуры, вдохновленной Google Spanner. YugabyteDB использует собственное специальное распределенное хранилище документов под названием DocDB. Но он обеспечивает функциональность SQL и хранимых процедур, повторно используя верхнюю половину стандартного исходного кода PostgreSQL. Это объясняется в двух частях сообщения блога Распределенный PostgreSQL на архитектуре Google Spanner: (1) Уровень хранения; и (2) Слой запроса.
Обратите внимание, что все, что я говорю в этой серии из трех частей, относится как к vanilla PostgreSQL, так и к YugabyteDB. Все примеры кода, которые я описываю, дают одинаковый результат в обеих средах. Никаких особых соображений относительно распределенного SQL нам не потребуется.
Что такое табличные функции?
В контексте языков программирования в целом мы привыкли к тому, что функция может быть вызвана в любом месте синтаксиса, где допустимо выражение, и что она вычисляет единственное значение, которое зависит от фактических аргументов, с которыми она вызывается. Значение может быть составным, как запись или массив записей, но, тем не менее, это одно значение.
Базы данных SQL допускают новый тип функций. Табличная функция — это функция, которая возвращает вычисленный набор строк, зависящий от фактических аргументов, с которыми она вызывается. Поэтому его можно использовать в списке from подзапроса в произвольной комбинации с таблицами и представлениями; и подзапрос, чей список from включает табличную функцию, может использоваться в любом месте синтаксиса в операторе SQL, где может использоваться подзапрос.
YugabyteDB наследует набор встроенных функций SQL от PostgreSQL. Среди них несколько встроенных табличных функций. Он также наследует возможность реализации пользовательских табличных функций. Вы делаете это, используя те же самые конструкции, с помощью которых вы реализуете обычные хранимые процедуры и функции.
Встроенные табличные функции SQL
В этом разделе я опишу три встроенные табличные функции SQL.
генерировать_серии()
Наиболее очевидным примером встроенной табличной функции SQL является generate_series(). Он возвращает таблицу с одним столбцом типа данных integer. Он предоставляет полезный способ генерации тестовых данных, если требуемый шаблон можно указать с помощью выражений SQL, например:
create table t(k int primary key, v varchar(20)); insert into t(k, v) select a.v*100, case a.v % 3 when 1 then ‘t_’||ltrim(to_char(a.v, ‘09’)) when 2 then ‘t_’||ltrim(to_char(a.v*2, ‘09’)) else ‘t_42’ end from (select generate_series(1, 9) as v) as a;
Он генерирует эти строки:
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.
раскладывать()
Другой пример — unnest(). Это генерирует таблицу из массива значений строк. Вот пример. Он использует ту же таблицу t.
insert into t(k, v) select k, v from unnest( array[(1, 'one'), (2, 'two'), (3, 'three')]::t[]);
Обратите внимание на приведение типа “::t[]”. Здесь "t" используется как имя типа. Возможно, вы не осознавали, что таблицы и типы находятся в разных пространствах имен, поэтому таблица и тип могут называться t. И, возможно, вы не осознавали, что при создании таблицы также неявно создается тип с тем же именем и формой строки таблицы.
В примере использования Компактный синтаксис для массовой вставки в моем третьем посте из этой серии я покажу, как, когда вы используете вставить в… выбрать… из unnest( …) в качестве статического оператора SQL в процедуре PL/pgSQL, вы можете вставить набор произвольно вычисляемых программным способом строк с помощью одного оператора SQL. В этом случае аргументом unnest() будет локальная переменная или формальный параметр, тип данных которого представляет собой массив вставляемых записей (или скалярных значений).
Встроенная функция unnest() также полезна в предложении where подзапроса, когда аргумент предиката in является подзапросом.
select k, v from t where k in (select unnest from unnest(array[1, 2, 3])) order by k;
ценности()
Я включаю это для полноты картины. Его полезность ограничена, потому что он должен иметь явный литеральный кортеж для каждой вставляемой строки. Это означает, что вы не можете использовать его, если вы не знаете количество вычисляемых строк до момента выполнения. Сказав это, я видел, как люди описывали, как генерировать текст оператора вставки, который использует определенное во время выполнения количество кортежей, а затем выполнять его динамически. Мне это кажется бессмысленным, потому что unnest() позволяет вам реализовать предполагаемое поведение с помощью инструкции SQL, которая фиксируется во время компиляции. Вот пример, снова использующий ту же таблицу t:
insert into t(k, v) select column1 as k, column2 as v from (values(4, 'four'), (5, 'five'), (6, 'six')) as a;
Вы, вероятно, больше привыкли видеть опущенную форму этого синтаксиса:
insert into t(k, v) values(4, 'four'), (5, 'five'), (6, 'six');
Пользовательские табличные функции
Определяемая пользователем функция plpgsql на обычном языке реализуется с помощью простого оператора return. Его аргумент может быть только одним значением. Пользовательская табличная функция, напротив, реализуется с помощью либо return next, чтобы вернуть следующую явно вычисленную строку, либо return query, чтобы вернуть весь результирующий набор оператор выбрать. Вы можете определить оператор, используя либо статический SQL, либо динамический SQL. Вы также можете реализовать табличную функцию language sql, полностью определив тело с помощью одного статического оператора SQL select. (Вы можете использовать один из формальных параметров функции в месте синтаксиса в операторе select, где требуется значение.)
Мой второй пост Реализация пользовательских табличных функций PostgreSQL в YugabyteDB подробно освещает эту тему.
Безобидное любопытство
Оказывается, можно использовать обычную функцию в списке from оператора SQL, даже если (по определению) она возвращает только одно значение. Вот пример, в котором используется встроенная функция SQL:
select sqrt from sqrt(4);
Конечно, вы можете сделать то же самое с обычной пользовательской функцией. Возможность сделать это, кажется, является следствием широко уважаемой парадигмы программирования:
Не пишите защитный код (и не берите на себя его технический долг), чтобы помешать пользователю сделать что-то бессмысленное, но безвредное.
Скорее пусть пользователь решит не заниматься бессмысленным делом! В этом случае использования (оценка функции SQL в клиентской программе) проще и понятнее использовать голый синтаксис "select sqrt(4)":
Вывод
Когда вы выполняете высокоуровневый проект реализации для приложения, использующего базу данных SQL (распределенную или монолитную), шансы выбрать наилучший проект максимально увеличиваются, когда вы полностью понимаете возможности каждого из ваших основных модулей (программируемый браузер, балансировщик нагрузки, сервер приложений, пул соединений, база данных и т. д.). Так что, кроме всего прочего, нужно понимать весь спектр возможностей реализации бизнес-логики внутри БД. Эти знания охватывают язык SQL, встроенные функции SQL и все, что касается реализации хранимых процедур. В частности, поэтому вам нужно знать о табличных функциях. Когда вариант использования вызывает табличную функцию, альтернативные реализации обычно громоздки для программирования и неэффективны для выполнения.
Поэтому я призываю вас прочитать другие сообщения в этой серии. Мой второй пост объясняет как реализовать пользовательские табличные функции во всех вариантах. Он использует голые примеры, которые поэтому несколько нереалистичны. В моем третьем посте обсуждаются четыре реалистичных варианта использования, которые требуют пользовательских табличных функций и объясняется, как выполняется каждый случай, с помощью полных, автономных примеров кода.
Что дальше?
- Подробно сравните YugabyteDB с такими базами данных, как CockroachDB, Google Cloud Spanner и MongoDB.
- Начните с YugabyteDB на macOS, Linux, Docker и Kubernetes.
- Свяжитесь с нами, чтобы узнать больше о лицензировании, ценах или запланировать технический обзор.
Первоначально опубликовано на https://blog.yugabyte.com 16 декабря 2019 г.
