On 11/12/2013 12:56 PM, Johannes Björk wrote:
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_tblAS $$
SELECT tblA.id, tblA.method, tblA.species, tblA.location
FROM tblA
WHERE method=input_methodAND 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 tblAvalues ('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.
It looks like it is succeeding, it returns a setof. What are you looking
to do?
|CREATE FUNCTION print_out(x TEXT, y TEXT) RETURNS voidAS $$
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
|
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
