dbTalk Databases Forums  

RULE: UPDATE on INSERT, but INSERT on UPDATE

Go Back dbTalk Databases Forums Databases comp.databases.postgresql RULE: UPDATE on INSERT, but INSERT on UPDATE

comp.databases.postgresql comp.databases.postgresql


Discuss RULE: UPDATE on INSERT, but INSERT on UPDATE in the comp.databases.postgresql forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Johannes Bauer
 
Posts: n/a

Default RULE: UPDATE on INSERT, but INSERT on UPDATE - 06-13-2008 , 02:16 PM





Hello list,

I've a table where I want a UPDATE performed if the primary key that
should be inserted already exists, but which performs a INSERT if the
primary key does not exist. It doesn't really work. Here's what I have:

CREATE RULE nonexistent_insert_crit_hosts AS
ON UPDATE TO crit_hosts WHERE
(new.host) NOT IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
INSERT INTO crit_hosts (host, properties) VALUES (new.host,
new.properties);

CREATE RULE duplicate_update_crit_hosts AS
ON INSERT TO crit_hosts WHERE
(new.host) IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
UPDATE crit_hosts SET
properties = new.properties
WHERE host = new.host;

Upon UPDATE with a non-existing primary key leads to:

ERROR: infinite recursion spotted in rules for relation crit_hosts
' in 'UPDATE crit_hosts SET properties = properties |
B'00000000000000000000000000000001' WHERE host = '192.168.1.3';'

(the error message is translated from German so it might well be a
little different in the original)

What am I doing wrong?

Thanks in advance,
Johannes

--
"Wer etwas kritisiert muss es noch lange nicht selber besser knnen. Es
reicht zu wissen, da andere es besser knnen und andere es auch
besser machen um einen Vergleich zu bringen." - Wolfgang Gerber
in de.sci.electronics <47fa8447$0$11545$9b622d9e (AT) news (DOT) freenet.de>

Reply With Quote
  #2  
Old   
Łukasz Filut
 
Posts: n/a

Default Re: RULE: UPDATE on INSERT, but INSERT on UPDATE - 07-24-2008 , 02:32 AM





Johannes Bauer pisze:
Quote:
Hello list,

I've a table where I want a UPDATE performed if the primary key that
should be inserted already exists, but which performs a INSERT if the
primary key does not exist. It doesn't really work. Here's what I have:

CREATE RULE nonexistent_insert_crit_hosts AS
ON UPDATE TO crit_hosts WHERE
(new.host) NOT IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
INSERT INTO crit_hosts (host, properties) VALUES (new.host,
new.properties);

CREATE RULE duplicate_update_crit_hosts AS
ON INSERT TO crit_hosts WHERE
(new.host) IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
UPDATE crit_hosts SET
properties = new.properties
WHERE host = new.host;

Upon UPDATE with a non-existing primary key leads to:

ERROR: infinite recursion spotted in rules for relation »crit_hosts«
' in 'UPDATE crit_hosts SET properties = properties |
B'00000000000000000000000000000001' WHERE host = '192.168.1.3';'

(the error message is translated from German so it might well be a
little different in the original)

What am I doing wrong?
on INSERT you do UPDATE, on UPDATE do INSERT, on INSERT do UPDATE, on
UPDATE DO INSERT ... WHERE clause is not a help for postgresql.
Quote:
Thanks in advance,
Johannes

If you want do such thing create a view on a table and create rules on
it, but inserts and updates you must do to base table.

Lukasz



Reply With Quote
  #3  
Old   
Łukasz Filut
 
Posts: n/a

Default Re: RULE: UPDATE on INSERT, but INSERT on UPDATE - 07-24-2008 , 02:32 AM


Johannes Bauer pisze:
Quote:
Hello list,

I've a table where I want a UPDATE performed if the primary key that
should be inserted already exists, but which performs a INSERT if the
primary key does not exist. It doesn't really work. Here's what I have:

CREATE RULE nonexistent_insert_crit_hosts AS
ON UPDATE TO crit_hosts WHERE
(new.host) NOT IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
INSERT INTO crit_hosts (host, properties) VALUES (new.host,
new.properties);

CREATE RULE duplicate_update_crit_hosts AS
ON INSERT TO crit_hosts WHERE
(new.host) IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
UPDATE crit_hosts SET
properties = new.properties
WHERE host = new.host;

Upon UPDATE with a non-existing primary key leads to:

ERROR: infinite recursion spotted in rules for relation »crit_hosts«
' in 'UPDATE crit_hosts SET properties = properties |
B'00000000000000000000000000000001' WHERE host = '192.168.1.3';'

(the error message is translated from German so it might well be a
little different in the original)

What am I doing wrong?
on INSERT you do UPDATE, on UPDATE do INSERT, on INSERT do UPDATE, on
UPDATE DO INSERT ... WHERE clause is not a help for postgresql.
Quote:
Thanks in advance,
Johannes

If you want do such thing create a view on a table and create rules on
it, but inserts and updates you must do to base table.

Lukasz



Reply With Quote
  #4  
Old   
Łukasz Filut
 
Posts: n/a

Default Re: RULE: UPDATE on INSERT, but INSERT on UPDATE - 07-24-2008 , 02:32 AM


Johannes Bauer pisze:
Quote:
Hello list,

I've a table where I want a UPDATE performed if the primary key that
should be inserted already exists, but which performs a INSERT if the
primary key does not exist. It doesn't really work. Here's what I have:

CREATE RULE nonexistent_insert_crit_hosts AS
ON UPDATE TO crit_hosts WHERE
(new.host) NOT IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
INSERT INTO crit_hosts (host, properties) VALUES (new.host,
new.properties);

CREATE RULE duplicate_update_crit_hosts AS
ON INSERT TO crit_hosts WHERE
(new.host) IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
UPDATE crit_hosts SET
properties = new.properties
WHERE host = new.host;

Upon UPDATE with a non-existing primary key leads to:

ERROR: infinite recursion spotted in rules for relation »crit_hosts«
' in 'UPDATE crit_hosts SET properties = properties |
B'00000000000000000000000000000001' WHERE host = '192.168.1.3';'

(the error message is translated from German so it might well be a
little different in the original)

What am I doing wrong?
on INSERT you do UPDATE, on UPDATE do INSERT, on INSERT do UPDATE, on
UPDATE DO INSERT ... WHERE clause is not a help for postgresql.
Quote:
Thanks in advance,
Johannes

If you want do such thing create a view on a table and create rules on
it, but inserts and updates you must do to base table.

Lukasz



Reply With Quote
  #5  
Old   
Łukasz Filut
 
Posts: n/a

Default Re: RULE: UPDATE on INSERT, but INSERT on UPDATE - 07-24-2008 , 02:32 AM


Johannes Bauer pisze:
Quote:
Hello list,

I've a table where I want a UPDATE performed if the primary key that
should be inserted already exists, but which performs a INSERT if the
primary key does not exist. It doesn't really work. Here's what I have:

CREATE RULE nonexistent_insert_crit_hosts AS
ON UPDATE TO crit_hosts WHERE
(new.host) NOT IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
INSERT INTO crit_hosts (host, properties) VALUES (new.host,
new.properties);

CREATE RULE duplicate_update_crit_hosts AS
ON INSERT TO crit_hosts WHERE
(new.host) IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
UPDATE crit_hosts SET
properties = new.properties
WHERE host = new.host;

Upon UPDATE with a non-existing primary key leads to:

ERROR: infinite recursion spotted in rules for relation »crit_hosts«
' in 'UPDATE crit_hosts SET properties = properties |
B'00000000000000000000000000000001' WHERE host = '192.168.1.3';'

(the error message is translated from German so it might well be a
little different in the original)

What am I doing wrong?
on INSERT you do UPDATE, on UPDATE do INSERT, on INSERT do UPDATE, on
UPDATE DO INSERT ... WHERE clause is not a help for postgresql.
Quote:
Thanks in advance,
Johannes

If you want do such thing create a view on a table and create rules on
it, but inserts and updates you must do to base table.

Lukasz



Reply With Quote
  #6  
Old   
Łukasz Filut
 
Posts: n/a

Default Re: RULE: UPDATE on INSERT, but INSERT on UPDATE - 07-24-2008 , 02:32 AM


Johannes Bauer pisze:
Quote:
Hello list,

I've a table where I want a UPDATE performed if the primary key that
should be inserted already exists, but which performs a INSERT if the
primary key does not exist. It doesn't really work. Here's what I have:

CREATE RULE nonexistent_insert_crit_hosts AS
ON UPDATE TO crit_hosts WHERE
(new.host) NOT IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
INSERT INTO crit_hosts (host, properties) VALUES (new.host,
new.properties);

CREATE RULE duplicate_update_crit_hosts AS
ON INSERT TO crit_hosts WHERE
(new.host) IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
UPDATE crit_hosts SET
properties = new.properties
WHERE host = new.host;

Upon UPDATE with a non-existing primary key leads to:

ERROR: infinite recursion spotted in rules for relation »crit_hosts«
' in 'UPDATE crit_hosts SET properties = properties |
B'00000000000000000000000000000001' WHERE host = '192.168.1.3';'

(the error message is translated from German so it might well be a
little different in the original)

What am I doing wrong?
on INSERT you do UPDATE, on UPDATE do INSERT, on INSERT do UPDATE, on
UPDATE DO INSERT ... WHERE clause is not a help for postgresql.
Quote:
Thanks in advance,
Johannes

If you want do such thing create a view on a table and create rules on
it, but inserts and updates you must do to base table.

Lukasz



Reply With Quote
  #7  
Old   
Łukasz Filut
 
Posts: n/a

Default Re: RULE: UPDATE on INSERT, but INSERT on UPDATE - 07-24-2008 , 02:32 AM


Johannes Bauer pisze:
Quote:
Hello list,

I've a table where I want a UPDATE performed if the primary key that
should be inserted already exists, but which performs a INSERT if the
primary key does not exist. It doesn't really work. Here's what I have:

CREATE RULE nonexistent_insert_crit_hosts AS
ON UPDATE TO crit_hosts WHERE
(new.host) NOT IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
INSERT INTO crit_hosts (host, properties) VALUES (new.host,
new.properties);

CREATE RULE duplicate_update_crit_hosts AS
ON INSERT TO crit_hosts WHERE
(new.host) IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
UPDATE crit_hosts SET
properties = new.properties
WHERE host = new.host;

Upon UPDATE with a non-existing primary key leads to:

ERROR: infinite recursion spotted in rules for relation »crit_hosts«
' in 'UPDATE crit_hosts SET properties = properties |
B'00000000000000000000000000000001' WHERE host = '192.168.1.3';'

(the error message is translated from German so it might well be a
little different in the original)

What am I doing wrong?
on INSERT you do UPDATE, on UPDATE do INSERT, on INSERT do UPDATE, on
UPDATE DO INSERT ... WHERE clause is not a help for postgresql.
Quote:
Thanks in advance,
Johannes

If you want do such thing create a view on a table and create rules on
it, but inserts and updates you must do to base table.

Lukasz



Reply With Quote
  #8  
Old   
Łukasz Filut
 
Posts: n/a

Default Re: RULE: UPDATE on INSERT, but INSERT on UPDATE - 07-24-2008 , 02:32 AM


Johannes Bauer pisze:
Quote:
Hello list,

I've a table where I want a UPDATE performed if the primary key that
should be inserted already exists, but which performs a INSERT if the
primary key does not exist. It doesn't really work. Here's what I have:

CREATE RULE nonexistent_insert_crit_hosts AS
ON UPDATE TO crit_hosts WHERE
(new.host) NOT IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
INSERT INTO crit_hosts (host, properties) VALUES (new.host,
new.properties);

CREATE RULE duplicate_update_crit_hosts AS
ON INSERT TO crit_hosts WHERE
(new.host) IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
UPDATE crit_hosts SET
properties = new.properties
WHERE host = new.host;

Upon UPDATE with a non-existing primary key leads to:

ERROR: infinite recursion spotted in rules for relation »crit_hosts«
' in 'UPDATE crit_hosts SET properties = properties |
B'00000000000000000000000000000001' WHERE host = '192.168.1.3';'

(the error message is translated from German so it might well be a
little different in the original)

What am I doing wrong?
on INSERT you do UPDATE, on UPDATE do INSERT, on INSERT do UPDATE, on
UPDATE DO INSERT ... WHERE clause is not a help for postgresql.
Quote:
Thanks in advance,
Johannes

If you want do such thing create a view on a table and create rules on
it, but inserts and updates you must do to base table.

Lukasz



Reply With Quote
  #9  
Old   
Łukasz Filut
 
Posts: n/a

Default Re: RULE: UPDATE on INSERT, but INSERT on UPDATE - 07-24-2008 , 02:32 AM


Johannes Bauer pisze:
Quote:
Hello list,

I've a table where I want a UPDATE performed if the primary key that
should be inserted already exists, but which performs a INSERT if the
primary key does not exist. It doesn't really work. Here's what I have:

CREATE RULE nonexistent_insert_crit_hosts AS
ON UPDATE TO crit_hosts WHERE
(new.host) NOT IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
INSERT INTO crit_hosts (host, properties) VALUES (new.host,
new.properties);

CREATE RULE duplicate_update_crit_hosts AS
ON INSERT TO crit_hosts WHERE
(new.host) IN (SELECT host FROM crit_hosts WHERE host = new.host)
DO INSTEAD
UPDATE crit_hosts SET
properties = new.properties
WHERE host = new.host;

Upon UPDATE with a non-existing primary key leads to:

ERROR: infinite recursion spotted in rules for relation »crit_hosts«
' in 'UPDATE crit_hosts SET properties = properties |
B'00000000000000000000000000000001' WHERE host = '192.168.1.3';'

(the error message is translated from German so it might well be a
little different in the original)

What am I doing wrong?
on INSERT you do UPDATE, on UPDATE do INSERT, on INSERT do UPDATE, on
UPDATE DO INSERT ... WHERE clause is not a help for postgresql.
Quote:
Thanks in advance,
Johannes

If you want do such thing create a view on a table and create rules on
it, but inserts and updates you must do to base table.

Lukasz



Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top