Received: from computalog.com ([209.82.103.230]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA46932 for ; Mon, 10 Jan 2000 16:26:00 -0500 (EST) (envelope-from werner.fraga@computalog.com) Received: from tserver.edmonton.computalog.com (mug.computalog.com [209.82.103.229]) by computalog.com (8.8.7/8.8.7) with ESMTP id OAA02928 for ; Mon, 10 Jan 2000 14:39:44 -0700 Received: from shorty.edmonton.computalog.com (wfraga@shorty [10.89.4.26]) by tserver.edmonton.computalog.com (8.8.7/8.8.7) with SMTP id OAA01706 for ; Mon, 10 Jan 2000 14:26:39 -0700 From: Werner Fraga Reply-To: werner.fraga@computalog.com Organization: Computalog Ltd. To: pgsql-sql@postgresql.org Subject: problems creating functions, triggers, and rules Date: Mon, 10 Jan 2000 13:54:04 -0700 X-Mailer: KMail [version 1.0.28] Content-Type: text/plain MIME-Version: 1.0 Message-Id: <00011014302300.07948@shorty.edmonton.computalog.com> Content-Transfer-Encoding: 8bit I am having problems creating functions, triggers and rules in Postgres 6.5.3 on Linux Mandrake 6.1. They may all be related: 1. I noticed that some examples show the LANGUAGE in the CREATE FUNCTION command can be 'plpgsql'. However, if I try using this instead of 'sql', I get ERROR: Unrecognized language specified in a CREATE FUNCTION ... However, I read somewhere that triggers don't work with LANGUAGE 'sql'. Am I missing some part of Postgres installation? 2. If I create a function successfully (with LANGUAGE 'sql', then try to create a trigger using this function, I get create function checkStationID (int4) returns int4 as 'select Station.StationID from Station where Station.StationID = $1;' language 'SQL'; CREATE create trigger ifStationExists BEFORE INSERT OR UPDATE ON Employee FOR EACH ROW EXECUTE PROCEDURE checkStationID (stationID); ERROR: CreateTrigger: function checkstationid() does not exist 3. If I create an insert rule using create rule createStationAfterEmployeeInsert as on insert to Employee where new.StationID = 8 do insert into Station (StationID, StationName) values (new.StationID, 'RefIntegrity'); the WHERE clause is ignored, and the rule is applied to all INSERTs. I also tried 'Employee' instead of 'new'. 4. Whereas if I create an update rule using create rule createStationAfterEmployeeUpdate as on update to Employee where old.stationID=8 do insert into Station (StationID, StationName) values (new.StationID, 'RefIntegrity'); the rule is never applied. I also tried 'Employee' instead of 'old'. I tried 'current' instead of old, but this is not supported. 5. Is there anywhere I can find a list of the system tables? (that section in Bruce's book is not ready yet...)