Postgresql COPY 命令 - 如何删除临时文件？

Question

我有程序并且效果很好。

CREATE OR REPLACE FUNCTION f_dynamic_copy(_tbl     text = 'tmp1')
RETURNS void AS
$func$
DECLARE
   _filename VARCHAR;
BEGIN
  _filename := '/tmp/' || random() || '.csv';
  EXECUTE format($$COPY (select id, 1, 1, 1 from my_first_table) TO %L$$, _filename);
  EXECUTE format($$COPY my_second_table FROM %L$$, _filename);
END
$func$  LANGUAGE plpgsql;

但我想删除在此过程中创建的临时文件。怎么做？

Answer 1

我找到了 PostgreSQL 9.3+ 的解决方案:

-- Delete temporary file
EXECUTE format(
  $$COPY (SELECT 1) TO PROGRAM 'rm %I'$$,
  _filename
);

所以，我的功能是

CREATE OR REPLACE FUNCTION f_dynamic_copy(_tbl     text = 'tmp1')
RETURNS void AS
$func$
DECLARE
   _filename VARCHAR;
BEGIN
  _filename := '/tmp/' || random() || '.csv';
  EXECUTE format($$COPY (select id, 1, 1, 1 from my_first_table) TO %L$$, _filename);
  EXECUTE format($$COPY my_second_table FROM %L$$, _filename);

  -- Delete temporary file
  EXECUTE format(
    $$COPY (SELECT 1) TO PROGRAM 'rm %I'$$,
    _filename
  );

END
$func$  LANGUAGE plpgsql;