Update
"You'll have to check that out for each install by querying the catalog."—exactly, can you tell me such a query? – John Frazer Jun 17 at 8:03
Sure, so what you want to do is run psql -E, then run \?. This gives you the far more than what you've asked for (viz. list tables, views, and sequences , aggregates , access methods , tablespaces , conversions , casts , default privileges , domains , foreign tables , foreign servers , user mappings , foreign-data wrappers , functions , text search configurations , text search dictionaries , text search parsers , text search templates , roles , indexes , large objects, same as \lo_list , procedural languages , materialized views , schemas , operators , collations , table, view, and sequence access privileges , sequences , tables , data types , roles , views , foreign tables , extensions , event triggers , databases). Next pick one of them, like \df which lists functions,
You'll get a query like
SELECT n.nspname as "Schema",
p.proname as "Name",
pg_catalog.pg_get_function_result(p.oid) as "Result data type",
pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
CASE
WHEN p.proisagg THEN 'agg'
WHEN p.proiswindow THEN 'window'
WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
ELSE 'normal'
END as "Type"
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'
ORDER BY 1, 2, 4;
This is internally the query that psql uses to get this information. You can cut it up though if you only want the name and don't care about whether or not the function is an aggregate, window, or trigger; and, you can cut it further if you don't care about the result and arguments types.
SELECT n.nspname as "Schema",
p.proname as "Name"
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'
ORDER BY 1, 2;
Etc., or of course you can query the slower information_schema interface which is part of the SQL spec and standardized.
SELECT routine_catalog, routine_schema, routine_name
FROM information_schema.routines
WHERE routine_schema NOT IN ('pg_catalog', 'information_schema');
