Обсуждение: cast needed - but where and why?

Hello list,

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.

from our program:
sqlcode=-400 errmsg='column "event_ref_log_no" is of type integer but expression is of type text' in line 4138.

from pg_log:
2009-04-02 10:45:10 EDT:srm2api:ERROR:  column "event_ref_log_no" is of type integer but expression is of type text at
character146 
2009-04-02 10:45:10 EDT:srm2api:HINT:  You will need to rewrite or cast the expression.
2009-04-02 10:45:10 EDT:srm2api:STATEMENT:  insert into t_unit_event_log ( event_log_no  , unit_serial_no  , event_type
, event_category  , event_mesg  , event_severity  , event_status  , event_ref_log_no  , event_logged_by  , event_date
,
alarm  , last_updated_by  , last_updated_date  ) values ( nextval ( 'seq_event_log_no' ) ,  $1  ,  $2  ,  $3  ,  $4  ,
$
5  ,  $6  , case  when  $7  > 0 then  $8  else null end , current_user , now () ,  $9  , current_user , now () )

from our program:
    exec sql begin declare section;
        int     h_cnt                = 0;
    int    h_event_ref_log_no     = NULL;
...
    // insert into uel
    exec sql insert into t_unit_event_log
           (event_log_no,
            unit_serial_no,
            event_type,
            event_category,
            event_mesg,
            event_severity,
            event_status,
            event_ref_log_no,
            event_logged_by,
            event_date,
            alarm,
            last_updated_by,
            last_updated_date)
    values (nextval('seq_event_log_no'),
            :h_serial_no,
            :h_type,
            :h_category,
            :h_mesg,
            :h_sev,
            :h_status,
            case     when :h_event_ref_log_no > 0
                    then :h_event_ref_log_no
                    else null end,
            current_user,
            now(),
            :h_alarm,
            current_user,
            now());

Thanks,
Steve

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

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