Question 90207 in PostgreSQL Question posted in
The official documentation can be found here.

How to return vertices from different graphs on a single query with ApacheAGE? – Postgresql

I wanted to retrieve all the vertices from two different graphs I’ve created (one is called family_tree and the other is taxonomy_biology) and used the following command to do so :

SELECT * FROM cypher ('family_tree' AND 'taxonomy_biology', $$
MATCH (v)
RETURN v
$$) as (vertex agtype);

But then, the terminal returns this error :

ERROR:  invalid input syntax for type boolean: "family_tree"
LINE 1: SELECT * FROM cypher('family_tree' AND 'taxonomy_biology', $...

Guessing how ApacheAGE works, it is going to search all the available graphs and return true if there is an available graph with the same name or false if it doesn’t. Yet, passing two graphs that do exist will cause an error.

By the way, typing the same query, but for each graph, returned this :

SELECT * FROM cypher('taxonomy_biology', $$
MATCH (v)
RETURN v
$$) as (VERTEX agtype);
                                         vertex
-----------------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "Kingdom", "properties": {"name": "Animalia"}}::vertex
 {"id": 1125899906842625, "label": "Phylum", "properties": {"name": "Cnidaria"}}::vertex
(2 rows)

SELECT * FROM cypher('family_tree', $$                                                                                                                      MATCH (v)
RETURN v
$$) as (VERTEX agtype);
                                                                                                       vertex

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "Person", "properties": {"name": "Louis", "titles": ["Emperor of the Carolingian Empire", "King of the Franks", "King of Aquitaine"], "year_born": 778, "year_died": 840}}::vertex
 {"id": 844424930131970, "label": "Person", "properties": {"name": "Hildegard", "titles": ["Frankish queen consort"], "year_born": 754, "year_died": 783}}::vertex
 {"id": 844424930131971, "label": "Person", "properties": {"name": "Charlemagne", "titles": ["Holy Roman Emperor", "King of the Franks", "King of the Lombards"], "year_born": 747, "year_died": 814}}::vertex
(3 rows)

So, how can I return vertices from both of these graphs in a single query?

Tags:

3

Answers


  1. 0

    The UNION clause should work just fine, and another alternative would be using a JOIN clause, just like an example in the Apache docs

    In your case, the code should be something like this using UNION:

    SELECT * 
FROM cypher ('family_tree', $$
    MATCH (v)
    RETURN v
$$) as (vertex agtype)
UNION 
SELECT * 
FROM cypher ('taxonomy_biology', $$
    MATCH (v)
    RETURN v
$$) as (vertex agtype);
    Login or Signup to reply.
  2. 0

    No, unfortunately, you cannot use the cypher function to query multiple graphs in a single query like that. The first argument to the cypher function must be a single graph name, not a list of graph names.
    A possible solution as provided by Wendel would be using the UNION clause.

    Another approach you could try is to write a custom function or stored procedure that retrieves all the vertices from both graphs and returns them as a single result set.
    For eg:

    CREATE OR REPLACE FUNCTION get_all_vertices()
RETURNS TABLE(vertex agtype)
LANGUAGE plpgsql
AS $BODY$
BEGIN
    LOAD 'age';
    SET search_path TO ag_catalog;

    RETURN QUERY
        SELECT * FROM cypher('family_tree', $$ MATCH (n) RETURN n $$) as (n agtype)
        UNION
        SELECT * FROM cypher('taxonomy_biology', $$ MATCH (n) RETURN n $$) as (n agtype);
END
$BODY$;

    Once you have created this function, you can use it in a SELECT statement to retrieve all the vertices from both graphs using:

    SELECT * FROM get_all_vertices();
    Login or Signup to reply.
  3. 0

    The query returns an error because the syntax for the cypher() function doesn’t allow combination of two graphs.

    To query multiple graphs, the JOIN and ON clauses are used according to the AGE documentation.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search