Обсуждение: Some unknown error in a function

Поиск
Список
Период
Сортировка

Some unknown error in a function

От
"Jasbinder Singh Bali"
Дата:
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,'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

Re: Some unknown error in a function

От
Tom Lane
Дата:
"Jasbinder Singh Bali" <jsbali@gmail.com> writes:
> I run this function using
> select sp_insert_tbl_vrfy_mx(55,'jas','xyz.com','192.168.0.105', '
> 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

You didn't show us the actual error message, but I suppose it's unhappy
that ' ' is not a legal inet value.

            regards, tom lane

Re: Some unknown error in a function

От
"Chandra Sekhar Surapaneni"
Дата:

“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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jasbinder Singh Bali
Sent: Friday, February 09, 2007 11:30 AM
To: pgsql-general@postgresql.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,'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