postgresql - Save output from sql function to csv file (COPY) with dynamic filename

Question

I am running Postgres 9.3 on MacOSX. I am trying to add a COPY statement inside a function for an automatized save-to-file process.

I am new to any kind of sql coding, so this is what I have so far;

CREATE FUNCTION retrieve_info(input_method TEXT, input_species TEXT) RETURNS SETOF   
retrieve_info_tbl AS $$
   SELECT tblA.id, tblA.method, tblA.species, tblA.location
   FROM tblA
   WHERE method=input_method AND species=input_species
   GROUP BY id, method, species
   ORDER BY location
   COPY (SELECT * FROM retrieve_info_tbl) TO 'myfilepath/filename.csv' WITH CSV;
$$ LANGUAGE 'sql';

What the function does and which works is querying both a method and a species from a larger table in this example tblA with multiple species, methods and retrieving this together with its location data. What doesn't work is the COPY statement. So, what I would like to add is a statement which saves the output to a .csv file when executing the function. Also, is it possible to add a dynamic filename.csv depending on e.g. the input_method and input_species?

DUMMY DATA

tblA (filled)

create table tblA (id varchar(5) PRIMARY KEY, method text, species varchar(10), location    
text);
insert into tblA values ('1a', 'mtd1', 'sp1', 'locA'),('1b', 'mtd1', 'sp2', 'locC'),('1c',  
'mtd2', 'sp3', 'locB'),('1d', 'mtd1', 'sp1', 'locB'),('1e', 'mtd2', 'sp5', 'locA');

retrieve_info_tbl (empty)

create table retrieve_info_tbl (id varchar(5) PRIMARY KEY, method text, ind varchar(10),  
location text);

OUTPUT (when the COPY statement is not added to the function)

retrieve_info(mtd1, sp3)

id | method | ind | location
----------------------------
1a | mtd1   | sp3 | locA
1d | mtd1   | sp3 | locB

...and would like to save this to '/myfilepath/mtd1_sp3.csv (dynamic filename)

Many thanks,

UPDATE: I would be happy with just a save-to statement within the sql function

Answer 1

A quickly hacked together example using PLPGSQL instead of SQL.

Caveat: must be created as a superuser.

Replace the query etc in the function to whatever you need, and you can add in more input parameters to the function to create your query or output file(s) differently depending on what those input parameters are.

CREATE OR REPLACE FUNCTION copy_out_example ( p_path TEXT, p_filename_prefix TEXT, OUT file_and_path TEXT )
RETURNS TEXT AS
$func$
DECLARE
    qry TEXT;
BEGIN
    file_and_path := RTRIM(p_path,'/') || '/' || p_filename_prefix || '_' || ceil(random() * 1000000)::TEXT || '.csv';

    qry := FORMAT('COPY (select * from pg_catalog.pg_class) TO %L CSV HEADER',file_and_path);
    EXECUTE qry;
END;
$func$ LANGUAGE plpgsql STRICT SECURITY DEFINER;

SELECT copy_out_example('/path/to/the/file','some_test_file');

Results in a file like '/path/to/the/file/some_test_file_994216.csv'