There are similar questions but they didn't address my problem: I have a stored procedure defined in my remote database. As part of it operation, the SP has to bulk insert from a local file (through my application running on my local machine): the procedure:
CREATE OR REPLACE FUNCTION upsert_realtime(path text, traveltime_table regclass)
RETURNS void AS
$BODY$
BEGIN
EXECUTE format('COPY temp_table(link_id,start_time,end_time,travel_time,interval_time, travel_mode) FROM %L DELIMITER '';'' CSV', path);
--...
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION upsert_realtime(text, regclass)
OWNER TO postgres;
the path supplied to the SP is on local machine:
select * from upsert_realtime('/home/.../travel_time.txt','realtime_travel_time');
Therefore I get this error:
terminate called after throwing an instance of 'soci::postgresql_soci_error'
what(): Cannot execute query. ERROR: could not open file "/home/.../travel_time.txt" for reading: No such file or directory
The only thing that comes into my mind is to somehow automatically scp the file to the postgresql server and then run the SP, which is not the best idea.
can you help me find a solution? thanks
thanks
You're using the wrong path. This is a common source of errors. It's all about "from the viewpoint of the server."
COPY with a file name instructs the PostgreSQL server to directly read from or write to a file. The file must be accessible by the PostgreSQL user (the user ID the server runs as) and the name must be specified from the viewpoint of the server. When PROGRAM is specified, the server executes the given command and reads from the standard output of the program, or writes to the standard input of the program. The command must be specified from the viewpoint of the server, and be executable by the PostgreSQL user. When STDIN or STDOUT is specified, data is transmitted via the connection between the client and the server.
If you don't have a usable path, you need to
psql to connect to the remote server and read the local file.