Skip to content

Instantly share code, notes, and snippets.

@cszawisza

cszawisza/gist:c8a8456396a87a5db012

Last active August 29, 2015 14:18
Show Gist options
  • Save cszawisza/c8a8456396a87a5db012 to your computer and use it in GitHub Desktop.
Save cszawisza/c8a8456396a87a5db012 to your computer and use it in GitHub Desktop.
Download ZIP
Get functions from postgresql
Raw
gistfile1.sql
SELECT n.nspname as "Schema",
p.proname ,
pg_catalog.pg_get_function_result(p.oid) ,
pg_catalog.pg_get_function_arguments(p.oid) ,
CASE
WHEN length( pg_catalog.pg_get_function_arguments(p.oid)) = 0 THEN 0
ELSE array_length( regexp_split_to_array(pg_catalog.pg_get_function_arguments(p.oid), E'\,+'),1)
END as "nargs"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
-- AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
-- Select function type --
AND p.proisagg -- Agregate function
-- AND p.proiswindow -- Window function
-- AND p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype
-- AND NOT p.proisagg AND NOT p.proiswindow AND NOT p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype -- Normal function
-- AND NOT p.proname LIKE ANY(array['max','min','avg','sum','count'])
ORDER BY 1, 2, 4
-----------------------------------------------------------------------------------------------------------------
And full output
| Schema | proname | pg_get_function_result | pg_get_function_arguments | nargs |
|------------|---------------------|-----------------------------|------------------------------------|-------|
| pg_catalog | array_agg | anyarray | anyelement | 1 |
| pg_catalog | avg | numeric | bigint | 1 |
| pg_catalog | avg | double precision | double precision | 1 |
| pg_catalog | avg | numeric | integer | 1 |
| pg_catalog | avg | interval | interval | 1 |
| pg_catalog | avg | numeric | numeric | 1 |
| pg_catalog | avg | double precision | real | 1 |
| pg_catalog | avg | numeric | smallint | 1 |
| pg_catalog | bit_and | bigint | bigint | 1 |
| pg_catalog | bit_and | bit | bit | 1 |
| pg_catalog | bit_and | integer | integer | 1 |
| pg_catalog | bit_and | smallint | smallint | 1 |
| pg_catalog | bit_or | bigint | bigint | 1 |
| pg_catalog | bit_or | bit | bit | 1 |
| pg_catalog | bit_or | integer | integer | 1 |
| pg_catalog | bit_or | smallint | smallint | 1 |
| pg_catalog | bool_and | boolean | boolean | 1 |
| pg_catalog | bool_or | boolean | boolean | 1 |
| pg_catalog | corr | double precision | double precision, double precision | 2 |
| pg_catalog | count | bigint | | 0 |
| pg_catalog | count | bigint | "any" | 1 |
| pg_catalog | covar_pop | double precision | double precision, double precision | 2 |
| pg_catalog | covar_samp | double precision | double precision, double precision | 2 |
| pg_catalog | every | boolean | boolean | 1 |
| pg_catalog | json_agg | json | anyelement | 1 |
| pg_catalog | max | abstime | abstime | 1 |
| pg_catalog | max | anyarray | anyarray | 1 |
| pg_catalog | max | anyenum | anyenum | 1 |
| pg_catalog | max | bigint | bigint | 1 |
| pg_catalog | max | character | character | 1 |
| pg_catalog | max | date | date | 1 |
| pg_catalog | max | double precision | double precision | 1 |
| pg_catalog | max | integer | integer | 1 |
| pg_catalog | max | interval | interval | 1 |
| pg_catalog | max | money | money | 1 |
| pg_catalog | max | numeric | numeric | 1 |
| pg_catalog | max | oid | oid | 1 |
| pg_catalog | max | real | real | 1 |
| pg_catalog | max | smallint | smallint | 1 |
| pg_catalog | max | text | text | 1 |
| pg_catalog | max | tid | tid | 1 |
| pg_catalog | max | timestamp without time zone | timestamp without time zone | 1 |
| pg_catalog | max | timestamp with time zone | timestamp with time zone | 1 |
| pg_catalog | max | time without time zone | time without time zone | 1 |
| pg_catalog | max | time with time zone | time with time zone | 1 |
| pg_catalog | min | abstime | abstime | 1 |
| pg_catalog | min | anyarray | anyarray | 1 |
| pg_catalog | min | anyenum | anyenum | 1 |
| pg_catalog | min | bigint | bigint | 1 |
| pg_catalog | min | character | character | 1 |
| pg_catalog | min | date | date | 1 |
| pg_catalog | min | double precision | double precision | 1 |
| pg_catalog | min | integer | integer | 1 |
| pg_catalog | min | interval | interval | 1 |
| pg_catalog | min | money | money | 1 |
| pg_catalog | min | numeric | numeric | 1 |
| pg_catalog | min | oid | oid | 1 |
| pg_catalog | min | real | real | 1 |
| pg_catalog | min | smallint | smallint | 1 |
| pg_catalog | min | text | text | 1 |
| pg_catalog | min | tid | tid | 1 |
| pg_catalog | min | timestamp without time zone | timestamp without time zone | 1 |
| pg_catalog | min | timestamp with time zone | timestamp with time zone | 1 |
| pg_catalog | min | time without time zone | time without time zone | 1 |
| pg_catalog | min | time with time zone | time with time zone | 1 |
| pg_catalog | regr_avgx | double precision | double precision, double precision | 2 |
| pg_catalog | regr_avgy | double precision | double precision, double precision | 2 |
| pg_catalog | regr_count | bigint | double precision, double precision | 2 |
| pg_catalog | regr_intercept | double precision | double precision, double precision | 2 |
| pg_catalog | regr_r2 | double precision | double precision, double precision | 2 |
| pg_catalog | regr_slope | double precision | double precision, double precision | 2 |
| pg_catalog | regr_sxx | double precision | double precision, double precision | 2 |
| pg_catalog | regr_sxy | double precision | double precision, double precision | 2 |
| pg_catalog | regr_syy | double precision | double precision, double precision | 2 |
| pg_catalog | stddev | numeric | bigint | 1 |
| pg_catalog | stddev | double precision | double precision | 1 |
| pg_catalog | stddev | numeric | integer | 1 |
| pg_catalog | stddev | numeric | numeric | 1 |
| pg_catalog | stddev | double precision | real | 1 |
| pg_catalog | stddev | numeric | smallint | 1 |
| pg_catalog | stddev_pop | numeric | bigint | 1 |
| pg_catalog | stddev_pop | double precision | double precision | 1 |
| pg_catalog | stddev_pop | numeric | integer | 1 |
| pg_catalog | stddev_pop | numeric | numeric | 1 |
| pg_catalog | stddev_pop | double precision | real | 1 |
| pg_catalog | stddev_pop | numeric | smallint | 1 |
| pg_catalog | stddev_samp | numeric | bigint | 1 |
| pg_catalog | stddev_samp | double precision | double precision | 1 |
| pg_catalog | stddev_samp | numeric | integer | 1 |
| pg_catalog | stddev_samp | numeric | numeric | 1 |
| pg_catalog | stddev_samp | double precision | real | 1 |
| pg_catalog | stddev_samp | numeric | smallint | 1 |
| pg_catalog | string_agg | bytea | bytea, bytea | 2 |
| pg_catalog | string_agg | text | text, text | 2 |
| pg_catalog | sum | numeric | bigint | 1 |
| pg_catalog | sum | double precision | double precision | 1 |
| pg_catalog | sum | bigint | integer | 1 |
| pg_catalog | sum | interval | interval | 1 |
| pg_catalog | sum | money | money | 1 |
| pg_catalog | sum | numeric | numeric | 1 |
| pg_catalog | sum | real | real | 1 |
| pg_catalog | sum | bigint | smallint | 1 |
| pg_catalog | var_pop | numeric | bigint | 1 |
| pg_catalog | var_pop | double precision | double precision | 1 |
| pg_catalog | var_pop | numeric | integer | 1 |
| pg_catalog | var_pop | numeric | numeric | 1 |
| pg_catalog | var_pop | double precision | real | 1 |
| pg_catalog | var_pop | numeric | smallint | 1 |
| pg_catalog | var_samp | numeric | bigint | 1 |
| pg_catalog | var_samp | double precision | double precision | 1 |
| pg_catalog | var_samp | numeric | integer | 1 |
| pg_catalog | var_samp | numeric | numeric | 1 |
| pg_catalog | var_samp | double precision | real | 1 |
| pg_catalog | var_samp | numeric | smallint | 1 |
| pg_catalog | variance | numeric | bigint | 1 |
| pg_catalog | variance | double precision | double precision | 1 |
| pg_catalog | variance | numeric | integer | 1 |
| pg_catalog | variance | numeric | numeric | 1 |
| pg_catalog | variance | double precision | real | 1 |
| pg_catalog | variance | numeric | smallint | 1 |
| pg_catalog | xmlagg | xml | xml | 1 |
| public | int_array_aggregate | integer[] | integer | 1 |
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment