使用动态文件名将sql函数的输出保存到csv文件（COPY） [英] Save output from sql function to csv file (COPY) with dynamic filename

问题描述

我在MacOSX上运行Postgres 9.3。我正在尝试在函数中添加COPY语句以实现自动保存到文件的过程。

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.

对于任何一种sql编码我都是陌生的，所以这就是我到目前为止；

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

函数的作用和工作原理是从较大的表中查询方法和种类在此示例中为tblA ，具有多个种类，方法，并将其连同其位置数据一起检索。 COPY 语句不起作用。因此，我想添加的一条语句是在执行函数时将输出保存到.csv文件中。另外，是否可以根据例如，添加动态filename.csv input_method和input_species？

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?

虚拟数据

tblA（已填充）

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（空）

retrieve_info_tbl (empty)

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

输出
（当未将COPY语句添加到函数）

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

...并将其保存到'/myfilepath/mtd1_sp3.csv（动态文件名）

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

非常感谢，

更新：我很乐意sql函数中的save-to语句

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

推荐答案

使用 PLPGSQL 而不是 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');

结果类似于'/ path / to / the / file / some_test_file_994216.csv'

这篇关于使用动态文件名将sql函数的输出保存到csv文件（COPY）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！