Postgres:函数中的临时表是持久的。为什么？

Question

我的postgresql数据库中有以下函数：

CREATE OR REPLACE FUNCTION get_unused_part_ids()
  RETURNS integer[] AS
$BODY$
DECLARE
  part_ids integer ARRAY;
BEGIN
  create temporary table tmp_parts
      as
  select vendor_id, part_number, max(price) as max_price
    from refinery_akouo_parts
   where retired = false
   group by vendor_id, part_number
  having min(price) < max(price);

  -- do some work etc etc

  -- simulate ids being returned
  part_ids = '{1,2,3,4}';
  return part_ids;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION get_unused_part_ids()
  OWNER TO postgres;

这会编译，但当我运行时：

select get_unused_part_ids();

临时表tmp_parts仍然存在。之后我可以对它做个选择。原谅我，因为我已经习惯了具有MSSQL/MSSQL的特定功能。MSSQL就不是这样了。我做错了什么？

Answer 1

只有在会议结束时才会删除该表。您需要指定要删除的ON选项，它将在事务结束时删除该表。

  create temporary table tmp_parts 
  on commit drop
      as
  select vendor_id, part_number, max(price) as max_price
    from refinery_akouo_parts
   where retired = false
   group by vendor_id, part_number
  having min(price) < max(price);

Answer 2

手册后

临时表在会话结束时自动删除，或者在当前事务结束时自动删除(请参见下面提交)

会话在断开连接后结束。不是在事务提交之后。因此，默认行为是保留临时表，直到连接仍然打开为止。您必须添加ON COMMIT DROP;以实现您想要的行为：

create temporary table tmp_parts on commit drop
      as
  select vendor_id, part_number, max(price) as max_price
    from refinery_akouo_parts
   where retired = false
   group by vendor_id, part_number
  having min(price) < max(price)
on commit drop;

Answer 3

在这两个数据库中，临时表被不同的对待。在Server中，它们将在创建它们的存储过程的末尾自动删除。

在Postgres中，临时表分配给会话(或事务)，如文档中所解释的那样

如果指定，表将作为临时表创建。临时表在会话结束时自动删除，或者在当前事务结束时自动删除(参见下面提交的内容)。当临时表存在时，现有的同名永久表对当前会话不可见，除非用架构限定名引用它们。在临时表上创建的任何索引也都是自动临时的。

这种概念介于Server中的常规临时表和全局临时表之间(全局临时表以##开头)。