Passing a path as parameter to function-CodePudding

I have a function in which I pass a path to a file as a parameter. Within the function, I want to COPY the data that is located at the path

CREATE OR REPLACE FUNCTION load(path varchar)
    RETURNS void
    LANGUAGE plpgsql
  AS
$$
BEGIN
    COPY foo FROM path
    WITH DELIMITER ';' 
    CSV HEADER;

    ...

end;
$$

This gives a Syntax Error, pointing to path. If I hardcode the path to C:\Users...., it works. Why is that?

CodePudding user response：

copy does not work with variables. Shape and execute dynamic SQL. Here is an illustration - your example modified. I am using dollar quoting for clarity.

CREATE OR REPLACE FUNCTION load(path text) RETURNS void LANGUAGE plpgsql AS
$$
begin
 execute replace(
 $dynsql$
    COPY foo FROM '__PATH__'
    WITH DELIMITER ';' 
    CSV HEADER;
 $dynsql$,
 '__PATH__', path);

    ...

end;
$$