CODEWITHSUNDEEP
postgresql
Kunal Vyas
Kunal Vyas

Reputation: 1579

How to insert delimiter in a path name? [postgresql]

I need to upload a *.tsv file of the form File_[currentDate]_[currentMonth]_name.tsv. I've tried many ways, sadly in vain, I have to ask for the delimiter, or else the way to do this right:

SELECT CURRENT_DATE AS today;
SELECT EXTRACT(MONTH FROM TIMESTAMP 'today') AS MONTH;
SELECT EXTRACT(DAY FROM TIMESTAMP 'today') AS DAY;

COPY table_name FROM 'D:\File_**<DAY>**_**<MONTH>**_name.tsv' DELIMITER '   ' CSV header;

I'm expecting the Input file name to be File_19_04_name.tsv (today). What am I doing wrong? Any alternatives to do it right?

Upvotes: 1

Views: 203

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

Build and execute the COPY command dynamically inside a plpgsql function:

create or replace function cp()
returns text language plpgsql as $function$
begin
    execute $$
        COPY t
        FROM '$1'
        DELIMITER '   '
        CSV header
    $$ using 'D:\File_' || to_char(current_date, 'DD_MM') || '_name.tsv';
end;
$function$;

$1 will be substituted by the parameter passed with using

Upvotes: 1

Related Questions