Syntax error in create aggregate
Like CASE
, COALESCE
and NULLIF
, GREATEST
and LEAST
are listed in the chapter Conditional Expressions. These SQL constructs are not implemented as functions .. like @Laurenz provided in the meantime.
The manual advises:
Tip: If your needs go beyond the capabilities of these conditional expressions, you might want to consider writing a stored procedure in a more expressive programming language.
The terminology is a bit off here as well, since Postgres does not support true "stored procedures", just functions. (Which is why there is an open TODO item "Implement stored procedures".)
This manual page might be sharpened to avoid confusion ...
@Laurenz also provided an example. I would just use LEAST
in the function to get identical functionality:
CREATE FUNCTION f_least(anyelement, anyelement) RETURNS anyelement LANGUAGE sql IMMUTABLE AS'SELECT LEAST($1, $2)';
Do not make it STRICT
, that would be incorrect. LEAST(1, NULL)
returns 1
and not NULL
.
Even if STRICT
was correct, I would not use it, because it can prevent function inlining.
Note that this function is limited to exactly two parameters while LEAST
takes any number of parameters. You might overload the function to cover 3, 4 etc. input parameters. Or you could write a VARIADIC
function for up to 100 parameters.
LEAST
and GREATEST
are not real functions; internally they are parsed as MinMaxExpr
(see src/include/nodes/primnodes.h
).
You could achieve what you want with a generic function like this:
CREATE FUNCTION my_least(anyelement, anyelement) RETURNS anyelement LANGUAGE sql IMMUTABLE CALLED ON NULL INPUT AS 'SELECT LEAST($1, $2)';
(thanks to Erwin Brandstetter for the CALLED ON NULL INPUT
and the idea to use LEAST
.)
Then you can create your aggregate as
CREATE AGGREGATE min(my_type) (sfunc = my_least, stype = my_type);
This will only work if there are comparison functions for my_type
, otherwise you have to come up with a different my_least
function.