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

Howzit Henry

On 06/09/04, Henry Combrinck (henry@metroweb.co.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@campbell-lange.net>
<www.campbell-lange.net>