postgresql 这个查询中的变量的正确类型转换是什么?

lxkprmvk  于 2023-05-06  发布在  PostgreSQL
关注(0)|答案(1)|浏览(93)

我把这个密码查询写成了一个函数:

CREATE OR REPLACE FUNCTION public.count_friends(name agtype) 
    RETURNS agtype AS $function$
    BEGIN
        SELECT * FROM cypher('graph_name', $$
        MATCH (v:Person)-[]-(w)
        WHERE v.name = name
        RETURN COUNT(w) AS friends
    $$) AS (friends agtype);
    END;
$function$ LANGUAGE plpgsql;

我使用这个select查询进行测试:

SELECT * FROM cypher('graph_name', $$
        MATCH (v:Person)
      WHERE public.count_friends(v.name) > 3
      RETURN v.name
$$) AS (name agtype);

但是,我在执行它时遇到了一个错误:

ERROR:  could not find rte for name
LINE 3:         WHERE v.name = name

在这种情况下,变量名的正确类型转换是什么?还是我的代码有问题?
环境:Apache AGE 1.3.0版本、PostgreSQL 13.10、Ubuntu 22.04

pcww981p

pcww981p1#

您得到的错误可能是因为在WHERE子句中比较的用于测试的数字可能高于可用顶点的数量(WHERE public.count_friends(v.name) > 3)。
首先,我创建了一个图来存储具有name属性的Person顶点。

demo=# SELECT create_graph('graph_name');
NOTICE:  graph "graph_name" has been created
 create_graph 
--------------
 
(1 row)

demo=# SELECT * FROM cypher('graph_name', $$
CREATE (a:Person {name: 'Wendel'}), (b:Person {name: 'Matheus'})
RETURN a, b
$$) AS (a agtype, b agtype);
                                          a                                           |                                           b                                           
--------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "Person", "properties": {"name": "Wendel"}}::vertex | {"id": 844424930131970, "label": "Person", "properties": {"name": "Matheus"}}::vertex
(1 row)

在此之后,创建了函数:

CREATE OR REPLACE FUNCTION public.count_friends(name agtype) 
    RETURNS agtype AS $function$
    BEGIN
        SELECT * FROM cypher('graph_name', $$
        MATCH (v:Person)-[]-(w)
        WHERE v.name = name
        RETURN COUNT(w) AS friends
    $$) AS (friends agtype);
    END;
$function$ LANGUAGE plpgsql;

然后执行不带WHERE子句的测试查询:

demo=# SELECT * FROM cypher('graph_name', $$
      MATCH (v:Person)
      RETURN v.name                    
$$) AS (name agtype);

   name    
-----------
 "Wendel"
 "Matheus"
(2 rows)

这样很好。

相关问题