Re: Trigger/Function problem - Mailing list pgsql-sql

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@postgresql.org
>