Thread: Finding line of bug in sql function

Finding line of bug in sql function

From
Rory Campbell-Lange
Date:
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>

Re: Finding line of bug in sql function

From
nolan@celery.tssi.com
Date:
> DECLARE
>     id                  ALIAS for $1;
>     transport           ALIAS for $2;
>     operation           ALIAS for $3;
>     recone              RECORD;
>     setting VARCHAR     := '';
                             ^^
Doesn't this need to be ''''?
--
Mike Nolan

Re: Finding line of bug in sql function

From
"Nigel J. Andrews"
Date:
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.

>
>     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?

>
> 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     := '';

Could it be this by any chance?

You need to escape the single quote mark within the body of the function since
the single quote is delimiting body of the function itself. You do that by
doubling up on the single quotes. It's an easy mistake to make, I keep doing it
myself although now I seem to be going through the faze of putting two single
quotes instead of just one when using the likes of psql. So what you have there
is the first ' in your assignment is escaping the second and so rather than the
empty string you are obviously expecting you're making a string of everything
from the ; onwards, until the next '' sequence, whereever that may be.

Change that variable declaration to be:

    setting VARCHAR := '''';

and it should work. I'm not sure how long a varchar field is though, shouldn't
that have a length limit specified? I tend to use text type, haven't got a
single varchar in the application I'm working on at the moment.

--
Nigel J. Andrews



> 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;
>
>



Re: Finding line of bug in sql function

From
Rory Campbell-Lange
Date:
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>

Re: Finding line of bug in sql function

From
Rory Campbell-Lange
Date:
As far as the function goes, I now know the difference between ELSIFs
and ELSE IFs. The function is working now. Still interested in how to
find the bug line using vim though.

Thanks,
R

On 28/05/03, Rory Campbell-Lange (rory@campbell-lange.net) wrote:
> 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?

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>

Re: Finding line of bug in sql function

From
Mariusz Jadczak
Date:
On Wed, May 28, 2003 at 02:21:13PM +0100, Rory Campbell-Lange wrote:
> As far as the function goes, I now know the difference between ELSIFs
> and ELSE IFs. The function is working now. Still interested in how to
> find the bug line using vim though.

I think vim works ok. When pointed on the line with beginning of
function body (the starting ') I pressed 92j and vim went to the
exact  error  line  (the one  with  ELSE IF ...  where  postgres
expected END IF instead). Does your vim work differently?

Regards, Mariusz

--
Mariusz Jadczak <mjadczak@polsoft.pl>, linux user #192344
_________________________________________________________
Polsoft Engineering Sp. z.o.o,      http://www.polsoft.pl


Re: Finding line of bug in sql function

From
Rory Campbell-Lange
Date:
Beginning of function body. Great! Thanks. Rory.

On 28/05/03, Mariusz Jadczak (mjadczak@polsoft.pl) wrote:
>
> On Wed, May 28, 2003 at 02:21:13PM +0100, Rory Campbell-Lange wrote:
> > As far as the function goes, I now know the difference between ELSIFs
> > and ELSE IFs. The function is working now. Still interested in how to
> > find the bug line using vim though.
>
> I think vim works ok. When pointed on the line with beginning of
> function body (the starting ') I pressed 92j and vim went to the
> exact  error  line  (the one  with  ELSE IF ...  where  postgres
> expected END IF instead). Does your vim work differently?

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>