Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- NOTICE: table "temp_table" does not exist, skipping
- ERROR: could not open file "/Users/linu/downloads/numb.csv" 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 temp_table from '/Users/linu/downloads/numb.csv' with delimiter ';' quote '"' csv "
- PL/pgSQL function staging.load_csv_file(text,text,integer) line 22 at EXECUTE
- SQL state: 58P01
- **Function call**
- select staging.load_csv_file('dhl','/Users/linu/downloads/numb.csv',73)
- **Function Body**
- create or replace function staging.load_csv_file
- (
- target_table text,
- csv_path text,
- col_count integer
- )
- returns void as $$
- declare
- iter integer; -- dummy integer to iterate columns with
- col text; -- variable to keep the column name at each iteration
- col_first text; -- first column name, e.g., top left corner on a csv file or spreadsheet
- begin
- set schema 'staging';
- DROP TABLE IF EXISTS temp_table;
- create table temp_table ();
- -- add just enough number of columns
- for iter in 1..col_count
- loop
- execute format('alter table temp_table add column col_%s text;', iter);
- end loop;
- -- copy the data from csv file
- execute format('copy temp_table from %L with delimiter '';'' quote ''"'' csv ', csv_path);
- iter := 1;
- col_first := (select col_1 from temp_table limit 1);
- -- raise notice 'path', csv_path;
- -- update the column names based on the first row which has the column names
- for col in execute format('select unnest(string_to_array(trim(temp_table::text, ''()''), '','')) from temp_table where col_1 = %L', col_first)
- loop
- execute format('alter table temp_table rename column col_%s to %s', iter, col);
- iter := iter + 1;
- end loop;
- -- delete the columns row
- execute format('delete from temp_table where %s = %L', col_first, col_first);
- -- change the temp table name to the name given as parameter, if not blank
- if length(target_table) > 0 then
- execute format('alter table temp_table rename to %I', target_table);
- end if;
- end;
- $$ language plpgsql;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement