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>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Importance: Normal

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