PL/pgSQL Functions

Cypher commands can be run in PL/pgSQL functions without restriction.

Data Setup

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);

Function Creation

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$;

Query:

SELECT * FROM get_all_actor_names();

Results

actor

"Toby Maguire"

"Tom Holland"

"Willam Dafoe"

"Robert Downey Jr"

4 row(s) returned

Developer's Note:

It's recommended that users use the LOAD 'age' command and set the search_path in the function declaration, to ensure the CREATE FUNCTION command works consistently.

Dynamic 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"');

Results

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