Thread: Probs "compiling" a function

Probs "compiling" a function

From
gordoncl@optusnet.com.au
Date:
Greetings, Having probs just "compiling" this fn and it comes back with a very
stark error msg which is below. I'm using version 7.1.3

I run the script as:
\i procedures/zff

and get the error msg:
psql:procedures/zff:51: ERROR:  parser: parse error at or near "b"

Line 51 is the line that starts LANGUAGE....

And the script is:
CREATE FUNCTION bsub_insert(varchar,varchar,varchar,varchar,varchar,
varchar,varchar,varchar,varchar,varchar,bool,date,int2,int4,varchar,int4)
RETURNS integer AS '
DECLARE   tmp_bsub    RECORD;   t_sub_id2    INTEGER;   t_class_sun INTEGER;   p_band_nm    ALIAS FOR $1;
BEGIN   IF p_band_nm IS NULL THEN       RETURN 0;   END IF;   IF $3 IS NULL THEN       RETURN 0;   END IF;   IF $4 IS
NULLTHEN       RETURN 0;   END IF;   IF $11 IS NULL THEN       RETURN 0;   END IF;   IF $12 IS NULL THEN       RETURN
0;  END IF;   IF $15 IS NULL THEN       RETURN 0;   END IF;
 
   SELECT INTO tmp_bsub *   FROM sub_class   WHERE sub_class.class_ds = upper($15);   IF NOT FOUND THEN       RETURN
-1;  END IF;   t_class_sun := tmp_bsub.class_sun;
 
   INSERT INTO sub(band_commnty_nm,street_no,first_nm,surname,home_phone_no,
work_phone_no,mobile_phone_no,email_tx,web_addr_tx,sub_comment,
voluntr_fl,sub_expiry_dt,card_cnt,receipt_no,sub_type_ind,    class_sun,street_sun)
VALUES(upper($1),$2,upper($3),upper($4),$5,$6,$7,$8,$9,upper($10),$11,    $12,$13,$14,/'B/',t_class_sun,$16);
 
   GET DIAGNOSTICS t_sub_id2 = RESULT_OID;   SELECT INTO tmp_bsub *   FROM sub   WHERE oid = t_sub_id2;
   RETURN tmp_bsub.sub_no;
END; '
LANGUAGE 'plpgsql';

Thanks for any help as this is driving me made and I've rewritten it checked all
the b's and compared it to other fns I've written and nothing I'm using here I
haven't already used.

Cheers...Gordon


Re: Probs "compiling" a function

From
Richard Huxton
Date:
On Tuesday 08 Oct 2002 3:14 am, gordoncl@optusnet.com.au wrote:
> Greetings, Having probs just "compiling" this fn and it comes back with a
> very stark error msg which is below. I'm using version 7.1.3
>
> I run the script as:
> \i procedures/zff
>
> and get the error msg:
> psql:procedures/zff:51: ERROR:  parser: parse error at or near "b"
>
> Line 51 is the line that starts LANGUAGE....
...
>     VALUES(upper($1),$2,upper($3),upper($4),$5,$6,$7,$8,$9,upper($10),$11,
>       $12,$13,$14,/'B/',t_class_sun,$16);
I think the 'B' here might be the problem - did you mean \'B\' or ''B''?

It probably doesn't spot it until the end of the definition when it hits
another quote on the "language" line.

- Richard Huxton