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
