From: Andreas Johansson <andreas.johansson@cention.se>
To: pgsql-sql@postgresql.org
Subject: Trigger/Function problem
Date: Wed, 21 Aug 2002 09:34:20 +0200
Message-ID: <CGEMKBAODBHMBJBDNABNOENOCDAA.andreas.johansson@cention.se> (raw)
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 THEN
update 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 THEN
update $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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas.johansson@cention.se
Subject: Re: Trigger/Function problem
In-Reply-To: <CGEMKBAODBHMBJBDNABNOENOCDAA.andreas.johansson@cention.se>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox