postgresql 在执行格式中调用架构

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

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

  1. /* ------------- CRIA A TABELA BOTS ------------- */
  2. CREATE TEMPORARY TABLE DEF_BOTS (
  3. LIN INT,
  4. NOME VARCHAR(5)
  5. );
  6. INSERT INTO DEF_BOTS(NOME)
  7. VALUES
  8. ('BOT_1'),
  9. ('BOT_2'),
  10. ('BOT_3');
  11. UPDATE DEF_BOTS A
  12. SET LIN=B.LIN
  13. FROM (SELECT ROW_NUMBER() OVER(ORDER BY NOME) AS LIN, NOME FROM DEF_BOTS)B
  14. WHERE A.NOME=B.NOME;
  15. /* ------------- CRIA AS TABELAS TEST ------------- */
  16. DO $$
  17. DECLARE COUNTER INTEGER:=1;
  18. MAX_COUNTER INTEGER;
  19. TABELA TEXT;
  20. BEGIN
  21. SELECT MAX(LIN) INTO MAX_COUNTER FROM DEF_BOTS;
  22. WHILE COUNTER <= MAX_COUNTER LOOP
  23. SELECT NOME INTO TABELA FROM DEF_BOTS WHERE LIN=COUNTER;
  24. EXECUTE FORMAT('CREATE TEMPORARY TABLE %I(
  25. "var1" VARCHAR (30),
  26. "var2" VARCHAR(50),
  27. "bigvar3" VARCHAR(65535),
  28. "var4" VARCHAR(250),
  29. "var5" VARCHAR(250),
  30. "var6" VARCHAR(250),
  31. "datetime1" TIMESTAMP,
  32. "datetime2" TIMESTAMP
  33. )','TB_TEST_' || TABELA);
  34. COUNTER:= COUNTER + 1;
  35. END LOOP;
  36. END $$;
  37. SELECT * FROM "TB_TEST_BOT_1"
9udxz4iz

9udxz4iz1#

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

相关问题