Syntax error in create aggregate

Question

Trying to create an aggregate function:

create aggregate min (my_type) (
    sfunc = least,
    stype = my_type
);
ERROR:  syntax error at or near "least"
LINE 2:     sfunc = least,
                    ^

What am I missing?

Although the manual calls least a function:

The GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions.

I can not find it:

\dfS least
                       List of functions
 Schema | Name | Result data type | Argument data types | Type 
--------+------+------------------+---------------------+------
(0 rows)

Answer 1

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.

Answer 2

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.