Обсуждение: can a function have a setof (returned from another function) as input
I'm trying to create a function that will take setof results from various
other functions (they all produce the same output format). Is this possible?
if so how do call it.
ex.
CREATE TYPE emp_t AS (
ID int,
name varchar(10),
age int,
salary real,
start_date date,
city varchar(10),
region char(1)
);
CREATE OR REPLACE FUNCTION func1() RETURNS SETOF emp_t AS $$
DECLARE
v_row emp_t;
BEGIN
FOR v_row in SELECT * from employee
LOOP
RETURN NEXT v_row;
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION func2() RETURNS SETOF emp_t AS $$
DECLARE
v_row emp_t;
BEGIN
FOR v_row in SELECT * from diff_table_or constraints
LOOP
RETURN NEXT v_row;
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION myanalyze(in_t emp_t) RETURNS SETOF <something> AS $$
DECLARE
v_row emp_t;
BEGIN
FOR v_row in EXECUTE in_t
LOOP
-- do something
RETURN NEXT v_row;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- so I would like the call to be something like
select * from myanalyze(select * funct1());
or
select * from myanalyze(select * funct2());
other functions (they all produce the same output format). Is this possible?
if so how do call it.
ex.
CREATE TYPE emp_t AS (
ID int,
name varchar(10),
age int,
salary real,
start_date date,
city varchar(10),
region char(1)
);
CREATE OR REPLACE FUNCTION func1() RETURNS SETOF emp_t AS $$
DECLARE
v_row emp_t;
BEGIN
FOR v_row in SELECT * from employee
LOOP
RETURN NEXT v_row;
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION func2() RETURNS SETOF emp_t AS $$
DECLARE
v_row emp_t;
BEGIN
FOR v_row in SELECT * from diff_table_or constraints
LOOP
RETURN NEXT v_row;
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION myanalyze(in_t emp_t) RETURNS SETOF <something> AS $$
DECLARE
v_row emp_t;
BEGIN
FOR v_row in EXECUTE in_t
LOOP
-- do something
RETURN NEXT v_row;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- so I would like the call to be something like
select * from myanalyze(select * funct1());
or
select * from myanalyze(select * funct2());
______________________________
Terry Kop
Database Developer
Clear Capital
office: 530.550.2500, ext. 2589
Clear Capital is a trade name of ClearCapital.com, Inc. The information contained in this email is for the exclusive use of its intended recipient(s) and may contain confidential information. All parties other than the intended recipient(s) should refrain from disseminating or otherwise using this information. If you have received this information in error, please immediately notify the sender, delete this information from your computer, and destroy all copies of the information. Clear Capital reserves the right to delete consumer non-public information from the contents of any email to which it responds.
Hello 2011/3/28 Terry Kop <terry.kop@clearcapital.com>: > I'm trying to create a function that will take setof results from various > other functions (they all produce the same output format). Is this possible? > if so how do call it. No, this isn't possible. Regards Pavel Stehule > > ex. > CREATE TYPE emp_t AS ( > ID int, > name varchar(10), > age int, > salary real, > start_date date, > city varchar(10), > region char(1) > ); > > CREATE OR REPLACE FUNCTION func1() RETURNS SETOF emp_t AS $$ > DECLARE > v_row emp_t; > BEGIN > FOR v_row in SELECT * from employee > LOOP > RETURN NEXT v_row; > END LOOP; > END; > $$ LANGUAGE plpgsql; > > CREATE OR REPLACE FUNCTION func2() RETURNS SETOF emp_t AS $$ > DECLARE > v_row emp_t; > BEGIN > FOR v_row in SELECT * from diff_table_or constraints > LOOP > RETURN NEXT v_row; > END LOOP; > END; > $$ LANGUAGE plpgsql; > > CREATE OR REPLACE FUNCTION myanalyze(in_t emp_t) RETURNS SETOF <something> > AS $$ > DECLARE > v_row emp_t; > BEGIN > FOR v_row in EXECUTE in_t > LOOP > -- do something > RETURN NEXT v_row; > END LOOP; > END; > $$ LANGUAGE plpgsql; > > -- so I would like the call to be something like > select * from myanalyze(select * funct1()); > or > select * from myanalyze(select * funct2()); > > > ______________________________ > > Terry Kop > > Database Developer > > Clear Capital > > > > office: 530.550.2500, ext. 2589 > > terry.kop@clearcapital.com > > > > www.ClearCapital.com > > > > > > Clear Capital is a trade name of ClearCapital.com, Inc. The information > contained in this email is for the exclusive use of its intended > recipient(s) and may contain confidential information. All parties other > than the intended recipient(s) should refrain from disseminating or > otherwise using this information. If you have received this information in > error, please immediately notify the sender, delete this information from > your computer, and destroy all copies of the information. Clear Capital > reserves the right to delete consumer non-public information from the > contents of any email to which it responds. >
On Mon, Mar 28, 2011 at 1:55 PM, Terry Kop <terry.kop@clearcapital.com> wrote: > I'm trying to create a function that will take setof results from various > other functions (they all produce the same output format). Is this possible? > if so how do call it. > > ex. > CREATE TYPE emp_t AS ( > ID int, > name varchar(10), > age int, > salary real, > start_date date, > city varchar(10), > region char(1) > ); > > CREATE OR REPLACE FUNCTION func1() RETURNS SETOF emp_t AS $$ > DECLARE > v_row emp_t; > BEGIN > FOR v_row in SELECT * from employee > LOOP > RETURN NEXT v_row; > END LOOP; > END; > $$ LANGUAGE plpgsql; > > CREATE OR REPLACE FUNCTION func2() RETURNS SETOF emp_t AS $$ > DECLARE > v_row emp_t; > BEGIN > FOR v_row in SELECT * from diff_table_or constraints > LOOP > RETURN NEXT v_row; > END LOOP; > END; > $$ LANGUAGE plpgsql; > > CREATE OR REPLACE FUNCTION myanalyze(in_t emp_t) RETURNS SETOF <something> > AS $$ > DECLARE > v_row emp_t; > BEGIN > FOR v_row in EXECUTE in_t > LOOP > -- do something > RETURN NEXT v_row; > END LOOP; > END; > $$ LANGUAGE plpgsql; > > -- so I would like the call to be something like > select * from myanalyze(select * funct1()); > or > select * from myanalyze(select * funct2()); sure: but it is not going to be scalable past medium result sets (i'd get nervous around 10k or so). make myanalyze take a emp_t[]; create or replace function myanalyze(emps emp_t[]) returns... $$ declare e emp_t[]; for e in select unnest(emps) loop ... select myanalyze(array(select funct1()); also, be sure to check out the recent for-in-array feature if you use this. merlin