table / query as a prameter for PL/pgSQL function

From: Ondrej Ivanič <ondrej(dot)ivanic(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: table / query as a prameter for PL/pgSQL function
Date: 2011-08-08 03:24:30
Message-ID: CAM6mie+BVqTNWXiBnh-JCQE0eTOv7AA0B=FJn9Kf6W_-F2PDjg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

It is possible to pass query result (or cursor?) as function
parameter? I need a function which emits zero or more rows per input
row (map function from map&reduce paradigm). Function returns record
(or array): (value1, value2, value3)
I've tried the following:

1) create or replace function test (r record) returns setof record as $$ ...
Doesn't work: PL/pgSQL functions cannot accept type record

2) pass query as text parameter and open no scroll cursor inside the function
It works but it's ugly.

3) hardcode the query inside function
Similar to (2) and looks better but I need several functions with
different queries inside:
...
for r in (query) loop
...
end loop;
...

4) use function in "select" clause:
select my_map_func(col1, col2, col3, col4) from ... -- the rest of the query
In this case I wasn't able figure out how to access record members
returned by the function:

select ?, ?, ?, count(*) from (
select my_map_func(col1, col2, col3, col4) as map_func_result from ...
) as map
group by 1, 2, 3

The '?' should be something like map.map_func_result.value1 (both
map.value1 and map_func_result.value1 doesn't not work). If function
returns array then I can access value1 by using map_func_result[1]

Is there a better way how to solve this? I'm kind of satisfied with 4
(maybe 3) but it is little bit cumbersome

Thanks,
--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2011-08-08 03:50:02 Re: table / query as a prameter for PL/pgSQL function
Previous Message Craig Ringer 2011-08-08 02:09:19 Re: Effect of a kill -9 on postgres