Re: Finding line of bug in sql function

Thanks to everyone's help, I've found that I wasn't quoting my 's
properly. Sorry for the newbie issue.

Still, my original question was about how to find the error line in
function after loading it from file.

For instance the function I originally wrote about still has a bug (even
after quoting properly!). psql reports:

temporary=> \i sql_functions/fn_c2c_transports_person.sql
CREATE FUNCTION
temporary=> select fn_c2c_transports_person (1, 'email', 'validate');
WARNING:  plpgsql: ERROR during compile of fn_c2c_transports_person near line 92
ERROR:  parse error at or near "ELSE"

Is this line 92, starting at the BEGIN statement? Will the lines of the
function accord with the way I laid out the input file? Does the parser
recognise comments and blank lines?

I've included my buggy function again below. Sorry about its length!

Thanks,
Rory

On 27/05/03, Nigel J. Andrews (nandrews@investsystems.co.uk) wrote:
> On Tue, 27 May 2003, Rory Campbell-Lange wrote:
>
> > 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.)
>
> Bet your file has more or less 202 lines after the declaration section
> of your function though.

Not sure what you mean. The total sql file length of the function is 125
lines.

> >     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
>
> Unterminated string eh?

Oops yes. Thanks!

> >     setting VARCHAR     := '';


---------------------------------------------------------------------

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

    IF id IS NULL
        THEN RAISE EXCEPTION
            ''no person id found at fn_c2c_transports_person'';
        RETURN 0;
    END IF;

    IF transport IS NULL
        THEN RAISE EXCEPTION
            ''no transport found at fn_c2c_transports_person'';
        RETURN 0;
    END IF;

    IF operation IS NULL
        THEN RAISE EXCEPTION
            ''no operation found at fn_c2c_transports_person'';
        RETURN 0;
    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>