Pass Parameters to Stored Function in Postgres

Question

This PostgreSQL COPY command works:

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

but I want the tablename and filepath to be dynamically generated. How can I do this?

I did try with following by calling the 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;

I got Error:

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)

Vao Tsun · Accepted Answer · 2017-05-16 11:05:55Z

3

change to:

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

%I quotes db object, while path is just a string

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.

emphasis mine

edited May 16, 2017 at 11:05

answered May 16, 2017 at 11:03

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ajay Takur Over a year ago

Got thanks.how about for truncate the table just before the line execute format

Vao Tsun Over a year ago

@AjayTakur If I answered your question, could you please accept it?

Collectives™ on Stack Overflow

Pass Parameters to Stored Function in Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related