Re: [SQL] Are int4/int8 ops defined?

Jose,

(Hope I got your name right;-) It works by following your advise.
So it was a parser bug?

Thank you for the help!

Min

In message <3770E7AB(dot)B31B6F77(at)sferacarta(dot)com>, =?iso-8859-1?Q?Jos=E9?= Soares w
rites:
>
>
>Min Qiu ha scritto:
>
>> Hi,
>>
>> I'm new to postgresql and learning writing the trigger.
>> Included below was one of the execise I did. It kept
>> producing an error message that suggested the operater '-'
>> was undefined for int8/int4. Since both int8 and int4
>> are the basic types, I believed I might miss something.
>>
>> Appology for the long post and thanks for any advise,
>>
>> Min
>>
>> DROP trigger t_diff on tb_cnt32;
>> DROP function f_diff();
>> DROP TABLE tb_cnt32;
>>
>> create table tb_cnt32 (t int8, s int8, v int8);
>> insert into tb_cnt32 values (0, 0, 0);
>>
>> --
>> -- diff function
>> --
>> create function f_diff() returns opaque as '
>> declare
>> prev record;
>> begin
>> select into prev * from tb_cnt32 where t=(select max(t) from tb_cnt32);
>> -- can not do
>> -- select into prev * from tb_cnt32 where t=max(t);
>> -- ^^^^^
>> if not found then
>> raise exception ''Empty table tb_cnt32???'';
>> end if;
>>
>> prev.v=new.v-prev.v;
>> update tb_cnt32 set v=prev.v where t=prev.t;
>>
>> return new;
>> end;
>> ' language 'plpgsql';
>>
>> --
>> -- t_diff trigger
>> --
>> create trigger t_diff
>> before insert on tb_cnt32
>> for each row
>> execute procedure
>> f_diff();
>>
>> insert into tb_cnt32 values (1000, 5, 10);
>> ERROR: There is no operator '-$' for types 'int8' and 'int4'
>> You will either have to retype this query using an explicit cast,
>> or you will have to define the operator using CREATE OPERATOR
>>
>
>May be this should be avoid by separate sign minus (-) with spaces.
>eg: prev.v = new.v - prev.v;
>
>>
>> Following the hint of the error message, I trid casting prev.v::int8,
>> new.v::int4 and prev.v::int4. But the results were the same.
>>
>> I'm using v6.5 on Linux 2.0.35 with gcc version egcs-2.91.66.
>> psql \df listed:
>> ...
>> int8 |int8div |int8 int8 |divide
>> int8 |int8larger |int8 int8 |larger of two
>> int8 |int8mi |int8 int8 |subtraction
>> int8 |int8mul |int8 int8 |multiply
>> int8 |int8pl |int8 int8 |addition
>> int8 |int8smaller |int8 int8 |smaller of two
>> int8 |int8um |int8 |unary minus
>> ...
>
>______________________________________________________________
>PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
>^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>Jose'
>
>