Postgres User-defined Aggregation

Question

i'd like to create a user defined aggregation function. My own type:

CREATE TYPE state AS(   
    reservoir integer[5],
    skipcnt int,
    reservoir_size int
);

SFUNC:

 create function res_trans (currentstate state,newsample int)
    returns state
    as $$
    DECLARE
    pos integer := 0;
    BEGIN
    IF currentstate.skipcnt = -1 THEN
    currentstate.skipcnt := 5;
    ELSIF currentstate.skipcnt = 0 THEN 
    pos := floor(random()*currentstate.reservoir_size+1);
        currentstate.reservoir := res_array_replace(currentstate.reservoir,pos,newsample);
        currentstate.skipcnt := 5;
    ELSE 
    currentstate.skipcnt := currentstate.skipcnt - 1;
    END IF;
    RETURN currentstate;
    END;
    $$LANGUAGE plpgsql;

finalfunc:

CREATE FUNCTION finalize_trans(finalstate state) RETURNS integer[] 
AS $$
BEGIN
RETURN finalstate.reservoir;
END;
$$LANGUAGE plpgsql;

All the functions above can be created successfully. However, when i create the aggregation. It shows some error.

CREATE AGGREGATE reservoir_sampling(int)
(
    INITCOND = ROW('{1,2,3,4,5}',-1,5), 
    STYPE = state,
    SFUNC = res_trans,
    FINALFUNC = finalize_trans
);

enter image description here Is there a problem of the initcond? Please help me, Thanks!

I tried to let the initcond = null and deal with the null case in SFUNC, but i got enter image description here

user330315 · Accepted Answer · 2023-01-25 07:49:15Z

2

The manual about initial_condition says

This must be a string constant in the form accepted for the data type state_data_type

So you need to provide a string constant, not a "row" expression:

CREATE AGGREGATE reservoir_sampling(int)
(
  INITCOND = '("{1,2,3,4,5}",-1,5)', 
  STYPE = state,
  SFUNC = res_trans,
  FINALFUNC = finalize_trans
);

answered Jan 25, 2023 at 7:49

user330315

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

1 Comment

jjanes Over a year ago

And, if you don't know how to formulate your data as a string literal correctly, you can just use the ROW form in a select and the output of the select should be suitable as input: select ROW('{1,2,3,4,5}',-1,5)::state;

Collectives™ on Stack Overflow

Postgres User-defined Aggregation

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related