Add COPY statement inside sql function AND/OR call function within function

Поиск
Список
Период
Сортировка
От Johannes Björk
Тема Add COPY statement inside sql function AND/OR call function within function
Дата
Msg-id 432319CB-8330-4B63-89E4-D5867A1402FE@gmail.com
обсуждение исходный текст
Ответы Re: Add COPY statement inside sql function AND/OR call function within function  (Adrian Klaver <adrian.klaver@gmail.com>)
Re: Add COPY statement inside sql function AND/OR call function within function  (David Johnston <polobo@yahoo.com>)
Список pgsql-general
Hi, Im hoping someone could help me with this. I am new to any kind of sql coding so bare with me.

I have written the below working function which I would like to print to .csv file(s)

CREATE FUNCTION retrieve_info(input_method TEXT, input_species TEXT) RETURNS SETOF   
retrieve_info_tbl AS $$SELECT tblA.id, tblA.method, tblA.species, tblA.locationFROM tblAWHERE method=input_method AND species=input_speciesGROUP BY id, method, speciesORDER BY location
$$ LANGUAGE 'sql';

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

Calling function

SELECT * FROM retrieve_info('mtd1','sp1');

OUTPUT

retrieve_info(mtd1, sp3)

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

Since I have not succeeded in this, I tried to work around it creating a function which called this function and printed the result to a .csv file.

CREATE FUNCTION print_out(x TEXT, y TEXT) RETURNS void AS $$COPY (SELECT * FROM retrieve_info(x,y)) TO 'myfilepath/test.csv'    WITH CSV HEADER;
$$ LANGUAGE 'sql';

Calling nested function.

SELECT * FROM print_out('mtd1','sp1');

OUTPUT

The above gives this ERROR: column "x" does not exist SQL state: 42703 Context: SQL function "print_out" statement 1. However, when substituting x,y in print_out() with 'mtd1','sp1' the correct output is printed to test.csv

I would really appreciate any pointers on either one of the above problems. 

Many thanks,

Johannes

В списке pgsql-general по дате отправления:

Предыдущее
От: Gabriel Sánchez-Martínez
Дата:
Сообщение: Re: Partitioned table question
Следующее
От: whiplash
Дата:
Сообщение: Save many data chunks to file