Re: [SQL] Are int4/int8 ops defined? - Mailing list pgsql-sql
From | José Soares |
---|---|
Subject | Re: [SQL] Are int4/int8 ops defined? |
Date | |
Msg-id | 3770E7AB.B31B6F77@sferacarta.com Whole thread Raw |
In response to | Are int4/int8 ops defined? (Min Qiu <minq@UU.NET>) |
List | pgsql-sql |
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'