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

Question

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.

Just "looking at the source" will be of minimal use for this task - ask about the real task. See postgresql.org/docs/9.3/static/sql-createaggregate.html eg. — user2864740
– user2864740, Commented Oct 3, 2014 at 16:16

Craig Ringer · Accepted Answer · 2014-10-04 15:23:54Z

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.

Dwayne Towell · Accepted Answer · 2014-10-04 17:00:33Z

0

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.

answered Oct 4, 2014 at 17:00

Dwayne Towell

8,6635 gold badges39 silver badges54 bronze badges

1 Comment

Vladimir Stepanov Nov 13 at 13:44

Its like null argument. See sourceforge.net/p/py-billow/wiki/py-billow, but here NULL type named Nil. Have 'min' and 'max' functions that ignored Nil values. And have fast minmax_it.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related