pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger/Function problem
3+ messages / 3 participants
[nested] [flat]
* Trigger/Function problem
@ 2002-08-21 07:34 Andreas Johansson <andreas.johansson@cention.se>
0 siblings, 2 replies; 3+ messages in thread
From: Andreas Johansson @ 2002-08-21 07:34 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trigger/Function problem
@ 2002-08-21 09:11 Ludwig Lim <lud_nowhere_man@yahoo.com>
parent: Andreas Johansson <andreas.johansson@cention.se>
1 sibling, 0 replies; 3+ messages in thread
From: Ludwig Lim @ 2002-08-21 09:11 UTC (permalink / raw)
To: Andreas Johansson <andreas.johansson@cention.se>; +Cc: pgsql-sql
--- Andreas Johansson <andreas.johansson@cention.se> >
> ERROR: CreateTrigger: function fix_status() does
> not exist
Andreas :
>
> 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.
- Trigger procedures cannot have paramaters. Use the
variable TG_ARGV[] to access the arguments passed to
the trigger.
ludwig.
__________________________________________________
Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trigger/Function problem
@ 2002-08-21 09:13 Bhuvan A <bhuvansql@linuxfreemail.com>
parent: Andreas Johansson <andreas.johansson@cention.se>
1 sibling, 0 replies; 3+ messages in thread
From: Bhuvan A @ 2002-08-21 09:13 UTC (permalink / raw)
To: Andreas Johansson <andreas.johansson@cention.se>; +Cc: 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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-08-21 09:13 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-21 07:34 Trigger/Function problem Andreas Johansson <andreas.johansson@cention.se>
2002-08-21 09:11 ` Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-08-21 09:13 ` Bhuvan A <bhuvansql@linuxfreemail.com>
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