Loop in loop

From: "Moritz Bayer" <moritz(dot)bayer(at)googlemail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Loop in loop
Date: 2007-01-22 14:43:42
Message-ID: c244500b0701220643g715d8b7cg52ad994530d4a6a1@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron Peterson 2007-01-22 14:44:52 CAST function for user defined type
Previous Message Ron Johnson 2007-01-22 14:34:32 Re: More grist for the PostgreSQL vs MySQL mill