Compute an aggregated tsrange from a set of entries?

Question

I am trying to compute a aggregated tsrange from a set of row that I extract from an SQL query. Problem is that I keep getting errors that the input parameter is not being passed in.

CREATE OR REPLACE AGGREGATE range_merge(anyrange)
(
    sfunc = range_merge,
    stype = anyrange
);

DROP FUNCTION IF EXISTS aggregate_validity(entity_name regclass, entry bigint);

CREATE OR REPLACE FUNCTION aggregate_validity(entity_name regclass, entry bigint) returns tsrange AS
$$
DECLARE
    result tsrange;
BEGIN
   EXECUTE format('select range_merge(valid) from %s where entity_id = %U', entity_name, entry) into result;
   return result;
END
$$ LANGUAGE plpgsql;

When I do:

select * from aggregate_validity(country, 1);

I get an error stating that the entity name and entry do not exist. It does not seem to parameterize the input into the statement properly.

Answer 1

Function:

EXECUTE format('select range_merge(valid) from %s where entity_id=%U',entity_name, entry) 
into result;
=>
EXECUTE format('select range_merge(valid) from %I where entity_id=%s',entity_name, entry) 
into result;
--%I for identifier, %s for value

Call:

select * from aggregate_validity(country, 1)
=>
select * from aggregate_validity('country', 1);

db<>fiddle demo

Answer 2

CREATE OR REPLACE AGGREGATE range_merge(anyrange) (
   SFUNC = range_merge
 , STYPE = anyrange
);

-- DROP FUNCTION IF EXISTS aggregate_validity(entity_name regclass, entry bigint);
CREATE OR REPLACE FUNCTION aggregate_validity(entity_name regclass, entry bigint, OUT result tsrange)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE 'SELECT range_merge(valid) FROM ' || entity_name || ' WHERE entity_id = $1'
   INTO result
   USING entry;
END
$func$;

Call:

SELECT aggregate_validity('country', 1);

db<>fiddle here

The call does not need SELECT * FROM, as the function returns a single value per definition.

I used an OUT parameter to simplify (OUT result tsrange). See:

• Returning from a function with OUT parameter

Don't concatenate the entry value into the SQL string. Pass it as value with the USING clause. Cleaner, faster.

Since entity_name is passed as regclass, it's safe to simply concatenate (which is a bit cheaper). See:

• Table name as a PostgreSQL function parameter

Plus, missing quotes and incorrect format specifiers, as Lukasz already provided.

Your custom aggregate function range_merge() has some caveats:

1. I wouldn't name it "range_merge", that being the name of the plain function range_merge(), too. While that's legal, it still invites confusing errors.

2. You are aware that the function range_merge() includes gaps between input ranges in the output range?

3. range_merge() returns NULL for any NULL input. So if your table has any NULL values in the column valid, the result is always NULL. I strongly suggest that any involved columns shall be defined as NOT NULL.

If you are at liberty to install additional modules, consider range_agg by Paul Jungwirth who is also here on Stackovflow. It provides the superior function range_agg() addressing some of the mentioned issues.

If you don't want to include gaps, consider the Postgres Wiki page on range aggregation.

I would probably not use aggregate_validity() at all. It obscures the nested functionality from the Postgres query planner and may lead so suboptimal query plans. Typically, you can replace it with a correlated or a LATERAL subquery, which can be planned and optimized by Postgres in context of the outer query. I appended a demo to the fiddle:

db<>fiddle here

Related:

• What is the difference between LATERAL and a subquery in PostgreSQL?