I am running postgresql 7.4.2 and having problems creating a trigger
function properly. I keep getting the following error:

ERROR: OLD used in query that is not in rule

I have a table called journal_entries with a foreign key to a table
called journals. When a entry is added to journal_entries, I am trying
to get it to update the 'mtime' field of the corresponding entry in the
journals table.

Can anyone help me with this problem? If you need more information,
please let me know.

CREATE OR REPLACE FUNCTION public.update_journal_mtime()
RETURNS trigger AS
'
DECLARE
curtime TIMESTAMP;
BEGIN
curtime := \'now\';
IF OLD IS NOT NULL THEN
UPDATE journals SET mtime = curtime WHERE id = OLD.journ_id;
END IF;
IF NEW IS NOT NULL THEN
UPDATE journals SET mtime = curtime WHERE id = NEW.journ_id;
UPDATE journal_entries SET mtime = curtime WHERE id = NEW.id;
END IF;
RETURN null;
END;
'
LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER update_mtime_trigger
AFTER INSERT OR UPDATE OR DELETE
ON public.journal_entries
FOR EACH ROW
EXECUTE PROCEDURE public.update_journal_mtime();

Ok, I have determined that i can't do:
IF NEW IS NOT NULL -- or -- IF OLD IS NOT NULL
and that is what is causing the error.

So, with this now known, is there a way to create a trigger & function
that will allow inserts, updates, and deletes to occur while updating a
field (mtime) in another table? At the same time, if an update takes
place, it updates the modified time field (mtime) to the current time.

I can them all to work individually by modifying the function, but I
cant get the to all work in the same function. Do I need to create a
trigger for inserts/updates and another for deletes?

Any help is greatly appreciated. Thanks in advance.

If it helps, here are the table definitions:

CREATE TABLE journals (
id int NOT NULL DEFAULT nextval('journal_id_seq'::text),
owner_id int NOT NULL,
name varchar(15) NOT NULL,
descr varchar(50) NOT NULL,
ctime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6),
mtime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6),
CONSTRAINT journals_pkey PRIMARY KEY (id),
CONSTRAINT fkey_user_id FOREIGN KEY (owner_id) REFERENCES users
(user_id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT journal_descr CHECK descr::text <> ''::text,
CONSTRAINT journal_name CHECK name::text <> ''::text
);

CREATE TABLE journal_entries
(
id int NOT NULL DEFAULT nextval('journal_ent_id_seq'::text),
journ_id int NOT NULL,
entry varchar(1000) NOT NULL,
ctime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6),
mtime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6),
CONSTRAINT journal_entries_pkey PRIMARY KEY (id),
CONSTRAINT fkey_journal_id FOREIGN KEY (journ_id) REFERENCES journals
(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT entry_check CHECK entry::text <> ''::text
);


Peter Erickson wrote:[color=blue]
> I am running postgresql 7.4.2 and having problems creating a trigger
> function properly. I keep getting the following error:
>
> ERROR: OLD used in query that is not in rule
>
> I have a table called journal_entries with a foreign key to a table
> called journals. When a entry is added to journal_entries, I am trying
> to get it to update the 'mtime' field of the corresponding entry in the
> journals table.
>
> Can anyone help me with this problem? If you need more information,
> please let me know.
>
> CREATE OR REPLACE FUNCTION public.update_journal_mtime()
> RETURNS trigger AS
> '
> DECLARE
> curtime TIMESTAMP;
> BEGIN
> curtime := \'now\';
> IF OLD IS NOT NULL THEN
> UPDATE journals SET mtime = curtime WHERE id = OLD.journ_id;
> END IF;
> IF NEW IS NOT NULL THEN
> UPDATE journals SET mtime = curtime WHERE id = NEW.journ_id;
> UPDATE journal_entries SET mtime = curtime WHERE id = NEW.id;
> END IF;
> RETURN null;
> END;
> '
> LANGUAGE 'plpgsql' VOLATILE;
>
> CREATE TRIGGER update_mtime_trigger
> AFTER INSERT OR UPDATE OR DELETE
> ON public.journal_entries
> FOR EACH ROW
> EXECUTE PROCEDURE public.update_journal_mtime();[/color]

On Sunday 04 April 2004 20:40, Peter Erickson wrote:[color=blue]
> Ok, I have determined that i can't do:
> IF NEW IS NOT NULL -- or -- IF OLD IS NOT NULL
> and that is what is causing the error.
>
> So, with this now known, is there a way to create a trigger & function
> that will allow inserts, updates, and deletes to occur while updating a
> field (mtime) in another table? At the same time, if an update takes
> place, it updates the modified time field (mtime) to the current time.
>
> I can them all to work individually by modifying the function, but I
> cant get the to all work in the same function. Do I need to create a
> trigger for inserts/updates and another for deletes?[/color]

In plpgsql, there are a number of special TG_XXX variables defined. You want
to look at TG_OP. See the manuals for details.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Richard Huxton wrote:[color=blue]
> On Sunday 04 April 2004 20:40, Peter Erickson wrote:
>[color=green]
>>Ok, I have determined that i can't do:
>> IF NEW IS NOT NULL -- or -- IF OLD IS NOT NULL
>>and that is what is causing the error.
>>
>>So, with this now known, is there a way to create a trigger & function
>>that will allow inserts, updates, and deletes to occur while updating a
>>field (mtime) in another table? At the same time, if an update takes
>>place, it updates the modified time field (mtime) to the current time.
>>
>>I can them all to work individually by modifying the function, but I
>>cant get the to all work in the same function. Do I need to create a
>>trigger for inserts/updates and another for deletes?[/color]
>
>
> In plpgsql, there are a number of special TG_XXX variables defined. You want
> to look at TG_OP. See the manuals for details.
>[/color]

Thanks. Worked like a charm. I must have glossed over the variables
other than NEW and OLD.

Thanks again.

You want the TG_OP variable. For example:

IF TG_OP = 'INSERT' THEN
.. code here ..
ELSIF TG_OP = 'UPDATE' THEN
.. code here ..
ELSIF TG_OP = 'DELETE' THEN
.. code here ..
END IF;

Greg

Peter Erickson wrote:
[color=blue]
> Ok, I have determined that i can't do:
> IF NEW IS NOT NULL -- or -- IF OLD IS NOT NULL
> and that is what is causing the error.
>
> So, with this now known, is there a way to create a trigger & function
> that will allow inserts, updates, and deletes to occur while updating a
> field (mtime) in another table? At the same time, if an update takes
> place, it updates the modified time field (mtime) to the current time.
>
> I can them all to work individually by modifying the function, but I
> cant get the to all work in the same function. Do I need to create a
> trigger for inserts/updates and another for deletes?
>
> Any help is greatly appreciated. Thanks in advance.
>
> If it helps, here are the table definitions:
>
> CREATE TABLE journals (
> id int NOT NULL DEFAULT nextval('journal_id_seq'::text),
> owner_id int NOT NULL,
> name varchar(15) NOT NULL,
> descr varchar(50) NOT NULL,
> ctime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6),
> mtime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6),
> CONSTRAINT journals_pkey PRIMARY KEY (id),
> CONSTRAINT fkey_user_id FOREIGN KEY (owner_id) REFERENCES users
> (user_id) ON UPDATE CASCADE ON DELETE CASCADE,
> CONSTRAINT journal_descr CHECK descr::text <> ''::text,
> CONSTRAINT journal_name CHECK name::text <> ''::text
> );
>
> CREATE TABLE journal_entries
> (
> id int NOT NULL DEFAULT nextval('journal_ent_id_seq'::text),
> journ_id int NOT NULL,
> entry varchar(1000) NOT NULL,
> ctime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6),
> mtime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6),
> CONSTRAINT journal_entries_pkey PRIMARY KEY (id),
> CONSTRAINT fkey_journal_id FOREIGN KEY (journ_id) REFERENCES journals
> (id) ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT entry_check CHECK entry::text <> ''::text
> );
>
>
> Peter Erickson wrote:
>[color=green]
>> I am running postgresql 7.4.2 and having problems creating a trigger
>> function properly. I keep getting the following error:
>>
>> ERROR: OLD used in query that is not in rule
>>
>> I have a table called journal_entries with a foreign key to a table
>> called journals. When a entry is added to journal_entries, I am trying
>> to get it to update the 'mtime' field of the corresponding entry in
>> the journals table.
>>
>> Can anyone help me with this problem? If you need more information,
>> please let me know.
>>
>> CREATE OR REPLACE FUNCTION public.update_journal_mtime()
>> RETURNS trigger AS
>> '
>> DECLARE
>> curtime TIMESTAMP;
>> BEGIN
>> curtime := \'now\';
>> IF OLD IS NOT NULL THEN
>> UPDATE journals SET mtime = curtime WHERE id = OLD.journ_id;
>> END IF;
>> IF NEW IS NOT NULL THEN
>> UPDATE journals SET mtime = curtime WHERE id = NEW.journ_id;
>> UPDATE journal_entries SET mtime = curtime WHERE id = NEW.id;
>> END IF;
>> RETURN null;
>> END;
>> '
>> LANGUAGE 'plpgsql' VOLATILE;
>>
>> CREATE TRIGGER update_mtime_trigger
>> AFTER INSERT OR UPDATE OR DELETE
>> ON public.journal_entries
>> FOR EACH ROW
>> EXECUTE PROCEDURE public.update_journal_mtime();[/color]
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings[/color]

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

You've already received some help in later messages.
See below for a couple of additional comments.

--- Peter Erickson <news@redlamb.net> wrote:[color=blue]
> I am running postgresql 7.4.2 and having problems
> creating a trigger
> function properly. I keep getting the following
> error:
>
> ERROR: OLD used in query that is not in rule
>
> I have a table called journal_entries with a foreign
> key to a table
> called journals. When a entry is added to
> journal_entries, I am trying
> to get it to update the 'mtime' field of the
> corresponding entry in the
> journals table.
>
> Can anyone help me with this problem? If you need
> more information,
> please let me know.
>
> CREATE OR REPLACE FUNCTION
> public.update_journal_mtime()
> RETURNS trigger AS
> '
> DECLARE
> curtime TIMESTAMP;
> BEGIN
> curtime := \'now\';[/color]

You may just prefer to use CURRENT_TIMESTAMP, which is
the SQL-standard built in variable which gives you the
same thing.
[color=blue]
> IF OLD IS NOT NULL THEN
> UPDATE journals SET mtime = curtime WHERE id
> = OLD.journ_id;
> END IF;
> IF NEW IS NOT NULL THEN
> UPDATE journals SET mtime = curtime WHERE id
> = NEW.journ_id;
> UPDATE journal_entries SET mtime = curtime
> WHERE id = NEW.id;[/color]

This will get you an infinite loop, because you are
recursively calling this trigger function. Just
assign directly, i.e. NEW.mtime := curtime .

Note that the assignment operator is supposed to be
":=", not "=", which is a test of equality. But the
two ended up equivalent by mistake. Somebody might
fix that one day...
[color=blue]
> END IF;
> RETURN null;[/color]

If you return "null" from a trigger function, the
operation will be aborted. You will need to return
"NEW" or "OLD" as appropriate (hmm, I wonder if
returning "NEW" from a delete operation would cause an
error? I haven't tried it).

[color=blue]
> END;
> '
> LANGUAGE 'plpgsql' VOLATILE;
>
> CREATE TRIGGER update_mtime_trigger
> AFTER INSERT OR UPDATE OR DELETE
> ON public.journal_entries
> FOR EACH ROW
> EXECUTE PROCEDURE public.update_journal_mtime();
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend[/color]


__________________________________
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway
http://promotions.yahoo.com/design_giveaway/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match