postgresql - 将参数传递给 Postgres 中的存储函数

Question

此 PostgreSQL COPY 命令有效:

copy a from '/tmp/main.csv' WITH (FORMAT csv)

但我希望动态生成表名和文件路径。我该怎么做？

我确实尝试通过调用 select import_csv('/tmp/main.csv','a');

CREATE OR REPLACE FUNCTION import_csv(
    csv_path text,
    target_table text)
  RETURNS void AS
$BODY$
begin
    set schema 'public';
    raise notice 'CSV PATH: %,TABLE NAME: %',csv_path,target_table;
    execute format('copy %I from %I WITH (FORMAT csv)',target_table, csv_path);
    return;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION import_csv(text, text)
  OWNER TO postgres;

我得到了错误:

NOTICE: CSV PATH: /tmp/main.csv,TABLE NAME: a

ERROR: syntax error at or near ""/tmp/main.csv""

LINE 1: copy a from "/tmp/main.csv" WITH (FORMAT csv)

Answer 1

更改为:

execute format('copy %I from %L WITH (FORMAT csv)',target_table, csv_path);

%I 引用 db 对象，而 path 只是一个字符串

https://www.postgresql.org/docs/current/static/functions-string.html :

The type of format conversion to use to produce the format specifier's output. The following types are supported:

s formats the argument value as a simple string. A null value is treated as an empty string.

I treats the argument value as an SQL identifier, double-quoting it if necessary. It is an error for the value to be null (equivalent to quote_ident).

L quotes the argument value as an SQL literal. A null value is displayed as the string NULL, without quotes (equivalent to quote_nullable).

In addition to the format specifiers described above, the special sequence %% may be used to output a literal % character.

强调我的