Home>

postgresql - i want to include today's date when specifying the copy path

Stored.
I want to read a text file and add it to the table.
nowdate contains the date of the day.
The date is entered in the text file. To specify the file, get it with nowdate and connect it.

I think the date is probably a problem, but it's what I expect to output.
The content of the text file is aaa date.log.

Error message 
Cannot combine
Applicable source code 
COPY table name FROM 'path where the file is' || nowdate || '.log' DELIMITER ',';

The contents of the text file below
aaa, admin01,111, login, 2019-11-01 13:32:37
bbb, admin01,222, logout, 2019-11-01 13:46:30
ccc, 0611001,333, login, 2019-10-01 13:47:32

If i try the above, || returns a syntax error.

COPY table (aaa, bbb, ccc, ddd, eee) FROM 'path string 2019-11-30' (DELIMITER (','));
Then, it is registered normally.

Supplemental information (FW/tool version etc.)

postgres11.5.
Please include more detailed information here.

postgresql pl/pgsql
  • Answer # 1

    I'm not sure why the text file can't be read, but I'd like to give you a sample of a function that loads a csv file with a date in the file name below.

    CREATE OR REPLACE FUNCTION load_csv (var_nowdate VARCHAR)
  RETURNS boolean AS
$BODY $
DECLARE
  var_sql VARCHAR;
  var_csvfile VARCHAR;
BEGIN
  var_csvfile: = 'C: \\ temp \\ demo-' || var_nowdate || '.csv';
  RAISE NOTICE 'LOAD [%] file', var_csvfile;
  var_sql: = 'COPY demo FROM' '' || var_csvfile || '' '' || 'CSV DELIMITER' ',' '';
  RAISE NOTICE '%', var_sql;
  EXECUTE var_sql;
  RAISE NOTICE 'LOADED [%] file', var_csvfile;
  RETURN true;
END;
$BODY $
LANGUAGE plpgsql;

    The CSV file is assumed to be stored in the following image underC: \\ temp.

    demo-2019-12-03.csv
demo-2019-12-04.csv
demo-2019-12-05.csv

    Function calls are

    select load_csv ('2019-12-05');

    or

    select load_csv (TO_CHAR (NOW (), 'YYYY-MM-DD'));

    .

    It has been confirmed to work with PostgreSQL 11.0.1.

Related articles