How to delete a tree node and its childs records from a table (without cascade delete)?

Question

I have this table in postgreSQL v9.1:

CREATE TABLE ad_treenodemm
(
  ad_tree_id numeric(10,0) NOT NULL,
  node_id numeric(10,0) NOT NULL,
  ad_client_id numeric(10,0) NOT NULL,
  ad_org_id numeric(10,0) NOT NULL,
  name character varying(60) NOT NULL,
  isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
  created timestamp without time zone NOT NULL DEFAULT now(),
  createdby numeric(10,0) NOT NULL,
  updated timestamp without time zone NOT NULL DEFAULT now(),
  updatedby numeric(10,0) NOT NULL,
  parent_id numeric(10,0),
  seqno numeric(10,0),
  CONSTRAINT ad_treenodemm_pkey PRIMARY KEY (ad_tree_id , node_id ),
  CONSTRAINT adtree_adtreenodemm FOREIGN KEY (ad_tree_id)
      REFERENCES adempiere.ad_tree (ad_tree_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT ad_treenodemm_isactive_check CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))
)

Important columns description:
* ad_tree_id = The tree group id (connected to ad_tree table)
* node_id = The node id
* parent_id = The parent node id (if 0 => means the node is on the top)

The rests of columns can be ignored.

For example, I have the ad_treenodemm table data presentation like this:

# Group1 (all node belows are assigned with ad_tree_id=1001)
    -Accounting (node_id=101, parent_id=0)
        -Costing (node_id=202, parent_id=101)
            -Cost Type (node_id=103, parent_id=202)
            -Cost Element (node_id=24, parent_id=202)
        -Client Accounting Processor (node_id=105, parent_id=101)
        -Reset Accounting (node_id=6, parent_id=101)
            ...

    -Finance (node_id=4110, parent_id=0)
        ...

# Group2 (all node belows are assigned with ad_tree_id=1002)
    ...

Let's say, I want to delete Accounting node and its child nodes in the Group1. That means, it also deletes node: Costing, Cost Type, Cost Element, Reset Accounting,...etc. How to do it?

The solution can be in SQL or Java language with JDBC (but SQL would be preferred if possible).

UPDATE: I found a solution with WITH RECURSIVE (CTE) sql, however it's not too elegant:

WITH RECURSIVE temp(ad_tree_id, node_id, parent_id) AS (
    SELECT a.ad_tree_id, a.node_id, a.parent_id
    FROM ad_treenodemm a 
    WHERE ad_tree_id=1001 AND node_id=101      -- look at this

    UNION ALL

    SELECT b.ad_tree_id, b.node_id, b.parent_id
    FROM ad_treenodemm b
    INNER JOIN temp c on c.node_id = b.parent_id
    WHERE b.ad_tree_id=c.ad_tree_id
)
DELETE FROM ad_treenodemm a
WHERE (a.ad_tree_id, a.node_id) IN (
    SELECT ad_tree_id, node_id FROM temp
);

You see that I put the argument ( WHERE ad_tree_id=1001 AND node_id=101) inside the WITH clause. Anyone know how to improve the SQL by putting the argument statement outside the WITH clause?

For anyone who want to experiment the query without deleting the records, use this:

WITH RECURSIVE temp(ad_tree_id, node_id, parent_id) AS (
    SELECT a.ad_tree_id, a.node_id, a.parent_id
    FROM ad_treenodemm a 
    WHERE ad_tree_id=1001 AND node_id=101

    UNION ALL

    SELECT b.ad_tree_id, b.node_id, b.parent_id
    FROM ad_treenodemm b
    INNER JOIN temp c on c.node_id = b.parent_id
    WHERE b.ad_tree_id=c.ad_tree_id
)
SELECT * FROM ad_treenodemm a
WHERE (a.ad_tree_id, a.node_id) IN (
    SELECT ad_tree_id, node_id FROM temp
)
ORDER BY a.parent_id, a.node_id

Answer 1

Adding a FK constraint would qualify as "altering the table structure". Since you don't want that, we are down to a recursive query or a function doing the work recursively.

Without such restrictions, the most elegant solution would be to fix the null values you mentioned and add a NOT NULL constraint to the column. Then add the FK constraint with ON DELETE CASCADE as mentioned in comments, first by lc.

Recursive query

DELETE with a "writeable" CTE:

WITH RECURSIVE x AS (
   SELECT ad_tree_id, node_id
   FROM   ad_treenodemm
   WHERE (ad_tree_id, node_id) = (1,5)  -- enter dead node walking here

   UNION  ALL
   SELECT a.ad_tree_id, a.node_id
   FROM   x
   JOIN   ad_treenodemm a ON a.parent_id = x.node_id
   )
DELETE FROM ad_treenodemm a
USING  x
WHERE (a.ad_tree_id, a.node_id)
    = (x.ad_tree_id, x.node_id);

Data-modifying CTEs require PostgreSQL 9.1 or later. Else you have to run a separate SELECT to collect the rows and then DELETE.

fiddle
_{Old sqlfiddle}