Finding line of bug in sql function

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

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>