PostgreSQL 15：如何在指定Schema中循环创建非临时表？

关键修改点 #

• 移除CREATE TEMPORARY TABLE中的TEMPORARY关键字，切换为创建永久表
• 在表名前拼接目标Schema名称，用FORMAT函数的%I占位符保证标识符安全转义
• 提前确认目标Schema已存在（若不存在需先创建）

修改后的完整脚本 #

假设要将表创建到名为target_schema的Schema中，脚本如下：

/* ------------- 创建机器人定义表 ------------- */
CREATE TEMPORARY TABLE DEF_BOTS (
LIN INT,
NOME VARCHAR(5)
);

INSERT INTO DEF_BOTS(NOME)
VALUES
('BOT_1'),
('BOT_2'),
('BOT_3');

UPDATE DEF_BOTS A
SET LIN=B.LIN
FROM (SELECT ROW_NUMBER() OVER(ORDER BY NOME) AS LIN, NOME FROM DEF_BOTS)B
WHERE A.NOME=B.NOME;

/* ------------- 提前创建目标Schema（如果不存在的话） ------------- */
CREATE SCHEMA IF NOT EXISTS target_schema;

/* ------------- 循环创建指定Schema下的测试表 ------------- */
DO $$
DECLARE COUNTER INTEGER:=1;
    MAX_COUNTER INTEGER;
    TABELA TEXT;
    TARGET_SCHEMA TEXT := 'target_schema'; -- 这里指定目标Schema名称
BEGIN
SELECT MAX(LIN) INTO MAX_COUNTER FROM DEF_BOTS;
WHILE COUNTER <= MAX_COUNTER LOOP
SELECT NOME INTO TABELA FROM DEF_BOTS WHERE LIN=COUNTER;    
EXECUTE FORMAT('CREATE TABLE %I.%I(
"var1" VARCHAR (30),
"var2" VARCHAR(50),
"bigvar3" VARCHAR(65535),
"var4" VARCHAR(250),
"var5" VARCHAR(250),
"var6" VARCHAR(250),
"datetime1" TIMESTAMP,
"datetime2" TIMESTAMP
)', TARGET_SCHEMA, 'TB_TEST_' || TABELA);
COUNTER:= COUNTER + 1;
END LOOP;
END $$;

-- 查询时需带上Schema前缀
SELECT * FROM target_schema."TB_TEST_BOT_1";

补充说明 #

1. CREATE SCHEMA IF NOT EXISTS target_schema：确保目标Schema存在，避免因Schema不存在导致创建表失败
2. FORMAT('CREATE TABLE %I.%I(...)', TARGET_SCHEMA, 'TB_TEST_' || TABELA)：通过%I分别转义Schema名和表名，兼容包含特殊字符的标识符
3. 查询表时必须带上Schema前缀，或者通过SET search_path TO target_schema, public;设置搜索路径，否则数据库会默认在publicSchema中查找表

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