Thread: Finding line of bug in sql function
I am trying to load a function into a db using \i within psql. I am
getting an error, but I'm finding it difficult to find the line of the
function as the function itself only has 125 lines! (I use vim as my
editor.)
temporary=> \i sql_functions/fn_tmp.sql
CREATE FUNCTION
temporary=> select fn_c2c_transports_person (1, 'email', 'validate');
WARNING: plpgsql: ERROR during compile of fn_c2c_transports_person near line 202
ERROR: unterminated string
The function is below.
Thanks for any help.
Rory
--------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION
fn_c2c_transports_person ( integer, varchar, varchar ) RETURNS INTEGER
AS '
DECLARE
id ALIAS for $1;
transport ALIAS for $2;
operation ALIAS for $3;
recone RECORD;
setting VARCHAR := '';
BEGIN
-- more extensive checking to be done in client program
RAISE NOTICE ''HI'';
IF id IS NULL
THEN RAISE EXCEPTION
''no person id found at fn_c2c_transports_person'';
END IF;
IF transport IS NULL
THEN RAISE EXCEPTION
''no transport found at fn_c2c_transports_person'';
END IF;
IF operation IS NULL
THEN RAISE EXCEPTION
''no operation found at fn_c2c_transports_person'';
END IF;
/*
operations are:
validate (and turn on) 1
turn on 1
turn off 2
turn off all 2 (both)
*/
SELECT INTO recone
n_email_status, n_txt_status
FROM
people
WHERE
n_id = id;
IF NOT FOUND THEN
RAISE EXCEPTION
''no email or txt status found for person at fn_c2c_transports_person'';
RETURN 0;
END IF;
-- if transports = all
IF transport = ''all'' THEN
IF recone.n_email_status > 0 THEN
UPDATE
people
SET
n_email_status = 2
WHERE
n_id = id;
END IF;
IF recone.n_txt_status > 0 THEN
UPDATE
people
SET
n_txt_status = 2
WHERE
n_id = id;
END IF;
-- single settings changes for email and txt messaging
ELSE IF transport = ''email'' THEN
IF operation = ''validate'' THEN
setting := 1;
ELSE IF operation = ''on'' AND recone.n_email_status = 2 THEN
setting := 1;
ELSE IF operation = ''off'' AND recone.n_email_status = 1 THEN
setting := 2;
ELSE
return 0;
END IF;
UPDATE
people
SET
n_email_status = setting
WHERE
n_id = id;
ELSE IF transport = ''txt'' THEN
IF operation = ''validate'' THEN
setting := 1;
ELSE IF operation = ''on'' AND recone.n_txt_status = 2 THEN
setting := 1;
ELSE IF operation = ''off'' AND recone.n_txt_status = 1 THEN
setting := 2;
ELSE
return 0;
END IF;
UPDATE
people
SET
n_txt_status = setting
WHERE
n_id = id;
END IF;
RETURN 1;
END;'
LANGUAGE plpgsql;
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
> DECLARE
> id ALIAS for $1;
> transport ALIAS for $2;
> operation ALIAS for $3;
> recone RECORD;
> setting VARCHAR := '';
^^
Doesn't this need to be ''''?
--
Mike Nolan
On Tue, 27 May 2003, Rory Campbell-Lange wrote:
> I am trying to load a function into a db using \i within psql. I am
> getting an error, but I'm finding it difficult to find the line of the
> function as the function itself only has 125 lines! (I use vim as my
> editor.)
Bet your file has more or less 202 lines after the declaration section of your
function though.
>
> temporary=> \i sql_functions/fn_tmp.sql
> CREATE FUNCTION
> temporary=> select fn_c2c_transports_person (1, 'email', 'validate');
> WARNING: plpgsql: ERROR during compile of fn_c2c_transports_person near line 202
> ERROR: unterminated string
Unterminated string eh?
>
> The function is below.
>
> Thanks for any help.
> Rory
>
> --------------------------------------------------------------------------------------
>
> CREATE OR REPLACE FUNCTION
> fn_c2c_transports_person ( integer, varchar, varchar ) RETURNS INTEGER
> AS '
> DECLARE
> id ALIAS for $1;
> transport ALIAS for $2;
> operation ALIAS for $3;
> recone RECORD;
> setting VARCHAR := '';
Could it be this by any chance?
You need to escape the single quote mark within the body of the function since
the single quote is delimiting body of the function itself. You do that by
doubling up on the single quotes. It's an easy mistake to make, I keep doing it
myself although now I seem to be going through the faze of putting two single
quotes instead of just one when using the likes of psql. So what you have there
is the first ' in your assignment is escaping the second and so rather than the
empty string you are obviously expecting you're making a string of everything
from the ; onwards, until the next '' sequence, whereever that may be.
Change that variable declaration to be:
setting VARCHAR := '''';
and it should work. I'm not sure how long a varchar field is though, shouldn't
that have a length limit specified? I tend to use text type, haven't got a
single varchar in the application I'm working on at the moment.
--
Nigel J. Andrews
> BEGIN
>
> -- more extensive checking to be done in client program
> RAISE NOTICE ''HI'';
>
> IF id IS NULL
> THEN RAISE EXCEPTION
> ''no person id found at fn_c2c_transports_person'';
> END IF;
>
> IF transport IS NULL
> THEN RAISE EXCEPTION
> ''no transport found at fn_c2c_transports_person'';
> END IF;
>
> IF operation IS NULL
> THEN RAISE EXCEPTION
> ''no operation found at fn_c2c_transports_person'';
> END IF;
>
> /*
> operations are:
> validate (and turn on) 1
> turn on 1
> turn off 2
> turn off all 2 (both)
> */
>
> SELECT INTO recone
> n_email_status, n_txt_status
> FROM
> people
> WHERE
> n_id = id;
>
> IF NOT FOUND THEN
> RAISE EXCEPTION
> ''no email or txt status found for person at fn_c2c_transports_person'';
> RETURN 0;
> END IF;
>
> -- if transports = all
> IF transport = ''all'' THEN
>
> IF recone.n_email_status > 0 THEN
> UPDATE
> people
> SET
> n_email_status = 2
> WHERE
> n_id = id;
> END IF;
>
> IF recone.n_txt_status > 0 THEN
> UPDATE
> people
> SET
> n_txt_status = 2
> WHERE
> n_id = id;
> END IF;
>
> -- single settings changes for email and txt messaging
>
> ELSE IF transport = ''email'' THEN
>
> IF operation = ''validate'' THEN
> setting := 1;
> ELSE IF operation = ''on'' AND recone.n_email_status = 2 THEN
> setting := 1;
> ELSE IF operation = ''off'' AND recone.n_email_status = 1 THEN
> setting := 2;
> ELSE
> return 0;
> END IF;
>
> UPDATE
> people
> SET
> n_email_status = setting
> WHERE
> n_id = id;
>
> ELSE IF transport = ''txt'' THEN
>
> IF operation = ''validate'' THEN
> setting := 1;
> ELSE IF operation = ''on'' AND recone.n_txt_status = 2 THEN
> setting := 1;
> ELSE IF operation = ''off'' AND recone.n_txt_status = 1 THEN
> setting := 2;
> ELSE
> return 0;
> END IF;
>
> UPDATE
> people
> SET
> n_txt_status = setting
> WHERE
> n_id = id;
>
> END IF;
>
> RETURN 1;
>
> END;'
> LANGUAGE plpgsql;
>
>
Thanks to everyone's help, I've found that I wasn't quoting my 's
properly. Sorry for the newbie issue.
Still, my original question was about how to find the error line in
function after loading it from file.
For instance the function I originally wrote about still has a bug (even
after quoting properly!). psql reports:
temporary=> \i sql_functions/fn_c2c_transports_person.sql
CREATE FUNCTION
temporary=> select fn_c2c_transports_person (1, 'email', 'validate');
WARNING: plpgsql: ERROR during compile of fn_c2c_transports_person near line 92
ERROR: parse error at or near "ELSE"
Is this line 92, starting at the BEGIN statement? Will the lines of the
function accord with the way I laid out the input file? Does the parser
recognise comments and blank lines?
I've included my buggy function again below. Sorry about its length!
Thanks,
Rory
On 27/05/03, Nigel J. Andrews (nandrews@investsystems.co.uk) wrote:
> On Tue, 27 May 2003, Rory Campbell-Lange wrote:
>
> > I am trying to load a function into a db using \i within psql. I am
> > getting an error, but I'm finding it difficult to find the line of the
> > function as the function itself only has 125 lines! (I use vim as my
> > editor.)
>
> Bet your file has more or less 202 lines after the declaration section
> of your function though.
Not sure what you mean. The total sql file length of the function is 125
lines.
> > temporary=> \i sql_functions/fn_tmp.sql
> > CREATE FUNCTION
> > temporary=> select fn_c2c_transports_person (1, 'email', 'validate');
> > WARNING: plpgsql: ERROR during compile of fn_c2c_transports_person near line 202
> > ERROR: unterminated string
>
> Unterminated string eh?
Oops yes. Thanks!
> > setting VARCHAR := '';
---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION
fn_c2c_transports_person ( integer, varchar, varchar ) RETURNS INTEGER
AS '
DECLARE
id ALIAS for $1;
transport ALIAS for $2;
operation ALIAS for $3;
recone RECORD;
setting VARCHAR := '''';
BEGIN
-- more extensive checking to be done in client program
IF id IS NULL
THEN RAISE EXCEPTION
''no person id found at fn_c2c_transports_person'';
RETURN 0;
END IF;
IF transport IS NULL
THEN RAISE EXCEPTION
''no transport found at fn_c2c_transports_person'';
RETURN 0;
END IF;
IF operation IS NULL
THEN RAISE EXCEPTION
''no operation found at fn_c2c_transports_person'';
RETURN 0;
END IF;
/*
operations are:
validate (and turn on) 1
turn on 1
turn off 2
turn off all 2 (both)
*/
SELECT INTO recone
n_email_status, n_txt_status
FROM
people
WHERE
n_id = id;
IF NOT FOUND THEN
RAISE EXCEPTION
''no email or txt status found for person at fn_c2c_transports_person'';
RETURN 0;
END IF;
-- if transports = all
IF transport = ''all'' THEN
IF recone.n_email_status > 0 THEN
UPDATE
people
SET
n_email_status = 2
WHERE
n_id = id;
END IF;
IF recone.n_txt_status > 0 THEN
UPDATE
people
SET
n_txt_status = 2
WHERE
n_id = id;
END IF;
-- single settings changes for email and txt messaging
ELSE IF transport = ''email'' THEN
IF operation = ''validate'' THEN
setting := 1;
ELSE IF operation = ''on'' AND recone.n_email_status = 2 THEN
setting := 1;
ELSE IF operation = ''off'' AND recone.n_email_status = 1 THEN
setting := 2;
ELSE
return 0;
END IF;
UPDATE
people
SET
n_email_status = setting
WHERE
n_id = id;
ELSE IF transport = ''txt'' THEN
IF operation = ''validate'' THEN
setting := 1;
ELSE IF operation = ''on'' AND recone.n_txt_status = 2 THEN
setting := 1;
ELSE IF operation = ''off'' AND recone.n_txt_status = 1 THEN
setting := 2;
ELSE
return 0;
END IF;
UPDATE
people
SET
n_txt_status = setting
WHERE
n_id = id;
END IF;
RETURN 1;
END;'
LANGUAGE plpgsql;
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
As far as the function goes, I now know the difference between ELSIFs and ELSE IFs. The function is working now. Still interested in how to find the bug line using vim though. Thanks, R On 28/05/03, Rory Campbell-Lange (rory@campbell-lange.net) wrote: > Thanks to everyone's help, I've found that I wasn't quoting my 's > properly. Sorry for the newbie issue. > > Still, my original question was about how to find the error line in > function after loading it from file. > > For instance the function I originally wrote about still has a bug (even > after quoting properly!). psql reports: > > temporary=> \i sql_functions/fn_c2c_transports_person.sql > CREATE FUNCTION > temporary=> select fn_c2c_transports_person (1, 'email', 'validate'); > WARNING: plpgsql: ERROR during compile of fn_c2c_transports_person near line 92 > ERROR: parse error at or near "ELSE" > > Is this line 92, starting at the BEGIN statement? Will the lines of the > function accord with the way I laid out the input file? Does the parser > recognise comments and blank lines? -- Rory Campbell-Lange <rory@campbell-lange.net> <www.campbell-lange.net>
On Wed, May 28, 2003 at 02:21:13PM +0100, Rory Campbell-Lange wrote: > As far as the function goes, I now know the difference between ELSIFs > and ELSE IFs. The function is working now. Still interested in how to > find the bug line using vim though. I think vim works ok. When pointed on the line with beginning of function body (the starting ') I pressed 92j and vim went to the exact error line (the one with ELSE IF ... where postgres expected END IF instead). Does your vim work differently? Regards, Mariusz -- Mariusz Jadczak <mjadczak@polsoft.pl>, linux user #192344 _________________________________________________________ Polsoft Engineering Sp. z.o.o, http://www.polsoft.pl
Beginning of function body. Great! Thanks. Rory. On 28/05/03, Mariusz Jadczak (mjadczak@polsoft.pl) wrote: > > On Wed, May 28, 2003 at 02:21:13PM +0100, Rory Campbell-Lange wrote: > > As far as the function goes, I now know the difference between ELSIFs > > and ELSE IFs. The function is working now. Still interested in how to > > find the bug line using vim though. > > I think vim works ok. When pointed on the line with beginning of > function body (the starting ') I pressed 92j and vim went to the > exact error line (the one with ELSE IF ... where postgres > expected END IF instead). Does your vim work differently? -- Rory Campbell-Lange <rory@campbell-lange.net> <www.campbell-lange.net>