Re: [SQL] Are int4/int8 ops defined? - Mailing list pgsql-sql

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.B31B6F77@sferacarta.com>, José 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'
>
>