Re: Error Message - Mailing list pgsql-general
From | Bob Pawley |
---|---|
Subject | Re: Error Message |
Date | |
Msg-id | 001601c5daa0$d2e717f0$ac1d4318@OWNER Whole thread Raw |
In response to | Error Message (Bob Pawley <rjpawley@shaw.ca>) |
Responses |
Re: Error Message
|
List | pgsql-general |
I have a base table called "process". Each row of this table is anchored by a serial column labeled "fluid_id". After data has been entered into a row in "process", I want to trigger a row in another table labeled "specification" also with a column labeled "fluid_id". I would like this number from "process" entered into "specification" as an integer. I would like this to happen after each row in "process" has satisfied the not null requirements. I may not be employing the language you are use to using however, I hope this explanation is somewhat clear. Thanks for your help. Bob ----- Original Message ----- From: "Michael Fuhr" <mike@fuhr.org> To: "Terry Lee Tucker" <terry@esc1.com> Cc: "Postgre General" <pgsql-general@postgresql.org> Sent: Wednesday, October 26, 2005 6:00 PM Subject: Re: [GENERAL] Error Message > On Wed, Oct 26, 2005 at 07:45:19PM -0400, Terry Lee Tucker wrote: >> You cannot pass argments to trigger functions. You can to other types of >> functions, but not functions used as triggers. Arguments are passed >> regarding >> the old and new records and other built in variables regarding what kind >> of >> operation is going on, but all of that is "unseen". >> >> They must be created as in: >> CREATE TRIGGER trig1 AFTER INSERT >> ON process FOR EACH ROW >> EXECUTE PROCEDURE base(); >> ^^^^^^ >> Note: no argument. > > You *can* pass arguments to trigger functions but it's done a little > differently than with non-trigger functions. The function must be > defined to take no arguments; it reads the arguments from a context > structure instead of in the normal way. PL/pgSQL trigger functions, > for example, read their arguments from the TG_ARGV array. > > http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html > http://www.postgresql.org/docs/8.0/interactive/trigger-interface.html > > Example: > > CREATE TABLE foo (id integer, x integer); > > CREATE FUNCTION func() RETURNS trigger AS $$ > BEGIN > NEW.x := TG_ARGV[0]; > RETURN NEW; > END; > $$ LANGUAGE plpgsql; > > CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo > FOR EACH ROW EXECUTE PROCEDURE func(12345); > > INSERT INTO foo (id) VALUES (1); > > SELECT * FROM foo; > id | x > ----+------- > 1 | 12345 > (1 row) > > However, it's not clear if this is what Bob is trying to do. His > original attempt was: > >> CREATE TRIGGER trig1 AFTER INSERT >> ON process FOR EACH ROW >> EXECUTE PROCEDURE base(int4); > > He's given what looks like a function signature instead of passing > an argument. Even if this worked, he hasn't specified what argument > should be passed. Bob, can you explain what you're trying to do? > > -- > Michael Fuhr > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org
pgsql-general by date: