Hi all,
I have a slight problem using triggers and functions in PostGreSQL. I'm
currently running PostGreSQL 7.2.1 on a RedHat 7.2. I've compiled the
sources (no rpm installation).
I have several different tables and in each table there's a status flag
that's telling if a row is deleted or not (I don't actually want to delete
the rows).
Here are my tables (simplified).
Site:
id serial
name char(120)
status int
Page:
id serial
name char(120)
status int
site_id int
Text:
id serial
name char(120)
status int
page_id int
Now to my problem. What I want to do is if I change status in site or page
the child items to them should also change. No problem I thought, I'll solve
this with a trigger and a function. Did my first test like this:
-------
create function fix_status() returns opaque
as '
BEGIN
IF old.status <> new.status THENupdate text set status = new.status
where page_id = new.id;
END IF;
RETURN new;
END;
'
language 'plpgsql';
CREATE TRIGGER page_status AFTER UPDATE ON pages FOR EACH ROW EXECUTE PROCEDURE fix_status();
-------
Works fine. Now I'd like to add the same thing for the site table. No
worries I thought but I don't wont to duplicate the fix_status function so
I'll make it take a parameter.
-------
create function fix_status(char, char) returns opaque
as '
BEGIN
IF old.status <> new.status THENupdate $1 set status = new.status where $2 = new.id;
END IF;
RETURN new;
END;
'
language 'plpgsql';
CREATE TRIGGER page_status AFTER UPDATE ON pages FOR EACH ROW EXECUTE PROCEDURE fix_status('text','page_id');
-------
Then I get the following error:
ERROR: CreateTrigger: function fix_status() does not exist
Why doesn't the trigger acknowledge that I want to call fix_status with a
parameter for which table name I should use?
I'm completely stuck and I someone out there can help me.
-> Andreas
