[SQL] Trigger/Function problem
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
namechar(120)
status int
Page:
id serial
namechar(120)
status int
site_id int
Text:
id serial
namechar(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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Trigger/Function problem
--- Andreas Johansson <[EMAIL PROTECTED]> > > 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] 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:
> idserial
> name char(120)
> statusint
>
> Page:
> idserial
> name char(120)
> statusint
> site_id int
>
> Text:
> idserial
> name char(120)
> statusint
> 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 [EMAIL PROTECTED]
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Event recurrence - in database or in application code ????
On Tue, 2002-08-20 at 22:15, Mark Stosberg wrote: > > Hello Darrin, > > > I've been happy with this solution. I think the Perl turned out to be > fairly easy to understand and maintain, the SQL that needs to be used > ends up being fairly straightforward, and the performance is good > because the selects to view the calendar are fairly simple. The one > drawback is that sometime before 2028, I have to remember to add some > rows to the calendar table. :) > You need to add rows as well as re-populate a bunch of info for recurring dates that are not listed forward right? > -mark > > http://mark.stosberg.com/ > > > On Tue, 20 Aug 2002, Darrin Domoney wrote: > > > One of the features that I am attempting to implement in the system that I > > am building is > > the capability to schedule events (with recurrence). My question to those of > > you that are > > more experienced in postgresql is whether you would implement this > > functionality in the > > database level using triggers or at the application code level (PHP). > > > > > > Any suggestions, etc gratefully appreciated. > > I would strongly recommend you look at the "WebCalendar" project on sourceforge. Robert Treat ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Event recurrence - in database or in application code ????
On 21 Aug 2002, Robert Treat wrote: > On Tue, 2002-08-20 at 22:15, Mark Stosberg wrote: > > > > Hello Darrin, > > > > > > > I've been happy with this solution. I think the Perl turned out to be > > fairly easy to understand and maintain, the SQL that needs to be used > > ends up being fairly straightforward, and the performance is good > > because the selects to view the calendar are fairly simple. The one > > drawback is that sometime before 2028, I have to remember to add some > > rows to the calendar table. :) > > > > You need to add rows as well as re-populate a bunch of info for > recurring dates that are not listed forward right? Perhaps this will answer your question Robert-- one point I didn't mention before is that I don't allow events events to recur forever, they have end after some finite number of times. You could add a birthday and tell it to repeat it once a year for the next 100 years for example. I wouldn't have to go and add rows for these later though-- the rows needed for the next 100 years would already be generated in the events_calendar table. The only thing that "expires" with my solution is the dates in the calendar table. I could make the dates run for the next 100 years just as easy as 28 years, I just figured the system would probably get a significant revamp sometime in the next quarter century. :) -mark http://mark.stosberg.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [ADMIN] How to execute my trigger when update certain columns
On Wed, 2002-08-14 at 13:09, Raymond Chui wrote: > Let say I have a table has column1 and column2 and I made a trigger for > after INSERT OR UPDATE for each row to execute procedure my_function. > > What I want is the trigger execute my_function only when column1 is > insert or > update, but not going to execute my_function when column2 is insert or > update. The trigger is executed unconditionally, so put the condition inside my_function. If it's an INSERT, column1 must be new, so: IF TG_OP = ''INSERT'' OR (TG_OP = ''UPDATE'' AND (NEW.column1 != OLD.column1 OR (NEW.column1 IS NULL AND OLD.column1 IS NOT NULL) OR (NEW.column1 IS NOT NULL AND OLD.column1 IS NULL) ) ) THEN ... END IF; -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "For yourselves know perfectly that the day of the Lord so cometh as a thief in the night. For when they shall say, Peace and safety; then sudden destruction cometh upon them, as travail upon a woman with child; and they shall not escape." I Thessalonians 5:2,3 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
