Thread: update in triggers
Hi, Think im doing something wrong here, cant seem to resolve the problem i have a trigger which is calling a update function and when it gets to a update it goes into a infinite loop code Trigger CREATE TRIGGER "new_trigger" AFTER INSERT OR UPDATE ON "chargeratetest" FOR EACH ROW EXECUTE PROCEDURE "chargeratetest"(); function CREATE OR REPLACE FUNCTION "chargeratetest" () RETURNS trigger AS' begin UPDATE chargeratetest set notes=''hello'' where new."primary" = chargeratetest."primary"; return null; end; 'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER; would be greatful for any help thx
On Wed, Jan 19, 2005 at 03:45:53PM +1100, Jamie Deppeler wrote: > Think im doing something wrong here, cant seem to resolve the problem i > have a trigger which is calling a update function and when it gets to a > update it goes into a infinite loop recursion, noun. See recursion. > CREATE TRIGGER "new_trigger" AFTER INSERT OR UPDATE > ON "chargeratetest" FOR EACH ROW > EXECUTE PROCEDURE "chargeratetest"(); > > CREATE OR REPLACE FUNCTION "chargeratetest" () RETURNS trigger AS' > begin > > UPDATE chargeratetest > set notes=''hello'' > where new."primary" = chargeratetest."primary"; The trigger says to call the function after every insert or update on the table. Suppose you insert a record into the table. The trigger calls the function and the function executes UPDATE. The update causes the trigger to call the function, which executes UPDATE so the trigger calls the function, which executes UPDATE so the trigger calls the function, and so on. Infinite recursion. What are you trying to do? What's the trigger's purpose? > return null; > end; > 'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER; A function that has side effects like updating a table should be VOLATILE, not IMMUTABLE. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Isn't the syntax CREATE OR REPLACE FUNCTION chargeratetest() RETURNS "trigger" AS' ? On Wed, Jan 19, 2005 at 03:45:53PM +1100, Jamie Deppeler wrote: > Hi, > > Think im doing something wrong here, cant seem to resolve the problem i > have a trigger which is calling a update function and when it gets to a > update it goes into a infinite loop > > code > > Trigger > CREATE TRIGGER "new_trigger" AFTER INSERT OR UPDATE > ON "chargeratetest" FOR EACH ROW > EXECUTE PROCEDURE "chargeratetest"(); > > > function > > CREATE OR REPLACE FUNCTION "chargeratetest" () RETURNS trigger AS' > begin > > UPDATE chargeratetest > set notes=''hello'' > where new."primary" = chargeratetest."primary"; > > return null; > end; > 'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER; > > > would be greatful for any help thx > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
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
Michael Fuhr wrote:
On Wed, Jan 19, 2005 at 03:45:53PM +1100, Jamie Deppeler wrote:
Think im doing something wrong here, cant seem to resolve the problem i
have a trigger which is calling a update function and when it gets to a
update it goes into a infinite loop
recursion, noun. See recursion.
CREATE TRIGGER "new_trigger" AFTER INSERT OR UPDATE
ON "chargeratetest" FOR EACH ROW
EXECUTE PROCEDURE "chargeratetest"();
CREATE OR REPLACE FUNCTION "chargeratetest" () RETURNS trigger AS'
begin
UPDATE chargeratetest
set notes=''hello''
where new."primary" = chargeratetest."primary";
The trigger says to call the function after every insert or update
on the table. Suppose you insert a record into the table. The
trigger calls the function and the function executes UPDATE. The
update causes the trigger to call the function, which executes
UPDATE so the trigger calls the function, which executes UPDATE so
the trigger calls the function, and so on. Infinite recursion.
What are you trying to do? What's the trigger's purpose?
return null;
end;
'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
A function that has side effects like updating a table should be
VOLATILE, not IMMUTABLE.
[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/
On Tue, Jan 18, 2005 at 11:34:41PM -0600, Mike G. wrote: > Isn't the syntax CREATE OR REPLACE FUNCTION chargeratetest() RETURNS "trigger" AS' That's the same thing as > > CREATE OR REPLACE FUNCTION "chargeratetest" () RETURNS trigger AS' with different identifiers quoted. See the documentation for quoted identifiers: http://www.postgresql.org/docs/7.4/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Jamie Deppeler wrote:
> Trigger
> CREATE TRIGGER "new_trigger" AFTER INSERT OR UPDATE
> ON "chargeratetest" FOR EACH ROW
> EXECUTE PROCEDURE "chargeratetest"();
>
>
> function
>
> CREATE OR REPLACE FUNCTION "chargeratetest" () RETURNS trigger AS'
> begin
>
> UPDATE chargeratetest
> set notes=''hello''
> where new."primary" = chargeratetest."primary";
>
> return null;
> end;
> 'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
If you're only going to modify the updated/inserted record, you should
definitely take a look at RULEs (Chapter 34). They RULE for this kind of
thing ;)
I haven't used them yet, as I only knew about triggers until recently,
but you could do something like this:
CREATE RULE new_rule AS ON UPDATE
TO chargeratetest
DO INSTEAD
UPDATE chargeratetest
SET notes = 'hello'
WHERE primary = NEW.primary;
Alban.