Обсуждение: Add COPY statement inside sql function AND/OR call function within function
Hi, Im hoping someone could help me with this. I am new to any kind of sql coding so bare with me.
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
Re: Add COPY statement inside sql function AND/OR call function within function
От
Adrian Klaver
Дата:
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@gmail.com
Re: Add COPY statement inside sql function AND/OR call function within function
От
David Johnston
Дата:
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_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 What PostgreSQL version? SQL functions only recently could refer to input parameters by name. Before you had to use $1, $2, etc... To reference them. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Add-COPY-statement-inside-sql-function-AND-OR-call-function-within-function-tp5778666p5778683.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.