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

howzit Henry

I've pasted in the head of one of my functions. Hope it helps. It is
called through the following:

    SELECT * from fn_usr32_show_sections ( <integer> );

Rory

On 07/09/04, Henry Combrinck (henry@metroweb.co.za) wrote:
> Thanks for the CREATE TYPE samples.  *Using* the custom types seems to be
> a problem (or rather, I'm using it incorrectly).  The following code fails
> with the error message
>
> "WARNING:  Error occurred while executing PL/pgSQL function f_test00
> WARNING:  line 8 at SQL statement
> ERROR:  Attribute "rec1" not found"
>
> /* create same columns as table1 */
> create type TYPE_T1 as (col1 int, col2 int,... etc);
...
> declare
>     rec1 TYPE_T1%ROWTYPE;
> begin
>     for rec1 in select * from table1
>     loop
>         select f_test01(rec1);   /* this is where it fails. */
>     end loop;
>     return 0;
> end;
> ' LANGUAGE 'plpgsql';


CREATE TYPE sec_sections as (
    sectionid          INTEGER,
    sectionname        VARCHAR,
    secupdated         VARCHAR,
    secreports         INTEGER,
    secusers           INTEGER
);

CREATE OR REPLACE FUNCTION
    fn_usr32_show_sections ( integer ) RETURNS setof sec_sections
AS '
DECLARE
    userid           ALIAS for $1;
    resulter         sec_sections%rowtype;
BEGIN

    IF userid is NULL THEN
        RAISE EXCEPTION ''No user provided : ref usr32'';
        RETURN 0;
    END IF;

    PERFORM fn_util07_isadmin(userid);

    FOR resulter IN
        SELECT
            s.n_id                                                as sectionid,
            s.t_name                                              as sectionname,
            COALESCE(to_char(lupd.updated, ''DD Mon''), ''None'') as secupdated,
            COALESCE(rept.num,0)                                  as secreports,
            COALESCE(usr.num,0)                                   as secusers
            ...etc..
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>