By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
SELECT oid::regprocedure::text AS agg_func_plus_args
FROM pg_proc
WHERE prokind = 'a'
ORDER BY 1;
| agg_func_plus_args |
|---|
| array_agg(anyarray) |
| array_agg(anynonarray) |
| avg(bigint) |
| avg(double precision) |
| avg(integer) |
| avg(interval) |
| avg(numeric) |
| avg(real) |
| avg(smallint) |
| bit_and(bigint) |
| bit_and(bit) |
| bit_and(integer) |
| bit_and(smallint) |
| bit_or(bigint) |
| bit_or(bit) |
| bit_or(integer) |
| bit_or(smallint) |
| bool_and(boolean) |
| bool_or(boolean) |
| corr(double precision,double precision) |
| count() |
| count("any") |
| covar_pop(double precision,double precision) |
| covar_samp(double precision,double precision) |
| cume_dist("any") |
| dense_rank("any") |
| every(boolean) |
| json_agg(anyelement) |
| jsonb_agg(anyelement) |
| jsonb_object_agg("any","any") |
| json_object_agg("any","any") |
| max(anyarray) |
| max(anyenum) |
| max(bigint) |
| max(character) |
| max(date) |
| max(double precision) |
| max(inet) |
| max(integer) |
| max(interval) |
| max(money) |
| max(numeric) |
| max(oid) |
| max(real) |
| max(smallint) |
| max(text) |
| max(tid) |
| max(timestamp without time zone) |
| max(timestamp with time zone) |
| max(time without time zone) |
| max(time with time zone) |
| min(anyarray) |
| min(anyenum) |
| min(bigint) |
| min(character) |
| min(date) |
| min(double precision) |
| min(inet) |
| min(integer) |
| min(interval) |
| min(money) |
| min(numeric) |
| min(oid) |
| min(real) |
| min(smallint) |
| min(text) |
| min(tid) |
| min(timestamp without time zone) |
| min(timestamp with time zone) |
| min(time without time zone) |
| min(time with time zone) |
| mode(anyelement) |
| percentile_cont(double precision,double precision) |
| percentile_cont(double precision[],double precision) |
| percentile_cont(double precision,interval) |
| percentile_cont(double precision[],interval) |
| percentile_disc(double precision,anyelement) |
| percentile_disc(double precision[],anyelement) |
| percent_rank("any") |
| public.first(anyelement) |
| rank("any") |
| regr_avgx(double precision,double precision) |
| regr_avgy(double precision,double precision) |
| regr_count(double precision,double precision) |
| regr_intercept(double precision,double precision) |
| regr_r2(double precision,double precision) |
| regr_slope(double precision,double precision) |
| regr_sxx(double precision,double precision) |
| regr_sxy(double precision,double precision) |
| regr_syy(double precision,double precision) |
| stddev(bigint) |
| stddev(double precision) |
| stddev(integer) |
| stddev(numeric) |
| stddev_pop(bigint) |
| stddev_pop(double precision) |
| stddev_pop(integer) |
| stddev_pop(numeric) |
| stddev_pop(real) |
| stddev_pop(smallint) |
| stddev(real) |
| stddev_samp(bigint) |
| stddev_samp(double precision) |
| stddev_samp(integer) |
| stddev_samp(numeric) |
| stddev_samp(real) |
| stddev_samp(smallint) |
| stddev(smallint) |
| string_agg(bytea,bytea) |
| string_agg(text,text) |
| sum(bigint) |
| sum(double precision) |
| sum(integer) |
| sum(interval) |
| sum(money) |
| sum(numeric) |
| sum(real) |
| sum(smallint) |
| variance(bigint) |
| variance(double precision) |
| variance(integer) |
| variance(numeric) |
| variance(real) |
| variance(smallint) |
| var_pop(bigint) |
| var_pop(double precision) |
| var_pop(integer) |
| var_pop(numeric) |
| var_pop(real) |
| var_pop(smallint) |
| var_samp(bigint) |
| var_samp(double precision) |
| var_samp(integer) |
| var_samp(numeric) |
| var_samp(real) |
| var_samp(smallint) |
| xmlagg(xml) |
SELECT proname AS agg_func, pg_get_function_identity_arguments(oid) AS args
FROM pg_proc
WHERE prokind = 'a'
ORDER BY 1, 2;
| agg_func | args |
|---|---|
| array_agg | anyarray |
| array_agg | anynonarray |
| avg | bigint |
| avg | double precision |
| avg | integer |
| avg | interval |
| avg | numeric |
| avg | real |
| avg | smallint |
| bit_and | bigint |
| bit_and | bit |
| bit_and | integer |
| bit_and | smallint |
| bit_or | bigint |
| bit_or | bit |
| bit_or | integer |
| bit_or | smallint |
| bool_and | boolean |
| bool_or | boolean |
| corr | double precision, double precision |
| count | |
| count | "any" |
| covar_pop | double precision, double precision |
| covar_samp | double precision, double precision |
| cume_dist | VARIADIC "any" ORDER BY VARIADIC "any" |
| dense_rank | VARIADIC "any" ORDER BY VARIADIC "any" |
| every | boolean |
| first | anyelement |
| json_agg | anyelement |
| json_object_agg | "any", "any" |
| jsonb_agg | anyelement |
| jsonb_object_agg | "any", "any" |
| max | anyarray |
| max | anyenum |
| max | bigint |
| max | character |
| max | date |
| max | double precision |
| max | inet |
| max | integer |
| max | interval |
| max | money |
| max | numeric |
| max | oid |
| max | real |
| max | smallint |
| max | text |
| max | tid |
| max | timestamp without time zone |
| max | timestamp with time zone |
| max | time without time zone |
| max | time with time zone |
| min | anyarray |
| min | anyenum |
| min | bigint |
| min | character |
| min | date |
| min | double precision |
| min | inet |
| min | integer |
| min | interval |
| min | money |
| min | numeric |
| min | oid |
| min | real |
| min | smallint |
| min | text |
| min | tid |
| min | timestamp without time zone |
| min | timestamp with time zone |
| min | time without time zone |
| min | time with time zone |
| mode | ORDER BY anyelement |
| percent_rank | VARIADIC "any" ORDER BY VARIADIC "any" |
| percentile_cont | double precision ORDER BY double precision |
| percentile_cont | double precision[] ORDER BY double precision |
| percentile_cont | double precision ORDER BY interval |
| percentile_cont | double precision[] ORDER BY interval |
| percentile_disc | double precision ORDER BY anyelement |
| percentile_disc | double precision[] ORDER BY anyelement |
| rank | VARIADIC "any" ORDER BY VARIADIC "any" |
| regr_avgx | double precision, double precision |
| regr_avgy | double precision, double precision |
| regr_count | double precision, double precision |
| regr_intercept | double precision, double precision |
| regr_r2 | double precision, double precision |
| regr_slope | double precision, double precision |
| regr_sxx | double precision, double precision |
| regr_sxy | double precision, double precision |
| regr_syy | double precision, double precision |
| stddev | bigint |
| stddev | double precision |
| stddev | integer |
| stddev | numeric |
| stddev | real |
| stddev | smallint |
| stddev_pop | bigint |
| stddev_pop | double precision |
| stddev_pop | integer |
| stddev_pop | numeric |
| stddev_pop | real |
| stddev_pop | smallint |
| stddev_samp | bigint |
| stddev_samp | double precision |
| stddev_samp | integer |
| stddev_samp | numeric |
| stddev_samp | real |
| stddev_samp | smallint |
| string_agg | bytea, bytea |
| string_agg | text, text |
| sum | bigint |
| sum | double precision |
| sum | integer |
| sum | interval |
| sum | money |
| sum | numeric |
| sum | real |
| sum | smallint |
| var_pop | bigint |
| var_pop | double precision |
| var_pop | integer |
| var_pop | numeric |
| var_pop | real |
| var_pop | smallint |
| var_samp | bigint |
| var_samp | double precision |
| var_samp | integer |
| var_samp | numeric |
| var_samp | real |
| var_samp | smallint |
| variance | bigint |
| variance | double precision |
| variance | integer |
| variance | numeric |
| variance | real |
| variance | smallint |
| xmlagg | xml |
SELECT type_id::regtype::text, array_agg(proname) AS agg_functions
FROM (
SELECT proname, unnest(proargtypes::regtype[])::text AS type_id
FROM pg_proc
WHERE prokind = 'a'
ORDER BY 2, 1
) sub
GROUP BY type_id;
| type_id | agg_functions |
|---|---|
| "any" | {count,cume_dist,dense_rank,json_object_agg,json_object_agg,jsonb_object_agg,jsonb_object_agg,percent_rank,rank} |
| anyarray | {array_agg,max,min} |
| anyelement | {first,json_agg,jsonb_agg,mode,percentile_disc,percentile_disc} |
| anyenum | {max,min} |
| anynonarray | {array_agg} |
| bigint | {avg,bit_and,bit_or,max,min,stddev,stddev_pop,stddev_samp,sum,var_pop,var_samp,variance} |
| bit | {bit_and,bit_or} |
| boolean | {bool_and,bool_or,every} |
| bytea | {string_agg,string_agg} |
| character | {max,min} |
| date | {max,min} |
| double precision | {avg,corr,corr,covar_pop,covar_pop,covar_samp,covar_samp,max,min,percentile_cont,percentile_cont,percentile_cont,percentile_cont,percentile_disc,regr_avgx,regr_avgx,regr_avgy,regr_avgy,regr_count,regr_count,regr_intercept,regr_intercept,regr_r2,regr_r2,regr_slope,regr_slope,regr_sxx,regr_sxx,regr_sxy,regr_sxy,regr_syy,regr_syy,stddev,stddev_pop,stddev_samp,sum,var_pop,var_samp,variance} |
| double precision[] | {percentile_cont,percentile_cont,percentile_disc} |
| inet | {max,min} |
| integer | {avg,bit_and,bit_or,max,min,stddev,stddev_pop,stddev_samp,sum,var_pop,var_samp,variance} |
| interval | {avg,max,min,percentile_cont,percentile_cont,sum} |
| money | {max,min,sum} |
| numeric | {avg,max,min,stddev,stddev_pop,stddev_samp,sum,var_pop,var_samp,variance} |
| oid | {max,min} |
| real | {avg,max,min,stddev,stddev_pop,stddev_samp,sum,var_pop,var_samp,variance} |
| smallint | {avg,bit_and,bit_or,max,min,stddev,stddev_pop,stddev_samp,sum,var_pop,var_samp,variance} |
| text | {max,min,string_agg,string_agg} |
| tid | {max,min} |
| timestamp without time zone | {max,min} |
| timestamp with time zone | {max,min} |
| time without time zone | {max,min} |
| time with time zone | {max,min} |
| xml | {xmlagg} |