Postgresql – Possible to get comprehensive view on all defined names in PostgreSQL

postgresql

The SQL standard prescribes a rather large vocabulary; on top of this, RDBMSs implement their own constructs. Then they add hundreds of function names, internal and user-facing. On top of that, users go and define schema(ta)s, tables, indexes, custom functions, types, whatnot. Now, when I want to, say, create a function, a table or a column I have to name it. Some names (like all I think) are no-go, some names (for some kinds of objects?) are OK even though they clash (b/c they are not ambiguous). Good names clearly should be not confusing. But how can I get an exhaustive list of all the names that a given PostgreSQL DB instance 'knows' at any given point in time?

FWIW I scanned to Postgres docs for lists, but they don't seem to have any; instead, there's a list of SQL commands that does not list e.g. order and by (b/c that's subsumed under select); I'd have to peruse all the pages of Chapter 9. Functions and Operators to compile a list of all the function names. Not to speak of extensions, which by default put their stuff into the public schema.

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 know there's psql's \d+ and so on, and I already have a small collection of utility views to provide subsets of what I'm looking for, but nothing resembling a Grand Inclusive List of Everything.

Best Answer

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 <> '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');
Related Question