postgresql 在执行格式中调用架构

zpgglvta  于 2023-10-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(110)

我结合了一个脚本,在一个循环中创建具有相同列和长度的表。问题是,我想在特定的模式中创建它们,但我无法精确定位。它们当前被创建为临时表。
版本:PostgreSQL 15.2 on x86_64-pc-linux-gnu,compiled by gcc(GCC)11.3.1 20221121(Red Hat 11.3.1-4),64位

/* ------------- CRIA A TABELA BOTS ------------- */
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;

/* ------------- CRIA AS TABELAS TEST ------------- */
DO $$
DECLARE COUNTER INTEGER:=1;
    MAX_COUNTER INTEGER;
    TABELA TEXT;
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 TEMPORARY TABLE %I(
"var1" VARCHAR (30),
"var2" VARCHAR(50),
"bigvar3" VARCHAR(65535),
"var4" VARCHAR(250),
"var5" VARCHAR(250),
"var6" VARCHAR(250),
"datetime1" TIMESTAMP,
"datetime2" TIMESTAMP
)','TB_TEST_' || TABELA);
COUNTER:= COUNTER + 1;
END LOOP;
END $$;

SELECT * FROM "TB_TEST_BOT_1"
9udxz4iz

9udxz4iz1#

这对于临时表是不可能的,请参阅manual
临时表存在于特殊的模式中,因此在创建临时表时不能给出模式名称。

相关问题