CODEWITHSUNDEEP
postgresqlaggregate
user3419945
user3419945

Reputation: 367

How to add a custom aggregate function (eg. MAX/MIN) to PostgreSQL?

I would like to add some extension function in Postgresql like max/min, but I could not find the source function of them. Could anyone suggest which part of the source code should I view? thanks,

Here is an example. I have relation: model(id int) where model is a bunch of CAD models each one has an ID ; I want to find all models which id>5 and area>5. but I do not want to calculate all face area, so I uses having clause only calculate a subset. here is the query: 

select model.id, model.face_number
from
    model
where
    id>5
group by model.id
having
    area(model.id)>5;

I want to define function area(oid) function like max/min as FDW. but I do not know how to pass the input parameters, so I want to compare it with min/max.

Upvotes: 1

Views: 2119

Answers (2)

Dwayne Towell
Dwayne Towell

Reputation: 8593

You do not want an aggregate function for what you have described. You also should not worry about performance until you have a working version--likely PostgreSQL's query optimizer will do exactly what you want if you write this:

select model.id, model.face_number
from
    model
where
    id>5 and area(model.id)>5;

Here is an example function:

CREATE FUNCTION area(int in_id)
RETURNS double precision AS $$
    SELECT length*width FROM model WHERE id=in_id;
$$ LANGUAGE SQL STABLE;

Of course you can replace length*width with some more appropriate calculation.

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324541

This doesn't make much sense.

min and max are aggregate functions. They reduce a set of rows into a single value.

Your problem description doesn't seem to have much to do with aggregation. So it's not at all clear that aggregate functions have anything to do with it.

If you really do need to write an aggregate function, start with the PostgreSQL manual:

I strongly recommend that you prototype your aggregate function in PL/PgSQL or another procedural language. Write it in C only if you've demonstrated that it can work using a quicker-to-work-with language, and determined that you need it faster than you can do with PL/PgSQL or PL/Python or whatever.

Anyway, if you want to find the implementation of min/max, start here:

select a.*, so.oprname as aggsortopname, tt.typname as aggtranstypename
from pg_aggregate a 
inner join pg_proc p on (a.aggfnoid = p.oid) 
inner join pg_type tt on (a.aggtranstype = tt.oid)
inner join pg_operator so on (a.aggsortop = so.oid)
where p.proname = 'max';

There you'll see that the aggregate is composed of multiple parts: a transform function, a sort operator, a transitional state type, an optional final function, etc. The documentation on user-defined aggregates explains that in detail.

So there's no single "max function". The definition of max in pg_proc.h actually just refers to a dummy function.

So for max(int4), it's defined as the transition function int4larger (src/backend/utils/adt/int.c) over transition type int4, with the sort operator >, with no final function.

Upvotes: 3

Related Questions