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 по дате отправления:

Предыдущее
От: Ron Johnson
Дата:
Сообщение: Re: pl-pgsql question
Следующее
От: Hadley Willan
Дата:
Сообщение: Can anybody recommend an IDE for writing SQL/PLPSQL