求助：编写递归SQL解析PostgreSQL数据库Schema层级结构

Hey there! I get it—building that tree view to mirror DBeaver's schema explorer can be tricky when you need to dig beyond just the top-level schemas. Let’s break down all the SQL queries you’ll need to fetch every object type you mentioned, organized by schema, so you can populate your Tkinter tree recursively.

First, the base query you already know to get all schemas:

SELECT schema_name FROM information_schema.schemata;

Now, for each schema you retrieve, use these queries to pull the different object categories and their entities:

Tables (Base Tables) #

Fetch all regular tables in a schema:

SELECT 
    table_name AS object_name,
    'table' AS object_type
FROM information_schema.tables
WHERE table_schema = '{schema_name}' -- Replace with your target schema name
AND table_type = 'BASE TABLE';

Views #

Get all standard views:

SELECT 
    table_name AS object_name,
    'view' AS object_type
FROM information_schema.views
WHERE table_schema = '{schema_name}'
AND table_type = 'VIEW';

Materialized Views #

PostgreSQL stores these separately from regular views, so we use the pg_matviews catalog table:

SELECT 
    matviewname AS object_name,
    'materialized_view' AS object_type
FROM pg_catalog.pg_matviews
WHERE schemaname = '{schema_name}';

Indexes #

Fetch indexes along with their parent tables (useful if you want to nest indexes under their tables later):

SELECT 
    indexname AS object_name,
    'index' AS object_type,
    tablename AS parent_table
FROM pg_catalog.pg_indexes
WHERE schemaname = '{schema_name}';

Functions #

Retrieve regular functions (adjust the prokind filter if you need stored procedures or other function types):

SELECT 
    proname AS object_name,
    'function' AS object_type,
    pg_get_functiondef(oid) AS definition -- Optional: Get the full function definition
FROM pg_catalog.pg_proc
WHERE pronamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = '{schema_name}')
AND prokind = 'f'; -- 'f' = regular function; use 'p' for procedures, 'w' for window functions

Sequences #

Get all sequences in the schema:

SELECT 
    sequence_name AS object_name,
    'sequence' AS object_type
FROM information_schema.sequences
WHERE sequence_schema = '{schema_name}';

Custom Data Types #

Fetch user-defined composite types, domains, and enums (adjust the typtype filter to include/exclude types):

SELECT 
    typname AS object_name,
    'data_type' AS object_type
FROM pg_catalog.pg_type
WHERE typnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = '{schema_name}')
AND typtype IN ('c', 'd', 'e') -- 'c'=composite, 'd'=domain, 'e'=enum
AND NOT typisdefined = FALSE; -- Exclude undefined/incomplete types

Aggregate Functions #

Pull specifically aggregate functions using the prokind filter:

SELECT 
    proname AS object_name,
    'aggregate_function' AS object_type
FROM pg_catalog.pg_proc
WHERE pronamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = '{schema_name}')
AND prokind = 'a'; -- 'a' denotes aggregate functions

Bonus: Drill Down to Table Columns #

If you want to add columns as child nodes under tables (like DBeaver does), use this query for a specific table:

SELECT 
    column_name AS object_name,
    'column' AS object_type,
    data_type AS column_type
FROM information_schema.columns
WHERE table_schema = '{schema_name}'
AND table_name = '{table_name}';

Implementation Tip for Tkinter #

For better performance (especially with large databases), use lazy loading: only fetch the child nodes when the user expands a parent node. For example:

• Start by loading all schema nodes.
• When a user expands a schema, fetch the category nodes (Tables, Views, etc.) for that schema.
• When a user expands a category like Tables, fetch all tables in that schema and add them as child nodes.
• If you want columns, fetch them only when a table node is expanded.

That should cover all the layers you need to replicate DBeaver's schema tree view. Let me know if you need help tweaking any of these queries for your specific use case!

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