Loop in loop

Поиск
Список
Период
Сортировка
От Moritz Bayer
Тема Loop in loop
Дата
Msg-id c244500b0701220643g715d8b7cg52ad994530d4a6a1@mail.gmail.com
обсуждение исходный текст
Список 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 по дате отправления:

Предыдущее
От: Erik Jones
Дата:
Сообщение: Tracking database activity
Следующее
От: Ron Peterson
Дата:
Сообщение: Re: CAST function for user defined type