Debian Codegraph @

doc/src/sgml/ref/alter_aggregate.sgml

raw
1 <!--
2 doc/src/sgml/ref/alter_aggregate.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-alteraggregate">
7 <indexterm zone="sql-alteraggregate">
8 <primary>ALTER AGGREGATE</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>ALTER AGGREGATE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>ALTER AGGREGATE</refname>
19 <refpurpose>change the definition of an aggregate function</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>aggregate_signature</replaceable> ) RENAME TO <replaceable>new_name</replaceable>
25 ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>aggregate_signature</replaceable> )
26 OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
27 ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>aggregate_signature</replaceable> ) SET SCHEMA <replaceable>new_schema</replaceable>
28
29 <phrase>where <replaceable>aggregate_signature</replaceable> is:</phrase>
30
31 * |
32 [ <replaceable>argmode</replaceable> ] [ <replaceable>argname</replaceable> ] <replaceable>argtype</replaceable> [ , ... ] |
33 [ [ <replaceable>argmode</replaceable> ] [ <replaceable>argname</replaceable> ] <replaceable>argtype</replaceable> [ , ... ] ] ORDER BY [ <replaceable>argmode</replaceable> ] [ <replaceable>argname</replaceable> ] <replaceable>argtype</replaceable> [ , ... ]
34 </synopsis>
35 </refsynopsisdiv>
36
37 <refsect1>
38 <title>Description</title>
39
40 <para>
41 <command>ALTER AGGREGATE</command> changes the definition of an
42 aggregate function.
43 </para>
44
45 <para>
46 You must own the aggregate function to use <command>ALTER AGGREGATE</command>.
47 To change the schema of an aggregate function, you must also have
48 <literal>CREATE</literal> privilege on the new schema.
49 To alter the owner, you must be able to <literal>SET ROLE</literal> to the
50 new owning role, and that role must have <literal>CREATE</literal>
51 privilege on the aggregate function's schema.
52 (These restrictions enforce that altering
53 the owner doesn't do anything you couldn't do by dropping and recreating
54 the aggregate function. However, a superuser can alter ownership of any
55 aggregate function anyway.)
56 </para>
57 </refsect1>
58
59 <refsect1>
60 <title>Parameters</title>
61
62 <variablelist>
63 <varlistentry>
64 <term><replaceable class="parameter">name</replaceable></term>
65 <listitem>
66 <para>
67 The name (optionally schema-qualified) of an existing aggregate function.
68 </para>
69 </listitem>
70 </varlistentry>
71
72 <varlistentry>
73 <term><replaceable class="parameter">argmode</replaceable></term>
74
75 <listitem>
76 <para>
77 The mode of an argument: <literal>IN</literal> or <literal>VARIADIC</literal>.
78 If omitted, the default is <literal>IN</literal>.
79 </para>
80 </listitem>
81 </varlistentry>
82
83 <varlistentry>
84 <term><replaceable class="parameter">argname</replaceable></term>
85
86 <listitem>
87 <para>
88 The name of an argument.
89 Note that <command>ALTER AGGREGATE</command> does not actually pay
90 any attention to argument names, since only the argument data
91 types are needed to determine the aggregate function's identity.
92 </para>
93 </listitem>
94 </varlistentry>
95
96 <varlistentry>
97 <term><replaceable class="parameter">argtype</replaceable></term>
98 <listitem>
99 <para>
100 An input data type on which the aggregate function operates.
101 To reference a zero-argument aggregate function, write <literal>*</literal>
102 in place of the list of argument specifications.
103 To reference an ordered-set aggregate function, write
104 <literal>ORDER BY</literal> between the direct and aggregated argument
105 specifications.
106 </para>
107 </listitem>
108 </varlistentry>
109
110 <varlistentry>
111 <term><replaceable class="parameter">new_name</replaceable></term>
112 <listitem>
113 <para>
114 The new name of the aggregate function.
115 </para>
116 </listitem>
117 </varlistentry>
118
119 <varlistentry>
120 <term><replaceable class="parameter">new_owner</replaceable></term>
121 <listitem>
122 <para>
123 The new owner of the aggregate function.
124 </para>
125 </listitem>
126 </varlistentry>
127
128 <varlistentry>
129 <term><replaceable class="parameter">new_schema</replaceable></term>
130 <listitem>
131 <para>
132 The new schema for the aggregate function.
133 </para>
134 </listitem>
135 </varlistentry>
136 </variablelist>
137 </refsect1>
138
139 <refsect1>
140 <title>Notes</title>
141
142 <para>
143 The recommended syntax for referencing an ordered-set aggregate
144 is to write <literal>ORDER BY</literal> between the direct and aggregated
145 argument specifications, in the same style as in
146 <link linkend="sql-createaggregate"><command>CREATE AGGREGATE</command></link>. However, it will also work to
147 omit <literal>ORDER BY</literal> and just run the direct and aggregated
148 argument specifications into a single list. In this abbreviated form,
149 if <literal>VARIADIC "any"</literal> was used in both the direct and
150 aggregated argument lists, write <literal>VARIADIC "any"</literal> only once.
151 </para>
152 </refsect1>
153
154 <refsect1>
155 <title>Examples</title>
156
157 <para>
158 To rename the aggregate function <literal>myavg</literal> for type
159 <type>integer</type> to <literal>my_average</literal>:
160 <programlisting>
161 ALTER AGGREGATE myavg(integer) RENAME TO my_average;
162 </programlisting>
163 </para>
164
165 <para>
166 To change the owner of the aggregate function <literal>myavg</literal> for type
167 <type>integer</type> to <literal>joe</literal>:
168 <programlisting>
169 ALTER AGGREGATE myavg(integer) OWNER TO joe;
170 </programlisting>
171 </para>
172
173 <para>
174 To move the ordered-set aggregate <literal>mypercentile</literal> with
175 direct argument of type <type>float8</type> and aggregated argument
176 of type <type>integer</type> into schema <literal>myschema</literal>:
177 <programlisting>
178 ALTER AGGREGATE mypercentile(float8 ORDER BY integer) SET SCHEMA myschema;
179 </programlisting>
180 This will work too:
181 <programlisting>
182 ALTER AGGREGATE mypercentile(float8, integer) SET SCHEMA myschema;
183 </programlisting></para>
184 </refsect1>
185
186 <refsect1>
187 <title>Compatibility</title>
188
189 <para>
190 There is no <command>ALTER AGGREGATE</command> statement in the SQL
191 standard.
192 </para>
193 </refsect1>
194
195 <refsect1>
196 <title>See Also</title>
197
198 <simplelist type="inline">
199 <member><xref linkend="sql-createaggregate"/></member>
200 <member><xref linkend="sql-dropaggregate"/></member>
201 </simplelist>
202 </refsect1>
203 </refentry>