[Please don't post in HTML.]
On Wed, Jan 19, 2005 at 04:45:14PM +1100, Jamie Deppeler wrote:
> What i am trying to do is to update a field based on a sql query
> set notes='hello' is just being used as a test but i can not seem
> to make this simple update work
Do you want to modify a column in the row being inserted or updated,
or do you want to update other rows in a table? If you want to
modify the row being updated, use a BEFORE trigger, assign a value
to NEW.column_name, and return NEW. Example:
CREATE TABLE foo (
id serial PRIMARY KEY,
name text NOT NULL,
notes text
);
CREATE FUNCTION set_notes() RETURNS trigger AS '
BEGIN
NEW.notes := ''hello'';
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo
FOR EACH ROW EXECUTE PROCEDURE set_notes();
INSERT INTO foo (name) VALUES ('Jamie');
SELECT * FROM foo;
id | name | notes
----+-------+-------
1 | Jamie | hello
(1 row)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/