Postgres Function - shows a copy command error

Question

My function definition is this way, and it's throwing an error

CREATE OR REPLACE FUNCTION traffic.addcsv (
      filename text
    )
    RETURNS void AS
    $body$
    BEGIN
    DROP TABLE IF EXISTS traffic.tsdata;

    create table if not EXISTS traffic.tsdata
    (
        ldir text,
        dtime timestamp,
        epoch integer,
        ln_m integer,
        freeflow real,
        spdlimit integer,
        cnt integer,
        mean_spd real,
        stddev real,
        mn integer,
        mx integer,
        conf integer
    );

    COPY traffic.tsdata FROM '$1' DELIMITER ',' CSV HEADER;

    CREATE TABLE IF NOT EXISTS traffic.psdata
    (
         l_id BIGINT,
         direction char(1),
         dtime timestamp,
         day_number SMALLINT,
         epoch smallint,
         length_m integer,
         freeflow smallint,
         spdlimit smallint,
         cnt integer,
         mean_speed smallint,
         stddev double precision ,
         mn smallint,
         mx SMALLINT,
         confidence smallint
    ); 

    INSERT INTO traffic.psdata (
    l_id,
    direction,
    dtime,
    day_number,
    epoch,
    length_m,
    freeflow,
    spdlimit,
    cnt,
    mean_speed,
    stddev,
    mn,
    mx,
    confidence
    )
    SELECT
            (replace ( (replace(ldir,'F','')) ,'T',''  )::integer,
        RIGHT(tsdata.ldir,1)::char(1) ,
            dtime,
        EXTRACT(ISODOW FROM traffic.tsdata.dtime)::smallint ,
            epoch,
            ln_m,
            freeflow,
        spdlimit,
            cnt,
            mean_spd,
            stddev,
            mn,
            mx,
            conf)
    FROM traffic.tsdata;

    END;
    $body$
    LANGUAGE 'plpgsql'
    VOLATILE
    CALLED ON NULL INPUT
    SECURITY INVOKER
    PARALLEL UNSAFE
    COST 100;

ERROR: could not open file "$1" for reading: No such file or directory HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy. CONTEXT: SQL statement "COPY traffic.tsdata FROM '$1' DELIMITER ',' CSV HEADER" PL/pgSQL function traffic.addcsv(text) line 21 at SQL statement

Nick Barnes · Accepted Answer · 2019-04-30 16:53:03Z

1

I don't think PL/pgSQL allows variables in a COPY statement.

You can do it with dynamic SQL:

EXECUTE format('COPY traffic.tsdata FROM %L DELIMITER '','' CSV HEADER', $1);

answered Apr 30, 2019 at 16:53

Nick Barnes

21.8k5 gold badges64 silver badges70 bronze badges

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

Collectives™ on Stack Overflow

Postgres Function - shows a copy command error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related