Thread: Line length in pl/pgsql function
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
"David Olbersen" <DOlbersen@stbernard.com> writes:
> 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
Hmm ... plpgsql had some string-length issues as recently as 7.2.2, but
I don't know of any problems since then. Could you submit a *complete*
test case, rather than making us guess the details?
regards, tom lane
On Wednesday 17 March 2004 00:47, David Olbersen wrote: > > 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 [etc] I don't suppose your actual code has any comments in it? I stumbled across an obscure parser issue in plpgsql regarding comments/line-endings and I think that was somewhere in 7.3.x Might be worth a quick look in the -bugs list archives and see if anything looks like your problem. -- Richard Huxton Archonet Ltd
Tom Lane wrote:
> Hmm ... plpgsql had some string-length issues as recently as 7.2.2, but
> I don't know of any problems since then. Could you submit a *complete*
> test case, rather than making us guess the details?
Sure. I didn't want to dump a huge email to have somebody say "Addressed in 7.3.5" :)
PostgreSQL version...: 7.3.4
Compiled with........: gcc 2.95.3
Architecture.........: Intel Pentium III
Operating System.....: FreeBSD 4.5-STABLE
Reproduction
--------------
1) Connect to the database using psql
2) Attempt to create a return type and function using:
\i test_ratedby_category_lang.plsql
The errors from psql are attached as psql-errors.txt
The file with the function is attached as test_ratedby_category_lang.plsql
The postgresql.conf and current postmaster.opts are attached as well.
I can send syslog output as well, if desired, at any debug level (I don't know what's appropriate).
I tried reproducing the bug before I composed this message and it didn't break in the same way as yesterday. That's not
veryhelpful -- but it did still break with the same error message (as seen in psql-errors.txt) but my syslog output was
different.I can attach both if that's helpful.
Any more info needed?
--
David Olbersen
iGuard Engineer
St. Bernard Software
15015 Avenue of Sciences
San Diego, CA 92127
x2152
Attachment
Richard Huxton wrote: > On Wednesday 17 March 2004 00:47, David Olbersen wrote: > > > > 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 [etc] > > I don't suppose your actual code has any comments in it? I stumbled across an > obscure parser issue in plpgsql regarding comments/line-endings and I think > that was somewhere in 7.3.x That was the actual code. There are comments, but they're one line above that one, and on their own line. Everything is developedusing Vim on FreeBSD, so line-endings shouldn't be a problem, should they? > Might be worth a quick look in the -bugs list archives and see if anything > looks like your problem. Didn't see anything on a quick look. -- David Olbersen iGuard Engineer St. Bernard Software 15015 Avenue of Sciences San Diego, CA 92127 x2152
"David Olbersen" <DOlbersen@stbernard.com> writes:
> The errors from psql are attached as psql-errors.txt
> The file with the function is attached as =
> test_ratedby_category_lang.plsql
> The postgresql.conf and current postmaster.opts are attached as well.
Hate to tell you this, but it's just pilot error. You've got comments
like these embedded in the plpgsql function:
ELSIF cat = ''none'' THEN -- none,none = don't show the languages, or categories (whaaat?)
FOR result IN
That quote in "don't" has to be doubled. Remember this whole thing is a
giant string literal as far as the outer CREATE FUNCTION syntax is
concerned. The fact that the quote is within a comment in terms of the
plpgsql syntax doesn't mean a thing to the outer string-literal parser.
This'll all get a *lot* easier in 7.5 when you can use dollar-quoting
instead of having to double quote marks in function bodies ...
regards, tom lane
PS: note to hackers: Fabien's new error localization code does a pretty
decent job of fingering the problem. When I loaded this test file into
CVS tip I got
psql:test_ratedby_category_lang.plsql:95: ERROR: syntax error at or near "t" at character 3419
psql:test_ratedby_category_lang.plsql:95: LINE 81: -- none,everything = don't show the language...
psql:test_ratedby_category_lang.plsql:95: ^
Tom Lane wrote: > Hate to tell you this, but it's just pilot error. You've got comments > like these embedded in the plpgsql function: > > ELSIF cat = ''none'' THEN > -- none,none = don't show the languages, or categories > (whaaat?) FOR result IN > > That quote in "don't" has to be doubled. Remember this whole thing is a > giant string literal as far as the outer CREATE FUNCTION syntax is > concerned. The fact that the quote is within a comment in terms of the > plpgsql syntax doesn't mean a thing to the outer string-literal parser. *sigh* I'd rather have pilot error than having to wait for a patch :) It's funny, I use Vim with syntax highlighting to catch this sort of thing. 99% of the time it does, I guess this is thatother 1%. > PS: note to hackers: Fabien's new error localization code does a pretty > decent job of fingering the problem. When I loaded this test file into > CVS tip I got > > psql:test_ratedby_category_lang.plsql:95: ERROR: syntax error at or near "t" > at character 3419 psql:test_ratedby_category_lang.plsql:95: LINE 81: > -- none,everything = don't show the language... > psql:test_ratedby_category_lang.plsql:95: > ^ That would have been handy! -- David Olbersen iGuard Engineer St. Bernard Software 15015 Avenue of Sciences San Diego, CA 92127 x2152
David Olbersen wrote: > *sigh* I'd rather have pilot error than having to wait for a patch :) Hmmm, that doesn't look right in retrospect. What I meant to say was THANK YOU TOM! -- David Olbersen iGuard Engineer St. Bernard Software 15015 Avenue of Sciences San Diego, CA 92127 x2152
Hello All... In my query of time zone i have details of interval with me like '-9:00', '+5:30' etc my problem is regarding the format of Date which i receive... ie. when I add the interval field the results are like: Query: select current_date at TIME ZONE "interval" '+5:30'; timezone 11399908 years 8 mons 1538 days but instead of interval when i run query like" Query: select current_date at TIME ZONE 'IST' timezone 2004-03-17 20:30:00 I want the format should be like 2004-03-17 20:30:00 How can I do that with INTERVAL information with me?? pls guide me.. Also can I have link to some doucmentation where i can read about Date/Time zone related theroy/functions?? thanks in advance.. regards, Raman Garg
On Thu, 18 Mar 2004, Raman wrote: > In my query of time zone i have details of interval with me like '-9:00', > '+5:30' etc > > my problem is regarding the format of Date which i receive... ie. when I add > the interval field the results are like: > Query: > select current_date at TIME ZONE "interval" '+5:30'; > timezone > 11399908 years 8 mons 1538 days You didn't say what version you're using, but IIRC, there was a bug which caused the above sort of behavior. I think it was fixed in 7.4 and could be fixed in 7.3.x with a catalog change which you might be able to find in the archives.
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> You didn't say what version you're using, but IIRC, there was a bug which
> caused the above sort of behavior. I think it was fixed in 7.4 and could
> be fixed in 7.3.x with a catalog change which you might be able to find in
> the archives.
Good memory! Here's the CVS log entry:
2003-03-13 23:44 tgl
* src/include/catalog/pg_proc.h (REL7_3_STABLE): Repair incorrectprorettype entry for timestamptz_izone. Can't force
initdbin the7.3 branch, but we can at least make it right for people whoinstall 7.3.3 from scratch.
It looks likeUPDATE pg_proc SET prorettype = 1114 WHERE oid = 1026;
would fix it, but I counsel testing that in a scratch database ...
regards, tom lane