Finding line of bug in sql function
| От | Rory Campbell-Lange |
|---|---|
| Тема | Finding line of bug in sql function |
| Дата | |
| Msg-id | 20030527213727.GA3438@campbell-lange.net обсуждение исходный текст |
| Ответы |
Re: Finding line of bug in sql function
Re: Finding line of bug in sql function |
| Список | pgsql-general |
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>
В списке pgsql-general по дате отправления: