title: How to write a delete rule on a view?
body: <p>I'm trying to write a rule on a view to delete tuples from the component tables, but so far can only remove data from one of them.  I've used postgres with basic views for a while, but I don't have any experience with rules on views.</p>
  
  <p>I wrote a stupid little test case to figure out/show my problem. There's only one parent tuple per child tuple in this example (my actual schema isn't actually like this of course).</p>
  
  <p><strong>Component tables:</strong></p>
  
  <pre><code>CREATE TABLE parent(
     id serial PRIMARY KEY,
     p_data integer NOT NULL UNIQUE
  );
  CREATE TABLE child(
     id serial PRIMARY KEY,
     parent_id integer NOT NULL UNIQUE REFERENCES parent(id),
     c_data integer NOT NULL
  );
  </code></pre>
  
  <p><strong>View:</strong></p>
  
  <pre><code>CREATE TABLE child_view(
     id integer,
     p_data integer,
     c_data integer
  );
  CREATE RULE "_RETURN" AS ON SELECT TO child_view DO INSTEAD
     SELECT child.id, p_data, c_data 
        FROM parent JOIN child ON (parent_id=parent.id);
  </code></pre>
  
  <p><strong>Problem delete rule</strong></p>
  
  <pre><code>CREATE RULE child_delete AS ON DELETE TO child_view DO INSTEAD(
     DELETE FROM child WHERE id=OLD.id;
     DELETE FROM parent WHERE p_data=OLD.p_data;
  );
  </code></pre>
  
  <p>The intent of the above rule is to remove tuples referenced in the view from the component tables.  The <code>WHERE p_data=OLD.p_data</code> seems odd to me, but I don't see how else to reference the desired tuple in the parent table.</p>
  
  <p>Here's what happens when I try to use the above rule:</p>
  
  <pre><code>&gt;SELECT * FROM child_view;
   id | p_data | c_data 
  ----+--------+--------
    1 |      1 |     10
    2 |      2 |     11
    3 |      3 |     12
  (3 rows)
  
  &gt;DELETE FROM child_view WHERE id=3;
  DELETE 0
  
  &gt;SELECT * FROM child_view;
   id | p_data | c_data 
  ----+--------+--------
    1 |      1 |     10
    2 |      2 |     11
  (2 rows)
  </code></pre>
  
  <p>But looking at the parent table, the second part of the delete isn't working (id=3 "should" have been deleted):</p>
  
  <pre><code>&gt;SELECT * FROM parent;
   id | p_data 
  ----+--------
    1 |      1
    2 |      2
    3 |      3
  (3 rows)
  </code></pre>
  
  <p>How should I write the deletion rule to remove both child and parent tuples?</p>
  
  <p>This is using postgres v9. </p>
  
  <p>Any help is appreciated.  Also pointers to any materials covering rules on views beyond the postgres docs (unless I've obviously missed something) would also be appreciated. Thanks.</p>
  
  <p>EDIT: as jmz points out, it would be easier to use a cascading delete than a rule here, but that approach doesn't work for my actual schema.</p>