Re: Passing RECORD variable from func1() to func2()

Howzit Henry

On 06/09/04, Henry Combrinck (henry(at)metroweb(dot)co(dot)za) wrote:
> Essentially, I would like to pass a RECORD variable from one function to
> another using plpgsql:

You may want to have a look at using cursor references.

For instance:

CREATE FUNCTION use_cursors ( INTEGER ) RETURNS INTEGER AS '
DECLARE
ref_cursors REFCURSOR;
total INTEGER := 0;
BEGIN
curs := get_ref_cursor_from_other_function ( $1 );
total := use_curs_to_do_totaling_function ( ref_cursors );
RETURN total;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION get_ref_cursor_from_other_function ( INTEGER ) RETURNS REFCURSOR AS '
DECLARE
next_val REFCURSOR;
BEGIN
OPEN next_val FOR
SELECT * FROM mytable WHERE intcol = $1;
RETURN ( next_val);
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION use_curs_to_do_totaling_function ( REFCURSOR ) RETURNS INTEGER AS '
DECLARE
myrow mytable%rowtype;
total INTEGER := 0;
next_val ALIAS for $1;
BEGIN
LOOP
FETCH next_val INTO myrow;
EXIT WHEN NOT FOUND;
total := total + myrow.<somecolval>;
END LOOP;
RETURN (total);
END;
' LANGUAGE 'plpgsql';

--
Rory Campbell-Lange
<rory(at)campbell-lange(dot)net>
<www.campbell-lange.net>