Re: greatest cannot be used as sfunc for CREATE AGGREGATE

Hello

GREATEST and LEAST are SQL functions, but it is not internal functions
listed in pg_proc table due different implementation. It is much more
similar to CASE statement than function although syntax is same.

for your purpose, you should to create SQL function wrapper of this
construct - then necessary entry in pg_proc will be done, and you can
use as aggregate sfunc function

CREATE OR REPLACE FUNCTION public.greatest2(text, text)
 RETURNS text
 LANGUAGE sql
AS $function$
SELECT greatest($1,$2)
$function$

postgres=# CREATE AGGREGATE text_max(text) (SFUNC=greatest2, STYPE=text);
CREATE AGGREGATE

postgres=# SELECT * FROM test;
    a
---------
 Ahoj
 Zdravim
(2 rows)

postgres=# SELECT text_max(a) FROM test;
 text_max
----------
 Zdravim
(1 row)



2013/6/25 Ryan Kelly <rpkelly22@gmail.com>:
> In trying to learn about aggregates, I came across this seemingly odd
> behavior:
>
> (postgres@[local]:5435 08:27:42) [postgres]> CREATE AGGREGATE example_max (TEXT) (SFUNC = greatest, STYPE = TEXT);
> ERROR:  syntax error at or near "greatest"
> LINE 1: CREATE AGGREGATE example_max (TEXT) (SFUNC = greatest, STYPE...
>                                                      ^
>
> Of course, this is a silly example (one could just use max), but I'm
> interested in knowing why greatest cannot be used here. Someone on IRC
> mentioned that it was because "greatest was not actually a function" but
> the documentation for greatest says nothing along those lines:
> http://www.postgresql.org/docs/9.2/static/functions-conditional.html#FUNCTIONS-GREATEST-LEAST
>
> The documentation also indicates that greatest is non-reserved (cannot
> be function or type):
> http://www.postgresql.org/docs/9.2/static/sql-keywords-appendix.html
>
> But I'm not sure I understand how to interpret the "cannot be function
> or type" or portion of that.

what is important for this case, is  entry in pg_proc system table.
Functions with entry are "normal" creatures. Functions without entry
are "strange" creatures (from different reasons). Some years ago
PostgreSQL didn't support variadic functions, and implementation of
any variadic functions required some deeper hacking. There are still
some limitation - is not possible to write variadic functions with
same functionality yet, so it is reason why these functions are not
reimplemented.

It was my first or second patch for Postgres and it was great tutorial
of Postgres internals :)

Regards

Pavel

>
> Quoting "greatest" causes this error instead:
> (postgres@[local]:5435 08:30:08) [postgres]> CREATE AGGREGATE example_max (TEXT) (SFUNC = "greatest", STYPE = TEXT);
> ERROR:  function greatest(text, text) does not exist
>
> Which I would assume is because greatest is variadic and not simply a
> function of two arguments.
>
> I'm sure I'm just being dense and missing something obvious here...
>
> -Ryan P. Kelly
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general