Re: Trigger/Function problem

On Wed, 21 Aug 2002, Andreas Johansson wrote:

> 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.

Its not necessary to duplicate or change the parameters to execute the
same function triggered from multiple tables. Currently it is not possible
to trigger a function with arguments. All you have to do is to trigger the
same function on site table too,

create trigger site_status after update on sites
for each row execute procedure fix_status();

and it should do the trick.

>
> -------
> 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?

It doesn't acknowledge since it is unable to refer the oid of the function
it has been trying to trigger (with params). Refer pg_trigger for more
details.

regards,
bhuvaneswaran

>
> I'm completely stuck and I someone out there can help me.
>
> -> Andreas
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>