How do I find the implementation of an aggregate PostgreSQL function?

Question

I am interested in the implementation or code of the stddev_pop() function. I understand what it does, I know there is a definition in the documentation, but I am interested in the code itself. I tried to find it in pgAdmin within the public schema/functions and aggregates, but it's not there. I also tried this query:

SELECT *
FROM pg_proc
WHERE proname = 'stddev_pop';

that gave me 6 different results. But when I try to query using the function's oid, this is what happens:

SELECT pg_get_functiondef(2724);
ERROR:  "stddev_pop" is an aggregate function
SQL state: 42809

Also in psql I tried:

isa=# \df stddev_pop
                            List of functions
   Schema   |    Name    | Result data type | Argument data types | Type
------------+------------+------------------+---------------------+------
 pg_catalog | stddev_pop | numeric          | bigint              | agg
 pg_catalog | stddev_pop | double precision | double precision    | agg
 pg_catalog | stddev_pop | numeric          | integer             | agg
 pg_catalog | stddev_pop | numeric          | numeric             | agg
 pg_catalog | stddev_pop | double precision | real                | agg
 pg_catalog | stddev_pop | numeric          | smallint            | agg
(6 rows)

but couldn't get further.

How can I "look into" an aggregate function?

Laurenz Albe · Accepted Answer · 2024-01-30 14:00:17Z

You have to query the pg_aggregate catalog:

SELECT a.aggfnoid::regprocedure AS aggregate,
       a.agginitval AS initial_value,
       a.aggtransfn::regprocedure AS state_transition_function,
       a.aggfinalfn::regprocedure AS final_function
FROM pg_aggregate AS a
   JOIN pg_proc AS p
      ON a.aggfnoid = p.oid
WHERE p.prokind = 'a'
  AND p.proname = 'stddev_pop';

          aggregate           │ initial_value │             state_transition_function             │            final_function             
══════════════════════════════╪═══════════════╪═══════════════════════════════════════════════════╪═══════════════════════════════════════
 stddev_pop(bigint)           │ ∅             │ int8_accum(internal,bigint)                       │ numeric_stddev_pop(internal)
 stddev_pop(integer)          │ ∅             │ int4_accum(internal,integer)                      │ numeric_poly_stddev_pop(internal)
 stddev_pop(smallint)         │ ∅             │ int2_accum(internal,smallint)                     │ numeric_poly_stddev_pop(internal)
 stddev_pop(real)             │ {0,0,0}       │ float4_accum(double precision[],real)             │ float8_stddev_pop(double precision[])
 stddev_pop(double precision) │ {0,0,0}       │ float8_accum(double precision[],double precision) │ float8_stddev_pop(double precision[])
 stddev_pop(numeric)          │ ∅             │ numeric_accum(internal,numeric)                   │ numeric_stddev_pop(internal)
(6 rows)

Those are just the basic attributes; see the documentation of pg_aggregate for the rest.

If you need to get the CREATE AGGREGATE statement, you should use pg_dump. However, there is no way to dump only a single function.

To get to the definition of the constituent functions, query pg_proc:

SELECT l.lanname,
       p.prosrc
FROM pg_proc AS p
   JOIN pg_language AS l
      ON p.prolang = l.oid
WHERE p.oid = 'float8_accum(double precision[],double precision)'::regprocedure;

 lanname  │    prosrc    
══════════╪══════════════
 internal │ float8_accum
(1 row)

internal functions are implemented in the PostgreSQL server, so you'd have to look for the function float8_accum in the C source code.

Thank you! I was really looking for the inner mechanics of the function directly, but I see how can I get there from here.

Mukul Gupta · Accepted Answer · 2024-01-30 13:40:36Z

1

This is a built-in aggregate function. You will find the implementation in the postgres code base.

Datum
float8_stddev_pop(PG_FUNCTION_ARGS)
{
    ArrayType  *transarray = PG_GETARG_ARRAYTYPE_P(0);
    float8     *transvalues;
    float8      N,
                Sxx;

    transvalues = check_float8_array(transarray, "float8_stddev_pop", 3);
    N = transvalues[0];
    /* ignore Sx */
    Sxx = transvalues[2];

    /* Population stddev is undefined when N is 0, so return NULL */
    if (N == 0.0)
        PG_RETURN_NULL();

    /* Note that Sxx is guaranteed to be non-negative */

    PG_RETURN_FLOAT8(sqrt(Sxx / N));
}

answered Jan 30, 2024 at 13:40

Mukul Gupta

2,4654 gold badges25 silver badges39 bronze badges

Collectives™ on Stack Overflow

How do I find the implementation of an aggregate PostgreSQL function?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related