Как передать набор строк из одной функции в другую?

Обзор

Я использую PostgreSQL 9.1.14, и я пытаюсь передать результаты функции в другую функцию. Общая идея (с учетом минимального примера) заключается в том, что мы можем написать:

select * from (select * from foo ...)

и мы можем абстрагировать подвыбор в функции и выбрать из него:

create function foos() 
returns setof foo
language sql as $$
  select * from foo ...
$$;

select * from foos()

Есть ли способ абстрагироваться на один уровень дальше, чтобы иметь возможность делать что-то вроде этого (я знаю, что функции не могут иметь аргументы с типами setof):

create function more_foos( some_foos setof foo )
language sql as $$
  select * from some_foos ...  -- or unnest(some_foos), or ???
$$:

select * from more_foos(foos())

Минимальный пример и предпринятые обходные методы

Я использую PostgreSQL 9.1.14. Вот минимальный пример:

-- 1. create a table x with three rows                                                                                                                                                            
drop table if exists x cascade;
create table if not exists x (id int, name text);
insert into x values (1,'a'), (2,'b'), (3,'c');

-- 2. xs() is a function with type `setof x`
create or replace function xs()
returns setof x
language sql as $$
  select * from x
$$;

-- 3. xxs() should return the context of x, too
--    Ideally the argument would be a `setof x`,
--    but that not allowed (see below).
create or replace function xxs(x[])  
returns setof x
language sql as $$
  select x.* from x
  join unnest($1) y
       on x.id = y.id
$$;

Когда я загружаю этот код, я получаю ожидаемый вывод для определений таблиц, и я могу позвонить и выбрать из xs(), как я ожидал. Но когда я пытаюсь передать результат xs() на xxs(), я получаю сообщение об ошибке "Функция xxs (x) не существует":

db=> \i test.sql 
DROP TABLE
CREATE TABLE
INSERT 0 3
CREATE FUNCTION
CREATE FUNCTION

db=> select * from xs();
  1 | a
  2 | b
  3 | c

db=> select * from xxs(xs());
ERROR:  function xxs(x) does not exist
LINE 1: select * from xxs(xs());
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Я немного смущен о "функции xxs (x) не существует"; так как возвращаемый тип xs() был setof x, я ожидал, что его тип возврата будет setof x (или, может быть, x[]), а не x. После жалоб на тип, я могу перейти к одному из следующих, но хотя с любым определением я могу select xxs(xs());, я не могу select * from xxs(xs());.

create or replace function xxs( x )
returns setof x
language sql as $$
  select x.* from x
  join unnest(array[$1]) y    -- unnest(array[...]) seems pretty bad
       on x.id = y.id
$$;

create or replace function xxs( x )
returns setof x
language sql as $$
  select * from x
         where x.id in ($1.id)
$$;

db=> select xxs(xs());
 (1,a)
 (2,b)
 (3,c)

db=> select * from xxs(xs());
ERROR:  set-valued function called in context that cannot accept a set

Резюме

Каков правильный способ передачи результатов функции возвращающего множество в другую функцию? (Я заметил, что создать функцию... xxs (setof)... приводит к ошибке: ОШИБКА: функции не могут принимать заданные аргументы, поэтому ответ не будет буквально передаваться набор строк из одной функции в другую.)

Ответы

Ответ 1

Функции таблицы

Я выполняю очень высокую скорость, сложные миграции баз данных для жизни, используя SQL как язык клиента и сервера (не используется какой-либо другой язык), все на стороне сервера, где код редко выходит из механизма базы данных. Функции таблицы играют большую роль в моей работе. Я не использую "курсоры", так как они слишком медленны, чтобы соответствовать моим требованиям к производительности, и все, что я делаю, ориентировано на результат. Функции таблицы оказали мне огромную помощь в полном устранении использования курсоров, достижении очень высокой скорости и значительно способствовали уменьшению объема кода и улучшению простоты.

Короче говоря, вы используете запрос, который ссылается на две (или более) функции таблицы для передачи данных из одной таблицы в другую. Набор результатов запроса выбора, вызывающий функции таблицы, служит в качестве канала для передачи данных из одной таблицы в следующую. На платформе/версии DB2 я работаю, и она появляется на основе быстрого посмотрите в руководстве 9.1 Postgres, что там же верно, вы можете передавать только одну строку значений столбцов в качестве входных данных для любого вызова вызовов в таблице, как вы обнаружили. Однако, поскольку вызов функции таблицы происходит в середине обработки набора результатов запроса, вы достигаете того же эффекта передачи всего набора результатов каждому вызову функции таблицы, хотя в системе обработки данных базы данных данные передаются только одна строка за раз для каждой функции таблицы.

Функции таблицы принимают одну строку входных столбцов и возвращают один результат обратно в вызывающий запрос (т.е. select), который вызывает функцию. Столбцы набора результатов, возвращенные из функции таблицы, становятся частью набора результатов вызывающего запроса и поэтому доступны в качестве входных данных для следующей функции таблицы, упомянутой позже в том же запросе, как правило, в качестве последующего объединения, Первые столбцы результатов функции таблицы подаются как входные данные (по одной строке за раз) во вторую функцию таблицы, которая возвращает столбцы набора результатов в набор результатов вызывающего запроса. И первый, и второй столбец результатов функции таблицы теперь являются частью набора результатов вызывающего запроса и теперь доступны как вход (по одной строке за раз) для третьей функции таблицы. Каждый вызов функции таблицы расширяет набор результатов вызывающего запроса через столбцы, которые он возвращает.. Это может продолжаться до тех пор, пока вы не нажмете ограничения на ширину набора результатов, который, вероятно, будет варьироваться от одного механизма базы данных до следующий.

Рассмотрим этот пример (который может не соответствовать синтаксическим требованиям или возможностям Postgres при работе с DB2). Это один из многих шаблонов проектирования, в котором я использую функции таблицы, является одним из более простых, который я считаю очень показательным, и тот, который, как я ожидал, имел бы широкую привлекательность , если функции таблицы были в тяжелом (насколько мне известно, это не так, но я думаю, что они заслуживают большего внимания, чем получают).

В этом примере используемые функции таблицы: VALIDATE_TODAYS_ORDER_BATCH, POST_TODAYS_ORDER_BATCH и DATA_WAREHOUSE_TODAYS_ORDER_BATCH. В версии DB2, над которой я работаю, вы закрываете функцию таблицы внутри "TABLE (вызов функции таблицы таблицы и параметры здесь)", но, основываясь на быстром просмотре руководства Postgres, вы опускаете оболочку "TABLE()".

create table TODAYS_ORDER_PROCESSING_EXCEPTIONS as (

select      TODAYS_ORDER_BATCH.*
           ,VALIDATION_RESULT.ROW_VALID
           ,POST_RESULT.ROW_POSTED
           ,WAREHOUSE_RESULT.ROW_WAREHOUSED

from        TODAYS_ORDER_BATCH

cross join  VALIDATE_TODAYS_ORDER_BATCH ( ORDER_NUMBER, [either pass the remainder of the order columns or fetch them in the function]  ) 
              as VALIDATION_RESULT ( ROW_VALID )  --example: 1/0 true/false Boolean returned

left join   POST_TODAYS_ORDER_BATCH ( ORDER_NUMBER, [either pass the remainder of the order columns or fetch them in the function] )
              as POST_RESULT ( ROW_POSTED )  --example: 1/0 true/false Boolean returned
      on    ROW_VALIDATED = '1'

left join   DATA_WAREHOUSE_TODAYS_ORDER_BATCH ( ORDER_NUMBER, [either pass the remainder of the order columns or fetch them in the function] )
              as WAREHOUSE_RESULT ( ROW_WAREHOUSED )  --example: 1/0 true/false Boolean returned
      on    ROW_POSTED = '1'

where       coalesce( ROW_VALID,      '0' ) = '0'   --Capture only exceptions and unprocessed work.  
      or    coalesce( ROW_POSTED,     '0' ) = '0'   --Or, you can flip the logic to capture only successful rows.
      or    coalesce( ROW_WAREHOUSED, '0' ) = '0'

) with data
  • Если таблица TODAYS_ORDER_BATCH содержит 1 000 000 строк, то VALIDATE_TODAYS_ORDER_BATCH будет называться 1,000,000 раз, один раз для каждый ряд.
  • Если 900 000 строк проходят проверку внутри VALIDATE_TODAYS_ORDER_BATCH, тогда POST_TODAYS_ORDER_BATCH будет называться 900 000 раз.
  • Если успешно опубликовано только 850 000 строк, то VALIDATE_TODAYS_ORDER_BATCH нуждается в некоторых лазейках, закрытых LOL, а DATA_WAREHOUSE_TODAYS_ORDER_BATCH будет называться 850 000 раз.
  • Если 850 000 строк успешно попали в хранилище данных (т.е. никаких дополнительных исключений не было создано), то таблица TODAYS_ORDER_PROCESSING_EXCEPTIONS будет заполнена 1 000 000 - 850 000 = 150 000 строк исключений.

Вызов функции таблицы в этом примере возвращает только один столбец, но они могут возвращать много столбцов. Например, функция таблицы, проверяющая строку порядка, может вернуть причину неудачной проверки порядка.

В этом проекте практически вся болтовня между HLL и базой данных устранена, так как запросчик HLL просит базу данных обрабатывать всю партию в ОДНОМ запросе. Это приводит к сокращению миллионов запросов SQL к базе данных, в ОГРОМНОМ удалении миллионов вызовов процедур или вызовов HLL и, как результат, обеспечивает ОГРОМНОЕ улучшение выполнения. Напротив, устаревший код, который часто обрабатывает одну строку за раз, обычно отправляет 1 000 000 выборочных SQL-запросов, 1 для каждой строки в TODAYS_ORDER_BATCH, плюс не менее 1 000 000 запросов HLL и/или SQL для целей проверки, плюс не менее 1 000 000 HLL и/или SQL-запросы для целей проводки, плюс 1 000 000 запросов HLL и/или SQL для отправки заказа в хранилище данных. Конечно, используя эту конструкцию табличной функции, внутри функции таблицы запросы SQL отправляются в базу данных, но когда база данных делает запросы к себе (т.е. Изнутри функции таблицы), запросы SQL обслуживаются гораздо быстрее (особенно по сравнению с устаревший сценарий, когда запросчик HLL выполняет обработку одной строки из удаленной системы, причем наихудший случай над WAN-OMG, пожалуйста, не делайте этого).

Вы можете легко столкнуться с проблемами производительности, если используете функцию таблицы для "выборки набора результатов", а затем присоедините этот результирующий набор к другим таблицам. В этом случае оптимизатор SQL не может предсказать, какой набор строк будет возвращен из функции таблицы, и поэтому он не может оптимизировать объединение к последующим таблицам. По этой причине я редко использую их для получения набора результатов, если только я не знаю, что набор результатов будет очень маленьким числом строк, следовательно, это не вызовет проблемы с производительностью, или мне не нужно присоединяться к последующим таблицам.

По моему мнению, одна из причин недоиспользования функций таблицы заключается в том, что они часто воспринимаются как инструмент для набора результатов, который часто выполняется плохо, поэтому они списываются как "плохой" инструмент для использования.

Функции таблицы чрезвычайно полезны для того, чтобы направить больше функций на сервер, устраняя большую часть болтовни между сервером базы данных и программами на удаленных системах и даже устраняя болтовню между сервером базы данных и внешними программами на том же сервере. Даже болтовня между программами на одном сервере несет больше накладных расходов, чем многие люди понимают, и большая часть из них не нужна. Сердце силы функций таблицы заключается в их использовании для выполнения действий внутри обработки набора результатов.

Существуют более продвинутые шаблоны проектирования для использования функций таблицы, которые основываются на вышеприведенном шаблоне, где вы можете максимизировать обработку набора результатов еще больше, но этот пост для большинства больше всего подходит.