Re: Some unknown error in a function

"IF $4 = ' ' THEN"

Here ' ' is not a valid inet value. If you really want to check to see
if the inet value is null, you can cast it to text and compare it.

For example: "IF text('$4') = '' Then"

That will fix your issue.

-Chandra Sekhar Surapaneni

________________________________

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Jasbinder Singh
Bali
Sent: Friday, February 09, 2007 11:30 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Some unknown error in a function

Hi
Following is the script of my plpgsql function

CREATE OR REPLACE FUNCTION sp_insert_tbl_vrfy_mx(int4,text, text, inet,
text, text)
RETURNS void AS $$
DECLARE
sequence_no int4;
BEGIN
SELECT INTO sequence_no MAX(seq_no) FROM tbl_verify_mx WHERE
unmask_id = $1;

IF sequence_no > 1 THEN
sequence_no = sequence_no + 1;
ELSE
sequence_no = 1;
END IF;

IF $4 =' ' THEN
INSERT INTO tbl_verify_mx(unmask_id, seq_no, email_local,
email_domain, mail_server, mx_records )
VALUES ($1,sequence_no,$2,$3,$5,$6) ;
ELSE
INSERT INTO tbl_verify_mx(unmask_id, seq_no, email_local,
email_domain, ip_address, mail_server, mx_records )
VALUES ($1,sequence_no,$2,$3,CAST($4 as inet), $5,$6) ;
END IF;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;

I run this function using
select sp_insert_tbl_vrfy_mx(55,'jas','xyz.com','192.168.0.105', '
mail.xyz.com <http://mail.xyz.com> ,'mxrecoredmxjdlkfjdk')
and get the following error:-

CONTEXT: SQL statement "SELECT $1 =' '"
PL/pgSQL function "sp_insert_tbl_vrfy_mx" line 12 at if

Don't know where I'm going wrong.
Thanks
Jas