Обсуждение: can a function have a setof (returned from another function) as input

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