From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Terry Kop <terry(dot)kop(at)clearcapital(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: can a function have a setof (returned from another function) as input |
Date: | 2011-03-28 20:09:38 |
Message-ID: | AANLkTimyavETeRmBWFJdfM4V-MqRX-M9W+XfGVu88mPa@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Mar 28, 2011 at 1:55 PM, Terry Kop <terry(dot)kop(at)clearcapital(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2011-03-29 02:25:56 | Re: Autocommit off - commits/rollbacks |
Previous Message | Octavio Alvarez | 2011-03-28 19:27:38 | Re: How do i calculate a finish time when the start time is ambiguous? |