XrAQVVpY

1NOTICE:  table "temp_table" does not exist, skipping
2
3ERROR:  could not open file "/Users/linu/downloads/numb.csv" for reading: No such file or directory
4HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's copy.
5CONTEXT:  SQL statement "copy temp_table from '/Users/linu/downloads/numb.csv' with delimiter ';' quote '"' csv "
6PL/pgSQL function staging.load_csv_file(text,text,integer) line 22 at EXECUTE
7SQL state: 58P01 
8     
9**Function call**
10select staging.load_csv_file('dhl','/Users/linu/downloads/numb.csv',73)
11
12**Function Body**
13create or replace function staging.load_csv_file
14(
15    target_table text,
16    csv_path text,
17    col_count integer
18)
19
20returns void as $$
21
22declare
23
24iter integer; -- dummy integer to iterate columns with
25col text; -- variable to keep the column name at each iteration
26col_first text; -- first column name, e.g., top left corner on a csv file or spreadsheet
27
28begin
29    set schema 'staging';
30
31    DROP TABLE IF EXISTS temp_table;
32    create  table temp_table ();
33
34    -- add just enough number of columns
35    for iter in 1..col_count
36    loop
37        execute format('alter table temp_table add column col_%s text;', iter);
38    end loop;
39
40    -- copy the data from csv file
41    execute format('copy temp_table from %L with delimiter '';'' quote ''"'' csv ', csv_path);
42
43    iter := 1;
44    col_first := (select col_1 from temp_table limit 1);
45  -- raise notice 'path', csv_path;
46    -- update the column names based on the first row which has the column names
47    for col in execute format('select unnest(string_to_array(trim(temp_table::text, ''()''), '','')) from temp_table where col_1 = %L', col_first)
48    loop
49        execute format('alter table temp_table rename column col_%s to %s', iter, col);
50        iter := iter + 1;
51    end loop;
52
53    -- delete the columns row
54    execute format('delete from temp_table where %s = %L', col_first, col_first);
55
56    -- change the temp table name to the name given as parameter, if not blank
57    if length(target_table) > 0 then
58        execute format('alter table temp_table rename to %I', target_table);
59    end if;
60
61end;
62
63$$ language plpgsql;