Hello all.
I was minding my business, writing a nice long pl/pgsql function and all was well. I tried creating the function (using
\i<file-with-definition>) and started getting funny errors.
I figured out eventually that the problem seems due to line length in a construct of: FOR result IN <SELECT ...> LOOP
Here's the "clean" code that doesn't work:
FOR result IN SELECT initcap( c.name ) AS category,
initcap( l.language ) AS language, count(*) AS howmany
FROM urlinfo u JOIN ratings_by_serial r USING (id) JOIN
categoriesc USING (cid) JOIN languages l USING (lang_id) WHERE u.ratedon
BETWEENstartDate AND endDate GROUP BY category, language ORDER BY category,
language LOOP RETURN NEXT result; END LOOP;
But, if I scrunch up most of it to one line:
FOR result IN SELECT initcap( c.name ) AS category, initcap( l.language ) AS language, count(*) AS
howmanyFROM urlinfo u JOIN ratings_by_serial r USING (id) JOIN categories c USING (cid) JOIN languages l USING
(lang_id)WHERE u.ratedon BETWEEN startDate AND endDate GROUP BY category, language ORDER BY category, language LOOP
RETURN NEXT result; END LOOP;
... it loads this part just fine. This was after about an hour of hair-pulling and log reading. Currently running
PostgreSQL7.3.4 on FreeBSD 4.5-STABLE. I've read through the release notes for the more recent versions (7.3.5, 7.3.6,
7.4.1,7.4.2) but didn't see anything like this mentioned. Perhaps a fix for this is part of another fix?
Any thoughts on who I should start poking about a solution? Is there possibly a configuration setting I can change?
Upgradingis an option, but I'd rather not go there just yet if I can avoid it.
--
David Olbersen
iGuard Engineer
St. Bernard Software
15015 Avenue of Sciences
San Diego, CA 92127
x2152