Create the function cr_copy_from_scripts()

This procedure makes use of the row_number() Window function to enable a single invocation of the function to choose the first, the second, or the third .csv file with respect to the alphabetical order of their filenames.

Save this script as "cr-cr-copy-from-csv-scripts.sql"


drop function if exists cr_copy_from_scripts(int) cascade;

create function cr_copy_from_scripts(which in int)
  returns text
  language plpgsql
as $body$
<<b>>declare
  copy_from_csv constant text :=
    $$\copy ?1 from '?2' with (format 'csv', header true);$$;

  csv_file       text not null := '';
  staging_table  text not null := '';
begin
  with a as (
    select
      row_number() over (order by s.csv_file) as r,
      s.csv_file,
      s.staging_table
    from covidcast_names as s)
  select a.csv_file, a.staging_table
  into b.csv_file, b.staging_table
  from a where a.r = which;

  return replace(replace(
    copy_from_csv,
    '?1',
    staging_table),
    '?2',
    csv_file);
end b;
$body$;