基于PL/pgSQL检测并删除PostGIS数据库空列的技术方案

Got it, let's build out a robust, reusable solution for detecting and removing those empty columns from your PostGIS database after the FME migration. You already have a start with the single-column check, so we'll expand that into schema-wide and database-wide tools with safety features like dry runs—super important for avoiding accidental data loss!

First, let's refine your single-column check function to be safer (avoiding SQL injection risks) and more reliable:

Step 1: Refined Single-Column Empty Check Function #

CREATE OR REPLACE FUNCTION is_column_empty(p_schema_name TEXT, p_table_name TEXT, p_column_name TEXT)
RETURNS BOOLEAN AS $$
DECLARE
    v_non_null_count BIGINT;
    v_table_exists BOOLEAN;
BEGIN
    -- Verify the table exists first
    SELECT EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_schema = p_schema_name AND table_name = p_table_name
    ) INTO v_table_exists;

    IF NOT v_table_exists THEN
        RAISE EXCEPTION 'Table %.% does not exist', p_schema_name, p_table_name;
    END IF;

    -- Verify the column exists
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = p_schema_name AND table_name = p_table_name AND column_name = p_column_name
    ) THEN
        RAISE EXCEPTION 'Column %.%.% does not exist', p_schema_name, p_table_name, p_column_name;
    END IF;

    -- Count non-null values in the column (safe dynamic SQL with format())
    EXECUTE format(
        'SELECT COUNT(*) FROM %I.%I WHERE %I IS NOT NULL',
        p_schema_name, p_table_name, p_column_name
    ) INTO v_non_null_count;

    -- Return TRUE if all values are NULL (or table is empty)
    RETURN v_non_null_count = 0;
END;
$$ LANGUAGE plpgsql STABLE;

Step 2: Function to List All Empty Columns in a Schema #

This function scans a target schema and returns every empty column across all its tables:

CREATE OR REPLACE FUNCTION get_empty_columns(p_schema_name TEXT DEFAULT 'public')
RETURNS TABLE(
    schema_name TEXT,
    table_name TEXT,
    column_name TEXT,
    data_type TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        c.table_schema::TEXT,
        c.table_name::TEXT,
        c.column_name::TEXT,
        c.data_type::TEXT
    FROM information_schema.columns c
    JOIN information_schema.tables t 
        ON c.table_schema = t.table_schema AND c.table_name = t.table_name
    WHERE t.table_type = 'BASE TABLE' -- Skip views, only check actual tables
      AND c.table_schema = p_schema_name
      AND is_column_empty(c.table_schema, c.table_name, c.column_name)
    ORDER BY c.table_schema, c.table_name, c.column_name;
END;
$$ LANGUAGE plpgsql STABLE;

Step 3: Reusable Function to Delete Empty Columns (With Dry Run) #

This is the core tool—you can run it in dry-run mode first to preview changes, then execute the actual deletions when you're confident:

CREATE OR REPLACE FUNCTION delete_empty_columns(
    p_schema_name TEXT DEFAULT 'public',
    p_dry_run BOOLEAN DEFAULT TRUE
)
RETURNS TABLE(
    operation TEXT,
    result TEXT
) AS $$
DECLARE
    v_col_record RECORD;
    v_drop_sql TEXT;
BEGIN
    -- Loop through every empty column in the schema
    FOR v_col_record IN SELECT * FROM get_empty_columns(p_schema_name) LOOP
        v_drop_sql := format(
            'ALTER TABLE %I.%I DROP COLUMN %I',
            v_col_record.schema_name, v_col_record.table_name, v_col_record.column_name
        );

        IF p_dry_run THEN
            -- Just return the SQL that would run, no changes made
            RETURN NEXT ('DRY RUN', v_drop_sql);
        ELSE
            -- Execute the drop, with error handling for dependencies
            BEGIN
                EXECUTE v_drop_sql;
                RETURN NEXT ('SUCCESS', format('Dropped column %.%.%', v_col_record.schema_name, v_col_record.table_name, v_col_record.column_name));
            EXCEPTION
                WHEN OTHERS THEN
                    RETURN NEXT ('ERROR', format('Failed to drop column %.%.%: %s', v_col_record.schema_name, v_col_record.table_name, v_col_record.column_name, SQLERRM));
            END;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Step 4: Extend to Scan the Entire Database #

If you need to clean up empty columns across all non-system schemas (avoiding pg_catalog, information_schema, etc.), use this wrapper function:

CREATE OR REPLACE FUNCTION delete_empty_columns_all_schemas(p_dry_run BOOLEAN DEFAULT TRUE)
RETURNS TABLE(
    schema_name TEXT,
    operation TEXT,
    result TEXT
) AS $$
DECLARE
    v_schema TEXT;
    v_result RECORD;
BEGIN
    -- Iterate over all user-created schemas
    FOR v_schema IN 
        SELECT nspname FROM pg_namespace 
        WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
    LOOP
        FOR v_result IN SELECT * FROM delete_empty_columns(v_schema, p_dry_run) LOOP
            RETURN NEXT (v_schema, v_result.operation, v_result.result);
        END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

How to Use These Tools #

1. List all empty columns in a schema (e.g., public):

SELECT * FROM get_empty_columns('public');

2. Preview deletions (dry run) for a schema:

SELECT * FROM delete_empty_columns('public', TRUE);

3. Actually delete empty columns in a schema:

SELECT * FROM delete_empty_columns('public', FALSE);

4. Preview deletions across all schemas:

SELECT * FROM delete_empty_columns_all_schemas(TRUE);

5. Delete empty columns across all schemas:

SELECT * FROM delete_empty_columns_all_schemas(FALSE);

Critical Notes #

• Backup first: Always take a database backup before running any DDL operations like dropping columns—even with dry runs, mistakes can happen.
• Permissions: Ensure your user has ALTER permissions on all tables in the target schemas.
• Dependencies: If a column is part of a constraint (foreign key, index, trigger), the drop will fail. The function will return an error message, so you'll need to resolve those dependencies manually first.
• Performance: For very large tables, the is_column_empty function will scan the table to count non-null values. If speed is an issue, you could add a check for empty tables first (since empty tables have all empty columns).

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