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@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