CREATE TABLE
  public."Page" (
    id serial NOT NULL,
    station_id integer NULL,
    name character varying(512) NOT NULL,
    owner character varying(256) NOT NULL,
    ts_modified timestamp with time zone NOT NULL,
    context jsonb NULL
  );

ALTER TABLE
  public."Page"
ADD
  CONSTRAINT page_pkey PRIMARY KEY (id)


CREATE TABLE
  public."Component" (
    id serial NOT NULL,
    global_uuid uuid NOT NULL,
    visible_id character varying(1024) NOT NULL,
    name character varying(1024) NOT NULL,
    description character varying(4096) NULL,
    grid character varying(512) NULL,
    zone character varying(512) NULL,
    station character varying(512) NULL,
    type integer NULL,
    in_service boolean NULL DEFAULT false,
    state integer NULL DEFAULT 0,
    connected_bus integer NULL,
    context jsonb NULL,
    comment character varying(4096) NULL
  );

ALTER TABLE
  public."Component"
ADD
  CONSTRAINT "Component_pkey" PRIMARY KEY (global_uuid)





INSERT INTO public.circuit_diagram_1(id,name,created_at,updated_at,parent_id,other_params) VALUES(1,'母线','2024-11-07 09:37:00','2024-11-07 09:37:00',0,'voltage:35,status:0');

INSERT INTO public.circuit_diagram_1(id,name,created_at,updated_at,parent_id,other_params) VALUES(2,'电流互感器','2024-11-07 09:37:00','2024-11-07 09:37:00',1,'voltage:35,status:0');

INSERT INTO public.circuit_diagram_1(id,name,created_at,updated_at,parent_id,other_params) VALUES(3,'过继电流器','2024-11-07 09:37:00','2024-11-07 09:37:00',1,'voltage:35,status:0');

INSERT INTO public.circuit_diagram_1(id,name,created_at,updated_at,parent_id,other_params) VALUES(4,'断路器','2024-11-07 09:37:00','2024-11-07 09:37:00',2,'voltage:35,status:0');

INSERT INTO public.circuit_diagram_1(id,name,created_at,updated_at,parent_id,other_params) VALUES(5,'变压器','2024-11-07 09:37:00','2024-11-07 09:37:00',4,'voltage:35,status:0');


INSERT INTO public.circuit_diagram_1(id,name,created_at,updated_at,parent_id,other_params) VALUES(6,'线缆','2024-11-07 09:37:00','2024-11-07 09:37:00',5,'voltage:35,status:0');

INSERT INTO public.circuit_diagram_1(id,name,created_at,updated_at,parent_id,other_params) VALUES(7,'低压母线','2024-11-07 09:37:00','2024-11-07 09:37:00',6,'voltage:35,status:0');

INSERT INTO public.circuit_diagram_1(id,name,created_at,updated_at,parent_id,other_params) VALUES(8,' 电动机 1','2024-11-07 09:37:00','2024-11-07 09:37:00',7,'voltage:35,status:0');

INSERT INTO public.circuit_diagram_1(id,name,created_at,updated_at,parent_id,other_params) VALUES(9,' 电动机 2','2024-11-07 09:37:00','2024-11-07 09:37:00',7,'voltage:35,status:0');


SELECT id,parent_id,name,other_params
FROM circuit_diagram_1
WHERE id = 1


WITH RECURSIVE recursive_tree as (
SELECT id,parent_id,name,other_params
FROM circuit_diagram_1
WHERE id = 1
UNION ALL
SELECT ctd.id,ctd.parent_id,ctd.name,ctd.other_params
FROM circuit_diagram_1 ctd 
JOIN recursive_tree rt ON ctd.parent_id = rt.id
)
SELECT * FROM recursive_tree;