(Fwd) Re: [LIP] PostgreSQL stored Procedure

Hi all,

I had this with me to submit as a bug report for a misleading error message. Is 
this the place I send this or I need to send it to someplace else?

Shridhar

------- Forwarded message follows -------
Date sent:          Thu, 26 Dec 2002 12:39:59 +0530
From:               Rajesh Fowkar <Mail@dempos.com>
To:                 Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Subject:            Re: [LIP] PostgreSQL stored Procedure
Organization:       V. S. Dempo & Co. Pvt. Ltd.  <http://www.dempos.com>

On Thu, Dec 26, 2002 at 11:51:10AM +0530, Shridhar Daithankar wrote:

>On 26 Dec 2002 at 10:05, Rajesh Fowkar wrote:
>
>
>I am on n number of postgresql lists. If you send me complete description of 
>problem and how you fixed it, it could be forwarded for some better 
resolution.

What happened was, in my stored procedure there was a varchar parameter
passed. Sorry for the long lines. Just pasted the function below.

------------------------------------------------------------------------
CREATE FUNCTION 
grn_actions(varchar,char,char,integer,integer,char,char,date,char,char,char,char
,varchar,date,varchar,varchar)
returns char as '
DECLARE   vdocument_type               alias for $1;
------------------------------------------------------------------------

Now below there is a line to compare this vdocument_type

------------------------------------------------------------------------
if ( (vdocument_type = ''PO'') or (vdocument_type = ''LOCAL'') )  then    insert into goods_receipt_note values(
       cyear_id,cgrn_type,new_transaction_id,dgrn_date,                cpurchase_order_year_id,cpurchase_order_type,
           cpurchase_order_number,csupplier_code,vbill_number,dbill_date,                
 
vchallan_number,dchallan_date,vtransport,nfreight,vremark,false);
end if;
------------------------------------------------------------------------

In the above code paranthesis for 'if' statement are correct. However
when the first parathesis was missing and there was a mismatch of
parenthesis postgres gave me the following error :

------------------------------------------------------------------------
Dec 24 11:15:54 woody postgres[2324]: [2-1] ERROR:  Unable to identify
an operator '=' for types 'character varying' and 'boolean'
Dec 24 11:15:54 woody postgres[2324]: [2-2] You will have to retype
this query using an explicit cast
Dec 24 11:15:54 woody postgres[2324]: [3] NOTICE:  Error occurred while
executing PL/pgSQL function grn_actions
------------------------------------------------------------------------

Now due to the above error my full concentration went on converting
either of the two values to the same data type but no sucesss, thinking
that varchar cannot be compared with a string value in Postgres which
was wrong. 

However after putting the first opening bracket to 'if' all is good.

Don't you think the above error message is misleading. The error message
returning mechanism as far as PL/pgSQL is concerned is not that good as
yet.