问题描述
我知道您可以从文件中运行这样的复制命令:
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;"
我想从 ruby 变量中复制 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;"
推荐答案
这是不可能的 SQL COPY
命令.COPY
仅从 file 或 STDIN 复制.
That's not possible with the SQL COPY
command. COPY
only copies from a file or STDIN.
您可以将变量的内容写入文件或通过 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:
文件路径不能像其他数据项一样交换,并且您不能为此使用准备好的语句.在执行或使用服务器端函数使用动态 SQL 之前构建整个语句:
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;
调用:
SELECT f_cp('/var/lib/postgres/sync/myfile.csv')
format()
需要 Postgres 9.1 或更高版本.
format()
requires Postgres 9.1 or later.
这篇关于Postgres 从带有 CSV 数据的变量中复制的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!