On Mon, Mar 28, 2011 at 1:55 PM, Terry Kop <terry.kop@xxxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
