From: | Johannes Björk <bjork(dot)johannes(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Add COPY statement inside sql function AND/OR call function within function |
Date: | 2013-11-12 20:56:03 |
Message-ID: | 432319CB-8330-4B63-89E4-D5867A1402FE@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.location
FROM tblA
WHERE method=input_method AND species=input_species
GROUP BY id, method, species
ORDER 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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2013-11-12 22:05:30 | Re: Clang 3.3 Analyzer Results |
Previous Message | Tom Lane | 2013-11-12 20:50:55 | Re: simple query with radically different plan after 9.0 -> 9.2 upgrade |