Сохранить вывод из функции sql в файл csv (COPY) с динамическим именем файла

Я использую Postgres 9.3 на MacOSX. Я пытаюсь добавить инструкцию COPY внутри функции для автоматизированного процесса сохранения в файл.

Я новичок в любом кодировании sql, так что это то, что у меня есть до сих пор;

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

Что делает функция и что работает, так это запрашивает как метод, так и вид из более крупной таблицы in this example tblA с несколькими видами, методами и извлекает их вместе с данными о местоположении. Что не работает, так это оператор COPY. Итак, что я хотел бы добавить, так это оператор, который сохраняет вывод в файл .csv при выполнении функции. Кроме того, возможно ли добавить динамическое имя файла .csv в зависимости, например, от input_method и input_species?

ПРОЕКТНЫЕ ДАННЫЕ

таблица (заполнена)

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 (пусто)

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

ВЫВОД (если оператор COPY не добавлен в функцию)

retrieve_info(mtd1, sp3)

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

... и хотел бы сохранить это в '/myfilepath/mtd1_sp3.csv (динамическое имя файла)

Большое спасибо,

ОБНОВЛЕНИЕ: я был бы доволен просто оператором сохранения в функции sql


postgresql sql export-to-csv sql-function
person jO.    schedule 11.11.2013    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Быстро собранный пример с использованием PLPGSQL вместо SQL.

Предостережение: должен быть создан как суперпользователь.

Замените запрос и т. д. в функции на то, что вам нужно, и вы можете добавить дополнительные входные параметры в функцию, чтобы создать свой запрос или выходной файл (ы) по-разному, в зависимости от этих входных параметров.

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'

person bma    schedule 11.11.2013
comment
Гораздо удобнее использовать format(...) со спецификатором формата идентификатора %I, а не quote_literal IMO. В противном случае да, EXECUTE это способ сделать это. - person Craig Ringer; 11.11.2013
comment
Спасибо за ваш ответ. Так вы бы не рекомендовали делать это в sql? Я нашел синтаксис более понятным. - person jO.; 11.11.2013
comment
Я опубликовал исправленную версию с помощью функции FORMAT (postgresql.org/docs/current/static/). @CraigRinger, небольшое исправление в спецификаторе формата идентификатора, я считаю, что это должно быть %L, поскольку путь к файлу в операторе COPY представляет собой строку, а не идентификатор SQL. В любом случае, спасибо, что побудили меня улучшить его. - person bma; 11.11.2013
comment
@Джо. Дело не в том, что я бы не рекомендовал это в SQL, а в том, что вы не можете сделать это в простом SQL из-за вашего требования к динамическому имени файла. - person bma; 11.11.2013
comment
@bma: Спасибо. Итак, просто для уточнения. Это не замена моей функции sql, а отдельная функция «копирования»? В этом случае мне все равно не нужно было бы добавлять оператор «копирования» в функцию sql, поскольку вход для вашей функции — это путь к файлу..? - person jO.; 11.11.2013
comment
Нет, это plpgsql заменяет вашу функцию SQL. Измените его, чтобы выполнить любой запрос, который вам нравится, SQL в моем примере просто показывает, как он работает. - person bma; 12.11.2013
comment
@bma Да, вы правы, литерал, а не идентификатор. Упс и спасибо. - person Craig Ringer; 12.11.2013