Are multiple cypher calls possible within the same FROM clause?

[moeed-k]

If I write a query like so:

SELECT * 
FROM cypher('query1'), cypher('query2');

I get the following error: ERROR: table name "cypher" specified more than once

Is this working as intended? Or will it be possible to have multiple cypher calls in the same FROM clause in the future?

[aru-d-at]

Based on my trials, seems its only a combination of table, cypher() in whichever order you call them.

But, I am quite curious to know what would be the intention behind wanting such functionality? Could perhaps elaborate some use cases to justify the need?

At the moment, if your aim is to access more than one graph, you can just utilise the JOIN clause.

Like so:

SELECT * FROM cypher('graph1', $$
CREATE (c: Customer { user: 'gomeZ', tags: 'dev' })
$$) as (v agtype);

SELECT * FROM cypher('graph2', $$
CREATE (s: Queries { user: 'gomeZ', keywords: ['java', 'c', 'python'] })
$$) as (v agtype);

SELECT graph1.u, graph1.t, graph2.k         
FROM cypher('graph1', $$
MATCH (c:Customer)
RETURN c.user, c.tags
$$) as graph1(u agtype, t agtype)
JOIN cypher('graph2', $$
MATCH (s:Queries)
RETURN s.user, s.keywords
$$) as graph2(u agtype, k agtype)
ON graph1.u = graph2.u;

[jrgemignani]

@moeed-k You need to specify the output type and you need to specify the name, like in @aru-d-at example above, and as illustrated below -

psql-11.5-5432-pgsql=# SELECT * from cypher('graph1', $$ match (u) return u $$);
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * from cypher('graph1', $$ match (u) return u $$);
                      ^
psql-11.5-5432-pgsql=# SELECT * from cypher('graph2', $$ match (u) return u $$);
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * from cypher('graph2', $$ match (u) return u $$);
                      ^
psql-11.5-5432-pgsql=# SELECT * from cypher('graph1', $$ match (u) return u $$) as (result agtype);                                                                                    result
-------------------------------------------------------------------------------------------------------------
 {"id": 281474976710657, "label": "", "properties": {}}::vertex
 {"id": 281474976710658, "label": "", "properties": {}}::vertex
 {"id": 844424930131969, "label": "zero", "properties": {"name": "Zero", "value": 2.71828182845905}}::vertex
(3 rows)

psql-11.5-5432-pgsql=# SELECT * from cypher('graph2', $$ match (u) return u $$) as (result agtype);
                               result
---------------------------------------------------------------------
 {"id": 1407374883553281, "label": "loop", "properties": {}}::vertex
 {"id": 1407374883553282, "label": "loop", "properties": {}}::vertex
(2 rows)

psql-11.5-5432-pgsql=# SELECT * from cypher('graph1', $$ match (u) return u $$) as g1(result agtype), cypher('graph2', $$ match (u) return u $$) as g2(result agtype);
                                                   result                                                    |
         result
-------------------------------------------------------------------------------------------------------------+----------------------
-----------------------------------------------
 {"id": 281474976710657, "label": "", "properties": {}}::vertex                                              | {"id": 14073748835532
81, "label": "loop", "properties": {}}::vertex
 {"id": 281474976710657, "label": "", "properties": {}}::vertex                                              | {"id": 14073748835532
82, "label": "loop", "properties": {}}::vertex
 {"id": 281474976710658, "label": "", "properties": {}}::vertex                                              | {"id": 14073748835532
81, "label": "loop", "properties": {}}::vertex
 {"id": 281474976710658, "label": "", "properties": {}}::vertex                                              | {"id": 14073748835532
82, "label": "loop", "properties": {}}::vertex
 {"id": 844424930131969, "label": "zero", "properties": {"name": "Zero", "value": 2.71828182845905}}::vertex | {"id": 14073748835532
81, "label": "loop", "properties": {}}::vertex
 {"id": 844424930131969, "label": "zero", "properties": {"name": "Zero", "value": 2.71828182845905}}::vertex | {"id": 14073748835532
82, "label": "loop", "properties": {}}::vertex
(6 rows)

psql-11.5-5432-pgsql=#

[moeed-k]

@aru-d-at I'm working on load balancing for AGE using PgPool and I need to check each invocation of the cypher function to determine whether the overall query is a read or write query.

@jrgemignani I checked it out according to your suggestion and now I've gotten it to work.

Thanks guys. I needed to accommodate this edge case.

[aru-d-at]

@jrgemignani I see, the reason cypher(), cypher() queries was failing for me was cause of not adding the graphN(v agtype) at the end.

Seems like I can actually go ahead and overload with multiple cypher queries if need be:

SELECT * FROM
cypher('graph1', $$
    CREATE (c: Customer { user: 'jameS', tags: 'dev' })
$$) as graph1(v agtype),
cypher('graph2', $$
    CREATE (s: Queries { user: 'jameS', keywords: ['ocaml','c++'] })
$$) as graph2(v agtype),
cypher('metro', $$
    CREATE (n: Metro { user: 'jameS', keywords: ['ocaml','c++'] })
$$) as metro(v agtype);

[mahinash26]

It looks like the error message you received is due to the fact that you specified the table name "cypher" more than once in your FROM clause. To fix the issue, you could try aliasing the table names, like so:

SELECT * FROM cypher('query1') AS result1, cypher('query2') AS result2;

This should allow you to specify multiple cypher calls in the same FROM clause without encountering the "table name specified more than once" error.

[AbdulSamad4068]

If you need to execute multiple Cypher queries and combine their results, you can execute them separately and then combine the results using other SQL operations, such as JOIN, UNION, or subqueries. For example:

SELECT *
FROM (
  SELECT *
  FROM cypher('query1')
) AS result1
JOIN (
  SELECT *
  FROM cypher('query2')
) AS result2 ON <join_condition>

Replace <join_condition> with the appropriate conditions based on your data model for example you can write result1.column_name = result2.column_name

[Hamza-Mushtaque]

This is working as intended because you are not allowed to use same table name ("cypher") more than once in the same FROM clause and it is not possible to use multiple cypher calls in same from clause. But it is possible to run separate multiple Cypher queries and then combine their results using Joins. Checkout below query for reference.

SELECT *
FROM (
  SELECT * FROM cypher('query1')  -- Subquery 1
) AS result1
CROSS JOIN (
  SELECT * FROM cypher('query2')  -- Subquery 2
) AS result2;

[abdulmanann]

@jrgemignani I see, the reason cypher(), cypher() queries was failing for me was cause of not adding the graphN(v agtype) at the end.

Seems like I can actually go ahead and overload with multiple cypher queries if need be:

SELECT * FROM
cypher('graph1', $$
    CREATE (c: Customer { user: 'jameS', tags: 'dev' })
$$) as graph1(v agtype),
cypher('graph2', $$
    CREATE (s: Queries { user: 'jameS', keywords: ['ocaml','c++'] })
$$) as graph2(v agtype),
cypher('metro', $$
    CREATE (n: Metro { user: 'jameS', keywords: ['ocaml','c++'] })
$$) as metro(v agtype);

I tried this for the query below

SELECT * FROM cypher('usergraph', $$CREATE(u:User {name: 'user1', email: '[email protected]', phone: '1234-5678'}) RETURN u$$) as usergraph(u agtype), 
cypher('usergraph', $$CREATE(u:User{name:'user2', email:'user
[email protected]'}) RETURN u$$) as usergraph(u agtype);

but am getting the following error:

Is there any way to work around it without using JOIN?

[jrgemignani]

@abdulmanann Because you are specifying usergraph in the AS clause more than once. Look at the example you quoted above. Additionally, you can specify more than one item to create in the CREATE clause. How you are doing it is unnecessary.

[abdulmanann]

@abdulmanann Because you are specifying usergraph in the AS clause more than once. Look at the example you quoted above. Additionally, you can specify more than one item to create in the CREATE clause. How you are doing it is unnecessary.

Thank you for your response It worked when I specified the second query from another graph.

[github-actions[bot]]

This issue is stale because it has been open 45 days with no activity. Remove "Abondoned" label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been stalled for further 7 days with no activity.