Using Copy feature from postgresql with ftp server

Question

I need to use copy query from postgresql, my copy query is below (i run it with php)

create temp table tmp_food (like food); -- create temporary table like food

copy tmp_food from 'd:\testingfood.csv' delimiter '|' csv;  -- copy to temp table

insert into food (food_id, food_name)   -- insert into food from temp table
select food_id, food_name
from tmp_food
on conflict (food_id) do                -- update instead of error
update set food_name = excluded.food_name;

drop table tmp_food;                    -- drop temp table

But the thing is, i need to copy it from ftp server so i need to change this line

copy tmp_food from 'ftp\somefolder' delimiter '|' csv; -- copy to temp table

btw this is my code to read csv, but the thing i need is get the path and combine it to my php

$ftp_server = "127.0.0.1";
$ftp_username = "alex";
$ftp_userpass = "alex";
$ftp_conn = ftp_connect($ftp_server) or die("Could not connect to $ftp_server");
$login = ftp_login($ftp_conn, $ftp_username, $ftp_userpass);
echo "success connect to FTP";
$contents = fread($handle, filesize($filename));
echo $contents;

// close connection
ftp_close($ftp_conn); 

Answer 1

I can see the following options:

• Read the file into a PHP array and use pg_copy_from to load it to the temporary table using PostgreSQL COPY FROM STDIN.

• Write a function in a powerful language like PL/PerlU that performs the file transfer for you prior to executing COPY.