PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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));
}
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search