Thread: Line length in pl/pgsql function

Line length in pl/pgsql function

From
"David Olbersen"
Date:
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


Re: Line length in pl/pgsql function

From
Tom Lane
Date:
"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


Re: Line length in pl/pgsql function

From
Richard Huxton
Date:
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


Re: Line length in pl/pgsql function

From
"David Olbersen"
Date:
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

Re: Line length in pl/pgsql function

From
"David Olbersen"
Date:
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


Re: Line length in pl/pgsql function

From
Tom Lane
Date:
"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:                                                   ^


Re: Line length in pl/pgsql function

From
"David Olbersen"
Date:
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


Re: Line length in pl/pgsql function

From
"David Olbersen"
Date:
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


Date format issue

From
"Raman"
Date:
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



Re: Date format issue

From
Stephan Szabo
Date:
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.



Re: Date format issue

From
Tom Lane
Date:
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