编写PGSQL脚本：从database1取数并更新database2表数据

完全可行，但你提供的示例脚本存在几个关键问题，无法直接运行，需要修正后才能保证严谨性和正确性。以下是详细说明和优化后的实现方案：

核心问题说明 #

1. 跨库访问语法错误：PostgreSQL不支持直接用database1.table的语法跨数据库访问，必须通过dblink扩展或外部数据包装器（FDW）实现跨库连接。
2. 数据稳定性不足：LIMIT 1未搭配ORDER BY，无法保证每次获取的是确定的记录，可能导致数据不一致。
3. 空值未处理：如果从database1未查询到数据，变量会为空，直接更新可能导致业务异常。
4. 异常无处理：脚本未包含错误捕获逻辑，执行失败时无法回滚事务或给出明确错误信息。

方案一：使用dblink扩展（临时跨库访问） #

步骤1：安装dblink扩展 #

CREATE EXTENSION IF NOT EXISTS dblink;

步骤2：严谨的跨库操作脚本 #

DO $$
DECLARE
    v_data INTEGER;
    -- 填写database1的连接信息，根据实际环境调整
    v_dblink_conn TEXT := 'dbname=database1 user=your_user password=your_pwd host=127.0.0.1 port=5432';
BEGIN
    -- 从database1获取数据，通过ORDER BY保证结果稳定
    SELECT config INTO v_data
    FROM dblink(v_dblink_conn, 'SELECT config FROM "table" ORDER BY id LIMIT 1') AS t(config INTEGER);

    -- 处理空值：未获取到数据时抛出异常或跳过操作
    IF v_data IS NULL THEN
        RAISE EXCEPTION '未从database1获取到有效数据';
        -- 若允许跳过更新，替换为 RETURN;
    END IF;

    -- 执行更新（若需插入则替换为INSERT语句）
    UPDATE database2."table" 
    SET input_column = v_data;

    -- 若要插入数据，使用以下语句：
    -- INSERT INTO database2."table" (input_column) VALUES (v_data);

EXCEPTION
    WHEN OTHERS THEN
        -- 异常回滚并输出错误信息
        ROLLBACK;
        RAISE EXCEPTION '脚本执行失败: %', SQLERRM;
END $$;

方案二：使用外部数据包装器（FDW）（长期跨库访问） #

如果需要频繁跨库操作，推荐使用FDW创建持久化的外部表映射：

步骤1：创建外部服务器 #

CREATE SERVER database1_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'database1', host '127.0.0.1', port '5432');

步骤2：创建用户映射 #

CREATE USER MAPPING FOR current_user
SERVER database1_server
OPTIONS (user 'database1_user', password 'database1_pwd');

步骤3：创建外部表 #

CREATE FOREIGN TABLE database1_table (
    id INTEGER,
    config INTEGER
)
SERVER database1_server
OPTIONS (schema_name 'public', table_name 'table');

步骤4：编写操作脚本 #

DO $$
DECLARE
    v_data INTEGER;
BEGIN
    SELECT config INTO v_data
    FROM database1_table
    ORDER BY id LIMIT 1;

    IF v_data IS NULL THEN
        RAISE EXCEPTION '未从database1获取到有效数据';
    END IF;

    -- 更新或插入数据到database2
    UPDATE database2."table" SET input_column = v_data;
    -- INSERT INTO database2."table" (input_column) VALUES (v_data);

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE EXCEPTION '脚本执行失败: %', SQLERRM;
END $$;

注意事项 #

• 权限要求：执行脚本的用户需拥有database1的读取权限、database2的写入权限，以及安装扩展、创建外部对象的权限。
• 关键字表名：如果表名为PostgreSQL关键字（如table），必须用双引号"table"包裹，否则会触发语法错误。
• 事务一致性：DO块默认在单个事务中执行，异常时会自动回滚，显式捕获异常可提升脚本的可维护性。
• 并发控制：批量操作时需考虑锁机制，避免并发更新导致的数据冲突。

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