[SQL] Rule
I have a table with 3 fields, id, s_data, and time_stamp. The time_stamp field is set to now() by deault. The program that uses this table only uses the id and s_data file. I added and use the time_stamp field to delete old records after a certain time. What I want to do is setup some kind of rule so that whenever a s_data field is updated, that the time_stamp gets update to the current time/date. The program regretab I'm not really familar with rules, I've only used them in a certain places and very limitedly. Any help would be greatly appercated. David Hofmann _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Rule
Ok, I have no knowledge of Tiggers except what I just read in the docs section. Look like I need to make a procudure then call it with a trigger. Is there a better location for Tigger/Procudure Examples. The trigger seems fairly, however I got lost in the procudure part. David Normally you want to do that with a before trigger rather than a rule. _ FREE pop-up blocking with the new MSN Toolbar get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Tigger
I've look throught the docs and from what I can see the bellow code should work, however I keep getting the error: ERROR: parser: parse error at or near "$" at character 53 CREATE FUNCTION session_update() RETURNS trigger AS $session_update$ BEGIN -- Check date exists IF NEW.stamp_lastupdate IS NULL THEN NEW.stamp_lastupdate := 'now'; END IF; RETURN NEW; END; $session_update$ LANGUAGE plpgsql; CREATE TRIGGER session_update BEFORE INSERT OR UPDATE ON sessions FOR EACH ROW EXECUTE PROCEDURE session_update(); Any help or suggestions of websites I should read would be appercated. David _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Tigger
I'm using 7.3. From: Bricklen Anderson <[EMAIL PROTECTED]> To: David Hofmann <[EMAIL PROTECTED]> CC: pgsql-sql@postgresql.org Subject: Re: [SQL] Tigger Date: Fri, 22 Jul 2005 12:17:41 -0700 David Hofmann wrote: > I've look throught the docs and from what I can see the bellow code > should work, however I keep getting the error: > > ERROR: parser: parse error at or near "$" at character 53 > > CREATE FUNCTION session_update() RETURNS trigger AS $session_update$ > BEGIN > -- Check date exists > IF NEW.stamp_lastupdate IS NULL THEN > NEW.stamp_lastupdate := 'now'; > END IF; > RETURN NEW; > END; > > $session_update$ LANGUAGE plpgsql; > > CREATE TRIGGER session_update BEFORE INSERT OR UPDATE ON sessions FOR > EACH ROW EXECUTE PROCEDURE session_update(); > > > Any help or suggestions of websites I should read would be appercated. > > David Which version of postgresql are you using? I don't believe that the "$" quoting was available in older versions than 8 (or late 7?). -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Tigger
It didn't error out, however the function didn't work. Specificly the if statement. I took out the if statement and it started updating, I put it back in and it fails to update. Not sure why. I've confirmed that the program not attempting to update the stamp_lastupdate field. Here what I end up with that worked. CREATE or REPLEACE FUNCTION session_update() RETURNS trigger AS ' BEGIN -- Check date exists NEW.stamp_lastupdate := ''now''; RETURN NEW; END; ' LANGUAGE plpgsql; CREATE TRIGGER session_update BEFORE INSERT OR UPDATE ON sessions FOR EACH ROW EXECUTE PROCEDURE session_update(); I appercated the help Bricklen. David From: Bricklen Anderson <[EMAIL PROTECTED]> To: David Hofmann <[EMAIL PROTECTED]> CC: pgsql-sql@postgresql.org Subject: Re: [SQL] Tigger Date: Fri, 22 Jul 2005 12:28:32 -0700 David Hofmann wrote: > I'm using 7.3. > >> From: Bricklen Anderson <[EMAIL PROTECTED]> >> To: David Hofmann <[EMAIL PROTECTED]> >> CC: pgsql-sql@postgresql.org >> Subject: Re: [SQL] Tigger >> Date: Fri, 22 Jul 2005 12:17:41 -0700 >> >> David Hofmann wrote: >> > I've look throught the docs and from what I can see the bellow code >> > should work, however I keep getting the error: >> > >> > ERROR: parser: parse error at or near "$" at character 53 >> > >> > CREATE FUNCTION session_update() RETURNS trigger AS $session_update$ >> > BEGIN >> > -- Check date exists >> > IF NEW.stamp_lastupdate IS NULL THEN >> > NEW.stamp_lastupdate := 'now'; >> > END IF; >> > RETURN NEW; >> > END; >> > >> > $session_update$ LANGUAGE plpgsql; >> > >> > CREATE TRIGGER session_update BEFORE INSERT OR UPDATE ON sessions FOR >> > EACH ROW EXECUTE PROCEDURE session_update(); >> > >> > >> > Any help or suggestions of websites I should read would be appercated. >> > >> > David >> >> Which version of postgresql are you using? I don't believe that the >> "$" quoting >> was available in older versions than 8 (or late 7?). I don't think that it worked then. Simple test: CREATE FUNCTION session_update() RETURNS trigger AS ' BEGIN -- Check date exists IF NEW.stamp_lastupdate IS NULL THEN NEW.stamp_lastupdate := ''now''; END IF; RETURN NEW; END; ' LANGUAGE plpgsql; -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
