Received: from mail.opentec.com.au ([203.5.51.3]) by hub.org (8.10.1/8.10.1) with ESMTP id e671ZbW07335 for ; Thu, 6 Jul 2000 21:35:38 -0400 (EDT) Received: from opentec.com.au (ochre) by mail.opentec.com.au (Sun Internet Mail Server sims.3.5.1999.07.30.00.05.p8) with ESMTP id <0FXB005020NVIH@mail.opentec.com.au> for pgsql-sql@postgresql.org; Fri, 7 Jul 2000 11:40:43 +1000 (EST) Date: Fri, 07 Jul 2000 11:44:15 +1000 From: Paul McGarry Subject: MAX() of 0 records. To: pgsql-sql@postgresql.org Message-id: <396535EF.8132CF80@opentec.com.au> MIME-version: 1.0 X-Mailer: Mozilla 4.61 [en] (Win98; I) Content-type: text/plain; charset=us-ascii Content-transfer-encoding: 7bit X-Accept-Language: en X-Archive-Number: 200007/70 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