can a function have a setof (returned from another function) as input

From: Terry Kop <terry(dot)kop(at)clearcapital(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: can a function have a setof (returned from another function) as input
Date: 2011-03-28 18:55:27
Message-ID: AANLkTinYcWmpFbpAQqBZXWw=Wh0iA8tZrRUadtT1K-8C@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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());

______________________________

Terry Kop

Database Developer

Clear Capital

office: 530.550.2500, ext. 2589

terry(dot)kop(at)clearcapital(dot)com

www.ClearCapital.com <http://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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2011-03-28 19:24:08 Re: can a function have a setof (returned from another function) as input
Previous Message Nicholson, Brad (Toronto, ON, CA) 2011-03-28 16:59:58 Re: Disk space usage analyzer?