-- levels, parents, ancestors, children, titles
\set VERBOSITY terse
\set SHOW_CONTEXT never
DROP SCHEMA IF EXISTS t CASCADE;
NOTICE:  drop cascades to 6 other objects
DROP EXTENSION IF EXISTS codesystem;
CREATE EXTENSION codesystem;
CREATE SCHEMA t;
SET search_path = t, public;
CREATE TABLE stg (code text, level text, parent text, label text);
INSERT INTO stg VALUES
 ('A','section','','Agriculture'), ('B','section','','Mining'),
 ('01','division','A','Crop'), ('02','division','A','Forestry'), ('05','division','B','Coal'),
 ('011','group','01','Annual crops'), ('012','group','01','Perennial crops'), ('021','group','02','Silviculture'), ('051','group','05','Hard coal'),
 ('0111','class','011','Cereals'), ('0112','class','011','Rice'), ('0121','class','012','Grapes'), ('0210','class','021','Silviculture'), ('0510','class','051','Hard coal');
SELECT define_code_system('toy', '[{"name":"section","width":1,"charset":"[A-Z]","link":"none"},{"name":"division","width":2,"charset":"[0-9]","link":"grouping"},{"name":"group","width":3,"charset":"[0-9]","link":"prefix"},{"name":"class","width":4,"charset":"[0-9]","link":"prefix"}]', 'SELECT * FROM stg');
 define_code_system 
--------------------
                  1
(1 row)

SELECT c, code_level(c), code_parent(c), code_label(c) FROM (VALUES ('toy:A'::code), ('toy:01'), ('toy:011'), ('toy:0111')) v(c);
    c     | code_level | code_parent |  code_label  
----------+------------+-------------+--------------
 toy:A    | section    |             | Agriculture
 toy:01   | division   | toy:A       | Crop
 toy:011  | group      | toy:01      | Annual crops
 toy:0111 | class      | toy:011     | Cereals
(4 rows)

SELECT code_ancestor('toy:0111', 'class'), code_ancestor('toy:0111', 'group'), code_ancestor('toy:0111', 'division'), code_ancestor('toy:0111', 'section');
 code_ancestor | code_ancestor | code_ancestor | code_ancestor 
---------------+---------------+---------------+---------------
 toy:0111      | toy:011       | toy:01        | toy:A
(1 row)

SELECT code_ancestor('toy:01', 'class');
 code_ancestor 
---------------
 
(1 row)

SELECT code_ancestor('toy:01', 'nosuchlevel');
ERROR:  toy has no level "nosuchlevel"
SELECT code_children('toy:A');
 code_children 
---------------
 toy:01
 toy:02
(2 rows)

SELECT code_children('toy:011');
 code_children 
---------------
 toy:0111
 toy:0112
(2 rows)

SELECT count(*) FROM code_children('toy:0111');
 count 
-------
     0
(1 row)

-- the division's section is a grouping, not a prefix
SELECT code_parent('toy:05'), code_parent('toy:0510');
 code_parent | code_parent 
-------------+-------------
 toy:B       | toy:051
(1 row)

CREATE TABLE firms (name text, activity code('toy'));
INSERT INTO firms VALUES ('a', 'toy:0111');
INSERT INTO firms VALUES ('b', 'toy:0112');
INSERT INTO firms VALUES ('c', 'toy:0121');
INSERT INTO firms VALUES ('d', 'toy:0510');
INSERT INTO firms VALUES ('e', 'toy:0210');
SELECT code_ancestor(activity, 'division') AS division, code_label(code_ancestor(activity, 'division')) AS title, count(*)
  FROM firms GROUP BY 1 ORDER BY 1;
 division |  title   | count 
----------+----------+-------
 toy:01   | Crop     |     3
 toy:02   | Forestry |     1
 toy:05   | Coal     |     1
(3 rows)

SELECT code_ancestor(activity, 'section')::text AS section, count(*) FROM firms GROUP BY 1 ORDER BY 1;
 section | count 
---------+-------
 A       |     4
 B       |     1
(2 rows)