CREATE AGGREGATE

примечание

Эта страница переведена при помощи нейросети GigaChat.

CREATE AGGREGATE – создание новой агрегатной функции.

Синтаксис

CREATE [ OR REPLACE ] AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , SSPACE = state_data_size ]
    [ , FINALFUNC = ffunc ]
    [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , COMBINEFUNC = combinefunc ]
    [ , SERIALFUNC = serialfunc ]
    [ , DESERIALFUNC = deserialfunc ]
    [ , INITCOND = initial_condition ]
    [ , MSFUNC = msfunc ]
    [ , MINVFUNC = minvfunc ]
    [ , MSTYPE = mstate_data_type ]
    [ , MSSPACE = mstate_data_size ]
    [ , MFINALFUNC = mffunc ]
    [ , MFINALFUNC_EXTRA ]
    [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , MINITCOND = minitial_condition ]
    [ , SORTOP = sort_operator ]
    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
)

CREATE [ OR REPLACE ] AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ]
                        ORDER BY [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , SSPACE = state_data_size ]
    [ , FINALFUNC = ffunc ]
    [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , INITCOND = initial_condition ]
    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
    [ , HYPOTHETICAL ]
)

or the old syntax

CREATE [ OR REPLACE ] AGGREGATE name (
    BASETYPE = base_type,
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , SSPACE = state_data_size ]
    [ , FINALFUNC = ffunc ]
    [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , COMBINEFUNC = combinefunc ]
    [ , SERIALFUNC = serialfunc ]
    [ , DESERIALFUNC = deserialfunc ]
    [ , INITCOND = initial_condition ]
    [ , MSFUNC = msfunc ]
    [ , MINVFUNC = minvfunc ]
    [ , MSTYPE = mstate_data_type ]
    [ , MSSPACE = mstate_data_size ]
    [ , MFINALFUNC = mffunc ]
    [ , MFINALFUNC_EXTRA ]
    [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , MINITCOND = minitial_condition ]
    [ , SORTOP = sort_operator ]
)

Описание

CREATE AGGREGATE определяет новую агрегатную функцию. CREATE OR REPLACE AGGREGATE либо определит новую агрегатную функцию, либо заменит существующее определение. Некоторые основные и часто используемые агрегатные функции включены в дистрибутив; они описаны в «Агрегатные функции». Если кто-то определяет новые типы или нуждается в агрегатной функции, еще не предоставленной, то CREATE AGGREGATE можно использовать для предоставления желаемых функций.

При замене существующего определения типы аргументов, тип результата и количество прямых аргументов изменяться не могут. Кроме того, новое определение должно быть такого же рода (обычная агрегатная функция, упорядоченная агрегатная функция или гипотетическая агрегатная функция), что и старое.

Если указано имя схемы (например, CREATE AGGREGATE myschema.myagg ...), то агрегатная функция создается в указанной схеме. В противном случае она создается в текущей схеме.

Агрегатная функция идентифицируется по ее имени и типу(ам) входных данных. Две агрегированные функции в одной и той же схеме могут иметь одно и то же имя, если они работают с разными типами ввода. Имя и тип(ы) входных данных агрегата также должны отличаться от имени и типа(ов) входных данных каждой обычной функции в той же схеме. Это поведение идентично перегрузке обычных имен функций (см. CREATE FUNCTION).

Простая агрегатная функция состоит из одной или двух обычных функций: функции перехода состояния sfunc и необязательной конечной функции расчета ffunc. Они используются следующим образом:

sfunc( internal-state, next-data-values ) ---> next-internal-state
ffunc( internal-state ) ---> aggregate-value

PostgreSQL создает временную переменную типа данных stype для хранения текущего внутреннего состояния агрегата. В каждой строке ввода вычисляются значения аргументов агрегата, и функция перехода состояния вызывается с текущим значением состояния и новыми значениями аргумента(ов), чтобы вычислить новое внутреннее значение состояния. После обработки всех строк финальная функция вызывается один раз для расчета возвращаемого значения агрегата. Если конечная функция отсутствует, то конечное значение состояния возвращается как есть.

Агрегатная функция может предоставить начальное условие, т.е. начальное значение для внутреннего значения состояния. Это указывается и хранится в базе данных как значение типа text, но оно должно быть допустимым внешним представлением константы типа значения состояния. Если он не указан, то значение состояния начинается с нуля.

Если функция перехода состояния объявлена "строгая", то она не может быть вызвана с пустыми входными данными. С такой функцией перехода агрегат работает следующим образом. Строки с любыми пустыми значениями входных данных игнорируются (функция не вызывается, и предыдущее значение состояния сохраняется). Если начальное значение состояния равно нулю, то при первой строке со всеми непустыми входными значениями первое значение аргумента заменяет значение состояния, а функция перехода вызывается для каждой последующей строки со всеми непустыми входными значениями. Это удобно для реализации агрегатов, таких как max. Обратите внимание, что такое поведение доступно только тогда, когда state_data_type совпадает с первым arg_data_type. Когда эти типы различаются, необходимо указать ненулевое начальное условие или использовать не строгую функцию перехода.

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

Если конечная функция объявлена «строгая», то она не будет вызвана, когда значение конечного состояния равно нулю; вместо этого автоматически будет возвращен нулевой результат. (Конечно, это просто нормальное поведение строгих функций.) В любом случае у конечной функции есть возможность вернуть нулевое значение. Например, конечная функция для avg возвращает ноль, когда видит, что входных строк было ноль.

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

Агрегат может дополнительно поддерживать режим движущегося агрегирования, как описано в разделе «Режим движущегося агрегирования». Это требует указания параметров MSFUNC, MINVFUNC и MSTYPE, а также необязательных параметров MSSPACE, MFINALFUNC, MFINALFUNC_EXTRA, MFINALFUNC_MODIFY и MINITCOND. За исключением MINVFUNC, эти параметры работают аналогично соответствующим простым агрегатным параметрам без M; они определяют отдельную реализацию агрегата, включающую обратную переходную функцию.

Синтаксис с ORDER BY в списке параметров создает специальный тип агрегата, называемый упорядоченным агрегатом; или если HYPOTHETICAL указан, то создается гипотетический агрегат. Эти агрегаты работают над группами отсортированных значений зависимыми от порядка способами, так что указание входного порядка сортировки является важной частью вызова. Кроме того, они могут иметь прямые аргументы, которые являются аргументами, которые оцениваются только один раз для каждого объединения вместо одного раза для каждой строки ввода. Гипотетические агрегаты являются подклассом упорядоченных агрегатов, в которых некоторые из прямых аргументов должны соответствовать количеству и типу данных столбцов агрегированных аргументов. Это позволяет значениям этих прямых аргументов быть добавленными к коллекции строк агрегатного ввода в качестве дополнительной «гипотетической» строки.

Агрегат может дополнительно поддерживать частичную агрегацию, как описано в разделе «Частичная агрегация». Для этого требуется указать параметр COMBINEFUNC. Если state_data_type равен internal, обычно также целесообразно предоставить параметры SERIALFUNC и DESERIALFUNC, чтобы была возможна параллельная агрегация. Обратите внимание, что агрегат также должен быть помечен как PARALLEL SAFE, чтобы разрешить параллельную агрегацию.

Агрегаты, которые ведут себя как MIN или MAX, иногда могут быть оптимизированы путем просмотра индекса вместо сканирования каждой входной строки. Если этот агрегат может быть таким образом оптимизирован, укажите это, указав оператор сортировки. Основное требование заключается в том, что агрегат должен выдавать первый элемент в порядке сортировки, индуцированном оператором; другими словами:

SELECT agg(col) FROM tab;

должно быть эквивалентно:

SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;

Дополнительные предположения заключаются в том, что агрегат игнорирует пустые входы и возвращает пустой результат тогда и только тогда, когда не было непустых входов. Обычно оператор < типа данных является правильным оператором сортировки для MIN, а > является правильным оператором сортировки для MAX. Обратите внимание, что оптимизация никогда фактически не вступит в силу, если указанный оператор не является членом стратегии «меньше чем» или «больше чем» класса операторов индекса B-дерева.

Чтобы иметь возможность создать агрегатную функцию, необходимо иметь привилегию USAGE на типы аргументов, тип(ы) состояния и возвращаемый тип, а также привилегию EXECUTE на поддерживающие функции.

Параметры

name

Имя агрегатной функции, при необходимости дополненное схемой, которая должна быть создана.

argmode

Режим аргумента: IN или VARIADIC. Агрегатные функции не поддерживают аргументы OUT. Если опущено, используется значение по умолчанию IN. Только последний аргумент может быть помечен как VARIADIC.

argname

Имя аргумента. В настоящее время это полезно только для целей документирования. Если опустить, у аргумента нет имени.

arg_data_type

Тип входных данных, над которым работает эта агрегатная функция. Чтобы создать агрегатную функцию без аргументов, напишите * вместо списка спецификаций аргументов. (Примером такой агрегатной функции является count(*).)

base_type

В старом синтаксисе для CREATE AGGREGATE, тип входных данных указывается параметром basetype, а не записывается рядом с именем агрегата. Обратите внимание, что этот синтаксис позволяет использовать только один параметр ввода. Чтобы определить агрегатную функцию без аргументов с этим синтаксисом, укажите basetype как "ANY" (не *). Агрегаты упорядоченного набора не могут быть определены с использованием старого синтаксиса.

sfunc

Имя функции перехода состояния, которая будет вызываться для каждой строки ввода. Для нормальной агрегатной функции с N аргументами функция sfunc должна принимать N+1 аргументы, первый из которых имеет тип state_data_type, а остальные соответствуют объявленным типам входных данных агрегата. Функция должна возвращать значение типа state_data_type. Эта функция принимает текущее значение состояния и текущие значения входных данных и возвращает следующее значение состояния.

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

state_data_type

Тип данных для значения состояния агрегата.

state_data_size

Приблизительный средний размер (в байтах) агрегатного значения состояния. Если этот параметр опущен или равен нулю, используется оценка по умолчанию, основанная на state_data_type. Планировщик использует это значение для оценки памяти, необходимой для агрегированного запроса с группировкой.

ffunc

Имя конечной функции, вызываемой для вычисления результата агрегата после обработки всех входных строк. Для обычного агрегата эта функция должна принимать один аргумент типа state_data_type. Тип возвращаемых данных агрегата определяется как тип возврата этой функции. Если ffunc не указан, то конечное состояние используется в качестве результата агрегата, и тип возврата - state_data_type.

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

Если FINALFUNC_EXTRA указан, то помимо конечного значения состояния и любых прямых аргументов, конечная функция получает дополнительные пустые значения, соответствующие обычным (агрегированным) аргументам агрегата. Это полезно главным образом для того, чтобы обеспечить правильное разрешение типа результата агрегата при определении полиморфного агрегата.

FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }

Этот параметр указывает, является ли конечная функция чистой функцией, которая не изменяет свои аргументы. READ_ONLY указывает, что нет; два других значения указывают на то, что он может изменить значение переходного состояния. Смотрите «Примечания» ниже для получения более подробной информации. По умолчанию используется READ_ONLY, за исключением агрегированных множеств, для которых по умолчанию используется READ_WRITE.

combinefunc

Функция combinefunc может быть указана опционально, чтобы разрешить агрегатной функции поддерживать частичную агрегацию. Если она предоставлена, то combinefunc должна объединять два значения state_data_type, каждое из которых содержит результат агрегирования некоторой подмножества входных значений, чтобы получить новое значение state_data_type, которое представляет собой результат агрегирования обоих наборов входных данных. Эту функцию можно рассматривать как sfunc, где вместо того, чтобы действовать над отдельной строкой ввода и добавлять ее к текущему агрегатному состоянию, она добавляет другое агрегатное состояние к текущему состоянию.

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

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

serialfunc

Агрегатная функция, у которой state_data_type является internal, может участвовать в параллельной агрегации только при наличии функции serialfunc, которая должна сериализовать состояние агрегата в значение bytea для передачи другому процессу. Эта функция должна принимать один аргумент типа internal и возвращать тип bytea. Также требуется соответствующая функция deserialfunc.

deserialfunc

Десериализация ранее сериализованного состояния агрегата обратно в state_data_type. Эта функция должна принимать два аргумента типов bytea и internal и производить результат типа internal. Примечание: второй аргумент internal не используется, но он необходим по причинам безопасности типов.

initial_condition

Начальное значение для состояния. Это должно быть строковая константа в форме, принятой для типа данных state_data_type. Если не указано, начальное значение состояния равно нулю.

msfunc

Имя функции прямого перехода состояния для вызова каждой входной строки в режиме движущегося агрегата. Это точно так же, как обычная функция перехода, за исключением того, что ее первый аргумент и результат имеют тип mstate_data_type, который может отличаться от state_data_type.

minvfunc

Имя обратной функции перехода состояния, используемой в режиме движущегося агрегата. Эта функция имеет те же типы аргументов и результатов, что и msfunc, но она используется для удаления значения из текущего агрегатного состояния, а не для добавления значения к нему. Обратная функция перехода должна иметь тот же атрибут строгости, что и прямая функция перехода состояния.

mstate_data_type

Тип данных для значения состояния агрегата при использовании режима движущегося агрегата.

mstate_data_size

Приблизительный средний размер (в байтах) значения состояния агрегата при использовании режима движущегося агрегата. Это работает так же, как state_data_size.

mffunc

Имя конечной функции, вызываемой для вычисления результата агрегата после того, как были пройдены все входные строки, при использовании режима движущегося агрегирования. Это работает так же, как ffunc, за исключением того, что тип его первого аргумента - mstate_data_type и дополнительные фиктивные аргументы указываются путем записи MFINALFUNC_EXTRA. Тип агрегатного результата, определяемый mffunc или mstate_data_type, должен соответствовать тому, который определяется обычной реализацией агрегата.

MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }

Этот параметр аналогичен FINALFUNC_MODIFY, но он описывает поведение конечной функции движущегося агрегирования.

minitial_condition

Начальное значение для состояния, когда используется режим движущегося агрегирования. Это работает так же, как initial_condition.

sort_operator

Связанный оператор сортировки для агрегата типа MIN или MAX. Это просто имя оператора, при необходимости дополненное схемой. Предполагается, что у оператора такие же типы входных данных, что и у агрегата (который должен быть одноаргументным нормальным агрегатом).

PARALLEL = { SAFE | RESTRICTED | UNSAFE }

Значения PARALLEL SAFE, PARALLEL RESTRICTED и PARALLEL UNSAFE такие же, как в CREATE FUNCTION. Агрегат не будет рассматриваться для параллелизации, если он помечен как PARALLEL UNSAFE (что является значением по умолчанию!) или PARALLEL RESTRICTED. Обратите внимание, что отметки о безопасности параллельного выполнения вспомогательных функций агрегата планировщиком не рассматриваются; рассматривается только отметка самого агрегата.

HYPOTHETICAL

Только для агрегированных наборов с упорядоченным набором этот флаг указывает, что аргументы агрегата должны обрабатываться в соответствии с требованиями к агрегатам гипотетических наборов: то есть последние несколько прямых аргументов должны соответствовать типам данных агрегированных (WITHIN GROUP) аргументов. Флаг HYPOTHETICAL не влияет на поведение во время выполнения, а только на разрешение типов данных и сопоставление аргументов агрегата при синтаксическом анализе.

Параметры CREATE AGGREGATE могут быть записаны в любом порядке, а не только в том порядке, который проиллюстрирован выше.

Примечания

В параметрах, которые указывают имена функций поддержки, можно написать имя схемы при необходимости, например SFUNC = public.sum. Не указывайте там типы аргументов - типы аргументов функций поддержки определяются из других параметров.

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

Аналогично, хотя обычно от конечной функции агрегации ожидается, что она не будет изменять свои входные значения, иногда бывает непрактично избежать изменения аргумента состояния перехода. Такое поведение должно быть объявлено с использованием параметра FINALFUNC_MODIFY. Значение READ_WRITE указывает на то, что конечная функция изменяет состояние перехода неопределенным образом. Это значение предотвращает использование агрегата в качестве оконной функции и также предотвращает слияние состояний перехода для вызовов агрегатов, которые имеют одинаковые входные значения и функции перехода. Значение SHAREABLE указывает на то, что после конечной функции нельзя применять функцию перехода, но можно выполнить несколько вызовов конечных функций на конечном значении состояния перехода. Это значение предотвращает использование агрегата в качестве оконной функции, но позволяет объединять состояния перехода. (То есть оптимизация, которая здесь интересует, заключается не в применении одной и той же конечной функции повторно, а в применении различных конечных функций к одному и тому же конечному значению состояния перехода. Это разрешено до тех пор, пока ни одна из конечных функций не помечена как READ_WRITE).

Если агрегат поддерживает режим движущегося агрегата, это повысит эффективность вычислений при использовании агрегата в качестве оконной функции для окна с подвижным началом кадра (т.е. режима начала кадра, отличного от UNBOUNDED PRECEDING). Концептуально функция прямого перехода добавляет входные значения к состоянию агрегата, когда они входят в кадр окна снизу, и обратная функция перехода удаляет их снова, когда они покидают кадр сверху. Таким образом, когда значения удаляются, они всегда удаляются в том же порядке, в котором были добавлены. Всякий раз, когда вызывается обратная функция перехода, она будет получать самое раннее добавленное, но еще не удаленное значение аргумента(ов). Обратная функция перехода может предположить, что после удаления самой старой строки в текущем состоянии останется хотя бы одна строка. (Когда этого не произойдет, механизм оконной функции просто начинает новую агрегацию вместо использования обратной функции перехода.)

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

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

Синтаксис агрегатов упорядоченных наборов допускает указание VARIADIC как для последнего прямого параметра, так и для последнего агрегированного (WITHIN GROUP) параметра. Однако текущая реализация ограничивает использование VARIADIC двумя способами. Во-первых, агрегаты упорядоченного набора могут использовать только VARIADIC "any", а не другие типы массивов с переменным числом аргументов. Во-вторых, если последний прямой параметр является VARIADIC "any", то может быть только один агрегированный параметр, и он также должен быть VARIADIC "any". (В представлении, используемом в системных каталогах, эти два параметра объединены в один элемент VARIADIC "any", поскольку pg_proc не может представлять функции с более чем одним параметром VARIADIC). Если агрегат является гипотетическим агрегатом, то прямые аргументы, соответствующие параметру VARIADIC "any", являются гипотетическими; любые предыдущие параметры представляют собой дополнительные прямые аргументы, которые не обязаны соответствовать агрегированным аргументам.

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

Частичная (включая параллельную) агрегация в настоящее время не поддерживается для агрегатов упорядоченного множества. Кроме того, она никогда не будет использоваться для вызовов агрегатных функций, включающих предложения DISTINCT или ORDER BY, поскольку эти семантики не могут быть поддержаны во время частичной агрегации.

Примеры

Смотрите «Агрегаты, определяемые пользователем».

Совместимость

CREATE AGGREGATE является расширением языка PostgreSQL. Стандарт SQL не предусматривает агрегатные функции, определяемые пользователем.

Также смотрите

ALTER AGGREGATE, DROP AGGREGATE