1

I am migrating some client side stuff into the server, and want to put it into a function.

I need to get the results of a query into a CSV file. But, I'd like to pass the file name/location of the resulting file as an argument of the function.

So, this is a simple example of what I want to do:

CREATE FUNCTION send_email_results(filename1 varchar) RETURNS void AS $$
DECLARE
BEGIN
    COPY(SELECT * FROM mytable) TO filename1 WITH CSV;
END;
$$ LANGUAGE plpgsql;

Postgres is complaining about this though, as it is translating the filename1 argument to '$1', and it doesn't know what to do.

I can hardcode the path if need be, but being able to pass it as a parameter sure would be handy.

Anyone have any clues?

user272735
  • 10,473
  • 9
  • 65
  • 96
jasonmclose
  • 1,667
  • 4
  • 22
  • 38
  • To be fair, the reason I want to do this within a function is that I need to export to multiple files within the function. So imagine needing to pass 2 or 3 different filenames as arguments, and exporting a few different queries within that function. If I didn't need to export more than one query, then I could simply pipe out the output. – jasonmclose May 07 '14 at 16:07
  • Use EXECUTE 'your query' inside your function. – Frank Heikens May 07 '14 at 16:30
  • possible duplicate of [COPY with dynamic file name](http://stackoverflow.com/questions/16019508/copy-with-dynamic-file-name) – Erwin Brandstetter May 10 '14 at 12:42

1 Answers1

5

I just ran in to this. It turns out that you can't use parameterized arguments when the copy command is used (at least that's the case with python as the stored proc language). So, you have to build the command without arguments, like:

CREATE FUNCTION send_email_results(filename1 varchar) RETURNS void AS $$
DECLARE
BEGIN
    execute 'copy (select * frommytable) to ' || filename1 || ' with csv;';
END;
$$ LANGUAGE plpgsql;

You might have to use the quoting feature to make it a little more readable. I don't know, I don't use plpgsql as a postgres function language, so the syntax might be wrong.

execute 'copy (select * frommytable) to ' || quote_literal(filename1) || ' with csv;'
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Greg
  • 6,571
  • 2
  • 27
  • 39
  • This was it. Works great. One final note is that I still had to add escaped single quotation marks around the filename, so it ended up looking like `TO ''' || filename1 || ''' WITH CSV;';`. – jasonmclose May 07 '14 at 20:39
  • @jasonmclose: This is highly susceptible to SQL injection if you work with simple string concatenation. Rather use the second form with `quote_literal()` or [`format()` with `%L`](http://stackoverflow.com/questions/16019508/copy-with-dynamic-file-name/16021835#16021835). – Erwin Brandstetter May 10 '14 at 12:50
  • Now in postgres v.10 and this is still the case. Thank you for this. – petre May 08 '19 at 14:05