pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConditional rule?
11+ messages / 4 participants
[nested] [flat]
* Conditional rule?
@ 2000-07-27 14:43 André Næss <andre.nass@student.uib.no>
0 siblings, 3 replies; 11+ messages in thread
From: André Næss @ 2000-07-27 14:43 UTC (permalink / raw)
To: pgsql-sql
I've been looking through the material I have on postgreSQL, but can't seem
to find an answer to my problem. Very simplied, my tables are something like
this:
create table news (
id serial,
story text,
publishtime timestamp
)
create table news_unpublished (
news_id
)
I wish to make rule looking something like this:
create rule newsrule as
on insert to news do
if new.publishtime is not null insert into news_unpublished
values(new.id);
I.e. "On an insert to news, if new.publish is not null, insert the new
post's id into news_unpublished.
Is this possible?
Thanks
André Næss
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Conditional rule?
@ 2000-07-27 15:05 Itai Zukerman <zukerman@math-hat.com>
parent: André Næss <andre.nass@student.uib.no>
2 siblings, 0 replies; 11+ messages in thread
From: Itai Zukerman @ 2000-07-27 15:05 UTC (permalink / raw)
To: pgsql-sql
> I.e. "On an insert to news, if new.publish is not null, insert the new
> post's id into news_unpublished.
How about:
CREATE RULE newsrule AS
ON INSERT TO news
DO
INSERT INTO news_unpublished
SELECT NEW.id
WHERE NEW.publishtime IS NOT NULL
-itai
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Conditional rule?
@ 2000-07-27 15:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: André Næss <andre.nass@student.uib.no>
2 siblings, 2 replies; 11+ messages in thread
From: Tom Lane @ 2000-07-27 15:36 UTC (permalink / raw)
To: André Næss <andre.nass@student.uib.no>; +Cc: pgsql-sql
"=?iso-8859-1?B?QW5kcukgTuZzcw==?=" <andre.nass@student.uib.no> writes:
> I wish to make rule looking something like this:
> create rule newsrule as
> on insert to news do
> if new.publishtime is not null insert into news_unpublished
> values(new.id);
> I.e. "On an insert to news, if new.publish is not null, insert the new
> post's id into news_unpublished.
What you want here is a trigger, not a rule. The closest you could come
with a rule is to copy *all* unpublished ids into news_unpublished each
time something got inserted into news. There are applications for that
sort of thing, but this ain't it. See the trigger examples in the
plpgsql or pltcl sections of the manual.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Conditional rule?
@ 2000-07-27 17:21 André Næss <andre.nass@student.uib.no>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 11+ messages in thread
From: André Næss @ 2000-07-27 17:21 UTC (permalink / raw)
To: pgsql-sql
Hm... I'm a true newbie when it comes to plpgsql and triggers, but I looked
through some texts and managed to come up with the following rather odd (to
me at least) behaviour:
*****
create table news (
id serial,
title varchar(50),
time timestamp
)
create table news_un (
news_id int
)
Table "news_un"
Attribute | Type | Modifier
-----------+---------+----------
news_id | integer |
create function setpublish() returns opaque as '
begin
insert into news_un select news_id_seq.last_value where new.time is not
null;
return null;
end;
'
language 'plpgsql';
create trigger newstrigger after insert on news for each row execute
procedure setpublish();
*****
Attempting to do an insert to news:
testruledb=# insert into news (title, time) values('Test', now());
INSERT 24028 1
testruledb=# select * from news; select * from news_un;
id | title | time
----+-------+------------------------
48 | Test | 2000-07-27 19:20:24+02
(1 row)
news_id
---------
47
48
(2 rows)
I also tried setting time to null:
testruledb=# insert into news (title) values('Test2');
INSERT 24031 1
testruledb=# select * from news; select * from news_un;
id | title | time
----+-------+------
50 | Test2 |
(1 row)
news_id
---------
49
(1 row)
There's obviously something about triggers and functions I don't understand,
any help would be greatly appreciated.
Thanks
André Næss
----- Original Message -----
> "=?iso-8859-1?B?QW5kcukgTuZzcw==?=" <andre.nass@student.uib.no> writes:
> > I wish to make rule looking something like this:
> > create rule newsrule as
> > on insert to news do
> > if new.publishtime is not null insert into news_unpublished
> > values(new.id);
>
> > I.e. "On an insert to news, if new.publish is not null, insert the new
> > post's id into news_unpublished.
>
> What you want here is a trigger, not a rule. The closest you could come
> with a rule is to copy *all* unpublished ids into news_unpublished each
> time something got inserted into news. There are applications for that
> sort of thing, but this ain't it. See the trigger examples in the
> plpgsql or pltcl sections of the manual.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Conditional rule?
@ 2000-07-27 18:01 André Næss <andre.nass@student.uib.no>
parent: André Næss <andre.nass@student.uib.no>
0 siblings, 0 replies; 11+ messages in thread
From: André Næss @ 2000-07-27 18:01 UTC (permalink / raw)
To: pgsql-sql
Ooops... seems I had a rule tied to my news table which caused the
malfunction, sorry if I wasted anyone's time :(
André Næss
----- Original Message -----
From: "André Næss" <andre.nass@student.uib.no>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, July 27, 2000 7:21 PM
Subject: Re: [SQL] Conditional rule?
> Hm... I'm a true newbie when it comes to plpgsql and triggers, but I
looked
> through some texts and managed to come up with the following rather odd
(to
> me at least) behaviour:
>
> *****
>
> create table news (
> id serial,
> title varchar(50),
> time timestamp
> )
>
> create table news_un (
> news_id int
> )
>
> Table "news_un"
> Attribute | Type | Modifier
> -----------+---------+----------
> news_id | integer |
>
> create function setpublish() returns opaque as '
> begin
> insert into news_un select news_id_seq.last_value where new.time is
not
> null;
> return null;
> end;
> '
> language 'plpgsql';
>
> create trigger newstrigger after insert on news for each row execute
> procedure setpublish();
>
> *****
> Attempting to do an insert to news:
>
> testruledb=# insert into news (title, time) values('Test', now());
> INSERT 24028 1
> testruledb=# select * from news; select * from news_un;
> id | title | time
> ----+-------+------------------------
> 48 | Test | 2000-07-27 19:20:24+02
> (1 row)
>
> news_id
> ---------
> 47
> 48
> (2 rows)
>
> I also tried setting time to null:
>
> testruledb=# insert into news (title) values('Test2');
> INSERT 24031 1
> testruledb=# select * from news; select * from news_un;
> id | title | time
> ----+-------+------
> 50 | Test2 |
> (1 row)
>
> news_id
> ---------
> 49
> (1 row)
>
> There's obviously something about triggers and functions I don't
understand,
> any help would be greatly appreciated.
>
> Thanks
>
> André Næss
>
>
> ----- Original Message -----
> > "=?iso-8859-1?B?QW5kcukgTuZzcw==?=" <andre.nass@student.uib.no> writes:
> > > I wish to make rule looking something like this:
> > > create rule newsrule as
> > > on insert to news do
> > > if new.publishtime is not null insert into news_unpublished
> > > values(new.id);
> >
> > > I.e. "On an insert to news, if new.publish is not null, insert the new
> > > post's id into news_unpublished.
> >
> > What you want here is a trigger, not a rule. The closest you could come
> > with a rule is to copy *all* unpublished ids into news_unpublished each
> > time something got inserted into news. There are applications for that
> > sort of thing, but this ain't it. See the trigger examples in the
> > plpgsql or pltcl sections of the manual.
> >
> > regards, tom lane
> >
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Conditional rule?
@ 2000-07-27 22:16 Jan Wieck <JanWieck@t-online.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 11+ messages in thread
From: JanWieck@t-online.de @ 2000-07-27 22:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: André Næss?us-ascii?Q?=3D?= <andre.nass@student.uib.no>; pgsql-sql
Tom Lane wrote:
> "=?iso-8859-1?B?QW5kcukgTuZzcw==?=" <andre.nass@student.uib.no> writes:
> > I wish to make rule looking something like this:
> > create rule newsrule as
> > on insert to news do
> > if new.publishtime is not null insert into news_unpublished
> > values(new.id);
>
> > I.e. "On an insert to news, if new.publish is not null, insert the new
> > post's id into news_unpublished.
>
> What you want here is a trigger, not a rule. The closest you could come
> with a rule is to copy *all* unpublished ids into news_unpublished each
> time something got inserted into news. There are applications for that
> sort of thing, but this ain't it. See the trigger examples in the
> plpgsql or pltcl sections of the manual.
No. The rule
CREATE RULE newsrule AS ON INSERT TO news
WHERE new.publishtime NOTNULL DO
INSERT INTO news_unpublished VALUES (new.id);
should do the job perfectly. Maybe you want to have the
following rules too:
CREATE RULE newsrule2 AS ON UPDATE TO news
WHERE old.publishtime ISNULL AND new.publishtime NOTNULL DO
INSERT INTO news_unpublished VALUES (new.id);
CREATE RULE newsrule3 AS ON UPDATE TO news
WHERE old.publishtime NOTNULL AND new.publishtime ISNULL DO
DELETE FROM news_unpublished WHERE news_unpublished.id = old.id;
CREATE RULE newsrule4 AS ON DELETE TO news
WHERE old.publishtime NOTNULL DO
DELETE FROM news_unpublished WHERE news_unpublished.id = old.id;
With these four rules, all the inserts and deletes are done
automatically.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Conditional rule?
@ 2000-07-28 09:32 André Næss <andre.nass@student.uib.no>
parent: Jan Wieck <JanWieck@t-online.de>
0 siblings, 1 reply; 11+ messages in thread
From: André Næss @ 2000-07-28 09:32 UTC (permalink / raw)
To: pgsql-sql
----- Original Message -----
> No. The rule
>
> CREATE RULE newsrule AS ON INSERT TO news
> WHERE new.publishtime NOTNULL DO
> INSERT INTO news_unpublished VALUES (new.id);
>
> should do the job perfectly. Maybe you want to have the
> following rules too:
The following happens:
rules=# insert into news (title, time) values('Hei', now());
ERROR: <unnamed> referential integrity violation - key referenced from
news_unpublished not found in news
Seems the data is inserted into news_unpublished first, thereby violating
the constraint I have defined for the news_id field (see below). After
removing the constraint a second problem arose; the id created for news
(serial) was 4, while the id inserted into news_unpublished was 3. So far a
trigger procedure seems to be the best solution.
> CREATE RULE newsrule4 AS ON DELETE TO news
> WHERE old.publishtime NOTNULL DO
> DELETE FROM news_unpublished WHERE news_unpublished.id =
old.id;
This is also achieved by the following right? (id is the primary key for
news):
create table news_unpublished (
news_id int references news on delete cascade
);
André Næss
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Conditional rule?
@ 2000-07-28 11:04 Itai Zukerman <zukerman@math-hat.com>
parent: André Næss <andre.nass@student.uib.no>
0 siblings, 0 replies; 11+ messages in thread
From: Itai Zukerman @ 2000-07-28 11:04 UTC (permalink / raw)
To: pgsql-sql
> > CREATE RULE newsrule AS ON INSERT TO news
> > WHERE new.publishtime NOTNULL DO
> > INSERT INTO news_unpublished VALUES (new.id);
>
> The following happens:
> rules=# insert into news (title, time) values('Hei', now());
> ERROR: <unnamed> referential integrity violation - key referenced from
> news_unpublished not found in news
I noticed this, too.
More generally, I've found that whereas rules initially looked like a
great thing to use, in practice I have really no idea what they're
going to do. And I find their description in the programmer's guide
confusing. Is there any better rules tutorial out there?
-itai
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Conditional rule?
@ 2000-07-28 11:46 André Næss <andre.nass@student.uib.no>
parent: André Næss <andre.nass@student.uib.no>
2 siblings, 2 replies; 11+ messages in thread
From: André Næss @ 2000-07-28 11:46 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Thanks for all the help so far. What I now have is the following structure:
create table b_news (
id serial primary key,
title varchar(60),
time timestamp
);
create table b_news_unpublished (
news_id int references news on delete cascade
);
CREATE FUNCTION b_news_trigproc() RETURNS OPAQUE AS '
BEGIN
IF TG_OP = ''INSERT'' THEN
IF NEW.time NOTNULL THEN
INSERT INTO b_news_unpublished VALUES (NEW.id);
END IF;
END IF;
IF TG_OP = ''UPDATE'' THEN
IF NEW.time NOTNULL AND OLD.time ISNULL THEN
INSERT INTO b_news_unpublished VALUES (NEW.id);
END IF;
IF NEW.time ISNULL AND OLD.time NOTNULL THEN
DELETE FROM b_news_unpublished WHERE news_id=NEW.id;
END IF;
END IF;
RETURN null;
END;
' LANGUAGE 'plpgsql';
create trigger b_news_trigger
after insert or update on b_news
for each row execute procedure b_news_trigproc();
And this works as intended. There are however a few things that worries me.
First of all, I can't seem to find any way to list the trigger and the
function, they seem invisible. This is problematic because my work will be
continued by others, and allthough I will document everything I think it
should be possible to see the triggers and functions somehow...
Secondly, I miss one final idea, when a delete is performed on the
b_news_unpublished table, I would like to set up a rule or procedure that
sets the time value to null in b_news for each row that is affected by the
delete. I understand that the OLD and NEW objects are accessible only during
UPDATE or INSERT operations, so I can't quite see how to do this...
I also find it rather inelegant to use the constraint to handle DELETE
operations on news, whereas UPDATEs and INSERTs are handled by the trigger
procedure. Somehow I would like to either do all the tasks using the trigger
procedure, or using rules.
As for Itai Zukerman's comment: AOL. Good resources around triggers and
rules are very much needed!
Regards
André Næss
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Conditional rule?
@ 2000-07-28 15:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: André Næss <andre.nass@student.uib.no>
1 sibling, 0 replies; 11+ messages in thread
From: Tom Lane @ 2000-07-28 15:13 UTC (permalink / raw)
To: André Næss <andre.nass@student.uib.no>; +Cc: pgsql-sql
"=?iso-8859-1?B?QW5kcukgTuZzcw==?=" <andre.nass@student.uib.no> writes:
> Secondly, I miss one final idea, when a delete is performed on the
> b_news_unpublished table, I would like to set up a rule or procedure that
> sets the time value to null in b_news for each row that is affected by the
> delete. I understand that the OLD and NEW objects are accessible only during
> UPDATE or INSERT operations, so I can't quite see how to do this...
I might be wrong, but I thought the way it works is:
INSERT: "NEW" refers to to-be-inserted row
UPDATE: "OLD" is old row state, "NEW" is planned new row state
DELETE: "OLD" holds row to be deleted
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Conditional rule?
@ 2000-07-29 13:19 André Næss <andre.nass@student.uib.no>
parent: André Næss <andre.nass@student.uib.no>
1 sibling, 0 replies; 11+ messages in thread
From: André Næss @ 2000-07-29 13:19 UTC (permalink / raw)
To: Robert B. Easter <reaster@comptechnews.com>; +Cc: pgsql-sql
----- Original Message -----
> > And this works as intended. There are however a few things that worries
me.
> > First of all, I can't seem to find any way to list the trigger and the
> > function, they seem invisible. This is problematic because my work will
be
> > continued by others, and allthough I will document everything I think it
> > should be possible to see the triggers and functions somehow...
> >
> > Secondly, I miss one final idea, when a delete is performed on the
> > b_news_unpublished table, I would like to set up a rule or procedure
that
> > sets the time value to null in b_news for each row that is affected by
the
> > delete. I understand that the OLD and NEW objects are accessible only
during
> > UPDATE or INSERT operations, so I can't quite see how to do this...
>
>
> OLD.* is available during TG_OP = ''DELETE'' in a trigger.
Indeed it is, I just didn't read the manual good enough :)
> There are sometimes some referential integrity problems on DELETE when
using
> triggers to do things on tables that have a RI relationship. Like, if you
have
> a table that REFERENCES another table ON DELETE SET NULL, and there is a
DELETE
> proc on the referenced table that also does some other update on those
records,
> then the regular trigger might happen before the contraint trigger. In
this
> case, an update would have a RI problem when it updates some attribute and
the
> contraint is checked again - it would fail since the referenced primary
key is
> deleted already but foreign key has not been SET NULL yet by the contraint
> trigger. I'm not sure what the rules are on the order of contraint trigger
and
> other trigger execution but sometimes the order isn't what you want and
then
> you get the problem on delete. If it happens, like it happened to me, you
> might quit using foreign keys and just program your triggers as much as
you can
> to do the same checks that the contraint triggers are doing but by having
your
> own triggers do it, you have control of the order of how things happen on
> delete.
This seems ok to me, but I still don't like the invisibility as I pointed
out above. If someone else was to continue my work how would they be able to
see the triggers and procedures? They don't show up on "\d tablename", so it
is very tempting to simply do this in PHP (i.e. managing the
news_unpublished table with PHP.) I have the same issue with constraints, as
they are invisible too.
So, it comes down to something like this; I would really like to use
constraints and trigger procedures, but in order to do so I need to keep a
very close eye on my database, this means that I might get into trouble if I
need to do some changes to this system after several months -- will I
remember all these hidden behaviours? By doing it in PHP all the behaviour
will be there in code, all visible and (hopefully) understandable to both
myself and others.
Again, I'm a postgre newbie, so there might be something essential I've
simply overlooked, but some clarification concerning this topic would be
nice.
Thanks
André Næss
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2000-07-29 13:19 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-27 14:43 Conditional rule? André Næss <andre.nass@student.uib.no>
2000-07-27 15:05 ` Itai Zukerman <zukerman@math-hat.com>
2000-07-27 15:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-07-27 17:21 ` André Næss <andre.nass@student.uib.no>
2000-07-27 18:01 ` André Næss <andre.nass@student.uib.no>
2000-07-27 22:16 ` Jan Wieck <JanWieck@t-online.de>
2000-07-28 09:32 ` André Næss <andre.nass@student.uib.no>
2000-07-28 11:04 ` Itai Zukerman <zukerman@math-hat.com>
2000-07-28 11:46 ` André Næss <andre.nass@student.uib.no>
2000-07-28 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-07-29 13:19 ` André Næss <andre.nass@student.uib.no>
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