Re: cast needed - but where and why?

Tom Lane wrote:
> Steve Clark <sclark@netwolves.com> writes:
>> I am getting the following error after upgrading from 7.4.6 to 8.3.6
>> and can't figure out what is wrong. Any help would be greatly appreciated.
>> 2009-04-02 10:45:10 EDT:srm2api:ERROR:  column "event_ref_log_no" is of type integer but expression is of type text
atcharacter 146 
>
> I don't know ecpg very well, but if it doesn't provide any information
> about parameter datatypes then the backend would resolve this:
>
>     case  when  $7  > 0 then  $8  else null end
>
> as producing a result of type "text".  7.4 would have allowed that to be
> cast to int silently, but 8.3 won't (and the runtime cast involved
> would've been expensive anyway).  I suggest sticking a cast directly
> on the ambiguous parameter, ie
>
>>             case     when :h_event_ref_log_no > 0
>>                     then :h_event_ref_log_no :: integer
>>                     else null end,
>
> (You needn't cast the null, since the type attached to the other case
> arm is a sufficient cue.)
>
>             regards, tom lane
>
Thanks Tom,

that fixed the problem. I wasn't thinking about what the back end
was seeing, only that it was defined in my pgc program as an int.

Regards,
Steve