Re: table / query as a prameter for PL/pgSQL function

Hi,

2011/8/9 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> You have a few of different methods for passing sets between functions.

I do not want to pass data between functions. The ideal solution
should look like this:
select * from my_map_func(<select query>)

> 1) refcursor as David noted.  reasonably fast. however, I find the
> 'FETCH' mechanic a little inflexible.

I've came across this but manual example wasn't (isn't) clear to me:
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

What is the "funccursor"?

Anyway, I will try to use something like this
CREATE FUNCTION my_map_func(refcursor) returns setof text[] as $$
...
$$ LANGUAGE 'plpgsql';

BEGIN;
DECLARE my_cursor NO SCROLL CURSOR FOR <query>;
SELECT * FROM my_map_func(my_cursor);
COMMIT;

I'll keep you posted.

> 2) stage data to TABLE/TEMP TABLE; extremely flexible, but can be a
> headache because a non temp table can get thrashed pretty hard a and a
> 'TEMP' can cause severe function plan invalidation issues if you're
> not careful

I'm not familiar with this issue (function plan invalidation issues).
Could you please provide more details/links about it?

> 3) arrays of composites -- the most flexible and very fast for *small*
> amounts of records (say less than 10,000):

My data set is huge: between 1 and 5 mil rows and avg row size is 100
- 400 bytes

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