Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

sql - Postgres Copy from Variable with CSV data

I know you can run a copy command like this from a file:

"COPY zip_codes FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV;"

I'd like to copy csv data from a ruby variable so I can do like so

"COPY zip_codes FROM '#{csv_data}' DELIMITER ',' CSV;"
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

That's not possible with the SQL COPY command. COPY only copies from a file or STDIN.

You can either write the content of the variable to a file or pipe it via STDIN. Only makes sense for more than a couple of rows.


I think I misunderstood your question before the update, you probably don't need this:

The file path can not be exchanged like other data items, and you can't use a prepared statement for that. Build the whole statement before executing or resort to dynamic SQL with a server-side function like:

CREATE OR REPLACE FUNCTION f_cp(_file text)
  RETURNS void AS
$BODY$
BEGIN
EXECUTE format($$COPY zip_codes FROM %L DELIMITER ',' CSV$$, $1);
END
$BODY$
  LANGUAGE plpgsql;

Call:

SELECT f_cp('/var/lib/postgres/sync/myfile.csv')

format() requires Postgres 9.1 or later.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...