PL/pgSQL函数

数据准备

SELECT create_graph('imdb');
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
    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 rows)

动态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
        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: No Way Home"
 "Toby Maguire" | "Spiderman"
 "Tom Holland"  | "Spiderman: Homecoming"
 "Tom Holland"  | "Spiderman: No Way Home"
(4 rows)