Final function in AGGREGATE does not run in PostgreSQL

Question

I am creating an extension to PostgreSQL using the C language.

I implemented an aggregate function, in order to return an ordered array of timestamps.

The time_to_array function is responsible for adding all the timestamp elements of a table column to an array.

The time_to_array_final function receives the timestamps array and sorts.

The following are the definitions in sql:

CREATE OR REPLACE FUNCTION time_to_array(timestamp[], timestamp)
    RETURNS timestamp[]
    AS 'MODULE_PATHNAME','time_to_array'
    LANGUAGE C IMMUTABLE;

CREATE OR REPLACE FUNCTION time_to_array_final(timestamp[])
    RETURNS timestamp[]
    AS 'MODULE_PATHNAME', 'time_to_array_final'
    LANGUAGE C IMMUTABLE;

CREATE AGGREGATE array_time_agg(timestamp)
(
  SFUNC = time_to_array,
  STYPE = timestamp[],
  FINALFUNC = time_to_array_final
);

SELECT array_time_agg(column) FROM table;

elog() : NOTA: time_to_array CALL (5 times)

RESULT: array_time_agg | {"2016-06-01 00:00:00","2016-06-02 00:00:00","2016-06-05 00:00:00","2016-06-03 00:00:00","2016-07-03 00:00:00"}

I am currently using the elog to analyze what is happening in the code.

When executing the select it can be seen that the time_to_array function is called correctly every time.

But the time_to_array_final function does not. The elog() is at the beginning of the function, indicating that the same is not called:

PG_FUNCTION_INFO_V1(time_to_array_final);

Datum
time_to_array_final(PG_FUNCTION_ARGS)
{
  elog(NOTICE, "time_to_array_final");

  ArrayType *array_time;
  ArrayType *array_time_result;

  /* variables for "deconstructed" array*/
  Datum *datums_time;
  bool *nulls_time;
  int count_time;
  int16 typlen;
  bool typbyval;
  char typalign;

  array_time = PG_GETARG_ARRAYTYPE_P(0);

  /*values found in table pg_type*/
  get_typlenbyvalalign(TIMESTAMPOID, &typlen, &typbyval, &typalign);

  deconstruct_array(array_time, TIMESTAMPOID, typlen, typbyval, typalign , &datums_time, &nulls_time, &count_time);

  quick_sort(datums_time, 0, count_time - 1);

  array_time_result = construct_array(datums_time, 1, TIMESTAMPOID, typlen, typbyval, typalign);

  PG_RETURN_ARRAYTYPE_P(array_time_result);
}

Am I passing some wrong parameter?

Do I need anything else to create the aggregate function? I read that initcond is not necessary, and the function assumes that is null.

Thanks in advance

klin · Accepted Answer · 2017-08-29 16:49:57Z

1

Your DDL statements are correct, there must be something in your C function. Vide:

create or replace function time_to_array(timestamp[], timestamp)
returns timestamp[] language sql as $$
    select $1 || $2
$$;

create or replace function time_to_array_final(timestamp[])
returns timestamp[] language sql as $$
    select array_agg(elem order by elem)
    from unnest($1) as elem;
$$;

create aggregate array_time_agg(timestamp)
(
  sfunc = time_to_array,
  stype = timestamp[],
  finalfunc = time_to_array_final
);

with my_table(tstamp) as (
values
    ('2017-08-20'::timestamp),
    ('2017-08-19'),
    ('2017-08-22'),
    ('2017-08-21')
)
select array_time_agg(tstamp) 
from my_table;

                                      array_time_agg                                       
-------------------------------------------------------------------------------------------
 {"2017-08-19 00:00:00","2017-08-20 00:00:00","2017-08-21 00:00:00","2017-08-22 00:00:00"}
(1 row)

I've compiled your function (without quicksort()) and it seems to have a minor bug:

array_time_result = construct_array(datums_time, count_time, TIMESTAMPOID, typlen, typbyval, typalign);
//                                               | was 1

Anyway, it works well in my aggregate as a final function (without sorting though).

edited Aug 29, 2017 at 16:49

answered Aug 24, 2017 at 18:39

klin

123k15 gold badges241 silver badges263 bronze badges

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

3 Comments

Fabi_Z Over a year ago

Thank you for your answer! But since my C function (time_to_array) is returning an array, is the error probably in the time_to_array_final function, or not necessarily?

klin Over a year ago

I think so, all indications are that the C function time_to_array_final is problematic.

Fabi_Z Over a year ago

The final function is not called :/

Collectives™ on Stack Overflow

Final function in AGGREGATE does not run in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related