Understand Cypher Query Syntax and the cypher() Function in PolarDB - PolarDB

Cypher queries run through the cypher() function in ag_catalog, which returns a SETOF record.

cypher()

The cypher() function executes a Cypher query against a named graph.

Syntax

cypher(graph_name, query_string, parameters)

Parameters

Parameter	Description
`graph_name`	The name of the graph to query.
`query_string`	The Cypher query to execute.
`parameters`	An optional map of parameters for prepared statements. Default: `NULL`.

Note

Even when the Cypher query returns no results, a column definition must still be provided in the AS clause.

Return value

Returns a set of records. Define the output columns in the AS clause using agtype.

Example

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

Dollar-quoting with nested `$$`

If the Cypher query itself contains $$, use a named dollar-quote delimiter instead:

SELECT * FROM cypher('graph_name', $my_cypher$
    /* Cypher query that contains $$ */
$my_cypher$) AS (result1 agtype, result2 agtype);

Limitations

Cypher in expressions

Cypher cannot be used directly inside a SQL expression. Use a subquery instead.

The following query is invalid:

SELECT
    cypher('graph_name', $$
         MATCH (v:Person)
         RETURN v.name
     $$);

Result:

ERROR:  cypher(...) in expressions is not supported
LINE 3: 	cypher('graph_name', $$
            	^
HINT:  Use subquery instead if possible.

To reference Cypher results in an expression, wrap the call in a subquery or a common table expression (CTE):

-- Use a CTE to query and filter graph results in SQL
WITH graph_results AS (
    SELECT name
    FROM cypher('graph_name', $$
        MATCH (v:Person)
        RETURN v.name
    $$) AS (name agtype)
)
SELECT * FROM graph_results
WHERE name IS NOT NULL;

Cypher in the SELECT clause

Calling cypher() as a standalone column in the SELECT clause is not supported. However, Cypher can be used as part of conditions. Place the cypher() call in the FROM clause and define output columns with AS.