如何用PL/pgSQL循环函数从导入的外部模式创建表

Got it, let's work through why your table creation isn't sticking when migrating between PostgreSQL servers using foreign tables. Since you're opting out of pg_dump and already using IMPORT FOREIGN SCHEMA, the core issue is likely in how you're generating or executing the table creation logic. Let's break this down step by step.

Step 1: First, Confirm Your Foreign Tables Exist #

Before diving into creation logic, double-check that your foreign tables were actually imported correctly. Run these queries to verify:

-- List all foreign tables in your schema (psql command)
\det your_foreign_schema.*;

-- Or via information schema (works in any client)
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'your_foreign_schema' 
  AND table_type = 'FOREIGN TABLE';

If no tables show up, you'll need to fix your IMPORT FOREIGN SCHEMA statement first—ensure the foreign server is properly configured, permissions are set, and the schema mapping is correct.

Step 2: Use a Dynamic PL/pgSQL Function to Automate Table Creation #

Writing manual CREATE TABLE statements for every foreign table is error-prone. Instead, use a PL/pgSQL function to dynamically generate and execute the creation logic. This handles schema/table name escaping and ensures consistency.

Option 1: Copy Structure + Data in One Go #

This creates the target table and populates it with data from the foreign table in a single step (no constraints/indexes copied, which aligns with your acceptable limitations):

CREATE OR REPLACE FUNCTION migrate_foreign_tables(source_schema text, target_schema text)
RETURNS void AS $$
DECLARE
    table_rec record;
    create_query text;
BEGIN
    -- Create target schema if it doesn't exist
    IF NOT EXISTS (SELECT 1 FROM information_schema.schemata WHERE schema_name = target_schema) THEN
        EXECUTE 'CREATE SCHEMA ' || quote_ident(target_schema);
    END IF;

    -- Loop through all foreign tables in the source schema
    FOR table_rec IN 
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = source_schema 
          AND table_type = 'FOREIGN TABLE'
    LOOP
        -- Generate safe CREATE TABLE AS SELECT statement
        create_query := 'CREATE TABLE ' || 
                        quote_ident(target_schema) || '.' || quote_ident(table_rec.table_name) || 
                        ' AS SELECT * FROM ' || 
                        quote_ident(source_schema) || '.' || quote_ident(table_rec.table_name);
        
        -- Print the query for debugging (optional but helpful to spot issues)
        RAISE NOTICE 'Executing: %', create_query;
        
        -- Run the query
        EXECUTE create_query;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Option 2: Create Structure First, Then Insert Data #

If you want more control (e.g., adding custom constraints later), use LIKE to copy the table structure, then insert data separately:

CREATE OR REPLACE FUNCTION migrate_foreign_tables(source_schema text, target_schema text)
RETURNS void AS $$
DECLARE
    table_rec record;
    create_structure_query text;
    insert_data_query text;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.schemata WHERE schema_name = target_schema) THEN
        EXECUTE 'CREATE SCHEMA ' || quote_ident(target_schema);
    END IF;

    FOR table_rec IN 
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = source_schema 
          AND table_type = 'FOREIGN TABLE'
    LOOP
        -- Create table structure (use INCLUDING ALL if you want to copy constraints/indexes)
        create_structure_query := 'CREATE TABLE ' || 
                                  quote_ident(target_schema) || '.' || quote_ident(table_rec.table_name) || 
                                  ' (LIKE ' || quote_ident(source_schema) || '.' || quote_ident(table_rec.table_name) || 
                                  ' INCLUDING NONE)'; -- INCLUDING NONE skips constraints/indexes
        
        -- Insert data from foreign table
        insert_data_query := 'INSERT INTO ' || 
                             quote_ident(target_schema) || '.' || quote_ident(table_rec.table_name) || 
                             ' SELECT * FROM ' || 
                             quote_ident(source_schema) || '.' || quote_ident(table_rec.table_name);
        
        RAISE NOTICE 'Creating structure: %', create_structure_query;
        EXECUTE create_structure_query;
        
        RAISE NOTICE 'Inserting data: %', insert_data_query;
        EXECUTE insert_data_query;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

How to Run the Function #

Call it with your source foreign schema and target schema names:

SELECT migrate_foreign_tables('my_foreign_schema', 'my_target_schema');

Common Pitfalls to Check #

If the function runs but no tables are created, these are the most likely issues:

• Permissions: The user executing the function needs:
  • SELECT access on all foreign tables
  • CREATE access on the target schema
  • EXECUTE access on the function itself
• Quoting Issues: Always use quote_ident() for schema/table names—if your names have spaces, special characters, or match PostgreSQL keywords, unquoted names will break the query silently.
• Foreign Server Connection: Even if you think the query runs, test a manual select on a foreign table: SELECT * FROM my_foreign_schema.my_table LIMIT 1; If this fails, your foreign server connection is broken, which would prevent table creation via AS SELECT.
• Empty Foreign Tables: If the foreign table has no data, CREATE TABLE AS SELECT will still create an empty table—check information_schema.tables for the target schema to confirm it exists.

Remember, this approach won't copy sequences, triggers, or custom constraints (unless you adjust the INCLUDING clause), but you mentioned that's acceptable for your use case.

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