Home • Explore • Blog • CI
☰



postgresql
1st chunk of `doc/src/sgml/ref/create_aggregate.sgml`
9cb503acf699f0ce019e885ad94041e2b7b4bd438e47f5ec0000000100000fa3
<!--
doc/src/sgml/ref/create_aggregate.sgml
PostgreSQL documentation
-->

<refentry id="sql-createaggregate">
 <indexterm zone="sql-createaggregate">
  <primary>CREATE AGGREGATE</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>CREATE AGGREGATE</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>CREATE AGGREGATE</refname>
  <refpurpose>define a new aggregate function</refpurpose>
 </refnamediv>

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

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

<phrase>or the old syntax</phrase>

CREATE [ OR REPLACE ] AGGREGATE <replaceable class="parameter">name</replaceable> (
    BASETYPE = <replaceable class="parameter">base_type</replaceable>,
    SFUNC = <replaceable class="parameter">sfunc</replaceable>,
    STYPE = <replaceable class="parameter">state_data_type</replaceable>
    [ , SSPACE = <replaceable class="parameter">state_data_size</replaceable> ]
    [ , FINALFUNC = <replaceable class="parameter">ffunc</replaceable> ]
    [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , COMBINEFUNC = <replaceable class="parameter">combinefunc</replaceable> ]
    [ , SERIALFUNC = <replaceable class="parameter">serialfunc</replaceable> ]
    [ , DESERIALFUNC = <replaceable
Title: CREATE AGGREGATE Syntax
Summary
This section defines the syntax for the CREATE AGGREGATE command in PostgreSQL, which is used to define a new aggregate function. It outlines the various options and parameters available, including the function name, argument types, state transition function (SFUNC), state data type (STYPE), final function (FINALFUNC), initial condition (INITCOND), and more. The syntax includes options for ordered aggregates, hypothetical aggregates, and the older syntax using BASETYPE.