Finding line of bug in sql function

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>

Responses

Browse pgsql-general by date

  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