Postgresql COPY command - how to delete temp file?

Question

I have procedure and it works well.

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;

But I want to delete temporary files created in this procedure. How to do it?

You can't access the filesystem via neither plpgsql nor SQL for anything other than COPY FROM and COPY TO. You can use a plperlu function, or cron jobs to clean up old files. — Ezequiel Tolnay
– Ezequiel Tolnay, Commented May 18, 2016 at 6:40

Nick · Accepted Answer · 2016-05-18 16:22:20Z

3

I found solution for PostgreSQL 9.3+:

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

So, my function is

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;

edited May 18, 2016 at 16:22

answered May 18, 2016 at 16:16

Nick

10.2k8 gold badges65 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nick Over a year ago

Yes, it is trick, hack. In my case filename has absolutely controlled structure, therefore Im sure this is secure solution. If attacker will override random() or format() function, I may have problem. But such fact - is very bad. And this should not happen.

e4c5 · Accepted Answer · 2016-05-18 04:44:57Z

1

plpgsql does not have a function to delete files. You would need to create the stored function in perl or python to be able to delete the temporary file. But do you really need a temporary file here wouldn't an INSERT SELECT do the job just as well with the need for a stored function?

INSERT INTO my_second_table SELECT id, 1, 1, 1 from my_first_table

edited May 18, 2016 at 4:44

answered May 18, 2016 at 4:23

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

3 Comments

e4c5 Over a year ago

Is there are significant difference here in performance? Well if COPY is t he only option, I guess it will have to be plpython or similar

Nick Over a year ago

postgresql.org/docs/current/static/populate.html "Note that loading a large number of rows using COPY is almost always faster than using INSERT, even if PREPARE is used and multiple insertions are batched into a single transaction". In my case COPY faster than INSERT in 5 times.

Fedor Over a year ago

also what if you need to copy data from other DB instance?

Collectives™ on Stack Overflow

Postgresql COPY command - how to delete temp file?

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related