COPY with dynamic file name

by

You need dynamic SQL:

CREATE OR REPLACE FUNCTION loaddata(filepathname text)
  RETURNS void AS
$func$
BEGIN
   EXECUTE format ('
   COPY climatedata(
         climatestationid
       , date
         ... -- more columns 
       , tminsflag)
   FROM %L (FORMAT CSV, HEADER)'  -- current syntax
           -- WITH CSV HEADER'    -- tolerated legacy syntax
   , $1);  -- pass function parameter filepathname to format() 
END
$func$ LANGUAGE plpgsql;

format() requires PostgreSQL 9.1+.
Pass the file name without extra set of (escaped) single quotes:

SELECT loaddata('/absolute/path/to/my/file.csv')

format() with %L escapes the file name safely. Would be susceptible to SQL injection without it.

Aside, you have a function name mismatch:

CREATE OR REPLACE FUNCTION public.loaddata(filepathname varchar)
...
ALTER FUNCTION public.filltmaxa(character varying)

More Related Contents:

  1. Refactor a PL/pgSQL function to return the output of various SELECT queries
  2. PostgreSQL parameterized Order By / Limit in table function
  3. DROP FUNCTION without knowing the number/type of parameters?
  4. Is SELECT or INSERT in a function prone to race conditions?
  5. Save PL/pgSQL output from PostgreSQL to a CSV file
  6. Difference between language sql and language plpgsql in PostgreSQL functions
  7. How to return result of a SELECT inside a function in PostgreSQL?
  8. Pass multiple values in single parameter
  9. GROUP BY and aggregate sequential numeric values
  10. PL/pgSQL checking if a row exists
  11. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  12. PostgreSQL IF statement
  13. Call a set-returning function with an array argument multiple times
  14. Record returned from function has columns concatenated
  15. Postgres Dynamic Query Function
  16. Return setof record (virtual table) from function
  17. Window Functions or Common Table Expressions: count previous rows within range
  18. Export specific rows from a PostgreSQL table as INSERT SQL script
  19. SQL update fields of one table from fields of another one
  20. Postgres function returning table not returning data in columns
  21. PostgreSQL convert columns to rows? Transpose?
  22. Return multiple columns and rows from a function PostgreSQL instead of record
  23. Create PostgreSQL ROLE (user) if it doesn’t exist
  24. pseudo_encrypt() function in plpgsql that takes bigint
  25. How to perform a select query in a DO block?
  26. Function with SQL query has no destination for result data
  27. Postgres trigger after insert accessing NEW
  28. PostgreSQL – dynamic value as table name [duplicate]
  29. Is there a shortcut for SELECT * FROM?
  30. Why postgres returns unordered data in select query, after updation of row?

Leave a Comment