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)

FWIW, relevant mailing list post: postgresql.nabble.com/…

Nick Barnes
– Nick Barnes

2016-09-07 14:51:05 +00:00
Commented Sep 7, 2016 at 14:51 — Nick Barnes
– Nick Barnes, Commented Sep 7, 2016 at 14:51

Erwin Brandstetter · Accepted Answer · 2016-09-07 15:02:07Z

4

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.

edited Sep 7, 2016 at 15:02

answered Sep 7, 2016 at 14:39

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laurenz Albe Over a year ago

To define an aggregate you need a function with two arguments, so a VARIADIC function woudn't help. Your other comments are improvements though; I'll edit my answer accordingly.

Erwin Brandstetter Over a year ago

Good point. VARIADIC isn't meant for the aggregate. Just to remind the reader that the provided function cannot replace LEAST completely.

Laurenz Albe · Accepted Answer · 2016-09-07 14:58:32Z

3

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.

edited Sep 7, 2016 at 14:58

answered Sep 7, 2016 at 14:28

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Syntax error in create aggregate

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related