Thread: multiple parameters to an AGGREGATE function

multiple parameters to an AGGREGATE function

From

Graham

Date:

03 February 2012, 16:28:24

Hi,
   first time poster here ...

I'm trying to write a simple Aggregate function which returns the nth
element in a collection - ultimately I want to find 95th, 90th percentiles
and so on.

It'd be called like:

select nth_element( value, 95 ) from something group by ...

I'm basing this on an example I found on the Wiki:

http://wiki.postgresql.org/wiki/Aggregate_Mode

So, I have:

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_element( anyelement, integer ) (
  SFUNC=array_append,
  STYPE=anyarray,
  FINALFUNC=_final_nth,
  INITCOND='{}'
);

Where the 2nd parameter would be, say 5 for the 5th element an so on.

The function declaration seems fine. But the CREATE AGGREGATE declaration
fails with:

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

so, I suppose it's decided to call array_append with all the parameters on
the command line, rather than just the array in the 1st element. Is there
any way to stop it doing this?

I've searched the online documentation and Googled but haven't found
anything.

thanks,

Graham
x

Re: multiple parameters to an AGGREGATE function

From

Merlin Moncure

Date:

03 February 2012, 16:49:29

On Fri, Feb 3, 2012 at 11:27 AM, Graham <graham.stark@virtual-worlds.biz> wrote:
> Hi,
>   first time poster here ...
>
> I'm trying to write a simple Aggregate function which returns the nth
> element in a collection - ultimately I want to find 95th, 90th percentiles
> and so on.
>
> It'd be called like:
>
> select nth_element( value, 95 ) from something group by ...
>
> I'm basing this on an example I found on the Wiki:
>
> http://wiki.postgresql.org/wiki/Aggregate_Mode
>
> So, I have:
>
> 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_element( anyelement, integer ) (
>  SFUNC=array_append,
>  STYPE=anyarray,
>  FINALFUNC=_final_nth,
>  INITCOND='{}'
> );
>
> Where the 2nd parameter would be, say 5 for the 5th element an so on.
>
> The function declaration seems fine. But the CREATE AGGREGATE declaration
> fails with:
>
> ERROR:  function array_append(anyarray, anyelement, integer) does not exist
>
> so, I suppose it's decided to call array_append with all the parameters on
> the command line, rather than just the array in the 1st element. Is there
> any way to stop it doing this?

I'm too busy to check, but it's probably calling your function at the
end with the last element scanned.  Just create it the way it wants
and log the arguments -- it should be usable.

merlin

Re: multiple parameters to an AGGREGATE function

From

Christian Paminger

Date:

27 January 2015, 12:19:49

Hi,

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.

Greetings ...



On Fri, Feb 3, 2012 at 11:27 AM, Graham
<graham(dot)stark(at)virtual-worlds(dot)biz> wrote:

Hi,
    first time poster here ...

I'm trying to write a simple Aggregate function which returns the nth
element in a collection - ultimately I want to find 95th, 90th percentiles
and so on.

It'd be called like:

select nth_element( value, 95 ) from something group by ...

I'm basing this on an example I found on the Wiki:

http://wiki.postgresql.org/wiki/Aggregate_Mode

So, I have:

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_element( anyelement, integer ) (
   SFUNC=array_append,
   STYPE=anyarray,
   FINALFUNC=_final_nth,
   INITCOND='{}'
);

Where the 2nd parameter would be, say 5 for the 5th element an so on.

The function declaration seems fine. But the CREATE AGGREGATE declaration
fails with:

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

so, I suppose it's decided to call array_append with all the parameters on
the command line, rather than just the array in the 1st element. Is there
any way to stop it doing this?

I've searched the online documentation and Googled but haven't found
anything.

thanks,

Graham
x