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

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'