Received: from berith.cyberdeck.net (IDENT:root@berith5.cyberdeck.net [194.79.164.55]) by hub.org (8.10.1/8.10.1) with ESMTP id e678d8W40231 for ; Fri, 7 Jul 2000 04:39:08 -0400 (EDT) Received: from benoit.dagon.pdt (ALyon-102-2-86.abo.wanadoo.fr [193.251.90.86]) by berith.cyberdeck.net (8.9.3/Cyberdeck - Mail) with ESMTP id KAA25450; Fri, 7 Jul 2000 10:33:16 +0200 Received: from benoit.dagon.pdt (IDENT:benoit@localhost [127.0.0.1]) by benoit.dagon.pdt (8.9.3/8.9.3) with ESMTP id KAA23787; Fri, 7 Jul 2000 10:32:35 +0200 Message-Id: <200007070832.KAA23787@benoit.dagon.pdt> X-Mailer: exmh version 2.1.1 10/15/1999 To: Paul McGarry Cc: pgsql-sql@postgresql.org Subject: Re: MAX() of 0 records. In-Reply-To: Your message of "Fri, 07 Jul 2000 11:44:15 +1000." <396535EF.8132CF80@opentec.com.au> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Mime-Version: 1.0 Date: Fri, 07 Jul 2000 10:32:35 +0200 From: benoit@cyberdeck.net Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id e678d9W40293 X-Archive-Number: 200007/74 > Hello, > > I wish to perform the following query in a plsql function upon an > update/insert/delete trigger: > > UPDATE entry_stats > SET entry_maxprice=MAX(item_price) > FROM item > WHERE item.item_entry_id=NEW.item_entry_id > AND item.item_live = 't'; > > However there will be situations where there are no records for > a given item_entry_id with item_live='t'. Currently when I try > do update/insert a record so that this occurs I get the following > error 'ERROR: ExecutePlan: (junk) 'ctid' is NULL!' and the > insert/update attempt is rolled back. > > In this scenario I want entry_stats.entry_maxprice to be set to zero > (which is also the default value for that column if it's any help). > > Is there a good way of going about this or should I just be wrapping > the whole thing up in an > ==== > IF (COUNT(*) FROM item > WHERE item.item_entry_id=NEW.item_entry_id > AND item.item_live = 't')>0 > THEN > UPDATE ... =MAX() ... > ELSE > UPDATE SET ... =0 ... > END IF > ==== > ? > > Thanks > > -- > Paul McGarry mailto:paulm@opentec.com.au > Systems Integrator http://www.opentec.com.au > Opentec Pty Ltd http://www.iebusiness.com.au > 6 Lyon Park Road Phone: (02) 9878 1744 > North Ryde NSW 2113 Fax: (02) 9878 1755 I once had the same problem with an int4 column and solved it by using the function below : CREATE FUNCTION "nulliszero" (int4 ) RETURNS int4 AS 'SELECT 0 WHERE $1 IS NULL UNION SELECT $1 WHERE $1 IS NOT NULL' LANGUAGE 'SQL'; Your request then would look like : (...) SET entry_maxprice=nulliszero(MAX(item_price)); (...) -Benoit