From: | Rory Campbell-Lange <rory(at)campbell-lange(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Finding line of bug in sql function |
Date: | 2003-05-27 21:37:27 |
Message-ID: | 20030527213727.GA3438@campbell-lange.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)campbell-lange(dot)net>
<www.campbell-lange.net>
From | Date | Subject | |
---|---|---|---|
Next Message | Hadley Willan | 2003-05-27 21:56:23 | Can anybody recommend an IDE for writing SQL/PLPSQL |
Previous Message | Ron Johnson | 2003-05-27 21:35:03 | Re: pl-pgsql question |