Loop in loop - Mailing list pgsql-general

From Moritz Bayer
Subject Loop in loop
Date
Msg-id c244500b0701220643g715d8b7cg52ad994530d4a6a1@mail.gmail.com
Whole thread Raw
List pgsql-general
Hello group,

I've got a new problem where I hope someone can give me a solution.

I have witten a function which should give back a type created by me. To get the data into the type, I have to go through a loop, which holds other loops.
Simplified (not really), it looks like this:

CREATE OR REPLACE FUNCTION getregistrationtagging()
  RETURNS SETOF ty_usertrackung AS
$BODY$
DECLARE objReturn ty_usertrackung%rowtype;
DECLARE objDate ty_trackdate%rowtype;
DECLARE objTag ty_tagtype%rowtype;
DECLARE objDesignid ty_designidtype%rowtype;
BEGIN
        for objDate IN
         SELECT     date_part('day',trackdate) as iDay,
            date_part('month',trackdate) as iMonth,
            date_part('year',trackdate) as iYear
         FROM tbl_usertracking_registration
         GROUP BY     date_part('day',trackdate),
                date_part('month',trackdate),
                date_part('year',trackdate)
           loop
                 for obTag IN
                  SELECT  tag as ty_tag
                  FROM tbl_usertracking_registration
                  WHERE date_part('day',trackdate)= objDate.iDay
                  AND date_part('month',trackdate)=objDate.iMonth
                 AND date_part('year',trackdate)=objDate.iYear
                GROUP BY tag
            loop
               for objDesignid IN
                   SELECT designid as ty_designid
                  FROM tbl_usertracking_registration
                   WHERE date_part('day',trackdate)=objDate.iDay
                  AND date_part('month',trackdate)= objDate.iMonth
                  AND date_part('year',trackdate)=objDate.iYear
                  AND tag=objTag.ty_tag
                  GROUP BY designid
              loop
               
                objReturn.ty_designid := objDesignid.ty_designid;
                objReturn.ty_tag := objTag.ty_tag;
                SELECT INTO objReturn.ty_count count(*) FROM FROM tbl_usertracking_registration
                    WHERE date_part('day',trackdate)= objDate.iDay
                    AND date_part('month',trackdate)=objDate.iMonth
                    AND date_part('year',trackdate)=objDate.iYear
                    AND tag=objTag.ty_tag
                    AND designid= objDesignid.ty_designid;
                    return objReturn;
            END LOOP;
        END LOOP;
     END LOOP;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION getregistrationtagging() OWNER TO postgres;

Well, there are probably other ways (probably more grouping) to select the data I need. My brain came up with this solution, but it doesn't work and postgres gives back »loop variable of loop over rows must be record or row variable« as the error message. I don't know what it wants to tell me so any input is welcime.

Thanks in advance
Mo

pgsql-general by date:

Previous
From: Erik Jones
Date:
Subject: Tracking database activity
Next
From: Ron Peterson
Date:
Subject: Re: CAST function for user defined type