Create the function cr_copy_from_scripts()

This page documents the preview version (v2.21). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

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$;