sql - Postgres : Temporary table in function is persistent. 为什么？

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 仍然存在。之后我可以对其进行选择。请原谅我，因为我已经习惯了 t-sql/MSSQL 的特定功能。 MSSQL 不会出现这种情况。我做错了什么？

Answer 1

该表只会在 session 结束时被删除。您需要指定 ON COMMIT 选项来删除，它会在事务结束时删除表。

  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);