Insert COPY command's file result into a table

Question

I have a postgresql function that creates an csv file like below.

CREATE FUNCTION public.exportsnapshot()
    RETURNS void AS $$
    LANGUAGE 'plpgsql'
BEGIN
    COPY (my_query) TO 'tmp/test.csv' With DELIMITER ';' CSV HEADER;
END; $BODY$;

Is there a way I can insert the test.csv file into a bytea column directly from COPY command? Also, is there a way to compress it before saving it to the bd?

I was hoping i can do this without a intermediary file. i was trying something like ```COPY fileTable FROM (COPY (qurey) TO STDOUT))``` but i cannot figure it out — Lala, Apr 16 '19 at 13:06
So you want to copy it to a file AND put it in a table, in a single command? — 404, Apr 16 '19 at 13:12
yep, i'd like to avoid having to delete the file after it's in the db — Lala, Apr 16 '19 at 13:16
What about `insert into filetable select t::text from ( ... your query ..) t`? - that would create one row with comma separated values in `filetable` for each row returned by `your query` — , Apr 16 '19 at 14:14

Ancoron · Accepted Answer · 2019-04-19T18:37:31.963

As it is not possible at the moment using COPY only, you have to resort to external file handling.

Here's a version that uses the large object functions to get the file data into a single table entry:

CREATE TABLE snapshots (
    id serial primary key,
    data bytea
);

CREATE FUNCTION exportsnapshot()
    RETURNS integer
    LANGUAGE plpgsql
AS $$
DECLARE
    data_file_id oid;
    snapshot_id integer;
BEGIN
    COPY (my_query) TO '/tmp/test.csv' With DELIMITER ';' CSV HEADER;

    -- assign an OID to the external file...
    SELECT lo_import('/tmp/test.csv') INTO STRICT data_file_id;

    -- create a new snapshots entry by reading the complete file contents...
    INSERT INTO snapshots (data)
        SELECT lo_get(data_file_id)
        RETURNING id
        INTO snapshot_id;

    -- forget about the external file...
    PERFORM lo_unlink(data_file_id);

    -- delete the file...
    COPY (SELECT 1) TO PROGRAM 'rm /tmp/test.csv';

    -- return the new snapshot ID...
    RETURN snapshot_id;
END;
$$;

For compression you should really consider text rather than bytea.

Insert COPY command's file result into a table

1 Answers1