pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRule
7+ messages / 5 participants
[nested] [flat]
* Rule
@ 2001-11-20 13:38 Andriy Pyrozhenko <andriy.pyrozhenko@vanjaonline.com>
2001-11-20 15:24 ` Re: Rule Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Andriy Pyrozhenko @ 2001-11-20 13:38 UTC (permalink / raw)
To: pgsql-sql
Hello pgsql-sql,
I have the rule for table
CREATE RULE del_jobs_del_activity AS ON DELETE TO jobs DO DELETE FROM activities WHERE job_id = OLD.job_id;
When i call:
DELETE FROM jobs WHERE job_id IN (SELECT DISTINCT job_id FROM
activities WHERE load_no = 123)
This query does not delete the record from jobs but delete the
record by rule.
What must I change for work this query correctly?
Best regards,
Andriy mailto:andriy.pyrozhenko@vanjaonline.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rule
2001-11-20 13:38 Rule Andriy Pyrozhenko <andriy.pyrozhenko@vanjaonline.com>
@ 2001-11-20 15:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-20 15:31 ` Re: Rule Andriy Pyrozhenko <andriy.pyrozhenko@vanjaonline.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2001-11-20 15:24 UTC (permalink / raw)
To: Andriy Pyrozhenko <andriy.pyrozhenko@vanjaonline.com>; +Cc: pgsql-sql
Andriy Pyrozhenko <andriy.pyrozhenko@vanjaonline.com> writes:
> CREATE RULE del_jobs_del_activity AS ON DELETE TO jobs DO DELETE FROM activities WHERE job_id = OLD.job_id;
> DELETE FROM jobs WHERE job_id IN (SELECT DISTINCT job_id FROM
> activities WHERE load_no = 123)
> This query does not delete the record from jobs but delete the
> record by rule.
I think you're going to need to use a trigger, instead. The
rule-generated query runs first, and then by the time you get to the
actual DELETE FROM jobs, the SELECT DISTINCT subquery doesn't find any
rows ...
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rule
2001-11-20 13:38 Rule Andriy Pyrozhenko <andriy.pyrozhenko@vanjaonline.com>
2001-11-20 15:24 ` Re: Rule Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-11-20 15:31 ` Andriy Pyrozhenko <andriy.pyrozhenko@vanjaonline.com>
0 siblings, 0 replies; 7+ messages in thread
From: Andriy Pyrozhenko @ 2001-11-20 15:31 UTC (permalink / raw)
To: pgsql-sql
Hello Tom,
Tuesday, November 20, 2001, 5:24:29 PM, you wrote:
TL> Andriy Pyrozhenko <andriy.pyrozhenko@vanjaonline.com> writes:
>> CREATE RULE del_jobs_del_activity AS ON DELETE TO jobs DO DELETE FROM activities WHERE job_id = OLD.job_id;
>> DELETE FROM jobs WHERE job_id IN (SELECT DISTINCT job_id FROM
>> activities WHERE load_no = 123)
>> This query does not delete the record from jobs but delete the
>> record by rule.
TL> I think you're going to need to use a trigger, instead. The
TL> rule-generated query runs first, and then by the time you get to the
TL> actual DELETE FROM jobs, the SELECT DISTINCT subquery doesn't find any
TL> rows ...
TL> regards, tom lane
How can I do it. I use PostgreSQL 7.1.3
Best regards,
Andriy mailto:andriy.pyrozhenko@vanjaonline.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Rule
@ 2005-06-08 19:51 David Hofmann <mor4321@hotmail.com>
2005-06-08 20:06 ` Re: Rule Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 7+ messages in thread
From: David Hofmann @ 2005-06-08 19:51 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rule
2005-06-08 19:51 Rule David Hofmann <mor4321@hotmail.com>
@ 2005-06-08 20:06 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 7+ messages in thread
From: Bruno Wolff III @ 2005-06-08 20:06 UTC (permalink / raw)
To: David Hofmann <mor4321@hotmail.com>; +Cc: pgsql-sql
On Wed, Jun 08, 2005 at 15:51:35 -0400,
David Hofmann <mor4321@hotmail.com> wrote:
>
> 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.
Normally you want to do that with a before trigger rather than a rule.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rule
@ 2005-06-08 20:53 David Hofmann <mor4321@hotmail.com>
2005-06-08 21:50 ` Re: Rule Keith Worthington <KeithW@NarrowPathInc.com>
0 siblings, 1 reply; 7+ messages in thread
From: David Hofmann @ 2005-06-08 20:53 UTC (permalink / raw)
To: bruno@wolff.to; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rule
2005-06-08 20:53 Re: Rule David Hofmann <mor4321@hotmail.com>
@ 2005-06-08 21:50 ` Keith Worthington <KeithW@NarrowPathInc.com>
0 siblings, 0 replies; 7+ messages in thread
From: Keith Worthington @ 2005-06-08 21:50 UTC (permalink / raw)
To: David Hofmann <mor4321@hotmail.com>; +Cc: pgsql-sql
>>>David wrote:
>>>
>>>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.
>>
>> Normally you want to do that with a before trigger rather than a 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
David,
Here is a trigger function that I wrote for storing audit information.
Whether or not a query provides the user and/or timestamp this procedure
sets them. Naturally you will need to modify them for your data model.
CREATE OR REPLACE FUNCTION interface.tf_audit_data()
RETURNS "trigger" AS
$BODY$
BEGIN
-- Set the user name.
SELECT * FROM session_user INTO NEW.audit_user;
-- Set the timestamp.
NEW.audit_timestamp := ('now'::text)::timestamp(6) with time zone;
-- Send the modified record down the pipe.
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION interface.tf_audit_data() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION interface.tf_audit_data() TO postgres;
GRANT EXECUTE ON FUNCTION interface.tf_audit_data() TO public;
CREATE TRIGGER tgr_audit_data
BEFORE INSERT OR UPDATE
ON sales_order.tbl_line_item
FOR EACH ROW
EXECUTE PROCEDURE interface.tf_audit_data();
--
Kind Regards,
Keith
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-06-08 21:50 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-20 13:38 Rule Andriy Pyrozhenko <andriy.pyrozhenko@vanjaonline.com>
2001-11-20 15:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-20 15:31 ` Andriy Pyrozhenko <andriy.pyrozhenko@vanjaonline.com>
2005-06-08 19:51 Rule David Hofmann <mor4321@hotmail.com>
2005-06-08 20:06 ` Bruno Wolff III <bruno@wolff.to>
2005-06-08 20:53 Re: Rule David Hofmann <mor4321@hotmail.com>
2005-06-08 21:50 ` Keith Worthington <KeithW@NarrowPathInc.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