PostgreSQL UPDATE on VIEW multiple tables

Question

i have a VIEW in PostgreSQL which consists of 2 similar tables.

Basically both tables look like this:

id | data
---+------
 1 | foo
 2 | bar
[...]

Now in the VIEW, I have them with this command:

CREATE RULE "_RETURN" AS ON SELECT TO myView DO INSTEAD
SELECT id, data
  FROM table1
  UNION ALL
SELECT (id + (SELECT MAX(id) FROM table1)),   
    data
  FROM table2;

The result is a combination of both tables for means of data integration

id | data
---+------
 1 | foo
 2 | bar
 3 | foo
 4 | bar
[...]

Now I want to perform UPDATEs (and subsequently also DELETEs) on that VIEW. The Intention is to forward the operations to the tables, to which the data set belongs to.

At the moment I have something link this:

CREATE RULE "_UPDATE0"
  AS ON UPDATE TO users 
  DO INSTEAD NOTHING;

PostgreSQL requires an unconditional rule, this is "_UPDATE0"

CREATE RULE "_UPDATE1" 
  AS ON UPDATE TO users 
  WHERE NEW.id <= (SELECT MAX(table1.id) from table1) 
  DO ALSO
  (
    UPDATE table1 SET 
      data = NEW.data,
    WHERE table1.id = NEW.id
  );

CREATE RULE "_UPDATE2" 
  AS ON UPDATE TO myView
  WHERE NEW.id > (SELECT MAX(table1.id) from table1) 
  DO ALSO
  (
    UPDATE table2 SET 
      data = NEW.data,
    WHERE table2.id IN 
      (SELECT NEW.id - MAX(table1.id) from table1)
  );

_UPDATE1 works as intented and forwards UPDATEs on myView.id <= 2 to table 1.

_UPDATE2, seems not to work at all.

Edit: After further fiddeling, I think I can pinpoint it in _UPDATE2 to the part where I want to subtract the offset of the ID and use that in my WHERE clause.

Any ideas or explanations? What am I doing wrong?

Without trying to get into rules right away (which is a nest of snakes), why don't you define your view as SELECT 2*id, ... FROM table1 UNION SELECT 2*id+1, ... FROM table2? That would do away with the nasty subselect. Try the rules without condition and see if that works (if no row is updated/deleted, that's ok). — Laurenz Albe
– Laurenz Albe, Commented May 16, 2017 at 15:53
@LaurenzAlbe The VIEW must be this way, because I want to preserve the IDs of the first table for referencing other tables. Without condition before the DO ALSO, it updates data sets both tables. Without condition in the UPDATE, it updates ALL data sets in both tables. — and0x000
– and0x000, Commented May 16, 2017 at 16:40
Rules are discouraged, use an instead of that is much more flexible — user330315
– user330315, Commented May 16, 2017 at 18:18
@a_horse_with_no_name yes, i read that in a few other questions here. But I am stuck with that issue for 2 days now and even if I were to attempt it with triggers by tomorrow, it would be nice to have a more sophisticated explanation what I was doing wrong or why it is not possible the way I tried. — and0x000
– and0x000, Commented May 16, 2017 at 18:46

and0x000 · Accepted Answer · 2017-05-16 19:51:35Z

3

OK, found a working solution for my _UPDATE2.

I dragged the NEW.id out of the subselect.

CREATE RULE "_UPDATE2" 
  AS ON UPDATE TO myView
  WHERE NEW.id > (SELECT MAX(table1.id) from table1) 
  DO ALSO
  (
    UPDATE table2 SET 
      data = NEW.data,
    WHERE table2.id = NEW.id - (SELECT MAX(table1.id) from table1)
  );

However, I do not know why this works. Further explanation appreciated.

answered May 16, 2017 at 19:51

and0x000

841 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL UPDATE on VIEW multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related