Received: from trends.net (root@clio.trends.ca [209.47.148.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA02389 for ; Wed, 23 Jun 1999 10:36:45 -0400 (EDT) (envelope-from jose@sferacarta.com) Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by trends.net (8.8.8/8.8.8) with ESMTP id KAA25694 for ; Wed, 23 Jun 1999 10:04:59 -0400 (EDT) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.8/NETTuno 3.2) with ESMTP id QAA20106; Wed, 23 Jun 1999 16:02:11 +0200 (MDT) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 10wpRz-0002io-00; Wed, 23 Jun 1999 16:00:35 +0000 Message-ID: <3770E7AB.B31B6F77@sferacarta.com> Date: Wed, 23 Jun 1999 15:56:59 +0200 From: =?iso-8859-1?Q?Jos=E9?= Soares X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: Min Qiu CC: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Are int4/int8 ops defined? References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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'