Possible to get comprehensive view on all defined names in PostgreSQL?
RDBMSs implement their own constructs. Then they add hundreds of function names, internal and user-facing. 


All of them for PostgreSQL are defined in the schema pg_catalog. You can access them with the S argument in psql. For example \dfS for show all system functions.


  On top of that, users go and define schema(ta)s, tables, indexes, custom functions, types, whatnot.


You can never know what users create. You'll have to check that out for each install by querying the catalog.


  Is there a truly comprehensive way to list all these 'defined words' complete with their roles, origins, comments etc. in a single huge list? 


I'm not 100% sure it's what you want, but perhaps you're looking for the Reserved Keyword list?

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 &lt;&gt; 'pg_catalog'
      AND n.nspname &lt;&gt; '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 &lt;&gt; 'pg_catalog'
      AND n.nspname &lt;&gt; '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');