Hi,
2011/8/9 Merlin Moncure <mmoncure@gmail.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.ivanic@gmail.com)
