/* * Licensed to the Apache Software Foundation (ASF) under one * or more contributor license agreements. See the NOTICE file * distributed with this work for additional information * regarding copyright ownership. The ASF licenses this file * to you under the Apache License, Version 2.0 (the * "License"); you may not use this file except in compliance * with the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, * software distributed under the License is distributed on an * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY * KIND, either express or implied. See the License for the * specific language governing permissions and limitations * under the License. */ LOAD 'age'; SET search_path TO ag_catalog; -- -- Load data -- SELECT create_graph('cypher_with'); NOTICE: graph "cypher_with" has been created create_graph -------------- (1 row) SELECT * FROM cypher('cypher_with', $$ CREATE (andres {name : 'Andres', age : 36}), (caesar {name : 'Caesar', age : 25}), (bossman {name : 'Bossman', age : 55}), (david {name : 'David', age : 35}), (george {name : 'George', age : 37}), (andres)-[:BLOCKS]->(caesar), (andres)-[:KNOWS]->(bossman), (caesar)-[:KNOWS]->(george), (bossman)-[:BLOCKS]->(david), (bossman)-[:KNOWS]->(george), (david)-[:KNOWS]->(andres) $$) as (a agtype); a --- (0 rows) -- -- Test WITH clause -- SELECT * FROM cypher('cypher_with', $$ MATCH (n)-[e]->(m) WITH n,e,m RETURN n,e,m $$) AS (N1 agtype, edge agtype, N2 agtype); n1 | edge | n2 --------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------- {"id": 281474976710657, "label": "", "properties": {"age": 36, "name": "Andres"}}::vertex | {"id": 844424930131969, "label": "BLOCKS", "end_id": 281474976710658, "start_id": 281474976710657, "properties": {}}::edge | {"id": 281474976710658, "label": "", "properties": {"age": 25, "name": "Caesar"}}::vertex {"id": 281474976710659, "label": "", "properties": {"age": 55, "name": "Bossman"}}::vertex | {"id": 844424930131970, "label": "BLOCKS", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {}}::edge | {"id": 281474976710660, "label": "", "properties": {"age": 35, "name": "David"}}::vertex {"id": 281474976710657, "label": "", "properties": {"age": 36, "name": "Andres"}}::vertex | {"id": 1125899906842625, "label": "KNOWS", "end_id": 281474976710659, "start_id": 281474976710657, "properties": {}}::edge | {"id": 281474976710659, "label": "", "properties": {"age": 55, "name": "Bossman"}}::vertex {"id": 281474976710658, "label": "", "properties": {"age": 25, "name": "Caesar"}}::vertex | {"id": 1125899906842626, "label": "KNOWS", "end_id": 281474976710661, "start_id": 281474976710658, "properties": {}}::edge | {"id": 281474976710661, "label": "", "properties": {"age": 37, "name": "George"}}::vertex {"id": 281474976710659, "label": "", "properties": {"age": 55, "name": "Bossman"}}::vertex | {"id": 1125899906842627, "label": "KNOWS", "end_id": 281474976710661, "start_id": 281474976710659, "properties": {}}::edge | {"id": 281474976710661, "label": "", "properties": {"age": 37, "name": "George"}}::vertex {"id": 281474976710660, "label": "", "properties": {"age": 35, "name": "David"}}::vertex | {"id": 1125899906842628, "label": "KNOWS", "end_id": 281474976710657, "start_id": 281474976710660, "properties": {}}::edge | {"id": 281474976710657, "label": "", "properties": {"age": 36, "name": "Andres"}}::vertex (6 rows) -- WITH/AS SELECT * FROM cypher('cypher_with', $$ MATCH (n)-[e]->(m) WITH n.name AS n1, e as edge, m.name as n2 RETURN n1,label(edge),n2 $$) AS (start_node agtype,edge agtype, end_node agtype); start_node | edge | end_node ------------+----------+----------- "Andres" | "BLOCKS" | "Caesar" "Bossman" | "BLOCKS" | "David" "Andres" | "KNOWS" | "Bossman" "Caesar" | "KNOWS" | "George" "Bossman" | "KNOWS" | "George" "David" | "KNOWS" | "Andres" (6 rows) SELECT * FROM cypher('cypher_with',$$ MATCH (person)-[r]->(otherPerson) WITH *, type(r) AS connectionType RETURN person.name, connectionType, otherPerson.name $$) AS (start_node agtype, connection agtype, end_node agtype); start_node | connection | end_node ------------+------------+----------- "Andres" | "BLOCKS" | "Caesar" "Bossman" | "BLOCKS" | "David" "Andres" | "KNOWS" | "Bossman" "Caesar" | "KNOWS" | "George" "Bossman" | "KNOWS" | "George" "David" | "KNOWS" | "Andres" (6 rows) SELECT * FROM cypher('cypher_with', $$ WITH true AS b RETURN b $$) AS (b bool); b --- t (1 row) -- WITH/WHERE SELECT * FROM cypher('cypher_with', $$ MATCH (george {name: 'George'})<-[]-(otherPerson) WITH otherPerson, toUpper(otherPerson.name) AS upperCaseName WHERE upperCaseName STARTS WITH 'C' RETURN otherPerson.name $$) as (name agtype); name ---------- "Caesar" (1 row) SELECT * FROM cypher('cypher_with', $$ MATCH (david {name: 'David'})-[]-(otherPerson)-[]->() WITH otherPerson, count(*) AS foaf WHERE foaf > 1 RETURN otherPerson.name $$) as (name agtype); name ---------- "Andres" (1 row) SELECT * FROM cypher('cypher_with', $$ MATCH p = (m)-[*1..2]->(b) WITH p, length(p) AS path_length WHERE path_length > 1 RETURN p $$) AS (pattern agtype); pattern -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [{"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex, {"id": 1125899906842625, "label": "KNOWS", "end_id": 281474976710659, "start_id": 281474976710657, "properties": {}}::edge, {"id": 281474976710659, "label": "_ag_label_vertex", "properties": {"age": 55, "name": "Bossman"}}::vertex, {"id": 1125899906842627, "label": "KNOWS", "end_id": 281474976710661, "start_id": 281474976710659, "properties": {}}::edge, {"id": 281474976710661, "label": "_ag_label_vertex", "properties": {"age": 37, "name": "George"}}::vertex]::path [{"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex, {"id": 1125899906842625, "label": "KNOWS", "end_id": 281474976710659, "start_id": 281474976710657, "properties": {}}::edge, {"id": 281474976710659, "label": "_ag_label_vertex", "properties": {"age": 55, "name": "Bossman"}}::vertex, {"id": 844424930131970, "label": "BLOCKS", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {}}::edge, {"id": 281474976710660, "label": "_ag_label_vertex", "properties": {"age": 35, "name": "David"}}::vertex]::path [{"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex, {"id": 844424930131969, "label": "BLOCKS", "end_id": 281474976710658, "start_id": 281474976710657, "properties": {}}::edge, {"id": 281474976710658, "label": "_ag_label_vertex", "properties": {"age": 25, "name": "Caesar"}}::vertex, {"id": 1125899906842626, "label": "KNOWS", "end_id": 281474976710661, "start_id": 281474976710658, "properties": {}}::edge, {"id": 281474976710661, "label": "_ag_label_vertex", "properties": {"age": 37, "name": "George"}}::vertex]::path [{"id": 281474976710659, "label": "_ag_label_vertex", "properties": {"age": 55, "name": "Bossman"}}::vertex, {"id": 844424930131970, "label": "BLOCKS", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {}}::edge, {"id": 281474976710660, "label": "_ag_label_vertex", "properties": {"age": 35, "name": "David"}}::vertex, {"id": 1125899906842628, "label": "KNOWS", "end_id": 281474976710657, "start_id": 281474976710660, "properties": {}}::edge, {"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex]::path [{"id": 281474976710660, "label": "_ag_label_vertex", "properties": {"age": 35, "name": "David"}}::vertex, {"id": 1125899906842628, "label": "KNOWS", "end_id": 281474976710657, "start_id": 281474976710660, "properties": {}}::edge, {"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex, {"id": 1125899906842625, "label": "KNOWS", "end_id": 281474976710659, "start_id": 281474976710657, "properties": {}}::edge, {"id": 281474976710659, "label": "_ag_label_vertex", "properties": {"age": 55, "name": "Bossman"}}::vertex]::path [{"id": 281474976710660, "label": "_ag_label_vertex", "properties": {"age": 35, "name": "David"}}::vertex, {"id": 1125899906842628, "label": "KNOWS", "end_id": 281474976710657, "start_id": 281474976710660, "properties": {}}::edge, {"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex, {"id": 844424930131969, "label": "BLOCKS", "end_id": 281474976710658, "start_id": 281474976710657, "properties": {}}::edge, {"id": 281474976710658, "label": "_ag_label_vertex", "properties": {"age": 25, "name": "Caesar"}}::vertex]::path (6 rows) -- MATCH/WHERE with WITH/WHERE SELECT * FROM cypher('cypher_with', $$ MATCH (m)-[e]->(b) WHERE label(e) = 'KNOWS' WITH * WHERE m.name = 'Andres' RETURN m.name,label(e),b.name $$) AS (N1 agtype, edge agtype, N2 agtype); n1 | edge | n2 ----------+---------+----------- "Andres" | "KNOWS" | "Bossman" (1 row) -- WITH/ORDER BY SELECT * FROM cypher('cypher_with', $$ MATCH (n) WITH n ORDER BY id(n) RETURN n $$) as (name agtype); name -------------------------------------------------------------------------------------------- {"id": 281474976710657, "label": "", "properties": {"age": 36, "name": "Andres"}}::vertex {"id": 281474976710658, "label": "", "properties": {"age": 25, "name": "Caesar"}}::vertex {"id": 281474976710659, "label": "", "properties": {"age": 55, "name": "Bossman"}}::vertex {"id": 281474976710660, "label": "", "properties": {"age": 35, "name": "David"}}::vertex {"id": 281474976710661, "label": "", "properties": {"age": 37, "name": "George"}}::vertex (5 rows) -- WITH/ORDER BY/DESC SELECT * FROM cypher('cypher_with', $$ MATCH (n) WITH n ORDER BY n.name DESC LIMIT 3 RETURN collect(n.name) $$) as (names agtype); names ------------------------------- ["George", "David", "Caesar"] (1 row) SELECT * FROM cypher('cypher_with', $$ MATCH (n {name: 'Andres'})-[]-(m) WITH m ORDER BY m.name DESC LIMIT 1 MATCH (m)-[]-(o) RETURN o.name ORDER BY o.name $$) as (name agtype); name ----------- "Andres" "Bossman" (2 rows) -- multiple WITH clauses SELECT * FROM cypher('cypher_with', $$ MATCH (n)-[e]->(m) WITH n, e, m WHERE label(e) = 'KNOWS' WITH n.name as n1, label(e) as edge, m.name as n2 WHERE n1 = 'Andres' RETURN n1,edge,n2 $$) AS (N1 agtype, edge agtype, N2 agtype); n1 | edge | n2 ----------+---------+----------- "Andres" | "KNOWS" | "Bossman" (1 row) SELECT * FROM cypher('cypher_with', $$ UNWIND [1, 2, 3, 4, 5, 6] AS x WITH x WHERE x > 2 WITH x LIMIT 5 RETURN x $$) as (name agtype); name ------ 3 4 5 6 (4 rows) SELECT * FROM cypher('cypher_with', $$ MATCH (m)-[]->(b) WITH m,b ORDER BY id(m) DESC LIMIT 5 WITH m as start_node, b as end_node WHERE end_node.name = 'George' RETURN id(start_node),start_node.name,id(end_node),end_node.name $$) AS (id1 agtype, name1 agtype, id2 agtype, name2 agtype); id1 | name1 | id2 | name2 -----------------+-----------+-----------------+---------- 281474976710659 | "Bossman" | 281474976710661 | "George" 281474976710658 | "Caesar" | 281474976710661 | "George" (2 rows) -- Expression item must be aliased. SELECT * FROM cypher('cypher_with', $$ WITH 1 + 1 RETURN i $$) AS (i int); ERROR: expression item must be aliased LINE 2: WITH 1 + 1 ^ HINT: Items can be aliased by using AS. SELECT * FROM cypher('cypher_with', $$ MATCH (m)-[]->(b) WITH id(m) RETURN m $$) AS (id agtype); ERROR: expression item must be aliased LINE 3: WITH id(m) ^ HINT: Items can be aliased by using AS. -- Reference undefined variable in WITH clause (should error out) SELECT count(*) FROM cypher('cypher_with', $$ MATCH (m)-[]->(b) WITH m RETURN m,b $$) AS (a agtype, b agtype); ERROR: could not find rte for b LINE 4: RETURN m,b ^ SELECT * FROM cypher('cypher_with', $$ MATCH (m)-[]->(b) WITH m AS start_node,b AS end_node WHERE start_node.name = 'Andres' WITH start_node WHERE start_node.name = 'George' RETURN id(start_node),end_node.name $$) AS (id agtype, node agtype); ERROR: could not find rte for end_node LINE 7: RETURN id(start_node),end_node.name ^ -- Clean up SELECT drop_graph('cypher_with', true); NOTICE: drop cascades to 4 other objects DETAIL: drop cascades to table cypher_with._ag_label_vertex drop cascades to table cypher_with._ag_label_edge drop cascades to table cypher_with."BLOCKS" drop cascades to table cypher_with."KNOWS" NOTICE: graph "cypher_with" has been dropped drop_graph ------------ (1 row) -- Issue 329 (should error out) SELECT create_graph('graph'); NOTICE: graph "graph" has been created create_graph -------------- (1 row) SELECT * FROM cypher('graph', $$ CREATE (a:A)-[:incs]->(:C), (a)-[:incs]->(:C) RETURN a $$) AS (n agtype); n ----------------------------------------------------------------- {"id": 844424930131969, "label": "A", "properties": {}}::vertex (1 row) SELECT * FROM cypher('graph', $$ MATCH (a:A) WHERE ID(a)=0 WITH a OPTIONAL MATCH (a)-[:incs]->(c)-[d:incs]-() WITH a,c,COUNT(d) AS deps WHERE deps<=1 RETURN c,d $$) AS (n agtype, d agtype); ERROR: could not find rte for d LINE 8: RETURN c,d ^ -- Issue 396 (should error out) SELECT * FROM cypher('graph',$$ CREATE (v),(u),(w), (v)-[:hasFriend]->(u), (u)-[:hasFriend]->(w) $$) as (a agtype); a --- (0 rows) SELECT * FROM cypher('graph',$$ MATCH p=(v)-[*1..2]->(u) WITH p,length(p) AS path_length RETURN v,path_length $$) as (a agtype,b agtype); ERROR: could not find rte for v LINE 4: RETURN v,path_length ^ -- Clean up SELECT drop_graph('graph', true); NOTICE: drop cascades to 6 other objects DETAIL: drop cascades to table graph._ag_label_vertex drop cascades to table graph._ag_label_edge drop cascades to table graph."A" drop cascades to table graph.incs drop cascades to table graph."C" drop cascades to table graph."hasFriend" NOTICE: graph "graph" has been dropped drop_graph ------------ (1 row) -- -- End of test --