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
(nolan@celery.tssi.com)
Re: Finding line of bug in sql function ("Nigel J. Andrews" <nandrews@investsystems.co.uk>) |
Список | 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 по дате отправления: