How to pass list of arguments in CSV to a Postgres function

Question

I've written a Postgres function which involves inserting records in tables. It takes a few arguments as input.

CREATE OR REPLACE FUNCTION public.add_person(
    name character varying,
    email character varying,
    level integer,
    company_id integer,
    comp_list integer[])

I have a CSV which has columns that match the input arguments of the function. How do I feed the CSV as list of input and call the function?

EDIT The database lives in RDS instance of AWS. So I don't have superuser permission to copy the file to a temp table. file_fdw is also not an option since this extension is not supported by Amazon RDS.

Answer 1

According to COPY syntax you need a table to import data from a CSV file. Use a temporary table to buffer the data from a file, e.g.:

do $$
begin
    create temp table temp (
        name character varying, 
        email character varying, 
        level integer, 
        company_id integer, 
        comp_list integer[]);
    copy temp from 'c:\data\test.csv' (format csv, header);
    perform add_person(name, email, level, company_id, comp_list)
    from temp;
    drop table temp;
end
$$;

Alternatively, you can use the extension file_fdw, example:

create extension if not exists file_fdw;

create server csv_server foreign data wrapper file_fdw;

create foreign table csv_file (
    name character varying, 
    email character varying, 
    level integer, 
    company_id integer, 
    comp_list integer[]
) server csv_server
options (filename 'c:\data\test.csv', format 'csv', header 'on');

select add_person(name, email, level, company_id, comp_list)
from csv_file;