postgresql图数据如何存储_postgresql图结构方案介绍

CREATE TABLE nodes (
  id SERIAL PRIMARY KEY,
  label TEXT,
  properties JSONB
);
<p>CREATE TABLE edges (
id SERIAL PRIMARY KEY,
source_id INT REFERENCES nodes(id),
target_id INT REFERENCES nodes(id),
relation_type TEXT,
weight DECIMAL,
properties JSONB
);</p>

WITH RECURSIVE graph_traversal AS (
  -- 起始点
  SELECT 
    n.id, 
    n.label, 
    e.target_id AS next_id, 
    1 AS depth,
    ARRAY[n.id] AS path
  FROM nodes n
  LEFT JOIN edges e ON n.id = e.source_id
  WHERE n.id = 1  -- 起始节点
<p>UNION ALL</p>
                    <div class="aritcle_card">
                        <a class="aritcle_card_img" href="/ai/2066">
                            <img src="https://img.php.cn/upload/ai_manual/000/000/000/175680175078452.png" alt="Kive">
                        </a>
                        <div class="aritcle_card_info">
                            <a href="/ai/2066">Kive</a>
                            <p>一站式AI图像生成和管理平台</p>
                            <div class="">
                                <img src="/static/images/card_xiazai.png" alt="Kive">
                                <span>171</span>
                            </div>
                        </div>
                        <a href="/ai/2066" class="aritcle_card_btn">
                            <span>查看详情</span>
                            <img src="/static/images/cardxiayige-3.png" alt="Kive">
                        </a>
                    </div>
                <p>-- 递归部分
SELECT 
g.next_id AS id,
n.label,
e.target_id,
g.depth + 1,
g.path || n.id
FROM graph_traversal g
JOIN nodes n ON g.next_id = n.id
JOIN edges e ON n.id = e.source_id
WHERE g.depth < 5  -- 控制深度
AND NOT (n.id = ANY(g.path))  -- 避免环路
)
SELECT * FROM graph_traversal;</p>

SELECT * FROM cypher('graph_name', $$
  MATCH (n)-[r]->(m)
  WHERE n.name = 'Alice'
  RETURN n, r, m
$$) AS (from vertex, rel edge, to vertex);