版本历史
寻求帮助

关于 YMatrix

立即体验

部署数据库

使用数据库

管理集群

安全配置

最佳实践

技术原理

高级功能

图数据库

管理手册

SQL 参考

常见问题

PL/pgSQL 函数

Cypher 命令可以在 PL/pgSQL 函数中有限制地运行。

注意！
在 YMatrix AGE 中，不能使用 CREATE、DELETE、SET 等语句。

数据准备

SELECT *
FROM cypher('imdb', $$
        CREATE (toby:actor {name: 'Toby Maguire'}),
                (tom:actor {name: 'Tom Holland'}),
                (willam:actor {name: 'Willam Dafoe'}),
                (robert:actor {name: 'Robert Downey Jr'}),
                (spiderman:movie {title: 'Spiderman'}),
                (no_way_home:movie {title: 'Spiderman: No Way Home'}),
                (homecoming:movie {title: 'Spiderman: Homecoming'}),
                (ironman:movie {title: 'Ironman'}),
                (tropic_thunder:movie {title: 'Tropic Thunder'}),
                (toby)-[:acted_in {role: 'Peter Parker', alter_ego: 'Spiderman'}]->(spiderman),
                (willam)-[:acted_in {role: 'Norman Osborn', alter_ego: 'Green Goblin'}]->(spiderman),
                (toby)-[:acted_in {role: 'Toby Maguire'}]->(tropic_thunder),
                (robert)-[:acted_in {role: 'Kirk Lazarus'}]->(tropic_thunder),
                (robert)-[:acted_in {role: 'Tony Stark', alter_ego: 'Ironman'}]->(homecoming),
                (tom)-[:acted_in {role: 'Peter Parker', alter_ego: 'Spiderman'}]->(homecoming),
                (tom)-[:acted_in {role: 'Peter Parker', alter_ego: 'Spiderman'}]->(no_way_home),
                (toby)-[:acted_in {role: 'Peter Parker', alter_ego: 'Spiderman'}]->(no_way_home),
                (willam)-[:acted_in {role: 'Norman Osborn', alter_ego: 'Green Goblin'}]->(no_way_home)
$$) AS (a agtype);

函数创建

CREATE OR REPLACE FUNCTION get_all_actor_names()
RETURNS TABLE(actor agtype)
LANGUAGE plpgsql
AS $BODY$
BEGIN
    LOAD 'age';
    SET search_path TO ag_catalog;

    RETURN QUERY
    SELECT *
    FROM ag_catalog.cypher('imdb', $$
        MATCH (v:actor)
        RETURN v.name
    $$) AS (a agtype);
END
$BODY$;

查询：

SELECT * FROM get_all_actor_names();

结果：

actor
"Toby Maguire"
"Tom Holland"
"Willam Dafoe"
"Robert Downey Jr"
4 row(s) returned

注意! 建议用户在函数声明中使用 LOAD 'age' 命令并设置 search_path，以确保 CREATE FUNCTION 命令始终正常工作。

动态 Cypher

CREATE OR REPLACE FUNCTION get_actors_who_played_role(role agtype)
RETURNS TABLE(actor agtype, movie agtype)
LANGUAGE plpgsql
AS $function$
DECLARE sql VARCHAR;
BEGIN
        load 'age';
        SET search_path TO ag_catalog;

        sql := format('
                SELECT *
                FROM cypher(''imdb'', $$
                        MATCH (actor)-[:acted_in {role: %s}]->(movie:movie)
                        RETURN actor.name, movie.title
                $$) AS (actor agtype, movie agtype);
        ', role);

        RETURN QUERY EXECUTE sql;

END
$function$;
SELECT * FROM get_actors_who_played_role('"Peter Parker"');

结果：

actor movie
"Toby Maguire" "Spiderman"
"Toby Maguire" "Spiderman: No Way Home"
"Tom Holland" "Spiderman: No Way Home"
"Tom Holland" "Spiderman: Homecoming"
4 row(s) returned
← 上一篇
在 CTE/JOIN/SQL 表达式中使用 Cypher
下一篇 →
预处理语句