Skip site navigation (1) Skip section navigation (2)

Line length in pl/pgsql function

From: "David Olbersen" <DOlbersen(at)stbernard(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Line length in pl/pgsql function
Date: 2004-03-17 00:47:34
Message-ID: E7E213858379814A9AE48CA6754F5ECB0D749E@mail01.stbernard.com (view raw or flat)
Thread:
Lists: pgsql-sql
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 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;

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 howmany FROM 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 PostgreSQL 7.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? Upgrading is 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

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2004-03-17 05:35:47
Subject: Re: Line length in pl/pgsql function
Previous:From: Frank FinnerDate: 2004-03-16 17:56:35
Subject: Re: working with unix timestamp

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group