Release History
Help

YMatrix

Quick Start

Deployment

Data Usage

Manage Clusters

Security

Best Practice

Technical Principles

Advanced Features

Graph Database

Guide

SQL Reference

FAQ

PL/pgSQL Functions

Cypher commands can be executed in a limited manner within PL/pgSQL functions.

Note!
In YMatrix AGE, statements such as CREATE, DELETE, and SET are not supported.

Sample 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 Execution

SELECT * FROM get_all_actor_names();

Result

actor
"Toby Maguire"
"Tom Holland"
"Willam Dafoe"
"Robert Downey Jr"
4 row(s) returned 
Developer Note:
We recommend including the `LOAD 'age'` command and setting `search_path` in the function body to ensure that `CREATE FUNCTION` always succeeds.

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

Result

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
← Previous
Using Cypher in a CTE/JOIN/SQL Expression
Next →
Prepared Statements