>From http://www.postgresql.org/docs/8.1/interactive/triggers.html: The trigger function must be defined before the trigger itself can be created. The trigger function must be declared as a function taking no arguments and returning type trigger. (The trigger function receives its input through a specially-passed TriggerData structure, not in the form of ordinary function arguments.)
On Thu, Dec 01, 2005 at 10:49:49PM +0000, Jozef Behran wrote: > > The following bug has been logged online: > > Bug reference: 2087 > Logged by: Jozef Behran > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.1.0 > Operating system: Short answer: Mandrake 10.2. Long answer: Linux sarkan > 2.6.11-6mdk #1 Tue Mar 22 16:04:32 CET 2005 i686 AMD Athlon(tm) Processor > unknown GNU/Linux > Description: Bogus error message on CREATE TRIGGER with a SQL > function > Details: > > Here is a sample shell session showing the SQL file and > the output: > > $ cat bug.sql > \set VERBOSITY verbose > > BEGIN; > > CREATE table pokus ( > id INT4, > sval VARCHAR(100), > sortsval VARCHAR(100), > PRIMARY KEY(id) > ); > > CREATE FUNCTION tpokus(pokus) RETURNS pokus as $$ > SELECT $1.id,$1.sval,translate($1.sval,'abc','ABC'); > $$ LANGUAGE SQL IMMUTABLE; > > INSERT INTO pokus (id,sval) values (1,'aha'); > > SELECT tpokus(pokus.*) FROM pokus; > > CREATE TRIGGER trigger_pokus > BEFORE INSERT OR UPDATE > ON pokus FOR EACH ROW > EXECUTE PROCEDURE tpokus() > ; > > ROLLBACK; > $ psql -f bug.sql > BEGIN > psql:bug.sql:8: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit > index "pokus_pkey" for table "pokus" > CREATE TABLE > CREATE FUNCTION > INSERT 0 1 > tpokus > ------------- > (1,aha,AhA) > (1 row) > > psql:bug.sql:22: ERROR: function tpokus() does not exist > LOCATION: LookupFuncName, parse_func.c:1192 > ROLLBACK > $ _ > > The buggy error message is the line before `ROLLBACK' (the one saying that > `tpokus' does not exist). The problem is that there *is* a function named > `tpokus' (the previous SELECT executed the function without problems). If it > is impossible to use a SQL function in a trigger, the error message should > say so (for example ERROR: cannot use SQL function `name' in a trigger). If > it is possible to have SQL-only triggers but the code is wrong, you should > add a complete example of a SQL trigger into the documentation to show all > the nasty quirks that may get into the way (for example the documentation > says that the trigger must return `trigger' but it is impossible to declare > such a SQL function). > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
