pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTracking Down Error in Stored Procedure
3+ messages / 2 participants
[nested] [flat]
* Tracking Down Error in Stored Procedure
@ 2006-12-02 05:48 Aaron Bono <postgresql@aranya.com>
2006-12-02 06:20 ` Re: Tracking Down Error in Stored Procedure Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Aaron Bono @ 2006-12-02 05:48 UTC (permalink / raw)
To: pgsql-sql
I have a nightly batch that runs a stored procedure/function. This
procedure has a habit of erroring on the first of each month and gives the
following:
ERROR: cannot EXECUTE a null querystring
CONTEXT: PL/pgSQL function "update_web_site_hits_fn" line 200 at execute
statement
My question is this: What is line 200? Is it the 200th line after the
CREATE part of the stored procedure? Or is it the 200th line after the
BEGIN? Or maybe something else? I have a possible culprit at both places
and don't know which one is the offender.
Also, if the EXECUTE is over many lines, is line 200 the first line of the
EXECUTE statement or the ending line of the EXECUTE (where the ; is)?
I am using PostgreSQL 8.1.3 on CENTOS Linux.
Thanks,
Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Tracking Down Error in Stored Procedure
2006-12-02 05:48 Tracking Down Error in Stored Procedure Aaron Bono <postgresql@aranya.com>
@ 2006-12-02 06:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-02 07:19 ` Re: Tracking Down Error in Stored Procedure Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2006-12-02 06:20 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-sql
"Aaron Bono" <postgresql@aranya.com> writes:
> CONTEXT: PL/pgSQL function "update_web_site_hits_fn" line 200 at execute
> statement
> My question is this: What is line 200? Is it the 200th line after the
> CREATE part of the stored procedure? Or is it the 200th line after the
> BEGIN? Or maybe something else?
It's the 200th line in the function body string. IIRC there is a
special case to not count a leading newline in the body string,
so that the counting goes like
CREATE FUNCTION foo ...
RETURNS ...
AS $$
DECLARE -- this is line 1
or without exploiting the special case
CREATE FUNCTION foo ...
RETURNS ...
AS $$DECLARE -- this is line 1
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Tracking Down Error in Stored Procedure
2006-12-02 05:48 Tracking Down Error in Stored Procedure Aaron Bono <postgresql@aranya.com>
2006-12-02 06:20 ` Re: Tracking Down Error in Stored Procedure Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-12-02 07:19 ` Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 3+ messages in thread
From: Aaron Bono @ 2006-12-02 07:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On 12/2/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Aaron Bono" <postgresql@aranya.com> writes:
> > CONTEXT: PL/pgSQL function "update_web_site_hits_fn" line 200 at
> execute
> > statement
>
> > My question is this: What is line 200? Is it the 200th line after the
> > CREATE part of the stored procedure? Or is it the 200th line after the
> > BEGIN? Or maybe something else?
>
> It's the 200th line in the function body string. IIRC there is a
> special case to not count a leading newline in the body string,
> so that the counting goes like
>
> CREATE FUNCTION foo ...
> RETURNS ...
> AS $$
> DECLARE -- this is line 1
>
> or without exploiting the special case
>
> CREATE FUNCTION foo ...
> RETURNS ...
> AS $$DECLARE -- this is line 1
>
> regards, tom lane
>
I got it fixed. Thanks for the help!
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-12-02 07:19 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-02 05:48 Tracking Down Error in Stored Procedure Aaron Bono <postgresql@aranya.com>
2006-12-02 06:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-02 07:19 ` Aaron Bono <postgresql@aranya.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox