求PostgreSQL递归剖析Schema结构的SQL语句（Tkinter树形视图用）

Got it, let's break this down step by step. You're trying to replicate DBeaver's schema browser tree in Tkinter, right? The core challenge is pulling all nested entities from PostgreSQL—from top-level schemas down to leaf nodes like table columns or index fields. Below are the targeted SQL queries for each category you mentioned, plus a plan to tie them into your Tkinter treeview with lazy loading (so you don't crash your app on large databases).

First: Get Top-Level Schemas #

You already have this covered, but just to confirm:

SELECT schema_name FROM information_schema.schemata;

This gives you all schemas including information_schema, pg_catalog, and public.

Next: Query Entities Under Each Schema #

For each schema, you'll need to pull data for the 8 categories you listed. I've included queries for each, plus their child entities (like table columns or index fields) to get to the leaf nodes.

1. Tables & Their Children #

Get tables in a schema:

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = :schema_name 
AND table_type = 'BASE TABLE'; -- Excludes views, only gets actual tables

Get columns for a specific table:

SELECT column_name, data_type, is_nullable
FROM information_schema.columns 
WHERE table_schema = :schema_name 
AND table_name = :table_name;

Get constraints for a specific table:

SELECT constraint_name, constraint_type
FROM information_schema.table_constraints 
WHERE table_schema = :schema_name 
AND table_name = :table_name;

2. Views & Their Children #

Get views in a schema:

SELECT table_name 
FROM information_schema.views 
WHERE table_schema = :schema_name;

Get view columns:

SELECT column_name, data_type
FROM information_schema.columns 
WHERE table_schema = :schema_name 
AND table_name = :view_name;

3. Materialized Views & Their Children #

PostgreSQL doesn't store these in information_schema, so use the system table pg_matviews:

Get materialized views:

SELECT matview_name 
FROM pg_matviews 
WHERE schemaname = :schema_name;

Get materialized view columns:

Same as table/view columns—use information_schema.columns with the matview name.

4. Indexes & Their Children #

Get indexes in a schema:

SELECT index_name, table_name
FROM information_schema.indexes 
WHERE table_schema = :schema_name;

Get columns tied to an index:

SELECT column_name
FROM information_schema.index_columns 
WHERE table_schema = :schema_name 
AND table_name = :table_name 
AND index_name = :index_name;

5. Functions #

Get functions in a schema:

SELECT routine_name, data_type, routine_definition
FROM information_schema.routines 
WHERE routine_schema = :schema_name 
AND routine_type = 'FUNCTION'; -- Filters out stored procedures

6. Sequences & Their Details #

Get sequences in a schema:

SELECT sequence_name 
FROM information_schema.sequences 
WHERE sequence_schema = :schema_name;

Get sequence metadata (leaf node details):

SELECT start_value, increment_by, max_value
FROM information_schema.sequences 
WHERE sequence_schema = :schema_name 
AND sequence_name = :sequence_name;

7. User-Defined Data Types & Their Children #

Get custom data types (composite, domains, enums):

SELECT udt_name, udt_type
FROM information_schema.user_defined_types 
WHERE udt_schema = :schema_name;

Get columns for composite types:

SELECT attribute_name, data_type
FROM information_schema.attributes 
WHERE udt_schema = :schema_name 
AND udt_name = :composite_type_name;

8. Aggregate Functions #

PostgreSQL stores aggregates in system tables, so join pg_aggregate, pg_proc, and pg_namespace:

SELECT p.proname AS aggregate_name
FROM pg_aggregate a
JOIN pg_proc p ON a.aggfnoid = p.oid
JOIN pg_namespace ns ON p.pronamespace = ns.oid
WHERE ns.nspname = :schema_name;

Tkinter Treeview Implementation Tip #

Don't load all data at once—use lazy loading (load child nodes only when the user expands a parent). Here's a quick logic outline:

1. Start by adding all schemas to the treeview root.
2. Bind the <TreeviewOpen> event to a function that loads child nodes on demand:
   • When a schema is expanded, add the 8 category nodes (Tables, Views, etc.).
   • When a category is expanded, run the corresponding SQL query and add its entities.
   • When an entity like a table is expanded, add its child nodes (Columns, Constraints) and load their details when expanded.

Example snippet (pseudocode for your Tkinter setup):

def on_tree_expand(event):
    item = tree.focus()
    item_values = tree.item(item, "values")
    item_type = item_values[0]

    # Load categories when a schema is expanded
    if item_type == "schema":
        schema_name = tree.item(item, "text")
        categories = ["Tables", "Views", "Materialized Views", "Indexes", "Functions", "Sequences", "Data Types", "Aggregate Functions"]
        for cat in categories:
            tree.insert(item, "end", text=cat, values=("category", schema_name, cat))
    
    # Load tables when the Tables category is expanded
    elif item_type == "category":
        schema_name = item_values[1]
        category = item_values[2]
        if category == "Tables":
            cur.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = %s AND table_type = 'BASE TABLE'", (schema_name,))
            for table in cur.fetchall():
                tree.insert(item, "end", text=table[0], values=("table", schema_name, table[0]))

    # Load columns when a table is expanded
    elif item_type == "table":
        schema_name = item_values[1]
        table_name = item_values[2]
        tree.insert(item, "end", text="Columns", values=("table_child", schema_name, table_name, "columns"))

Key Notes #

• Always use parameterized queries (%s placeholders) to avoid SQL injection.
• System schemas like pg_catalog have thousands of entities—add an option to hide them by default if needed.
• Ensure your PostgreSQL user has SELECT permissions on information_schema and system tables like pg_matviews and pg_aggregate.

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