SQL in Cypher-PolarDB(PolarDB)-阿里云帮助中心

Apache AGE does not support SQL written directly inside a Cypher query. To run SQL logic within Cypher, wrap it in a user-defined function and call that function from your Cypher query.

Run SQL in a Cypher query

The following example shows how to create a SQL function and call it in a Cypher query.

Step 1: Create a SQL function

Define a SQL function that queries the history table and returns the year for a given event name:

CREATE OR REPLACE FUNCTION public.get_event_year(name agtype) RETURNS agtype AS $$
    SELECT year::agtype
    FROM history AS h
    WHERE h.event_name = name::text
    LIMIT 1;
$$ LANGUAGE sql;

Step 2: Call the function in a Cypher query

Use the function in a WHERE clause to filter graph nodes based on SQL data:

SELECT * FROM cypher('graph_name', $$
    MATCH (e:event)
    WHERE e.year < public.get_event_year(e.name)
    RETURN e.name
$$) AS (n agtype);

Expected output:

 n
-------------------
 "Apache Con 2021"
(1 row)

What's next

User-defined functions — learn how to create and manage user-defined functions in PolarDB for PostgreSQL