SQL unlimited depth recursive Chart of Accounts, postgres version

[Wm]

Here is a (for practical purposes, it works with 10 levels) unlimited
depth recursive Chart of Accounts in postgres.  It shouldn't need much
done to it to work in sqlite (ask if you're interested, I have sqlite
handy) but will require non-trivial fiddling to work in mysql if my
reading is correct as mysql doesn't have WITH RECURSIVE at the moment.

===
CREATE OR REPLACE VIEW CoA AS
 WITH RECURSIVE tree(guid, parent_guid, name, name_tree, name_tabs,
account_type, depth) AS (
         SELECT accounts.guid,
            accounts.parent_guid,
            accounts.name,
            ''::text || accounts.name::text AS name_tree,
            ''::text AS name_tabs,
            accounts.account_type,
            0 AS depth
           FROM accounts
          WHERE accounts.parent_guid IS NULL
          AND accounts.name::text <> 'Template Root'::text
        UNION ALL
         SELECT a.guid,
            a.parent_guid,
            a.name,
            (tree_1.name_tree || ':'::text) || a.name::text AS
name_tree,
            lpad(''::text, tree_1.depth * 4) || a.name::text AS
name_tabs,
            a.account_type,
            tree_1.depth + 1 AS depth
           FROM tree tree_1
             JOIN accounts a ON tree_1.guid::text = a.parent_guid::text
        )
 SELECT tree.guid,
    tree.parent_guid,
    tree.name,
    tree.name_tree,
    tree.name_tabs,
    tree.account_type,
    tree.depth
   FROM tree
  ORDER BY tree.name_tree;
===

This can be used to produce nice presentable balance sheets quite easily
because both indented and full account names are included.

Interesting or not?
-- 
Wm...