From: Werner Fraga <werner.fraga@computalog.com>
To: pgsql-sql@postgresql.org
Subject: problems creating functions, triggers, and rules
Date: Mon, 10 Jan 2000 13:54:04 -0700
Message-ID: <00011014302300.07948@shorty.edmonton.computalog.com> (raw)
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...)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: werner.fraga@computalog.com
Subject: Re: problems creating functions, triggers, and rules
In-Reply-To: <00011014302300.07948@shorty.edmonton.computalog.com>
References: <00011014302300.07948@shorty.edmonton.computalog.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox