Обсуждение: Add COPY statement inside sql function AND/OR call function within function

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

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.