| /* |
| * 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 |
| -- |
