rule with multiple DELETE action part

I'm just wondering anybody read my question?
If not, here you are:

On Mon, 18 Sep 2000, Papp Gyozo wrote:

Papp Gyozo <s7461pap@hszk.bme.hu> writes:

try=# CREATE TABLE t_one (c_i integer PRIMARY KEY, c_t text);
CREATE
try=# CREATE TABLE t_two (c_i integer REFERENCES t_one, c_d date);
CREATE
try=# CREATE VIEW v_one_two AS SELECT t_one.oid as c_oid, t_one.c_i, c_d, c_t
try-# FROM t_one, t_two WHERE t_one.c_i = t_two.c_i;
CREATE 81186 1
try=# CREATE RULE r_one_two_del AS ON DELETE TO v_one_two
try-# DO INSTEAD (DELETE FROM t_two WHERE c_i = old.c_i; DELETE FROM t_one WHERE c_i = old.c_i;);
CREATE 81187 1

I don't think you can make that work --- once you've deleted the t_two
row for a particular c_i value, there's no longer any row in the
v_one_two join for that c_i, and so of course the second DELETE doesn't
find anything to delete. "old.c_i" isn't some magical constant, it's
just a shorthand for referring to the current contents of the rule's
event table, ie the v_one_two join. We can't change that behavior of
rules without breaking other cases that are at least as useful as this.

What you need is something that will hang onto the specific value you're
deleting for long enough to hit both tables. I haven't tried it but I
think you could do it with a function. Perhaps
... ON DELETE TO v_one_two DO INSTEAD SELECT delboth(old.c_i);
where delboth() is a SQL or PLPGSQL function that does the deletions
and then returns some dummy value. This should work because the
specific c_i value is held as a parameter of the function.

regards, tom lane

First, thank youu for the accurate and exhausting answer.

And a new idea... I've encountered a problem and I think rules would be
the nicest and the best solutions for it. After SELECTing rows I need to
update some statistic fields such as timestamp of last reference and
number of all references up to now, etc.

In pgsql 7.0.2, there is no way to create a rule which can perform an
additional query after SELECT. What I'm writing about:

CREATE RULE r_my_dream AS ON SELECT TO xxx
DO UPDATE xxx SET c_lastref = current_timestamp, c_refnum = c_refnum + 1
WHERE xxx.oid = old.oid;

I know that this could be solved with FOR UPDATE clause and then with an
UPDATE query.
But, it would be so nice if it was totally transparent. (_only_one_ query)
Can my dream be realised?

Papp Gyozo

s7461pap@hszk.bme.hu, gerzson17@freemail.hu

Hi everyone,

I have built a database that uses int4 as the unique identifier for a news
database.

Therefore an article has a identifier 1, the next one has 2 etc.

Then I order them when they are displayed on the web reversely so that the
last article added is at the top of the list.

I now face a major problem. If we need to back dackdate an article - I
can't. This is because the all the indexes are taken up ie 1, 2, 3......75
odd records. If I want to add one between 50 and 51 ie 50.5, I cannot
because the field is an int4.

One idea I had to get around this was to create a new table with this column
as a float and read and write all therecords in with a PHP script.

Is there an easier way such as converting the column data type from a int4
to a float.

Thanks in advance.
Abe.

Papp Gyozo <s7461pap@hszk.bme.hu> writes:

In pgsql 7.0.2, there is no way to create a rule which can perform an
additional query after SELECT. What I'm writing about:
CREATE RULE r_my_dream AS ON SELECT TO xxx
DO UPDATE xxx SET c_lastref = current_timestamp, c_refnum = c_refnum + 1
WHERE xxx.oid = old.oid;

I don't think this is a real good idea. If it were enforced by the
database then you couldn't (just to take one example) produce a backup
dump without clobbering all your lastref information. And what of
queries like SELECT count(*) FROM table --- should that update the
timestamps of all the rows it reads, and if not why not?

I think you'll have to consider "access" (ie, an action that triggers
a lastref update) to be an application-defined concept, and that means
implementing the updates on the application side.

regards, tom lane

Abe,
How do you generate your article number? If you are using a SERIAL, then
you could change it to use a specific SEQUENCE and step the SEQUENCE by
some value other than one. Something like:

CREATE SEQUENCE article_sequence INCREMENT 100 MINVALUE 1 START 101;

CREATE TABLE article (
article_number int4 default nextval('article_sequence')
);

That would automatically generate article numbers 101, 201, 301, ...
thus leaving room for "inserted" articles.

On Fri, 22 Sep 2000, Abe Asghar wrote:

I'm looking for at least two good books on psql, are there any out ther, if so
can someone suggest some to me?

Mike

http://www.postgresql.org/docs/awbook.html

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Mike" <msears@vianet.ca>
To: "Adam Haberlach" <adam@newsnipple.com>; <pgsql-general@postgresql.org>
Sent: Friday, September 22, 2000 1:28 PM
Subject: [GENERAL] PSQL Books

I'm looking for at least two good books on psql, are there any out ther,

if so

On Fri, Sep 22, 2000 at 03:33:46PM +0100, Abe Asghar wrote:

Hi everyone,

I have built a database that uses int4 as the unique identifier for a news
database.

Therefore an article has a identifier 1, the next one has 2 etc.

Then I order them when they are displayed on the web reversely so that the
last article added is at the top of the list.

I now face a major problem. If we need to back dackdate an article - I
can't. This is because the all the indexes are taken up ie 1, 2, 3......75
odd records. If I want to add one between 50 and 51 ie 50.5, I cannot
because the field is an int4.

Perhaps you should store the date of the article in the database, and
then sort by that. You can even set the default for the field to be now() so
that it automatically gets set on insert, and you can override it during
backdate inserts or with later updates...

--
Adam Haberlach | A billion hours ago, human life appeared on
adam@newsnipple.com | earth. A billion minutes ago, Christianity
http://www.newsnipple.com | emerged. A billion Coca-Colas ago was
'88 EX500 | yesterday morning. -1996 Coca-Cola Ann. Rpt.

Well, how about just adding something other than 1 (such as 100) to the
value in the table? As for the existing table, perhaps, before moving it
to the new table, you could do something like:

UPDATE table SET article_number=article_number*100;

This would change all the current article numbers to higher values. If
these numbers are referenced in other columns, then do an equivalent
UPDATE command on those columns as well.

This is about all that I can come up with.

John

On Fri, 22 Sep 2000, Abe Asghar wrote:

ID shouldn't contain any additional informations (like order). It's ID. Use
another field for sorting.

At 16:33 22.9.2000 , Abe Asghar wrote: