Creating an aggregate function fails

Question

While looking for an aggregate function that returns the nth element of a collection I found the following solution:

CREATE OR REPLACE FUNCTION _final_nth( anyarray, integer )
  RETURNS anyelement AS
$BODY$
    SELECT a
    FROM unnest( $1 ) a
    ORDER BY a
    offset $2
    LIMIT 1;
$BODY$
LANGUAGE 'sql' IMMUTABLE; 

CREATE AGGREGATE nth( anyelement, integer ) (
  SFUNC=array_append,
  STYPE=anyarray,
  FINALFUNC=_final_nth,
  INITCOND='{}'
);

Creating the function works fine but executing CREATE AGGREGATE produces the following error:

ERROR: function array_append(anyarray, anyelement, integer) does not exist

I am not able to resolve this error.

I can't make heads or tails of this question. You say you want the nth element of a collection, but what's a "collection" in PG? If a "collection" is a result set, what's wrong with a straight OFFSET/LIMIT, and if it's an array, what's wrong with a straight myarray[n]? Why do you need an aggregate at all? Also, the error looks pretty obvious to me. PostgreSQL is trying to pass the current state plus the aggregate's arguments to the SFUNC, and there's no array_append that takes those arguments (nor would it make any sense for there to be). — jpmc26
– jpmc26, Commented Jan 27, 2015 at 7:03

Pamstl · Accepted Answer · 2015-01-27 09:07:19Z

1

Maybe this will help:

CREATE OR REPLACE FUNCTION _final_nth( anyarray, integer )
  RETURNS anyelement AS
$BODY$
    SELECT a
    FROM unnest( $1 ) a
    ORDER BY a
    offset $2
    LIMIT 1;
$BODY$

Use it without an extra aggregate-function.

SELECT _final_nth(array_agg(someelement),n) AS someelement
FROM sometable
GROUP BY someelement;

n is the offset.

edited Jan 27, 2015 at 9:07

user330315

answered Jan 27, 2015 at 9:04

Pamstl

263 bronze badges

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

Collectives™ on Stack Overflow

Creating an aggregate function fails

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related