Re: Finding line of bug in sql function

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(at)investsystems(dot)co(dot)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(at)campbell-lange(dot)net>
<www.campbell-lange.net>