如何查看PostgreSQL数据库对象注释？含函数等对象查询方案

Got it, let's tackle this since you're using COMMENT ON to build smart documentation and ran into issues with function comments. The PostgreSQL catalogs hold all the metadata you need—here's how to access it clearly:

Key Catalog Tables for Different Objects #

Each database object type stores its metadata in a specific system catalog table:

• Tables & Views: pg_class (this is why obj_description('schema.table'::regclass) works—regclass maps directly to pg_class.oid)
• Functions: pg_proc (functions are tracked here, with comments linked via pg_description)

Querying Comments & Metadata for Tables/Views #

You already know obj_description, but here's a more comprehensive query to list all tables/views with their schemas, types, and comments:

SELECT 
  n.nspname AS schema_name,
  c.relname AS object_name,
  CASE c.relkind 
    WHEN 'r' THEN 'table'
    WHEN 'v' THEN 'view'
  END AS object_type,
  COALESCE(d.description, 'No comment added') AS comment
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_description d ON c.oid = d.objoid AND d.classoid = 'pg_class'::regclass
WHERE c.relkind IN ('r', 'v') -- Filter for tables (r) and views (v)
ORDER BY schema_name, object_type, object_name;

Querying Comments & Metadata for Functions #

obj_description works for functions too—you just need to specify the correct catalog class. For a single function:

-- Get comment for a specific function (include signature if overloaded)
SELECT obj_description('my_schema.my_function'::regproc, 'pg_proc') AS function_comment;

To list all functions with their signatures and comments (excluding system functions by default):

SELECT 
  n.nspname AS schema_name,
  p.proname AS function_name,
  pg_get_function_identity_arguments(p.oid) AS function_signature,
  COALESCE(d.description, 'No comment added') AS comment
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
LEFT JOIN pg_description d ON p.oid = d.objoid AND d.classoid = 'pg_proc'::regclass
-- Exclude system schemas to focus on your custom functions
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schema_name, function_name;

The pg_get_function_identity_arguments function is handy here—it returns the function's argument list to distinguish overloaded functions with the same name.

Bonus: Unified Query for All Object Types (Optional) #

If you want a single query to pull tables, views, and functions together:

-- Combine tables/views and functions into one result set
SELECT 
  schema_name,
  object_name,
  object_type,
  comment
FROM (
  -- Tables and views
  SELECT 
    n.nspname AS schema_name,
    c.relname AS object_name,
    CASE c.relkind 
      WHEN 'r' THEN 'table'
      WHEN 'v' THEN 'view'
    END AS object_type,
    COALESCE(d.description, 'No comment added') AS comment
  FROM pg_class c
  JOIN pg_namespace n ON c.relnamespace = n.oid
  LEFT JOIN pg_description d ON c.oid = d.objoid AND d.classoid = 'pg_class'::regclass
  WHERE c.relkind IN ('r', 'v')

  UNION ALL

  -- Functions
  SELECT 
    n.nspname AS schema_name,
    p.proname AS object_name,
    'function' AS object_type,
    COALESCE(d.description, 'No comment added') AS comment
  FROM pg_proc p
  JOIN pg_namespace n ON p.pronamespace = n.oid
  LEFT JOIN pg_description d ON p.oid = d.objoid AND d.classoid = 'pg_proc'::regclass
  WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
) AS all_objects
ORDER BY schema_name, object_type, object_name;

This should give you everything you need to generate those smart documentation reports using PostgreSQL's built-in metadata!

内容的提问来源于stack exchange，提问作者Peter Krauss