Re: Finding line of bug in sql function

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