Database Reference
In-Depth Information
the result. The initial condition is also optional. When it is present, the command uses
it to initialize the state value.
The optional sort operator can serve as the associated sort operator for a
MIN
- or
MAX
-
like aggregate. It is used to take advantage of indexes. It is just an operator name such
as
>
and
<
. It should be used only when the two following statements are equivalent:
SELECT
agg
(
col
) FROM
sometable
;
SELECT
col
FROM
sometable
ORDER BY
col
USING
sortop
LIMIT 1;
The PostgreSQL 9.4
CREATE AGGREGATE
structure was expanded to
include support for creating moving aggregates, which are useful with
window functions that move the window. See
PostgreSQL 9.4: CRE‐
ATE AGGREGATE
for details.
Aggregates need not depend on a single column. If you need more than one column for
your aggregate (an example is a built-in covariance function), see
How to Create Multi-
Column Aggregates
for guidance.
SQL language functions are easy to write. You don't have fancy control flow commands
to worry about, and you probably have a good grasp of SQL to begin with. When it
comes to writing aggregates, you can get pretty far with the SQL language alone. We
demonstrate aggregates in
“Writing SQL Aggregate Functions” on page 149
.
Trusted and Untrusted Languages
Function languages are characterized by trust level: trusted versus untrusted. Many—
but not all—languages offer both a trusted and untrusted version. The term
trusted
connotes that the language can do no harm to the underlying operating system:
Trusted
A trusted language lacks access to the server's file system beyond the data cluster.
It therefore cannot execute OS commands. Users of any level can create functions
in a trusted language. Languages such as SQL, PL/pgSQL, and PL/Perl are trusted.
Untrusted
An untrusted language can interact with the OS. It can execute OS functions and
call web services. Only superusers have the privilege of authoring functions in an
untrusted language. However, a superuser can grant permission to another role to
run an untrusted function. By convention, languages that are untrusted end in the
letter U (PL/PerlU, PL/PythonU, etc.).
