/* * 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; SELECT * FROM create_graph('expr'); NOTICE: graph "expr" has been created create_graph -------------- (1 row) -- -- map literal -- -- empty map SELECT * FROM cypher('expr', $$RETURN {}$$) AS r(c agtype); c ---- {} (1 row) -- map of scalar values SELECT * FROM cypher('expr', $$ RETURN {s: 's', i: 1, f: 1.0, b: true, z: null} $$) AS r(c agtype); c ---------------------------------------------------- {"b": true, "f": 1.0, "i": 1, "s": "s", "z": null} (1 row) -- nested maps SELECT * FROM cypher('expr', $$ RETURN {s: {s: 's'}, t: {i: 1, e: {f: 1.0}, s: {a: {b: true}}}, z: null} $$) AS r(c agtype); c --------------------------------------------------------------------------------------- {"s": {"s": "s"}, "t": {"e": {"f": 1.0}, "i": 1, "s": {"a": {"b": true}}}, "z": null} (1 row) -- -- list literal -- -- empty list SELECT * FROM cypher('expr', $$RETURN []$$) AS r(c agtype); c ---- [] (1 row) -- list of scalar values SELECT * FROM cypher('expr', $$ RETURN ['str', 1, 1.0, true, null] $$) AS r(c agtype); c ----------------------------- ["str", 1, 1.0, true, null] (1 row) -- nested lists SELECT * FROM cypher('expr', $$ RETURN [['str'], [1, [1.0], [[true]]], null] $$) AS r(c agtype); c --------------------------------------- [["str"], [1, [1.0], [[true]]], null] (1 row) -- -- parameter -- PREPARE cypher_parameter(agtype) AS SELECT * FROM cypher('expr', $$ RETURN $var $$, $1) AS t(i agtype); EXECUTE cypher_parameter('{"var": 1}'); i --- 1 (1 row) PREPARE cypher_parameter_object(agtype) AS SELECT * FROM cypher('expr', $$ RETURN $var.innervar $$, $1) AS t(i agtype); EXECUTE cypher_parameter_object('{"var": {"innervar": 1}}'); i --- 1 (1 row) PREPARE cypher_parameter_array(agtype) AS SELECT * FROM cypher('expr', $$ RETURN $var[$indexvar] $$, $1) AS t(i agtype); EXECUTE cypher_parameter_array('{"var": [1, 2, 3], "indexvar": 1}'); i --- 2 (1 row) -- missing parameter PREPARE cypher_parameter_missing_argument(agtype) AS SELECT * FROM cypher('expr', $$ RETURN $var, $missingvar $$, $1) AS t(i agtype, j agtype); EXECUTE cypher_parameter_missing_argument('{"var": 1}'); i | j ---+--- 1 | (1 row) -- invalid parameter PREPARE cypher_parameter_invalid_argument(agtype) AS SELECT * FROM cypher('expr', $$ RETURN $var $$, $1) AS t(i agtype); EXECUTE cypher_parameter_invalid_argument('[1]'); ERROR: array index must resolve to an integer value -- missing parameters argument PREPARE cypher_missing_params_argument(int) AS SELECT $1, * FROM cypher('expr', $$ RETURN $var $$) AS t(i agtype); ERROR: parameters argument is missing from cypher() function call LINE 3: RETURN $var ^ SELECT * FROM cypher('expr', $$ RETURN $var $$) AS t(i agtype); ERROR: parameters argument is missing from cypher() function call LINE 2: RETURN $var ^ --list concatenation SELECT * FROM cypher('expr', $$RETURN ['str', 1, 1.0] + [true, null]$$) AS r(c agtype); c ----------------------------- ["str", 1, 1.0, true, null] (1 row) --list IN (contains), should all be true SELECT * FROM cypher('expr', $$RETURN 1 IN ['str', 1, 1.0, true, null]$$) AS r(c boolean); c --- t (1 row) SELECT * FROM cypher('expr', $$RETURN 'str' IN ['str', 1, 1.0, true, null]$$) AS r(c boolean); c --- t (1 row) SELECT * FROM cypher('expr', $$RETURN 1.0 IN ['str', 1, 1.0, true, null]$$) AS r(c boolean); c --- t (1 row) SELECT * FROM cypher('expr', $$RETURN true IN ['str', 1, 1.0, true, null]$$) AS r(c boolean); c --- t (1 row) SELECT * FROM cypher('expr', $$RETURN [1,3,5,[2,4,6]] IN ['str', 1, 1.0, true, null, [1,3,5,[2,4,6]]]$$) AS r(c boolean); c --- t (1 row) SELECT * FROM cypher('expr', $$RETURN {bool: true, int: 1} IN ['str', 1, 1.0, true, null, {bool: true, int: 1}, [1,3,5,[2,4,6]]]$$) AS r(c boolean); c --- t (1 row) -- should return SQL null, nothing SELECT * FROM cypher('expr', $$RETURN null IN ['str', 1, 1.0, true, null]$$) AS r(c boolean); c --- (1 row) SELECT * FROM cypher('expr', $$RETURN null IN ['str', 1, 1.0, true]$$) AS r(c boolean); c --- (1 row) SELECT * FROM cypher('expr', $$RETURN 'str' IN null $$) AS r(c boolean); c --- (1 row) -- should all return false SELECT * FROM cypher('expr', $$RETURN 0 IN ['str', 1, 1.0, true, null]$$) AS r(c boolean); c --- f (1 row) SELECT * FROM cypher('expr', $$RETURN 1.1 IN ['str', 1, 1.0, true, null]$$) AS r(c boolean); c --- f (1 row) SELECT * FROM cypher('expr', $$RETURN 'Str' IN ['str', 1, 1.0, true, null]$$) AS r(c boolean); c --- f (1 row) SELECT * FROM cypher('expr', $$RETURN [1,3,5,[2,4,5]] IN ['str', 1, 1.0, true, null, [1,3,5,[2,4,6]]]$$) AS r(c boolean); c --- f (1 row) SELECT * FROM cypher('expr', $$RETURN {bool: true, int: 2} IN ['str', 1, 1.0, true, null, {bool: true, int: 1}, [1,3,5,[2,4,6]]]$$) AS r(c boolean); c --- f (1 row) -- should error - ERROR: object of IN must be a list SELECT * FROM cypher('expr', $$RETURN null IN 'str' $$) AS r(c boolean); ERROR: object of IN must be a list SELECT * FROM cypher('expr', $$RETURN 'str' IN 'str' $$) AS r(c boolean); ERROR: object of IN must be a list -- list access SELECT * FROM cypher('expr', $$RETURN [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10][0]$$) AS r(c agtype); c --- 0 (1 row) SELECT * FROM cypher('expr', $$RETURN [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10][5]$$) AS r(c agtype); c --- 5 (1 row) SELECT * FROM cypher('expr', $$RETURN [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10][10]$$) AS r(c agtype); c ---- 10 (1 row) SELECT * FROM cypher('expr', $$RETURN [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10][-1]$$) AS r(c agtype); c ---- 10 (1 row) SELECT * FROM cypher('expr', $$RETURN [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10][-3]$$) AS r(c agtype); c --- 8 (1 row) -- should return null SELECT * FROM cypher('expr', $$RETURN [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10][11]$$) AS r(c agtype); c --- (1 row) -- list slice SELECT * FROM cypher('expr', $$RETURN [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10][0..]$$) AS r(c agtype); c ------------------------------------ [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10] (1 row) SELECT * FROM cypher('expr', $$RETURN [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10][..11]$$) AS r(c agtype); c ------------------------------------ [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10] (1 row) SELECT * FROM cypher('expr', $$RETURN [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10][0..0]$$) AS r(c agtype); c ---- [] (1 row) SELECT * FROM cypher('expr', $$RETURN [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10][10..10]$$) AS r(c agtype); c ---- [] (1 row) SELECT * FROM cypher('expr', $$RETURN [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10][0..1]$$) AS r(c agtype); c ----- [0] (1 row) SELECT * FROM cypher('expr', $$RETURN [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10][9..10]$$) AS r(c agtype); c ----- [9] (1 row) SELECT * FROM cypher('expr', $$RETURN [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10][-1..]$$) AS r(c agtype); c ------ [10] (1 row) SELECT * FROM cypher('expr', $$RETURN [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10][-1..11]$$) AS r(c agtype); c ------ [10] (1 row) SELECT * FROM cypher('expr', $$RETURN [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10][-3..11]$$) AS r(c agtype); c ------------ [8, 9, 10] (1 row) -- this one should return null SELECT * FROM cypher('expr', $$RETURN [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10][-1..10]$$) AS r(c agtype); c ---- [] (1 row) SELECT agtype_access_slice('[0]'::agtype, 'null'::agtype, '1'::agtype); agtype_access_slice --------------------- [0] (1 row) SELECT agtype_access_slice('[0]'::agtype, '0'::agtype, 'null'::agtype); agtype_access_slice --------------------- [0] (1 row) -- should error - ERROR: slice must access a list SELECT * from cypher('expr', $$RETURN 0[0..1]$$) as r(a agtype); ERROR: slice must access a list SELECT * from cypher('expr', $$RETURN 0[[0]..[1]]$$) as r(a agtype); ERROR: slice must access a list -- should return nothing SELECT * from cypher('expr', $$RETURN [0][0..-2147483649]$$) as r(a agtype); a ---- [] (1 row) -- access and slice operators nested SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[0] $$) as (results agtype); results --------- 0 (1 row) SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[2] $$) as (results agtype); results ----------- [2, 3, 4] (1 row) SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[-1] $$) as (results agtype); results --------- 9 (1 row) SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[2][-2] $$) as (results agtype); results --------- 3 (1 row) SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[2][-2..] $$) as (results agtype); results --------- [3, 4] (1 row) SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[-2..] $$) as (results agtype); results ---------------- [[6, 7, 8], 9] (1 row) SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[-2..][-1..][-1..] $$) as (results agtype); results --------- [9] (1 row) SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[-2..][-1..][0] $$) as (results agtype); results --------- 9 (1 row) SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[-2..][-1..][-1] $$) as (results agtype); results --------- 9 (1 row) SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[-2..][-2..-1] $$) as (results agtype); results ------------- [[6, 7, 8]] (1 row) SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[-4..-2] $$) as (results agtype); results ---------------- [[2, 3, 4], 5] (1 row) SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[-4..-2][-2] $$) as (results agtype); results ----------- [2, 3, 4] (1 row) SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[-4..-2][0] $$) as (results agtype); results ----------- [2, 3, 4] (1 row) SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[-4..-2][-2][-2..] $$) as (results agtype); results --------- [3, 4] (1 row) SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[-4..-2][-2][-2..][0] $$) as (results agtype); results --------- 3 (1 row) -- empty list SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[-2..][-1..][-2..-2] $$) as (results agtype); results --------- [] (1 row) -- should return null SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[2][3] $$) as (results agtype); results --------- (1 row) SELECT * from cypher('expr', $$ WITH [0, 1, [2, 3, 4], 5, [6, 7, 8], 9] as l RETURN l[-2..][-1..][-2] $$) as (results agtype); results --------- (1 row) -- -- String operators -- -- String LHS + String RHS SELECT * FROM cypher('expr', $$RETURN 'str' + 'str'$$) AS r(c agtype); c ---------- "strstr" (1 row) -- String LHS + Integer RHS SELECT * FROM cypher('expr', $$RETURN 'str' + 1$$) AS r(c agtype); c -------- "str1" (1 row) -- String LHS + Float RHS SELECT * FROM cypher('expr', $$RETURN 'str' + 1.0$$) AS r(c agtype); c ---------- "str1.0" (1 row) -- Integer LHS + String LHS SELECT * FROM cypher('expr', $$RETURN 1 + 'str'$$) AS r(c agtype); c -------- "1str" (1 row) -- Float LHS + String RHS SELECT * FROM cypher('expr', $$RETURN 1.0 + 'str'$$) AS r(c agtype); c ---------- "1.0str" (1 row) -- -- Test transform logic for operators -- SELECT * FROM cypher('expr', $$ RETURN (-(3 * 2 - 4.0) ^ ((10 / 5) + 1)) % -3 $$) AS r(result agtype); result -------- -2.0 (1 row) -- -- Test transform logic for comparison operators -- SELECT * FROM cypher('expr', $$ RETURN 1 = 1.0 $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN 1 > -1.0 $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN -1.0 < 1 $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN "aaa" < "z" $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN "z" > "aaa" $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN false = false $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN ("string" < true) $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN true < 1 $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN (1 + 1.0) = (7 % 5) $$) AS r(result boolean); result -------- t (1 row) -- -- Test chained comparisons -- SELECT * FROM create_graph('chained'); NOTICE: graph "chained" has been created create_graph -------------- (1 row) SELECT * FROM cypher('chained', $$ CREATE (:people {name: "Jason", age:50}) $$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('chained', $$ CREATE (:people {name: "Amy", age:25}) $$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('chained', $$ CREATE (:people {name: "Samantha", age:35}) $$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('chained', $$ CREATE (:people {name: "Mark", age:40}) $$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('chained', $$ CREATE (:people {name: "David", age:15}) $$) AS (result agtype); result -------- (0 rows) -- should return 1 SELECT * FROM cypher('chained', $$ MATCH (u:people) WHERE 35 < u.age <= 49 RETURN u $$) AS (result agtype); result ----------------------------------------------------------------------------------------------- {"id": 844424930131972, "label": "people", "properties": {"age": 40, "name": "Mark"}}::vertex (1 row) SELECT * FROM cypher('chained', $$ MATCH (u:people) WHERE 25 <= u.age <= 25 RETURN u $$) AS (result agtype); result ---------------------------------------------------------------------------------------------- {"id": 844424930131970, "label": "people", "properties": {"age": 25, "name": "Amy"}}::vertex (1 row) SELECT * FROM cypher('chained', $$ MATCH (u:people) WHERE 35 = u.age = 35 RETURN u $$) AS (result agtype); result --------------------------------------------------------------------------------------------------- {"id": 844424930131971, "label": "people", "properties": {"age": 35, "name": "Samantha"}}::vertex (1 row) SELECT * FROM cypher('chained', $$ MATCH (u:people) WHERE 50 > u.age > 35 RETURN u $$) AS (result agtype); result ----------------------------------------------------------------------------------------------- {"id": 844424930131972, "label": "people", "properties": {"age": 40, "name": "Mark"}}::vertex (1 row) -- should return 3 SELECT * FROM cypher('chained', $$ MATCH (u:people) WHERE 40 <> u.age <> 35 RETURN u $$) AS (result agtype); result ------------------------------------------------------------------------------------------------ {"id": 844424930131969, "label": "people", "properties": {"age": 50, "name": "Jason"}}::vertex {"id": 844424930131970, "label": "people", "properties": {"age": 25, "name": "Amy"}}::vertex {"id": 844424930131973, "label": "people", "properties": {"age": 15, "name": "David"}}::vertex (3 rows) -- should return 2 SELECT * FROM cypher('chained', $$ MATCH (u:people) WHERE 30 <= u.age <= 49 > u.age RETURN u $$) AS (result agtype); result --------------------------------------------------------------------------------------------------- {"id": 844424930131971, "label": "people", "properties": {"age": 35, "name": "Samantha"}}::vertex {"id": 844424930131972, "label": "people", "properties": {"age": 40, "name": "Mark"}}::vertex (2 rows) -- should return 0 SELECT * FROM cypher('chained', $$ MATCH (u:people) WHERE 30 <= u.age <= 49 = u.age RETURN u $$) AS (result agtype); result -------- (0 rows) -- should return 2 SELECT * FROM cypher('chained', $$ MATCH (u:people) WHERE 35 < u.age + 1 <= 50 RETURN u $$) AS (result agtype); result --------------------------------------------------------------------------------------------------- {"id": 844424930131971, "label": "people", "properties": {"age": 35, "name": "Samantha"}}::vertex {"id": 844424930131972, "label": "people", "properties": {"age": 40, "name": "Mark"}}::vertex (2 rows) -- should return 3 SELECT * FROM cypher('chained', $$ MATCH (u:people) WHERE NOT 35 < u.age + 1 <= 50 RETURN u $$) AS (result agtype); result ------------------------------------------------------------------------------------------------ {"id": 844424930131969, "label": "people", "properties": {"age": 50, "name": "Jason"}}::vertex {"id": 844424930131970, "label": "people", "properties": {"age": 25, "name": "Amy"}}::vertex {"id": 844424930131973, "label": "people", "properties": {"age": 15, "name": "David"}}::vertex (3 rows) -- -- Test transform logic for IS NULL & IS NOT NULL -- SELECT * FROM cypher('expr', $$ RETURN null IS NULL $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN 1 IS NULL $$) AS r(result boolean); result -------- f (1 row) SELECT * FROM cypher('expr', $$ RETURN 1 IS NOT NULL $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN null IS NOT NULL $$) AS r(result boolean); result -------- f (1 row) -- -- Test transform logic for AND, OR, NOT and XOR -- SELECT * FROM cypher('expr', $$ RETURN NOT false $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN NOT true $$) AS r(result boolean); result -------- f (1 row) SELECT * FROM cypher('expr', $$ RETURN true AND true $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN true AND false $$) AS r(result boolean); result -------- f (1 row) SELECT * FROM cypher('expr', $$ RETURN false AND true $$) AS r(result boolean); result -------- f (1 row) SELECT * FROM cypher('expr', $$ RETURN false AND false $$) AS r(result boolean); result -------- f (1 row) SELECT * FROM cypher('expr', $$ RETURN true OR true $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN true OR false $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN false OR true $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN false OR false $$) AS r(result boolean); result -------- f (1 row) SELECT * FROM cypher('expr', $$ RETURN NOT ((true OR false) AND (false OR true)) $$) AS r(result boolean); result -------- f (1 row) SELECT * FROM cypher('expr', $$ RETURN true XOR true $$) AS r(result boolean); result -------- f (1 row) SELECT * FROM cypher('expr', $$ RETURN true XOR false $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN false XOR true $$) AS r(result boolean); result -------- t (1 row) SELECT * FROM cypher('expr', $$ RETURN false XOR false $$) AS r(result boolean); result -------- f (1 row) -- -- Test indirection transform logic for object.property, object["property"], -- and array[element] -- SELECT * FROM cypher('expr', $$ RETURN [ 1, { bool: true, int: 3, array: [ 9, 11, { boom: false, float: 3.14 }, 13 ] }, 5, 7, 9 ][1].array[2]["float"] $$) AS r(result agtype); result -------- 3.14 (1 row) -- -- Test STARTS WITH, ENDS WITH, and CONTAINS transform logic -- SELECT * FROM cypher('expr', $$ RETURN "abcdefghijklmnopqrstuvwxyz" STARTS WITH "abcd" $$) AS r(result agtype); result -------- true (1 row) SELECT * FROM cypher('expr', $$ RETURN "abcdefghijklmnopqrstuvwxyz" ENDS WITH "wxyz" $$) AS r(result agtype); result -------- true (1 row) SELECT * FROM cypher('expr', $$ RETURN "abcdefghijklmnopqrstuvwxyz" CONTAINS "klmn" $$) AS r(result agtype); result -------- true (1 row) -- these should return false SELECT * FROM cypher('expr', $$ RETURN "abcdefghijklmnopqrstuvwxyz" STARTS WITH "bcde" $$) AS r(result agtype); result -------- false (1 row) SELECT * FROM cypher('expr', $$ RETURN "abcdefghijklmnopqrstuvwxyz" ENDS WITH "vwxy" $$) AS r(result agtype); result -------- false (1 row) SELECT * FROM cypher('expr', $$ RETURN "abcdefghijklmnopqrstuvwxyz" CONTAINS "klmo" $$) AS r(result agtype); result -------- false (1 row) -- these should return SQL NULL SELECT * FROM cypher('expr', $$ RETURN "abcdefghijklmnopqrstuvwxyz" STARTS WITH NULL $$) AS r(result agtype); result -------- (1 row) SELECT * FROM cypher('expr', $$ RETURN "abcdefghijklmnopqrstuvwxyz" ENDS WITH NULL $$) AS r(result agtype); result -------- (1 row) SELECT * FROM cypher('expr', $$ RETURN "abcdefghijklmnopqrstuvwxyz" CONTAINS NULL $$) AS r(result agtype); result -------- (1 row) -- -- Test =~ aka regular expression comparisons -- SELECT create_graph('regex'); NOTICE: graph "regex" has been created create_graph -------------- (1 row) SELECT * FROM cypher('regex', $$ CREATE (n:Person {name: 'John'}) RETURN n $$) AS r(result agtype); result ------------------------------------------------------------------------------------ {"id": 844424930131969, "label": "Person", "properties": {"name": "John"}}::vertex (1 row) SELECT * FROM cypher('regex', $$ CREATE (n:Person {name: 'Jeff'}) RETURN n $$) AS r(result agtype); result ------------------------------------------------------------------------------------ {"id": 844424930131970, "label": "Person", "properties": {"name": "Jeff"}}::vertex (1 row) SELECT * FROM cypher('regex', $$ CREATE (n:Person {name: 'Joan'}) RETURN n $$) AS r(result agtype); result ------------------------------------------------------------------------------------ {"id": 844424930131971, "label": "Person", "properties": {"name": "Joan"}}::vertex (1 row) SELECT * FROM cypher('regex', $$ MATCH (n:Person) WHERE n.name =~ 'JoHn' RETURN n $$) AS r(result agtype); result -------- (0 rows) SELECT * FROM cypher('regex', $$ MATCH (n:Person) WHERE n.name =~ '(?i)JoHn' RETURN n $$) AS r(result agtype); result ------------------------------------------------------------------------------------ {"id": 844424930131969, "label": "Person", "properties": {"name": "John"}}::vertex (1 row) SELECT * FROM cypher('regex', $$ MATCH (n:Person) WHERE n.name =~ 'Jo.n' RETURN n $$) AS r(result agtype); result ------------------------------------------------------------------------------------ {"id": 844424930131969, "label": "Person", "properties": {"name": "John"}}::vertex {"id": 844424930131971, "label": "Person", "properties": {"name": "Joan"}}::vertex (2 rows) SELECT * FROM cypher('regex', $$ MATCH (n:Person) WHERE n.name =~ 'J.*' RETURN n $$) AS r(result agtype); result ------------------------------------------------------------------------------------ {"id": 844424930131969, "label": "Person", "properties": {"name": "John"}}::vertex {"id": 844424930131970, "label": "Person", "properties": {"name": "Jeff"}}::vertex {"id": 844424930131971, "label": "Person", "properties": {"name": "Joan"}}::vertex (3 rows) -- --Coearce to Postgres 3 int types (smallint, int, bigint) -- SELECT create_graph('type_coercion'); NOTICE: graph "type_coercion" has been created create_graph -------------- (1 row) SELECT * FROM cypher('type_coercion', $$ RETURN NULL $$) AS (i bigint); i --- (1 row) SELECT * FROM cypher('type_coercion', $$ RETURN 1 $$) AS (i smallint); i --- 1 (1 row) SELECT * FROM cypher('type_coercion', $$ RETURN 1 $$) AS (i int); i --- 1 (1 row) SELECT * FROM cypher('type_coercion', $$ RETURN 1 $$) AS (i bigint); i --- 1 (1 row) SELECT * FROM cypher('type_coercion', $$ RETURN 1.0 $$) AS (i bigint); i --- 1 (1 row) SELECT * FROM cypher('type_coercion', $$ RETURN 1.0::numeric $$) AS (i bigint); i --- 1 (1 row) SELECT * FROM cypher('type_coercion', $$ RETURN '1' $$) AS (i bigint); i --- 1 (1 row) --Invalid String Format SELECT * FROM cypher('type_coercion', $$ RETURN '1.0' $$) AS (i bigint); ERROR: invalid input syntax for integer: "1.0" -- Casting to ints that will cause overflow SELECT * FROM cypher('type_coercion', $$ RETURN 10000000000000000000 $$) AS (i smallint); ERROR: smallint out of range SELECT * FROM cypher('type_coercion', $$ RETURN 10000000000000000000 $$) AS (i int); ERROR: integer out of range --Invalid types SELECT * FROM cypher('type_coercion', $$ RETURN true $$) AS (i bigint); ERROR: cannot cast agtype boolean to type int SELECT * FROM cypher('type_coercion', $$ RETURN {key: 1} $$) AS (i bigint); ERROR: cannot cast agtype object to type int SELECT * FROM cypher('type_coercion', $$ RETURN [1] $$) AS (i bigint); ERROR: cannot cast agtype array to type int SELECT * FROM cypher('type_coercion', $$CREATE ()-[:edge]->()$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('type_coercion', $$ MATCH (v) RETURN v $$) AS (i bigint); ERROR: cannot cast agtype vertex to type int SELECT * FROM cypher('type_coercion', $$ MATCH ()-[e]-() RETURN e $$) AS (i bigint); ERROR: cannot cast agtype edge to type int SELECT * FROM cypher('type_coercion', $$ MATCH p=()-[]-() RETURN p $$) AS (i bigint); ERROR: cannot cast agtype path to type int -- -- Test typecasting '::' transform and execution logic -- -- -- Test from an agtype value to agtype int -- SELECT * FROM cypher('expr', $$ RETURN 0.0::int $$) AS r(result agtype); result -------- 0 (1 row) SELECT * FROM cypher('expr', $$ RETURN 0.0::integer $$) AS r(result agtype); result -------- 0 (1 row) SELECT * FROM cypher('expr', $$ RETURN '0'::int $$) AS r(result agtype); result -------- 0 (1 row) SELECT * FROM cypher('expr', $$ RETURN '0'::integer $$) AS r(result agtype); result -------- 0 (1 row) SELECT * FROM cypher('expr', $$ RETURN 0.0::numeric::int $$) AS r(result agtype); result -------- 0 (1 row) SELECT * FROM cypher('expr', $$ RETURN 2.71::int $$) AS r(result agtype); result -------- 3 (1 row) SELECT * FROM cypher('expr', $$ RETURN 2.71::numeric::int $$) AS r(result agtype); result -------- 3 (1 row) SELECT * FROM cypher('expr', $$ RETURN ([0, {one: 1.0, pie: 3.1415927, e: 2::numeric}, 2, null][1].one)::int $$) AS r(result agtype); result -------- 1 (1 row) SELECT * FROM cypher('expr', $$ RETURN ([0, {one: 1.0::int, pie: 3.1415927, e: 2.718281::numeric}, 2, null][1].one) $$) AS r(result agtype); result -------- 1 (1 row) SELECT * FROM cypher('expr', $$ RETURN ([0, {one: 1::float, pie: 3.1415927, e: 2.718281::numeric}, 2, null][1].one)::int $$) AS r(result agtype); result -------- 1 (1 row) SELECT * FROM cypher('expr', $$ RETURN ([0, {one: 1, pie: 3.1415927, e: 2.718281::numeric}, 2, null][3])::int $$) AS r(result agtype); result -------- (1 row) -- should return SQL null SELECT agtype_typecast_int('null'::agtype); agtype_typecast_int --------------------- (1 row) SELECT agtype_typecast_int(null); agtype_typecast_int --------------------- (1 row) SELECT * FROM cypher('expr', $$ RETURN null::int $$) AS r(result agtype); result -------- (1 row) -- should return JSON null SELECT agtype_in('null::int'); agtype_in ----------- null (1 row) -- these should fail SELECT * FROM cypher('expr', $$ RETURN '0.0'::int $$) AS r(result agtype); ERROR: invalid input syntax for integer: "0.0" SELECT * FROM cypher('expr', $$ RETURN '1.5'::int $$) AS r(result agtype); ERROR: invalid input syntax for integer: "1.5" SELECT * FROM cypher('graph_name', $$ RETURN "15555555555555555555555555555"::int $$) AS (string_result agtype); ERROR: graph "graph_name" does not exist LINE 1: SELECT * FROM cypher('graph_name', $$ ^ SELECT * FROM cypher('expr', $$ RETURN 'NaN'::float::int $$) AS r(result agtype); ERROR: bigint out of range SELECT * FROM cypher('expr', $$ RETURN 'infinity'::float::int $$) AS r(result agtype); ERROR: bigint out of range -- Test from an agtype value to an agtype numeric -- SELECT * FROM cypher('expr', $$ RETURN 0::numeric $$) AS r(result agtype); result ------------ 0::numeric (1 row) SELECT * FROM cypher('expr', $$ RETURN 2.71::numeric $$) AS r(result agtype); result --------------- 2.71::numeric (1 row) SELECT * FROM cypher('expr', $$ RETURN '2.71'::numeric $$) AS r(result agtype); result --------------- 2.71::numeric (1 row) SELECT * FROM cypher('expr', $$ RETURN (2.71::numeric)::numeric $$) AS r(result agtype); result --------------- 2.71::numeric (1 row) SELECT * FROM cypher('expr', $$ RETURN ('2.71'::numeric)::numeric $$) AS r(result agtype); result --------------- 2.71::numeric (1 row) SELECT * FROM cypher('expr', $$ RETURN ('NaN'::numeric)::numeric $$) AS r(result agtype); result -------------- NaN::numeric (1 row) SELECT * FROM cypher('expr', $$ RETURN ((1 + 2.71) * 3)::numeric $$) AS r(result agtype); result ---------------- 11.13::numeric (1 row) SELECT * FROM cypher('expr', $$ RETURN ([0, {one: 1, pie: 3.1415927, e: 2.718281::numeric}, 2, null][1].pie)::numeric $$) AS r(result agtype); result -------------------- 3.1415927::numeric (1 row) SELECT * FROM cypher('expr', $$ RETURN ([0, {one: 1, pie: 3.1415927, e: 2.718281::numeric}, 2, null][1].e) $$) AS r(result agtype); result ------------------- 2.718281::numeric (1 row) SELECT * FROM cypher('expr', $$ RETURN ([0, {one: 1, pie: 3.1415927, e: 2.718281::numeric}, 2, null][1].e)::numeric $$) AS r(result agtype); result ------------------- 2.718281::numeric (1 row) SELECT * FROM cypher('expr', $$ RETURN ([0, {one: 1, pie: 3.1415927, e: 2.718281::numeric}, 2, null][3])::numeric $$) AS r(result agtype); result -------- (1 row) SELECT * FROM cypher('expr', $$ RETURN ([0, {one: 1, pie: 3.1415927, e: 2.718281::numeric}, 2::numeric, null]) $$) AS r(result agtype); result ----------------------------------------------------------------------------- [0, {"e": 2.718281::numeric, "one": 1, "pie": 3.1415927}, 2::numeric, null] (1 row) -- should return SQL null SELECT agtype_typecast_numeric('null'::agtype); agtype_typecast_numeric ------------------------- (1 row) SELECT agtype_typecast_numeric(null); agtype_typecast_numeric ------------------------- (1 row) SELECT * FROM cypher('expr', $$ RETURN null::numeric $$) AS r(result agtype); result -------- (1 row) -- should return JSON null SELECT agtype_in('null::numeric'); agtype_in ----------- null (1 row) -- these should fail SELECT * FROM cypher('expr', $$ RETURN ('2:71'::numeric)::numeric $$) AS r(result agtype); ERROR: invalid input syntax for type numeric: "2:71" SELECT * FROM cypher('expr', $$ RETURN ('inf'::numeric)::numeric $$) AS r(result agtype); ERROR: invalid input syntax for type numeric: "inf" SELECT * FROM cypher('expr', $$ RETURN ('infinity'::numeric)::numeric $$) AS r(result agtype); ERROR: invalid input syntax for type numeric: "infinity" -- verify that output can be accepted and reproduced correctly via agtype_in SELECT agtype_in('2.71::numeric'); agtype_in --------------- 2.71::numeric (1 row) SELECT agtype_in('[0, {"e": 2.718281::numeric, "one": 1, "pie": 3.1415927}, 2::numeric, null]'); agtype_in ----------------------------------------------------------------------------- [0, {"e": 2.718281::numeric, "one": 1, "pie": 3.1415927}, 2::numeric, null] (1 row) SELECT * FROM cypher('expr', $$ RETURN (['NaN'::numeric, {one: 1, pie: 3.1415927, nan: 'nAn'::numeric}, 2::numeric, null]) $$) AS r(result agtype); result ------------------------------------------------------------------------------------- [NaN::numeric, {"nan": NaN::numeric, "one": 1, "pie": 3.1415927}, 2::numeric, null] (1 row) SELECT agtype_in('[NaN::numeric, {"nan": NaN::numeric, "one": 1, "pie": 3.1415927}, 2::numeric, null]'); agtype_in ------------------------------------------------------------------------------------- [NaN::numeric, {"nan": NaN::numeric, "one": 1, "pie": 3.1415927}, 2::numeric, null] (1 row) -- -- Test from an agtype value to agtype float -- SELECT * FROM cypher('expr', $$ RETURN 0::float $$) AS r(result agtype); result -------- 0.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN '2.71'::float $$) AS r(result agtype); result -------- 2.71 (1 row) SELECT * FROM cypher('expr', $$ RETURN 2.71::float $$) AS r(result agtype); result -------- 2.71 (1 row) SELECT * FROM cypher('expr', $$ RETURN ([0, {one: 1, pie: 3.1415927, e: 2::numeric}, 2, null][1].one)::float $$) AS r(result agtype); result -------- 1.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN ([0, {one: 1::float, pie: 3.1415927, e: 2.718281::numeric}, 2, null][1].one) $$) AS r(result agtype); result -------- 1.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN ([0, {one: 1::float, pie: 3.1415927, e: 2.718281::numeric}, 2, null][1].one)::float $$) AS r(result agtype); result -------- 1.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN ([0, {one: 1, pie: 3.1415927, e: 2.718281::numeric}, 2, null][3])::float $$) AS r(result agtype); result -------- (1 row) -- test NaN, infinity, and -infinity SELECT * FROM cypher('expr', $$ RETURN 'NaN'::float $$) AS r(result agtype); result -------- NaN (1 row) SELECT * FROM cypher('expr', $$ RETURN 'inf'::float $$) AS r(result agtype); result ---------- Infinity (1 row) SELECT * FROM cypher('expr', $$ RETURN '-inf'::float $$) AS r(result agtype); result ----------- -Infinity (1 row) SELECT * FROM cypher('expr', $$ RETURN 'infinity'::float $$) AS r(result agtype); result ---------- Infinity (1 row) SELECT * FROM cypher('expr', $$ RETURN '-infinity'::float $$) AS r(result agtype); result ----------- -Infinity (1 row) -- should return SQL null SELECT agtype_typecast_float('null'::agtype); agtype_typecast_float ----------------------- (1 row) SELECT agtype_typecast_float(null); agtype_typecast_float ----------------------- (1 row) SELECT * FROM cypher('expr', $$ RETURN null::float $$) AS r(result agtype); result -------- (1 row) -- should return JSON null SELECT agtype_in('null::float'); agtype_in ----------- null (1 row) -- these should fail SELECT * FROM cypher('expr', $$ RETURN '2:71'::float $$) AS r(result agtype); ERROR: invalid input syntax for type double precision: "2:71" SELECT * FROM cypher('expr', $$ RETURN 'infi'::float $$) AS r(result agtype); ERROR: invalid input syntax for type double precision: "infi" -- verify that output can be accepted and reproduced correctly via agtype_in SELECT * FROM cypher('expr', $$ RETURN ([0, {one: 1::float, pie: 3.1415927, e: 2.718281::numeric}, 2::numeric, null]) $$) AS r(result agtype); result ------------------------------------------------------------------------------- [0, {"e": 2.718281::numeric, "one": 1.0, "pie": 3.1415927}, 2::numeric, null] (1 row) SELECT agtype_in('[0, {"e": 2.718281::numeric, "one": 1.0, "pie": 3.1415927}, 2::numeric, null]'); agtype_in ------------------------------------------------------------------------------- [0, {"e": 2.718281::numeric, "one": 1.0, "pie": 3.1415927}, 2::numeric, null] (1 row) SELECT * FROM cypher('expr', $$ RETURN (['NaN'::float, {one: 'inf'::float, pie: 3.1415927, e: 2.718281::numeric}, 2::numeric, null]) $$) AS r(result agtype); result -------------------------------------------------------------------------------------- [NaN, {"e": 2.718281::numeric, "one": Infinity, "pie": 3.1415927}, 2::numeric, null] (1 row) SELECT agtype_in('[NaN, {"e": 2.718281::numeric, "one": Infinity, "pie": 3.1415927}, 2::numeric, null]'); agtype_in -------------------------------------------------------------------------------------- [NaN, {"e": 2.718281::numeric, "one": Infinity, "pie": 3.1415927}, 2::numeric, null] (1 row) -- -- Test typecast :: transform and execution logic for object (vertex & edge) -- SELECT * FROM cypher('expr', $$ RETURN {id:0, label:"vertex 0", properties:{}}::vertex $$) AS r(result agtype); result ---------------------------------------------------------- {"id": 0, "label": "vertex 0", "properties": {}}::vertex (1 row) SELECT * FROM cypher('expr', $$ RETURN {vertex_0:{id:0, label:"vertex 0", properties:{}}::vertex} $$) AS r(result agtype); result ------------------------------------------------------------------------ {"vertex_0": {"id": 0, "label": "vertex 0", "properties": {}}::vertex} (1 row) SELECT * FROM cypher('expr', $$ RETURN {name:"container 0", vertices:[{vertex_0:{id:0, label:"vertex 0", properties:{}}::vertex}, {vertex_0:{id:0, label:"vertex 0", properties:{}}::vertex}]} $$) AS r(result agtype); result --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {"name": "container 0", "vertices": [{"vertex_0": {"id": 0, "label": "vertex 0", "properties": {}}::vertex}, {"vertex_0": {"id": 0, "label": "vertex 0", "properties": {}}::vertex}]} (1 row) SELECT * FROM cypher('expr', $$ RETURN {id:3, label:"edge 0", properties:{}, start_id:0, end_id:1}::edge $$) AS r(result agtype); result ---------------------------------------------------------------------------------- {"id": 3, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge (1 row) SELECT * FROM cypher('expr', $$ RETURN {edge_0:{id:3, label:"edge 0", properties:{}, start_id:0, end_id:1}::edge} $$) AS r(result agtype); result ---------------------------------------------------------------------------------------------- {"edge_0": {"id": 3, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge} (1 row) --invalid edge typecast SELECT * FROM cypher('expr', $$ RETURN {edge_0:{id:3, label:"edge 0", properties:{}, startid:0, end_id:1}::edge} $$) AS r(result agtype); ERROR: edge typecast object has an invalid or missing start_id SELECT * FROM cypher('expr', $$ RETURN {edge_0:{id:3, label:"edge 0", properties:{}, start_id:0, endid:1}::edge} $$) AS r(result agtype); ERROR: edge typecast object has an invalid or missing end_id SELECT * FROM cypher('expr', $$ RETURN {name:"container 1", edges:[{id:3, label:"edge 0", properties:{}, start_id:0, end_id:1}::edge, {id:4, label:"edge 1", properties:{}, start_id:1, end_id:0}::edge]} $$) AS r(result agtype); result -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {"name": "container 1", "edges": [{"id": 3, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 4, "label": "edge 1", "end_id": 0, "start_id": 1, "properties": {}}::edge]} (1 row) SELECT * FROM cypher('expr', $$ RETURN {name:"path 1", path:[{id:0, label:"vertex 0", properties:{}}::vertex, {id:2, label:"edge 0", properties:{}, start_id:0, end_id:1}::edge, {id:1, label:"vertex 1", properties:{}}::vertex]} $$) AS r(result agtype); result ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ {"name": "path 1", "path": [{"id": 0, "label": "vertex 0", "properties": {}}::vertex, {"id": 2, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 1, "label": "vertex 1", "properties": {}}::vertex]} (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN NULL::vertex $$) AS r(result agtype); result -------- (1 row) SELECT * FROM cypher('expr', $$ RETURN NULL::edge $$) AS r(result agtype); result -------- (1 row) SELECT agtype_typecast_vertex('null'::agtype); agtype_typecast_vertex ------------------------ (1 row) SELECT agtype_typecast_vertex(null); agtype_typecast_vertex ------------------------ (1 row) SELECT agtype_typecast_edge('null'::agtype); agtype_typecast_edge ---------------------- (1 row) SELECT agtype_typecast_edge(null); agtype_typecast_edge ---------------------- (1 row) -- should return JSON null SELECT agtype_in('null::vertex'); agtype_in ----------- null (1 row) SELECT agtype_in('null::edge'); agtype_in ----------- null (1 row) -- should all fail SELECT * FROM cypher('expr', $$ RETURN {id:0, labelz:"vertex 0", properties:{}}::vertex $$) AS r(result agtype); ERROR: vertex typecast object has invalid or missing label SELECT * FROM cypher('expr', $$ RETURN {id:0, label:"vertex 0"}::vertex $$) AS r(result agtype); ERROR: typecast object is not a vertex SELECT * FROM cypher('expr', $$ RETURN {id:"0", label:"vertex 0", properties:{}}::vertex $$) AS r(result agtype); ERROR: vertex typecast object has invalid or missing id SELECT * FROM cypher('expr', $$ RETURN {}::vertex $$) AS r(result agtype); ERROR: typecast object is not a vertex SELECT * FROM cypher('expr', $$ RETURN {id:3, labelz:"edge 0", properties:{}, start_id:0, end_id:1}::edge $$) AS r(result agtype); ERROR: edge typecast object has an invalid or missing label SELECT * FROM cypher('expr', $$ RETURN {id:3, label:"edge 0", start_id:0, end_id:1}::edge $$) AS r(result agtype); ERROR: typecast object is not an edge SELECT * FROM cypher('expr', $$ RETURN {}::edge $$) AS r(result agtype); ERROR: typecast object is not an edge -- make sure that output can be read back in and reproduce the output SELECT agtype_in('{"name": "container 0", "vertices": [{"vertex_0": {"id": 0, "label": "vertex 0", "properties": {}}::vertex}, {"vertex_0": {"id": 0, "label": "vertex 0", "properties": {}}::vertex}]}'); agtype_in --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {"name": "container 0", "vertices": [{"vertex_0": {"id": 0, "label": "vertex 0", "properties": {}}::vertex}, {"vertex_0": {"id": 0, "label": "vertex 0", "properties": {}}::vertex}]} (1 row) SELECT agtype_in('{"name": "container 1", "edges": [{"id": 3, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 4, "label": "edge 1", "end_id": 0, "start_id": 1, "properties": {}}::edge]}'); agtype_in -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {"name": "container 1", "edges": [{"id": 3, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 4, "label": "edge 1", "end_id": 0, "start_id": 1, "properties": {}}::edge]} (1 row) SELECT agtype_in('{"name": "path 1", "path": [{"id": 0, "label": "vertex 0", "properties": {}}::vertex, {"id": 2, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 1, "label": "vertex 1", "properties": {}}::vertex]}'); agtype_in ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ {"name": "path 1", "path": [{"id": 0, "label": "vertex 0", "properties": {}}::vertex, {"id": 2, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 1, "label": "vertex 1", "properties": {}}::vertex]} (1 row) -- typecast to path SELECT agtype_in('[{"id": 0, "label": "vertex 0", "properties": {}}::vertex, {"id": 2, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 1, "label": "vertex 1", "properties": {}}::vertex]::path'); agtype_in -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [{"id": 0, "label": "vertex 0", "properties": {}}::vertex, {"id": 2, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 1, "label": "vertex 1", "properties": {}}::vertex]::path (1 row) SELECT agtype_in('{"Path" : [{"id": 0, "label": "vertex 0", "properties": {}}::vertex, {"id": 2, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 1, "label": "vertex 1", "properties": {}}::vertex]::path}'); agtype_in ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ {"Path": [{"id": 0, "label": "vertex 0", "properties": {}}::vertex, {"id": 2, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 1, "label": "vertex 1", "properties": {}}::vertex]::path} (1 row) SELECT * FROM cypher('expr', $$ RETURN [{id: 0, label: "vertex 0", properties: {}}::vertex, {id: 2, label: "edge 0", end_id: 1, start_id: 0, properties: {}}::edge, {id: 1, label: "vertex 1", properties: {}}::vertex]::path $$) AS r(result agtype); result -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [{"id": 0, "label": "vertex 0", "properties": {}}::vertex, {"id": 2, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 1, "label": "vertex 1", "properties": {}}::vertex]::path (1 row) SELECT * FROM cypher('expr', $$ RETURN {path : [{id: 0, label: "vertex 0", properties: {}}::vertex, {id: 2, label: "edge 0", end_id: 1, start_id: 0, properties: {}}::edge, {id: 1, label: "vertex 1", properties: {}}::vertex]::path} $$) AS r(result agtype); result ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ {"path": [{"id": 0, "label": "vertex 0", "properties": {}}::vertex, {"id": 2, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 1, "label": "vertex 1", "properties": {}}::vertex]::path} (1 row) -- verify that the output can be input SELECT agtype_in('[{"id": 0, "label": "vertex 0", "properties": {}}::vertex, {"id": 2, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 1, "label": "vertex 1", "properties": {}}::vertex]::path'); agtype_in -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [{"id": 0, "label": "vertex 0", "properties": {}}::vertex, {"id": 2, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 1, "label": "vertex 1", "properties": {}}::vertex]::path (1 row) SELECT agtype_in('{"path": [{"id": 0, "label": "vertex 0", "properties": {}}::vertex, {"id": 2, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 1, "label": "vertex 1", "properties": {}}::vertex]::path}'); agtype_in ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ {"path": [{"id": 0, "label": "vertex 0", "properties": {}}::vertex, {"id": 2, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 1, "label": "vertex 1", "properties": {}}::vertex]::path} (1 row) -- invalid paths should fail SELECT agtype_in('[{"id": 0, "label": "vertex 0", "properties": {}}::vertex, {"id": 2, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge]::path'); ERROR: array is not a valid path SELECT agtype_in('{"Path" : [{"id": 2, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 0, "label": "vertex 0", "properties": {}}::vertex, {"id": 2, "label": "edge 0", "end_id": 1, "start_id": 0, "properties": {}}::edge, {"id": 1, "label": "vertex 1", "properties": {}}::vertex]::path}'); ERROR: array is not a valid path SELECT * FROM cypher('expr', $$ RETURN [{id: 0, label: "vertex 0", properties: {}}::vertex]::path $$) AS r(result agtype); ERROR: typecast argument is not a valid path SELECT * FROM cypher('expr', $$ RETURN [{id: 2, label: "edge 0", end_id: 1, start_id: 0, properties: {}}::edge]::path $$) AS r(result agtype); ERROR: typecast argument is not a valid path SELECT * FROM cypher('expr', $$ RETURN []::path $$) AS r(result agtype); ERROR: typecast argument is not a valid path -- should be JSON null SELECT agtype_in('null::path'); agtype_in ----------- null (1 row) -- should be SQL null SELECT * FROM cypher('expr', $$ RETURN null::path $$) AS r(result agtype); result -------- (1 row) SELECT agtype_typecast_path(agtype_in('null')); agtype_typecast_path ---------------------- (1 row) SELECT agtype_typecast_path(null); agtype_typecast_path ---------------------- (1 row) -- test functions -- create some vertices and edges SELECT * FROM cypher('expr', $$CREATE (:v)$$) AS (a agtype); a --- (0 rows) SELECT * FROM cypher('expr', $$CREATE (:v {i: 0})$$) AS (a agtype); a --- (0 rows) SELECT * FROM cypher('expr', $$CREATE (:v {i: 1})$$) AS (a agtype); a --- (0 rows) SELECT * FROM cypher('expr', $$ CREATE (:v1 {id:'initial'})-[:e1]->(:v1 {id:'middle'})-[:e1]->(:v1 {id:'end'}) $$) AS (a agtype); a --- (0 rows) -- show them SELECT * FROM cypher('expr', $$ MATCH (v) RETURN v $$) AS (expression agtype); expression ---------------------------------------------------------------------------------- {"id": 844424930131969, "label": "v", "properties": {}}::vertex {"id": 844424930131970, "label": "v", "properties": {"i": 0}}::vertex {"id": 844424930131971, "label": "v", "properties": {"i": 1}}::vertex {"id": 1125899906842625, "label": "v1", "properties": {"id": "initial"}}::vertex {"id": 1125899906842626, "label": "v1", "properties": {"id": "middle"}}::vertex {"id": 1125899906842627, "label": "v1", "properties": {"id": "end"}}::vertex (6 rows) SELECT * FROM cypher('expr', $$ MATCH ()-[e]-() RETURN e $$) AS (expression agtype); expression --------------------------------------------------------------------------------------------------------------------------- {"id": 1407374883553282, "label": "e1", "end_id": 1125899906842626, "start_id": 1125899906842625, "properties": {}}::edge {"id": 1407374883553281, "label": "e1", "end_id": 1125899906842627, "start_id": 1125899906842626, "properties": {}}::edge {"id": 1407374883553282, "label": "e1", "end_id": 1125899906842626, "start_id": 1125899906842625, "properties": {}}::edge {"id": 1407374883553281, "label": "e1", "end_id": 1125899906842627, "start_id": 1125899906842626, "properties": {}}::edge (4 rows) -- id() SELECT * FROM cypher('expr', $$ MATCH ()-[e]-() RETURN id(e) $$) AS (id agtype); id ------------------ 1407374883553282 1407374883553281 1407374883553282 1407374883553281 (4 rows) SELECT * FROM cypher('expr', $$ MATCH (v) RETURN id(v) $$) AS (id agtype); id ------------------ 844424930131969 844424930131970 844424930131971 1125899906842625 1125899906842626 1125899906842627 (6 rows) -- should return null SELECT * FROM cypher('expr', $$ RETURN id(null) $$) AS (id agtype); id ---- (1 row) -- should error SELECT * FROM cypher('expr', $$ RETURN id() $$) AS (id agtype); ERROR: function ag_catalog.age_id() does not exist LINE 2: RETURN id() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- start_id() SELECT * FROM cypher('expr', $$ MATCH ()-[e]-() RETURN start_id(e) $$) AS (start_id agtype); start_id ------------------ 1125899906842625 1125899906842626 1125899906842625 1125899906842626 (4 rows) -- should return null SELECT * FROM cypher('expr', $$ RETURN start_id(null) $$) AS (start_id agtype); start_id ---------- (1 row) -- should error SELECT * FROM cypher('expr', $$ MATCH (v) RETURN start_id(v) $$) AS (start_id agtype); ERROR: start_id() argument must be an edge or null SELECT * FROM cypher('expr', $$ RETURN start_id() $$) AS (start_id agtype); ERROR: function ag_catalog.age_start_id() does not exist LINE 2: RETURN start_id() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- end_id() SELECT * FROM cypher('expr', $$ MATCH ()-[e]-() RETURN end_id(e) $$) AS (end_id agtype); end_id ------------------ 1125899906842626 1125899906842627 1125899906842626 1125899906842627 (4 rows) -- should return null SELECT * FROM cypher('expr', $$ RETURN end_id(null) $$) AS (end_id agtype); end_id -------- (1 row) -- should error SELECT * FROM cypher('expr', $$ MATCH (v) RETURN end_id(v) $$) AS (end_id agtype); ERROR: end_id() argument must be an edge or null SELECT * FROM cypher('expr', $$ RETURN end_id() $$) AS (end_id agtype); ERROR: function ag_catalog.age_end_id() does not exist LINE 2: RETURN end_id() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- startNode() SELECT * FROM cypher('expr', $$ MATCH ()-[e]-() RETURN id(e), start_id(e), startNode(e) $$) AS (id agtype, start_id agtype, startNode agtype); id | start_id | startnode ------------------+------------------+---------------------------------------------------------------------------------- 1407374883553281 | 1125899906842626 | {"id": 1125899906842626, "label": "v1", "properties": {"id": "middle"}}::vertex 1407374883553281 | 1125899906842626 | {"id": 1125899906842626, "label": "v1", "properties": {"id": "middle"}}::vertex 1407374883553282 | 1125899906842625 | {"id": 1125899906842625, "label": "v1", "properties": {"id": "initial"}}::vertex 1407374883553282 | 1125899906842625 | {"id": 1125899906842625, "label": "v1", "properties": {"id": "initial"}}::vertex (4 rows) -- should return null SELECT * FROM cypher('expr', $$ RETURN startNode(null) $$) AS (startNode agtype); startnode ----------- (1 row) -- should error SELECT * FROM cypher('expr', $$ MATCH (v) RETURN startNode(v) $$) AS (startNode agtype); ERROR: startNode() argument must be an edge or null SELECT * FROM cypher('expr', $$ RETURN startNode() $$) AS (startNode agtype); ERROR: function ag_catalog.age_startnode() does not exist LINE 2: RETURN startNode() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- endNode() SELECT * FROM cypher('expr', $$ MATCH ()-[e]-() RETURN id(e), end_id(e), endNode(e) $$) AS (id agtype, end_id agtype, endNode agtype); id | end_id | endnode ------------------+------------------+--------------------------------------------------------------------------------- 1407374883553281 | 1125899906842627 | {"id": 1125899906842627, "label": "v1", "properties": {"id": "end"}}::vertex 1407374883553281 | 1125899906842627 | {"id": 1125899906842627, "label": "v1", "properties": {"id": "end"}}::vertex 1407374883553282 | 1125899906842626 | {"id": 1125899906842626, "label": "v1", "properties": {"id": "middle"}}::vertex 1407374883553282 | 1125899906842626 | {"id": 1125899906842626, "label": "v1", "properties": {"id": "middle"}}::vertex (4 rows) -- should return null SELECT * FROM cypher('expr', $$ RETURN endNode(null) $$) AS (endNode agtype); endnode --------- (1 row) -- should error SELECT * FROM cypher('expr', $$ MATCH (v) RETURN endNode(v) $$) AS (endNode agtype); ERROR: endNode() argument must be an edge or null SELECT * FROM cypher('expr', $$ RETURN endNode() $$) AS (endNode agtype); ERROR: function ag_catalog.age_endnode() does not exist LINE 2: RETURN endNode() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- type() SELECT * FROM cypher('expr', $$ MATCH ()-[e]-() RETURN type(e) $$) AS (type agtype); type ------ "e1" "e1" "e1" "e1" (4 rows) -- should return null SELECT * FROM cypher('expr', $$ RETURN type(null) $$) AS (type agtype); type ------ (1 row) -- should error SELECT * FROM cypher('expr', $$ MATCH (v) RETURN type(v) $$) AS (type agtype); ERROR: type() argument must be an edge or null SELECT * FROM cypher('expr', $$ RETURN type() $$) AS (type agtype); ERROR: function ag_catalog.age_type() does not exist LINE 2: RETURN type() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- label () SELECT * FROM cypher('expr', $$ MATCH (v) RETURN label(v) $$) AS (label agtype); label ------- "v" "v" "v" "v1" "v1" "v1" (6 rows) SELECT * FROM cypher('expr', $$ MATCH ()-[e]->() RETURN label(e) $$) AS (label agtype); label ------- "e1" "e1" (2 rows) SELECT * FROM cypher('expr', $$ RETURN label({id: 0, label: 'typecast', properties: {}}::vertex) $$) AS (label agtype); label ------------ "typecast" (1 row) -- return NULL SELECT * FROM cypher('expr', $$ RETURN label(NULL) $$) AS (label agtype); label ------- (1 row) SELECT ag_catalog.age_label(NULL); age_label ----------- (1 row) -- should error SELECT * FROM cypher('expr', $$ MATCH p=()-[]->() RETURN label(p) $$) AS (label agtype); ERROR: label() argument must resolve to an edge or vertex SELECT * FROM cypher('expr', $$ RETURN label(1) $$) AS (label agtype); ERROR: label() argument must resolve to an edge or vertex SELECT * FROM cypher('expr', $$ MATCH (n) RETURN label([n]) $$) AS (label agtype); ERROR: label() argument must resolve to an edge or vertex SELECT * FROM cypher('expr', $$ RETURN label({id: 0, label: 'failed', properties: {}}) $$) AS (label agtype); ERROR: label() argument must resolve to an edge or vertex -- timestamp() can't be done as it will always have a different value -- size() of a string SELECT * FROM cypher('expr', $$ RETURN size('12345') $$) AS (size agtype); size ------ 5 (1 row) SELECT * FROM cypher('expr', $$ RETURN size("1234567890") $$) AS (size agtype); size ------ 10 (1 row) -- size() of an array SELECT * FROM cypher('expr', $$ RETURN size([1, 2, 3, 4, 5]) $$) AS (size agtype); size ------ 5 (1 row) SELECT * FROM cypher('expr', $$ RETURN size([]) $$) AS (size agtype); size ------ 0 (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN size(null) $$) AS (size agtype); size ------ (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN size(1234567890) $$) AS (size agtype); ERROR: size() unsupported argument SELECT * FROM cypher('expr', $$ RETURN size() $$) AS (size agtype); ERROR: function ag_catalog.age_size() does not exist LINE 2: RETURN size() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- head() of an array SELECT * FROM cypher('expr', $$ RETURN head([1, 2, 3, 4, 5]) $$) AS (head agtype); head ------ 1 (1 row) SELECT * FROM cypher('expr', $$ RETURN head([1]) $$) AS (head agtype); head ------ 1 (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN head([]) $$) AS (head agtype); head ------ (1 row) SELECT * FROM cypher('expr', $$ RETURN head(null) $$) AS (head agtype); head ------ (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN head(1234567890) $$) AS (head agtype); ERROR: head() argument must resolve to a list or null SELECT * FROM cypher('expr', $$ RETURN head() $$) AS (head agtype); ERROR: function ag_catalog.age_head() does not exist LINE 2: RETURN head() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- last() SELECT * FROM cypher('expr', $$ RETURN last([1, 2, 3, 4, 5]) $$) AS (last agtype); last ------ 5 (1 row) SELECT * FROM cypher('expr', $$ RETURN last([1]) $$) AS (last agtype); last ------ 1 (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN last([]) $$) AS (last agtype); last ------ (1 row) SELECT * FROM cypher('expr', $$ RETURN last(null) $$) AS (last agtype); last ------ (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN last(1234567890) $$) AS (last agtype); ERROR: last() argument must resolve to a list or null SELECT * FROM cypher('expr', $$ RETURN last() $$) AS (last agtype); ERROR: function ag_catalog.age_last() does not exist LINE 2: RETURN last() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- properties() SELECT * FROM cypher('expr', $$ MATCH (v) RETURN properties(v) $$) AS (properties agtype); properties ------------------- {} {"i": 0} {"i": 1} {"id": "initial"} {"id": "middle"} {"id": "end"} (6 rows) SELECT * FROM cypher('expr', $$ MATCH ()-[e]-() RETURN properties(e) $$) AS (properties agtype); properties ------------ {} {} {} {} (4 rows) -- should return null SELECT * FROM cypher('expr', $$ RETURN properties(null) $$) AS (properties agtype); properties ------------ (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN properties(1234) $$) AS (properties agtype); ERROR: properties() argument must be a vertex, an edge or null SELECT * FROM cypher('expr', $$ RETURN properties() $$) AS (properties agtype); ERROR: function ag_catalog.age_properties() does not exist LINE 2: RETURN properties() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- coalesce SELECT * FROM cypher('expr', $$ RETURN coalesce(null, 1, null, null) $$) AS (coalesce agtype); coalesce ---------- 1 (1 row) SELECT * FROM cypher('expr', $$ RETURN coalesce(null, -3.14, null, null) $$) AS (coalesce agtype); coalesce ---------- -3.14 (1 row) SELECT * FROM cypher('expr', $$ RETURN coalesce(null, "string", null, null) $$) AS (coalesce agtype); coalesce ---------- "string" (1 row) SELECT * FROM cypher('expr', $$ RETURN coalesce(null, null, null, []) $$) AS (coalesce agtype); coalesce ---------- [] (1 row) SELECT * FROM cypher('expr', $$ RETURN coalesce(null, null, null, {}) $$) AS (coalesce agtype); coalesce ---------- {} (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN coalesce(null, id(null), null) $$) AS (coalesce agtype); coalesce ---------- (1 row) SELECT * FROM cypher('expr', $$ RETURN coalesce(null) $$) AS (coalesce agtype); coalesce ---------- (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN coalesce() $$) AS (coalesce agtype); ERROR: syntax error at or near ")" LINE 2: RETURN coalesce() ^ -- toBoolean() SELECT * FROM cypher('expr', $$ RETURN toBoolean(true) $$) AS (toBoolean agtype); toboolean ----------- true (1 row) SELECT * FROM cypher('expr', $$ RETURN toBoolean(false) $$) AS (toBoolean agtype); toboolean ----------- false (1 row) SELECT * FROM cypher('expr', $$ RETURN toBoolean("true") $$) AS (toBoolean agtype); toboolean ----------- true (1 row) SELECT * FROM cypher('expr', $$ RETURN toBoolean("false") $$) AS (toBoolean agtype); toboolean ----------- false (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN toBoolean("falze") $$) AS (toBoolean agtype); toboolean ----------- (1 row) SELECT * FROM cypher('expr', $$ RETURN toBoolean(null) $$) AS (toBoolean agtype); toboolean ----------- (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN toBoolean(1) $$) AS (toBoolean agtype); ERROR: toBoolean() unsupported argument agtype 3 SELECT * FROM cypher('expr', $$ RETURN toBoolean() $$) AS (toBoolean agtype); ERROR: function ag_catalog.age_toboolean() does not exist LINE 2: RETURN toBoolean() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- toFloat() SELECT * FROM cypher('expr', $$ RETURN toFloat(1) $$) AS (toFloat agtype); tofloat --------- 1.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN toFloat(1.2) $$) AS (toFloat agtype); tofloat --------- 1.2 (1 row) SELECT * FROM cypher('expr', $$ RETURN toFloat("1") $$) AS (toFloat agtype); tofloat --------- 1.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN toFloat("1.2") $$) AS (toFloat agtype); tofloat --------- 1.2 (1 row) SELECT * FROM cypher('expr', $$ RETURN toFloat("1.2"::numeric) $$) AS (toFloat agtype); tofloat --------- 1.2 (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN toFloat("falze") $$) AS (toFloat agtype); tofloat --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN toFloat(null) $$) AS (toFloat agtype); tofloat --------- (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN toFloat(true) $$) AS (toFloat agtype); ERROR: toFloat() unsupported argument agtype 5 SELECT * FROM cypher('expr', $$ RETURN toFloat() $$) AS (toFloat agtype); ERROR: function ag_catalog.age_tofloat() does not exist LINE 2: RETURN toFloat() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- toInteger() SELECT * FROM cypher('expr', $$ RETURN toInteger(1) $$) AS (toInteger agtype); tointeger ----------- 1 (1 row) SELECT * FROM cypher('expr', $$ RETURN toInteger(1.2) $$) AS (toInteger agtype); tointeger ----------- 1 (1 row) SELECT * FROM cypher('expr', $$ RETURN toInteger("1") $$) AS (toInteger agtype); tointeger ----------- 1 (1 row) SELECT * FROM cypher('expr', $$ RETURN toInteger("1.2") $$) AS (toInteger agtype); tointeger ----------- 1 (1 row) SELECT * FROM cypher('expr', $$ RETURN toInteger("1.2"::numeric) $$) AS (toInteger agtype); tointeger ----------- 1 (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN toInteger("falze") $$) AS (toInteger agtype); tointeger ----------- (1 row) SELECT * FROM cypher('expr', $$ RETURN toInteger(null) $$) AS (toInteger agtype); tointeger ----------- (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN toInteger(true) $$) AS (toInteger agtype); ERROR: toInteger() unsupported argument agtype 5 SELECT * FROM cypher('expr', $$ RETURN toInteger() $$) AS (toInteger agtype); ERROR: function ag_catalog.age_tointeger() does not exist LINE 2: RETURN toInteger() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- length() of a path SELECT * FROM cypher('expr', $$ RETURN length([{id: 0, label: "vertex 0", properties: {}}::vertex, {id: 2, label: "edge 0", end_id: 1, start_id: 0, properties: {}}::edge, {id: 1, label: "vertex 1", properties: {}}::vertex]::path) $$) AS (length agtype); length -------- 1 (1 row) SELECT * FROM cypher('expr', $$ RETURN length([{id: 0, label: "vertex 0", properties: {}}::vertex, {id: 2, label: "edge 0", end_id: 1, start_id: 0, properties: {}}::edge, {id: 1, label: "vertex 1", properties: {}}::vertex, {id: 2, label: "edge 0", end_id: 1, start_id: 0, properties: {}}::edge, {id: 1, label: "vertex 1", properties: {}}::vertex]::path) $$) AS (length agtype); length -------- 2 (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN length(null) $$) AS (length agtype); length -------- (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN length(true) $$) AS (length agtype); ERROR: length() argument must resolve to a path or null SELECT * FROM cypher('expr', $$ RETURN length() $$) AS (length agtype); ERROR: function ag_catalog.age_length() does not exist LINE 2: RETURN length() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- -- toString() -- -- PG types SELECT * FROM age_toString(3); age_tostring -------------- "3" (1 row) SELECT * FROM age_toString(3.14); age_tostring -------------- "3.14" (1 row) SELECT * FROM age_toString(3.14::float); age_tostring -------------- "3.14" (1 row) SELECT * FROM age_toString(3.14::numeric); age_tostring -------------- "3.14" (1 row) SELECT * FROM age_toString(true); age_tostring -------------- "true" (1 row) SELECT * FROM age_toString(false); age_tostring -------------- "false" (1 row) SELECT * FROM age_toString('a string'); age_tostring -------------- "a string" (1 row) SELECT * FROM age_toString('a cstring'::cstring); age_tostring -------------- "a cstring" (1 row) SELECT * FROM age_toString('a text string'::text); age_tostring ----------------- "a text string" (1 row) -- agtypes SELECT * FROM age_toString(agtype_in('3')); age_tostring -------------- "3" (1 row) SELECT * FROM age_toString(agtype_in('3.14')); age_tostring -------------- "3.14" (1 row) SELECT * FROM age_toString(agtype_in('3.14::float')); age_tostring -------------- "3.14" (1 row) SELECT * FROM age_toString(agtype_in('3.14::numeric')); age_tostring -------------- "3.14" (1 row) SELECT * FROM age_toString(agtype_in('true')); age_tostring -------------- "true" (1 row) SELECT * FROM age_toString(agtype_in('false')); age_tostring -------------- "false" (1 row) SELECT * FROM age_toString(agtype_in('"a string"')); age_tostring -------------- "a string" (1 row) SELECT * FROM cypher('expr', $$ RETURN toString(3.14::numeric) $$) AS (results agtype); results --------- "3.14" (1 row) -- should return null SELECT * FROM age_toString(NULL); age_tostring -------------- (1 row) SELECT * FROM age_toString(agtype_in(null)); age_tostring -------------- (1 row) -- should fail SELECT * FROM age_toString(); ERROR: function age_tostring() does not exist LINE 1: SELECT * FROM age_toString(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN toString() $$) AS (results agtype); ERROR: function ag_catalog.age_tostring() does not exist LINE 1: SELECT * FROM cypher('expr', $$ RETURN toString() $$) AS (re... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- -- reverse(string) -- SELECT * FROM cypher('expr', $$ RETURN reverse("gnirts a si siht") $$) AS (results agtype); results -------------------- "this is a string" (1 row) SELECT * FROM age_reverse('gnirts a si siht'); age_reverse -------------------- "this is a string" (1 row) SELECT * FROM age_reverse('gnirts a si siht'::text); age_reverse -------------------- "this is a string" (1 row) SELECT * FROM age_reverse('gnirts a si siht'::cstring); age_reverse -------------------- "this is a string" (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN reverse(null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM age_reverse(null); age_reverse ------------- (1 row) -- should return error SELECT * FROM age_reverse([4923, 'abc', 521, NULL, 487]); ERROR: syntax error at or near "[" LINE 1: SELECT * FROM age_reverse([4923, 'abc', 521, NULL, 487]); ^ -- Should return the reversed list SELECT * FROM cypher('expr', $$ RETURN reverse([4923, 'abc', 521, NULL, 487]) $$) AS (u agtype); u ------------------------------- [487, null, 521, "abc", 4923] (1 row) SELECT * FROM cypher('expr', $$ RETURN reverse([4923]) $$) AS (u agtype); u -------- [4923] (1 row) SELECT * FROM cypher('expr', $$ RETURN reverse([4923, 257]) $$) as (u agtype); u ------------- [257, 4923] (1 row) SELECT * FROM cypher('expr', $$ RETURN reverse([4923, 257, null]) $$) as (u agtype); u ------------------- [null, 257, 4923] (1 row) SELECT * FROM cypher('expr', $$ RETURN reverse([4923, 257, 'tea']) $$) as (u agtype); u -------------------- ["tea", 257, 4923] (1 row) SELECT * FROM cypher('expr', $$ RETURN reverse([[1, 4, 7], 4923, [1, 2, 3], 'abc', 521, NULL, 487, ['fgt', 7, 10]]) $$) as (u agtype); u --------------------------------------------------------------------- [["fgt", 7, 10], 487, null, 521, "abc", [1, 2, 3], 4923, [1, 4, 7]] (1 row) SELECT * FROM cypher('expr', $$ RETURN reverse([4923, 257, {test1: "key"}]) $$) as (u agtype); u ------------------------------- [{"test1": "key"}, 257, 4923] (1 row) SELECT * FROM cypher('expr', $$ RETURN reverse([4923, 257, {test2: [1, 2, 3]}]) $$) as (u agtype); u ----------------------------------- [{"test2": [1, 2, 3]}, 257, 4923] (1 row) SELECT * FROM cypher('expr', $$ CREATE ({test: [1, 2, 3]}) $$) as (u agtype); u --- (0 rows) SELECT * FROM cypher('expr', $$ MATCH (v) WHERE exists(v.test) RETURN reverse(v.test) $$) as (u agtype); u ----------- [3, 2, 1] (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN reverse(true) $$) AS (results agtype); ERROR: reverse() unsupported argument agtype 5 SELECT * FROM age_reverse(true); ERROR: reverse() unsupported argument type 16 SELECT * FROM cypher('expr', $$ RETURN reverse(3.14) $$) AS (results agtype); ERROR: reverse() unsupported argument agtype 4 SELECT * FROM age_reverse(3.14); ERROR: reverse() unsupported argument type 1700 SELECT * FROM cypher('expr', $$ RETURN reverse() $$) AS (results agtype); ERROR: function ag_catalog.age_reverse() does not exist LINE 2: RETURN reverse() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_reverse(); ERROR: function age_reverse() does not exist LINE 1: SELECT * FROM age_reverse(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- -- toUpper() and toLower() -- SELECT * FROM cypher('expr', $$ RETURN toUpper('to uppercase') $$) AS (toUpper agtype); toupper ---------------- "TO UPPERCASE" (1 row) SELECT * FROM cypher('expr', $$ RETURN toLower('TO LOWERCASE') $$) AS (toLower agtype); tolower ---------------- "to lowercase" (1 row) SELECT * FROM age_toupper('text'::text); age_toupper ------------- "TEXT" (1 row) SELECT * FROM age_toupper('cstring'::cstring); age_toupper ------------- "CSTRING" (1 row) SELECT * FROM age_tolower('TEXT'::text); age_tolower ------------- "text" (1 row) SELECT * FROM age_tolower('CSTRING'::cstring); age_tolower ------------- "cstring" (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN toUpper(null) $$) AS (toUpper agtype); toupper --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN toLower(null) $$) AS (toLower agtype); tolower --------- (1 row) SELECT * FROM age_toupper(null); age_toupper ------------- (1 row) SELECT * FROM age_tolower(null); age_tolower ------------- (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN toUpper(true) $$) AS (toUpper agtype); ERROR: toUpper() unsupported argument agtype 5 SELECT * FROM cypher('expr', $$ RETURN toUpper() $$) AS (toUpper agtype); ERROR: function ag_catalog.age_toupper() does not exist LINE 2: RETURN toUpper() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN toLower(true) $$) AS (toLower agtype); ERROR: toLower() unsupported argument agtype 5 SELECT * FROM cypher('expr', $$ RETURN toLower() $$) AS (toLower agtype); ERROR: function ag_catalog.age_tolower() does not exist LINE 2: RETURN toLower() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_toupper(); ERROR: function age_toupper() does not exist LINE 1: SELECT * FROM age_toupper(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_tolower(); ERROR: function age_tolower() does not exist LINE 1: SELECT * FROM age_tolower(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- -- lTrim(), rTrim(), trim() -- SELECT * FROM cypher('expr', $$ RETURN lTrim(" string ") $$) AS (results agtype); results ------------- "string " (1 row) SELECT * FROM cypher('expr', $$ RETURN rTrim(" string ") $$) AS (results agtype); results ------------ " string" (1 row) SELECT * FROM cypher('expr', $$ RETURN trim(" string ") $$) AS (results agtype); results ---------- "string" (1 row) SELECT * FROM age_ltrim(' string '); age_ltrim ------------- "string " (1 row) SELECT * FROM age_rtrim(' string '); age_rtrim ------------ " string" (1 row) SELECT * FROM age_trim(' string '); age_trim ---------- "string" (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN lTrim(null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN rTrim(null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN trim(null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM age_ltrim(null); age_ltrim ----------- (1 row) SELECT * FROM age_rtrim(null); age_rtrim ----------- (1 row) SELECT * FROM age_trim(null); age_trim ---------- (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN lTrim(true) $$) AS (results agtype); ERROR: lTrim() unsupported argument agtype 5 SELECT * FROM cypher('expr', $$ RETURN rTrim(true) $$) AS (results agtype); ERROR: rTrim() unsupported argument agtype 5 SELECT * FROM cypher('expr', $$ RETURN trim(true) $$) AS (results agtype); ERROR: trim() unsupported argument agtype 5 SELECT * FROM cypher('expr', $$ RETURN lTrim() $$) AS (results agtype); ERROR: function ag_catalog.age_ltrim() does not exist LINE 2: RETURN lTrim() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN rTrim() $$) AS (results agtype); ERROR: function ag_catalog.age_rtrim() does not exist LINE 2: RETURN rTrim() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN trim() $$) AS (results agtype); ERROR: function ag_catalog.age_trim() does not exist LINE 2: RETURN trim() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_ltrim(); ERROR: function age_ltrim() does not exist LINE 1: SELECT * FROM age_ltrim(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_rtrim(); ERROR: function age_rtrim() does not exist LINE 1: SELECT * FROM age_rtrim(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_trim(); ERROR: function age_trim() does not exist LINE 1: SELECT * FROM age_trim(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- -- left(), right(), & substring() -- left() SELECT * FROM cypher('expr', $$ RETURN left("123456789", 1) $$) AS (results agtype); results --------- "1" (1 row) SELECT * FROM cypher('expr', $$ RETURN left("123456789", 3) $$) AS (results agtype); results --------- "123" (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN left("123456789", 0) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN left(null, 1) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN left(null, null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM age_left(null, 1); age_left ---------- (1 row) SELECT * FROM age_left(null, null); age_left ---------- (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN left("123456789", null) $$) AS (results agtype); ERROR: left() length parameter cannot be null SELECT * FROM cypher('expr', $$ RETURN left("123456789", -1) $$) AS (results agtype); ERROR: left() negative values are not supported for length SELECT * FROM cypher('expr', $$ RETURN left() $$) AS (results agtype); ERROR: function ag_catalog.age_left() does not exist LINE 2: RETURN left() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_left('123456789', null); ERROR: left() length parameter cannot be null SELECT * FROM age_left('123456789', -1); ERROR: left() negative values are not supported for length SELECT * FROM age_left(); ERROR: function age_left() does not exist LINE 1: SELECT * FROM age_left(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. --right() SELECT * FROM cypher('expr', $$ RETURN right("123456789", 1) $$) AS (results agtype); results --------- "9" (1 row) SELECT * FROM cypher('expr', $$ RETURN right("123456789", 3) $$) AS (results agtype); results --------- "789" (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN right("123456789", 0) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN right(null, 1) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN right(null, null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM age_right(null, 1); age_right ----------- (1 row) SELECT * FROM age_right(null, null); age_right ----------- (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN right("123456789", null) $$) AS (results agtype); ERROR: right() length parameter cannot be null SELECT * FROM cypher('expr', $$ RETURN right("123456789", -1) $$) AS (results agtype); ERROR: right() negative values are not supported for length SELECT * FROM cypher('expr', $$ RETURN right() $$) AS (results agtype); ERROR: function ag_catalog.age_right() does not exist LINE 2: RETURN right() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_right('123456789', null); ERROR: right() length parameter cannot be null SELECT * FROM age_right('123456789', -1); ERROR: right() negative values are not supported for length SELECT * FROM age_right(); ERROR: function age_right() does not exist LINE 1: SELECT * FROM age_right(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- substring() SELECT * FROM cypher('expr', $$ RETURN substring("0123456789", 0, 1) $$) AS (results agtype); results --------- "0" (1 row) SELECT * FROM cypher('expr', $$ RETURN substring("0123456789", 1, 3) $$) AS (results agtype); results --------- "123" (1 row) SELECT * FROM cypher('expr', $$ RETURN substring("0123456789", 3) $$) AS (results agtype); results ----------- "3456789" (1 row) SELECT * FROM cypher('expr', $$ RETURN substring("0123456789", 0) $$) AS (results agtype); results -------------- "0123456789" (1 row) SELECT * FROM age_substring('0123456789', 3, 2); age_substring --------------- "34" (1 row) SELECT * FROM age_substring('0123456789', 1); age_substring --------------- "123456789" (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN substring(null, null, null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN substring(null, null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN substring(null, 1) $$) AS (results agtype); results --------- (1 row) SELECT * FROM age_substring(null, null, null); age_substring --------------- (1 row) SELECT * FROM age_substring(null, null); age_substring --------------- (1 row) SELECT * FROM age_substring(null, 1); age_substring --------------- (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN substring("123456789", null) $$) AS (results agtype); ERROR: substring() offset or length cannot be null SELECT * FROM cypher('expr', $$ RETURN substring("123456789", 0, -1) $$) AS (results agtype); ERROR: substring() negative values are not supported for offset or length SELECT * FROM cypher('expr', $$ RETURN substring("123456789", -1) $$) AS (results agtype); ERROR: substring() negative values are not supported for offset or length SELECT * FROM cypher('expr', $$ RETURN substring("123456789") $$) AS (results agtype); ERROR: substring() invalid number of arguments SELECT * FROM age_substring('123456789', null); ERROR: substring() offset or length cannot be null SELECT * FROM age_substring('123456789', 0, -1); ERROR: substring() negative values are not supported for offset or length SELECT * FROM age_substring('123456789', -1); ERROR: substring() negative values are not supported for offset or length SELECT * FROM age_substring(); ERROR: function age_substring() does not exist LINE 1: SELECT * FROM age_substring(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- -- split() -- SELECT * FROM cypher('expr', $$ RETURN split("a,b,c,d,e,f", ",") $$) AS (results agtype); results -------------------------------- ["a", "b", "c", "d", "e", "f"] (1 row) SELECT * FROM cypher('expr', $$ RETURN split("a,b,c,d,e,f", "") $$) AS (results agtype); results --------------------------------------------------------- ["a", ",", "b", ",", "c", ",", "d", ",", "e", ",", "f"] (1 row) SELECT * FROM cypher('expr', $$ RETURN split("a,b,c,d,e,f", " ") $$) AS (results agtype); results ----------------- ["a,b,c,d,e,f"] (1 row) SELECT * FROM cypher('expr', $$ RETURN split("a,b,cd e,f", " ") $$) AS (results agtype); results ----------------------- ["a,b,cd", "", "e,f"] (1 row) SELECT * FROM cypher('expr', $$ RETURN split("a,b,cd e,f", " ") $$) AS (results agtype); results ------------------- ["a,b,cd", "e,f"] (1 row) SELECT * FROM cypher('expr', $$ RETURN split("a,b,c,d,e,f", "c,") $$) AS (results agtype); results ------------------- ["a,b,", "d,e,f"] (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN split(null, null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN split("a,b,c,d,e,f", null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN split(null, ",") $$) AS (results agtype); results --------- (1 row) SELECT * FROM age_split(null, null); age_split ----------- (1 row) SELECT * FROM age_split('a,b,c,d,e,f', null); age_split ----------- (1 row) SELECT * FROM age_split(null, ','); age_split ----------- (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN split(123456789, ",") $$) AS (results agtype); ERROR: split() unsupported argument agtype 3 SELECT * FROM cypher('expr', $$ RETURN split("a,b,c,d,e,f", -1) $$) AS (results agtype); ERROR: split() unsupported argument agtype 3 SELECT * FROM cypher('expr', $$ RETURN split("a,b,c,d,e,f") $$) AS (results agtype); ERROR: split() invalid number of arguments SELECT * FROM cypher('expr', $$ RETURN split() $$) AS (results agtype); ERROR: function ag_catalog.age_split() does not exist LINE 2: RETURN split() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_split(123456789, ','); ERROR: split() unsupported argument type 23 SELECT * FROM age_split('a,b,c,d,e,f', -1); ERROR: split() unsupported argument type 23 SELECT * FROM age_split('a,b,c,d,e,f'); ERROR: split() invalid number of arguments SELECT * FROM age_split(); ERROR: function age_split() does not exist LINE 1: SELECT * FROM age_split(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- -- replace() -- SELECT * FROM cypher('expr', $$ RETURN replace("Hello", "lo", "p") $$) AS (results agtype); results --------- "Help" (1 row) SELECT * FROM cypher('expr', $$ RETURN replace("Hello", "hello", "Good bye") $$) AS (results agtype); results --------- "Hello" (1 row) SELECT * FROM cypher('expr', $$ RETURN replace("abcabcabc", "abc", "a") $$) AS (results agtype); results --------- "aaa" (1 row) SELECT * FROM cypher('expr', $$ RETURN replace("abcabcabc", "ab", "") $$) AS (results agtype); results --------- "ccc" (1 row) SELECT * FROM cypher('expr', $$ RETURN replace("ababab", "ab", "ab") $$) AS (results agtype); results ---------- "ababab" (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN replace(null, null, null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN replace("Hello", null, null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN replace("Hello", "", null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN replace("", "", "") $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN replace("Hello", "Hello", "") $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN replace("", "Hello", "Mellow") $$) AS (results agtype); results --------- (1 row) SELECT * FROM age_replace(null, null, null); age_replace ------------- (1 row) SELECT * FROM age_replace('Hello', null, null); age_replace ------------- (1 row) SELECT * FROM age_replace('Hello', '', null); age_replace ------------- (1 row) SELECT * FROM age_replace('', '', ''); age_replace ------------- (1 row) SELECT * FROM age_replace('Hello', 'Hello', ''); age_replace ------------- (1 row) SELECT * FROM age_replace('', 'Hello', 'Mellow'); age_replace ------------- (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN replace() $$) AS (results agtype); ERROR: function ag_catalog.age_replace() does not exist LINE 2: RETURN replace() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN replace("Hello") $$) AS (results agtype); ERROR: replace() invalid number of arguments SELECT * FROM cypher('expr', $$ RETURN replace("Hello", null) $$) AS (results agtype); ERROR: replace() invalid number of arguments SELECT * FROM cypher('expr', $$ RETURN replace("Hello", "e", 1) $$) AS (results agtype); ERROR: replace() unsupported argument agtype 3 SELECT * FROM cypher('expr', $$ RETURN replace("Hello", 1, "e") $$) AS (results agtype); ERROR: replace() unsupported argument agtype 3 SELECT * FROM age_replace(); ERROR: function age_replace() does not exist LINE 1: SELECT * FROM age_replace(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_replace(null); ERROR: replace() invalid number of arguments SELECT * FROM age_replace(null, null); ERROR: replace() invalid number of arguments SELECT * FROM age_replace('Hello', 'e', 1); ERROR: replace() unsupported argument type 23 SELECT * FROM age_replace('Hello', 1, 'E'); ERROR: replace() unsupported argument type 23 -- -- sin, cos, tan, cot -- SELECT sin = results FROM cypher('expr', $$ RETURN sin(3.1415) $$) AS (results agtype), sin(3.1415); ?column? ---------- t (1 row) SELECT cos = results FROM cypher('expr', $$ RETURN cos(3.1415) $$) AS (results agtype), cos(3.1415); ?column? ---------- t (1 row) SELECT tan = results FROM cypher('expr', $$ RETURN tan(3.1415) $$) AS (results agtype), tan(3.1415); ?column? ---------- t (1 row) SELECT cot = results FROM cypher('expr', $$ RETURN cot(3.1415) $$) AS (results agtype), cot(3.1415); ?column? ---------- t (1 row) SELECT sin = age_sin FROM sin(3.1415), age_sin(3.1415); ?column? ---------- t (1 row) SELECT cos = age_cos FROM cos(3.1415), age_cos(3.1415); ?column? ---------- t (1 row) SELECT tan = age_tan FROM tan(3.1415), age_tan(3.1415); ?column? ---------- t (1 row) SELECT cot = age_cot FROM cot(3.1415), age_cot(3.1415); ?column? ---------- t (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN sin(null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN cos(null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN tan(null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN cot(null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM age_sin(null); age_sin --------- (1 row) SELECT * FROM age_cos(null); age_cos --------- (1 row) SELECT * FROM age_tan(null); age_tan --------- (1 row) SELECT * FROM age_cot(null); age_cot --------- (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN sin("0") $$) AS (results agtype); ERROR: sin() unsupported argument agtype 1 SELECT * FROM cypher('expr', $$ RETURN cos("0") $$) AS (results agtype); ERROR: cos() unsupported argument agtype 1 SELECT * FROM cypher('expr', $$ RETURN tan("0") $$) AS (results agtype); ERROR: tan() unsupported argument agtype 1 SELECT * FROM cypher('expr', $$ RETURN cot("0") $$) AS (results agtype); ERROR: cot() unsupported argument agtype 1 SELECT * FROM cypher('expr', $$ RETURN sin() $$) AS (results agtype); ERROR: function ag_catalog.age_sin() does not exist LINE 2: RETURN sin() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN cos() $$) AS (results agtype); ERROR: function ag_catalog.age_cos() does not exist LINE 2: RETURN cos() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN tan() $$) AS (results agtype); ERROR: function ag_catalog.age_tan() does not exist LINE 2: RETURN tan() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN cot() $$) AS (results agtype); ERROR: function ag_catalog.age_cot() does not exist LINE 2: RETURN cot() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_sin('0'); ERROR: sin() unsupported argument type 25 SELECT * FROM age_cos('0'); ERROR: cos() unsupported argument type 25 SELECT * FROM age_tan('0'); ERROR: tan() unsupported argument type 25 SELECT * FROM age_cot('0'); ERROR: cot() unsupported argument type 25 SELECT * FROM age_sin(); ERROR: function age_sin() does not exist LINE 1: SELECT * FROM age_sin(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_cos(); ERROR: function age_cos() does not exist LINE 1: SELECT * FROM age_cos(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_tan(); ERROR: function age_tan() does not exist LINE 1: SELECT * FROM age_tan(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_cot(); ERROR: function age_cot() does not exist LINE 1: SELECT * FROM age_cot(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- -- Arc functions: asin, acos, atan, & atan2 -- SELECT * FROM cypher('expr', $$ RETURN asin(1)*2 $$) AS (results agtype); results ------------------ 3.14159265358979 (1 row) SELECT * FROM cypher('expr', $$ RETURN acos(0)*2 $$) AS (results agtype); results ------------------ 3.14159265358979 (1 row) SELECT * FROM cypher('expr', $$ RETURN atan(1)*4 $$) AS (results agtype); results ------------------ 3.14159265358979 (1 row) SELECT * FROM cypher('expr', $$ RETURN atan2(1, 1)*4 $$) AS (results agtype); results ------------------ 3.14159265358979 (1 row) SELECT * FROM asin(1), age_asin(1); asin | age_asin -----------------+----------------- 1.5707963267949 | 1.5707963267949 (1 row) SELECT * FROM acos(0), age_acos(0); acos | age_acos -----------------+----------------- 1.5707963267949 | 1.5707963267949 (1 row) SELECT * FROM atan(1), age_atan(1); atan | age_atan -------------------+------------------- 0.785398163397448 | 0.785398163397448 (1 row) SELECT * FROM atan2(1, 1), age_atan2(1, 1); atan2 | age_atan2 -------------------+------------------- 0.785398163397448 | 0.785398163397448 (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN asin(1.1) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN acos(1.1) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN asin(-1.1) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN acos(-1.1) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN asin(null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN acos(null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN atan(null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN atan2(null, null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN atan2(null, 1) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN atan2(1, null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM age_asin(null); age_asin ---------- (1 row) SELECT * FROM age_acos(null); age_acos ---------- (1 row) SELECT * FROM age_atan(null); age_atan ---------- (1 row) SELECT * FROM age_atan2(null, null); age_atan2 ----------- (1 row) SELECT * FROM age_atan2(1, null); age_atan2 ----------- (1 row) SELECT * FROM age_atan2(null, 1); age_atan2 ----------- (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN asin("0") $$) AS (results agtype); ERROR: asin() unsupported argument agtype 1 SELECT * FROM cypher('expr', $$ RETURN acos("0") $$) AS (results agtype); ERROR: acos() unsupported argument agtype 1 SELECT * FROM cypher('expr', $$ RETURN atan("0") $$) AS (results agtype); ERROR: atan() unsupported argument agtype 1 SELECT * FROM cypher('expr', $$ RETURN atan2("0", 1) $$) AS (results agtype); ERROR: atan2() unsupported argument agtype 1 SELECT * FROM cypher('expr', $$ RETURN atan2(0, "1") $$) AS (results agtype); ERROR: atan2() unsupported argument agtype 1 SELECT * FROM cypher('expr', $$ RETURN asin() $$) AS (results agtype); ERROR: function ag_catalog.age_asin() does not exist LINE 2: RETURN asin() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN acos() $$) AS (results agtype); ERROR: function ag_catalog.age_acos() does not exist LINE 2: RETURN acos() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN atan() $$) AS (results agtype); ERROR: function ag_catalog.age_atan() does not exist LINE 2: RETURN atan() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN atan2() $$) AS (results agtype); ERROR: function ag_catalog.age_atan2() does not exist LINE 2: RETURN atan2() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN atan2(null) $$) AS (results agtype); ERROR: atan2() invalid number of arguments SELECT * FROM age_asin('0'); ERROR: asin() unsupported argument type 25 SELECT * FROM age_acos('0'); ERROR: acos() unsupported argument type 25 SELECT * FROM age_atan('0'); ERROR: atan() unsupported argument type 25 SELECT * FROM age_atan2('0', 1); ERROR: atan2() unsupported argument type 25 SELECT * FROM age_atan2(1, '0'); ERROR: atan2() unsupported argument type 25 SELECT * FROM age_asin(); ERROR: function age_asin() does not exist LINE 1: SELECT * FROM age_asin(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_acos(); ERROR: function age_acos() does not exist LINE 1: SELECT * FROM age_acos(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_atan(); ERROR: function age_atan() does not exist LINE 1: SELECT * FROM age_atan(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_atan2(); ERROR: function age_atan2() does not exist LINE 1: SELECT * FROM age_atan2(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM age_atan2(1); ERROR: atan2() invalid number of arguments -- -- pi -- SELECT * FROM cypher('expr', $$ RETURN pi() $$) AS (results agtype); results ------------------ 3.14159265358979 (1 row) SELECT * FROM cypher('expr', $$ RETURN sin(pi()) $$) AS (results agtype); results ---------------------- 1.22464679914735e-16 (1 row) SELECT * FROM cypher('expr', $$ RETURN sin(pi()/4) $$) AS (results agtype); results ------------------- 0.707106781186547 (1 row) SELECT * FROM cypher('expr', $$ RETURN cos(pi()) $$) AS (results agtype); results --------- -1.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN cos(pi()/2) $$) AS (results agtype); results ---------------------- 6.12323399573677e-17 (1 row) SELECT * FROM cypher('expr', $$ RETURN sin(pi()/2) $$) AS (results agtype); results --------- 1.0 (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN pi(null) $$) AS (results agtype); ERROR: function pg_catalog.pi(agtype) does not exist LINE 2: RETURN pi(null) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN pi(1) $$) AS (results agtype); ERROR: function pg_catalog.pi(agtype) does not exist LINE 2: RETURN pi(1) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- -- radians() & degrees() -- SELECT * FROM cypher('expr', $$ RETURN radians(0) $$) AS (results agtype); results --------- 0.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN degrees(0) $$) AS (results agtype); results --------- 0.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN radians(360), 2*pi() $$) AS (results agtype, Two_PI agtype); results | two_pi ------------------+------------------ 6.28318530717959 | 6.28318530717959 (1 row) SELECT * FROM cypher('expr', $$ RETURN degrees(2*pi()) $$) AS (results agtype); results --------- 360.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN radians(180), pi() $$) AS (results agtype, PI agtype); results | pi ------------------+------------------ 3.14159265358979 | 3.14159265358979 (1 row) SELECT * FROM cypher('expr', $$ RETURN degrees(pi()) $$) AS (results agtype); results --------- 180.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN radians(90), pi()/2 $$) AS (results agtype, Half_PI agtype); results | half_pi -----------------+----------------- 1.5707963267949 | 1.5707963267949 (1 row) SELECT * FROM cypher('expr', $$ RETURN degrees(pi()/2) $$) AS (results agtype); results --------- 90.0 (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN radians(null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN degrees(null) $$) AS (results agtype); results --------- (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN radians() $$) AS (results agtype); ERROR: function ag_catalog.age_radians() does not exist LINE 2: RETURN radians() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN degrees() $$) AS (results agtype); ERROR: function ag_catalog.age_degrees() does not exist LINE 2: RETURN degrees() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN radians("1") $$) AS (results agtype); ERROR: radians() unsupported argument agtype 1 SELECT * FROM cypher('expr', $$ RETURN degrees("1") $$) AS (results agtype); ERROR: degrees() unsupported argument agtype 1 -- -- abs(), ceil(), floor(), & round() -- SELECT * FROM cypher('expr', $$ RETURN abs(0) $$) AS (results agtype); results --------- 0 (1 row) SELECT * FROM cypher('expr', $$ RETURN abs(10) $$) AS (results agtype); results --------- 10 (1 row) SELECT * FROM cypher('expr', $$ RETURN abs(-10) $$) AS (results agtype); results --------- 10 (1 row) SELECT * FROM cypher('expr', $$ RETURN ceil(0) $$) AS (results agtype); results --------- 0.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN ceil(1) $$) AS (results agtype); results --------- 1.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN ceil(-1) $$) AS (results agtype); results --------- -1.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN ceil(1.01) $$) AS (results agtype); results --------- 2.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN ceil(-1.01) $$) AS (results agtype); results --------- -1.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN floor(0) $$) AS (results agtype); results --------- 0.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN floor(1) $$) AS (results agtype); results --------- 1.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN floor(-1) $$) AS (results agtype); results --------- -1.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN floor(1.01) $$) AS (results agtype); results --------- 1.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN floor(-1.01) $$) AS (results agtype); results --------- -2.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN round(0) $$) AS (results agtype); results --------- 0.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN round(4.49999999) $$) AS (results agtype); results --------- 4.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN round(4.5) $$) AS (results agtype); results --------- 5.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN round(-4.49999999) $$) AS (results agtype); results --------- -4.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN round(-4.5) $$) AS (results agtype); results --------- -5.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN round(7.4163, 3) $$) AS (results agtype); results --------- 7.416 (1 row) SELECT * FROM cypher('expr', $$ RETURN round(7.416343479, 8) $$) AS (results agtype); results ------------ 7.41634348 (1 row) SELECT * FROM cypher('expr', $$ RETURN round(7.416343479, NULL) $$) AS (results agtype); results --------- 7.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN round(NULL, 7) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN round(7, 2) $$) AS (results agtype); results --------- 7.0 (1 row) SELECT * FROM cypher('expr', $$ RETURN round(7.4342, 2.1123) $$) AS (results agtype); results --------- 7.43 (1 row) SELECT * FROM cypher('expr', $$ RETURN round(NULL, NULL) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN sign(10) $$) AS (results agtype); results --------- 1 (1 row) SELECT * FROM cypher('expr', $$ RETURN sign(-10) $$) AS (results agtype); results --------- -1 (1 row) SELECT * FROM cypher('expr', $$ RETURN sign(0) $$) AS (results agtype); results --------- 0 (1 row) -- should return null SELECT * FROM cypher('expr', $$ RETURN abs(null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN ceil(null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN floor(null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN round(null) $$) AS (results agtype); results --------- (1 row) SELECT * FROM cypher('expr', $$ RETURN sign(null) $$) AS (results agtype); results --------- (1 row) -- should fail SELECT * FROM cypher('expr', $$ RETURN abs() $$) AS (results agtype); ERROR: function ag_catalog.age_abs() does not exist LINE 2: RETURN abs() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN ceil() $$) AS (results agtype); ERROR: function ag_catalog.age_ceil() does not exist LINE 2: RETURN ceil() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN floor() $$) AS (results agtype); ERROR: function ag_catalog.age_floor() does not exist LINE 2: RETURN floor() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN round() $$) AS (results agtype); ERROR: function ag_catalog.age_round() does not exist LINE 2: RETURN round() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN sign() $$) AS (results agtype); ERROR: function ag_catalog.age_sign() does not exist LINE 2: RETURN sign() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('expr', $$ RETURN abs("1") $$) AS (results agtype); ERROR: abs() unsupported argument agtype 1 SELECT * FROM cypher('expr', $$ RETURN ceil("1") $$) AS (results agtype); ERROR: ceil() unsupported argument agtype 1 SELECT * FROM cypher('expr', $$ RETURN floor("1") $$) AS (results agtype); ERROR: floor() unsupported argument agtype 1 SELECT * FROM cypher('expr', $$ RETURN round("1") $$) AS (results agtype); ERROR: round() unsupported argument agtype 1 SELECT * FROM cypher('expr', $$ RETURN sign("1") $$) AS (results agtype); ERROR: sign() unsupported argument agtype 1 -- -- rand() -- -- should select 0 rows as rand() is in [0,1) SELECT * FROM cypher('expr', $$ RETURN rand() $$) AS (result agtype) WHERE result >= 1 or result < 0; result -------- (0 rows) -- should select 0 rows as rand() should not return the same value SELECT * FROM cypher('expr', $$ RETURN rand() $$) AS cypher_1(result agtype), cypher('expr', $$ RETURN rand() $$) AS cypher_2(result agtype) WHERE cypher_1.result = cypher_2.result; result | result --------+-------- (0 rows) -- -- log (ln) and log10 -- SELECT * from cypher('expr', $$ RETURN log(2.718281828459045) $$) as (result agtype); result -------- 1.0 (1 row) SELECT * from cypher('expr', $$ RETURN log10(10) $$) as (result agtype); result -------- 1.0 (1 row) -- should return null SELECT * from cypher('expr', $$ RETURN log(null) $$) as (result agtype); result -------- (1 row) SELECT * from cypher('expr', $$ RETURN log10(null) $$) as (result agtype); result -------- (1 row) SELECT * from cypher('expr', $$ RETURN log(0) $$) as (result agtype); result -------- (1 row) SELECT * from cypher('expr', $$ RETURN log10(0) $$) as (result agtype); result -------- (1 row) SELECT * from cypher('expr', $$ RETURN log(-1) $$) as (result agtype); result -------- (1 row) SELECT * from cypher('expr', $$ RETURN log10(-1) $$) as (result agtype); result -------- (1 row) -- should fail SELECT * from cypher('expr', $$ RETURN log() $$) as (result agtype); ERROR: function ag_catalog.age_log() does not exist LINE 2: RETURN log() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * from cypher('expr', $$ RETURN log10() $$) as (result agtype); ERROR: function ag_catalog.age_log10() does not exist LINE 2: RETURN log10() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- -- e() -- SELECT * from cypher('expr', $$ RETURN e() $$) as (result agtype); result ------------------ 2.71828182845905 (1 row) SELECT * from cypher('expr', $$ RETURN log(e()) $$) as (result agtype); result -------- 1.0 (1 row) -- -- exp() aka e^x -- SELECT * from cypher('expr', $$ RETURN exp(1) $$) as (result agtype); result ------------------ 2.71828182845905 (1 row) SELECT * from cypher('expr', $$ RETURN exp(0) $$) as (result agtype); result -------- 1.0 (1 row) -- should return null SELECT * from cypher('expr', $$ RETURN exp(null) $$) as (result agtype); result -------- (1 row) -- should fail SELECT * from cypher('expr', $$ RETURN exp() $$) as (result agtype); ERROR: function ag_catalog.age_exp() does not exist LINE 2: RETURN exp() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * from cypher('expr', $$ RETURN exp("1") $$) as (result agtype); ERROR: exp() unsupported argument agtype 1 -- -- sqrt() -- SELECT * from cypher('expr', $$ RETURN sqrt(25) $$) as (result agtype); result -------- 5.0 (1 row) SELECT * from cypher('expr', $$ RETURN sqrt(1) $$) as (result agtype); result -------- 1.0 (1 row) SELECT * from cypher('expr', $$ RETURN sqrt(0) $$) as (result agtype); result -------- 0.0 (1 row) -- should return null SELECT * from cypher('expr', $$ RETURN sqrt(-1) $$) as (result agtype); result -------- (1 row) SELECT * from cypher('expr', $$ RETURN sqrt(null) $$) as (result agtype); result -------- (1 row) -- should fail SELECT * from cypher('expr', $$ RETURN sqrt() $$) as (result agtype); ERROR: function ag_catalog.age_sqrt() does not exist LINE 2: RETURN sqrt() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * from cypher('expr', $$ RETURN sqrt("1") $$) as (result agtype); ERROR: sqrt() unsupported argument agtype 1 -- -- user defined function expressions - using pg functions for these tests -- SELECT * from cypher('expr', $$ RETURN pg_catalog.sqrt(25::pg_float8) $$) as (result agtype); result -------- 5.0 (1 row) SELECT * from cypher('expr', $$ RETURN ag_catalog.age_sqrt(25) $$) as (result agtype); result -------- 5.0 (1 row) -- should return null SELECT * from cypher('expr', $$ RETURN pg_catalog.sqrt(null::pg_float8) $$) as (result agtype); result -------- (1 row) -- should fail SELECT * from cypher('expr', $$ RETURN pg_catalog.sqrt() $$) as (result agtype); ERROR: function pg_catalog.sqrt() does not exist LINE 2: RETURN pg_catalog.sqrt() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * from cypher('expr', $$ RETURN pg_catalog.sqrt("1"::pg_float8) $$) as (result agtype); ERROR: cannot cast agtype string to type float SELECT * from cypher('expr', $$ RETURN pg_catalog.sqrt(-1::pg_float8) $$) as (result agtype); ERROR: cannot take square root of a negative number SELECT * from cypher('expr', $$ RETURN something.pg_catalog.sqrt("1"::pg_float8) $$) as (result agtype); ERROR: invalid indirection syntax LINE 2: RETURN something.pg_catalog.sqrt("1"::pg_float8) ^ -- should fail do to schema but using a reserved_keyword SELECT * from cypher('expr', $$ RETURN distinct.age_sqrt(25) $$) as (result agtype); ERROR: schema "distinct" does not exist LINE 2: RETURN distinct.age_sqrt(25) ^ SELECT * from cypher('expr', $$ RETURN contains.age_sqrt(25) $$) as (result agtype); ERROR: schema "contains" does not exist LINE 2: RETURN contains.age_sqrt(25) ^ -- -- aggregate functions avg(), sum(), count(), & count(*) -- SELECT create_graph('UCSC'); NOTICE: graph "UCSC" has been created create_graph -------------- (1 row) SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jack", gpa: 3.0, age: 21, zip: 94110})$$) AS (a agtype); a --- (0 rows) SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jill", gpa: 3.5, age: 27, zip: 95060})$$) AS (a agtype); a --- (0 rows) SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jim", gpa: 3.75, age: 32, zip: 96062})$$) AS (a agtype); a --- (0 rows) SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Rick", gpa: 2.5, age: 24, zip: "95060"})$$) AS (a agtype); a --- (0 rows) SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Ann", gpa: 3.8::numeric, age: 23})$$) AS (a agtype); a --- (0 rows) SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Derek", gpa: 4.0, age: 19, zip: 90210})$$) AS (a agtype); a --- (0 rows) SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jessica", gpa: 3.9::numeric, age: 20})$$) AS (a agtype); a --- (0 rows) SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN (u) $$) AS (vertex agtype); vertex ----------------------------------------------------------------------------------------------------------------------------- {"id": 844424930131969, "label": "students", "properties": {"age": 21, "gpa": 3.0, "zip": 94110, "name": "Jack"}}::vertex {"id": 844424930131970, "label": "students", "properties": {"age": 27, "gpa": 3.5, "zip": 95060, "name": "Jill"}}::vertex {"id": 844424930131971, "label": "students", "properties": {"age": 32, "gpa": 3.75, "zip": 96062, "name": "Jim"}}::vertex {"id": 844424930131972, "label": "students", "properties": {"age": 24, "gpa": 2.5, "zip": "95060", "name": "Rick"}}::vertex {"id": 844424930131973, "label": "students", "properties": {"age": 23, "gpa": 3.8::numeric, "name": "Ann"}}::vertex {"id": 844424930131974, "label": "students", "properties": {"age": 19, "gpa": 4.0, "zip": 90210, "name": "Derek"}}::vertex {"id": 844424930131975, "label": "students", "properties": {"age": 20, "gpa": 3.9::numeric, "name": "Jessica"}}::vertex (7 rows) SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN avg(u.gpa), sum(u.gpa), sum(u.gpa)/count(u.gpa), count(u.gpa), count(*) $$) AS (avg agtype, sum agtype, sum_divided_by_count agtype, count agtype, count_star agtype); avg | sum | sum_divided_by_count | count | count_star ------------------+----------------+-----------------------------+-------+------------ 3.49285714285714 | 24.45::numeric | 3.4928571428571429::numeric | 7 | 7 (1 row) -- add in 2 null gpa records SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Dave", age: 24})$$) AS (a agtype); a --- (0 rows) SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Mike", age: 18})$$) AS (a agtype); a --- (0 rows) SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN (u) $$) AS (vertex agtype); vertex ----------------------------------------------------------------------------------------------------------------------------- {"id": 844424930131969, "label": "students", "properties": {"age": 21, "gpa": 3.0, "zip": 94110, "name": "Jack"}}::vertex {"id": 844424930131970, "label": "students", "properties": {"age": 27, "gpa": 3.5, "zip": 95060, "name": "Jill"}}::vertex {"id": 844424930131971, "label": "students", "properties": {"age": 32, "gpa": 3.75, "zip": 96062, "name": "Jim"}}::vertex {"id": 844424930131972, "label": "students", "properties": {"age": 24, "gpa": 2.5, "zip": "95060", "name": "Rick"}}::vertex {"id": 844424930131973, "label": "students", "properties": {"age": 23, "gpa": 3.8::numeric, "name": "Ann"}}::vertex {"id": 844424930131974, "label": "students", "properties": {"age": 19, "gpa": 4.0, "zip": 90210, "name": "Derek"}}::vertex {"id": 844424930131975, "label": "students", "properties": {"age": 20, "gpa": 3.9::numeric, "name": "Jessica"}}::vertex {"id": 844424930131976, "label": "students", "properties": {"age": 24, "name": "Dave"}}::vertex {"id": 844424930131977, "label": "students", "properties": {"age": 18, "name": "Mike"}}::vertex (9 rows) SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN avg(u.gpa), sum(u.gpa), sum(u.gpa)/count(u.gpa), count(u.gpa), count(*) $$) AS (avg agtype, sum agtype, sum_divided_by_count agtype, count agtype, count_star agtype); avg | sum | sum_divided_by_count | count | count_star ------------------+----------------+-----------------------------+-------+------------ 3.49285714285714 | 24.45::numeric | 3.4928571428571429::numeric | 7 | 9 (1 row) -- should return null SELECT * FROM cypher('UCSC', $$ RETURN avg(NULL) $$) AS (avg agtype); avg ----- (1 row) SELECT * FROM cypher('UCSC', $$ RETURN sum(NULL) $$) AS (sum agtype); sum ----- (1 row) -- should return 0 SELECT * FROM cypher('UCSC', $$ RETURN count(NULL) $$) AS (count agtype); count ------- 0 (1 row) -- should fail SELECT * FROM cypher('UCSC', $$ RETURN avg() $$) AS (avg agtype); ERROR: function ag_catalog.age_avg() does not exist LINE 1: SELECT * FROM cypher('UCSC', $$ RETURN avg() $$) AS (avg agt... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('UCSC', $$ RETURN sum() $$) AS (sum agtype); ERROR: function ag_catalog.age_sum() does not exist LINE 1: SELECT * FROM cypher('UCSC', $$ RETURN sum() $$) AS (sum agt... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('UCSC', $$ RETURN count() $$) AS (count agtype); ERROR: pg_catalog.count(*) must be used to call a parameterless aggregate function LINE 1: SELECT * FROM cypher('UCSC', $$ RETURN count() $$) AS (count... ^ -- -- aggregate functions min() & max() -- SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN min(u.gpa), max(u.gpa), count(u.gpa), count(*) $$) AS (min agtype, max agtype, count agtype, count_star agtype); min | max | count | count_star -----+-----+-------+------------ 2.5 | 4.0 | 7 | 9 (1 row) SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN min(u.gpa), max(u.gpa), count(u.gpa), count(*) $$) AS (min agtype, max agtype, count agtype, count_star agtype); min | max | count | count_star -----+-----+-------+------------ 2.5 | 4.0 | 7 | 9 (1 row) SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN min(u.name), max(u.name), count(u.name), count(*) $$) AS (min agtype, max agtype, count agtype, count_star agtype); min | max | count | count_star -------+--------+-------+------------ "Ann" | "Rick" | 9 | 9 (1 row) -- check that min() & max() can work against mixed types SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN min(u.zip), max(u.zip), count(u.zip), count(*) $$) AS (min agtype, max agtype, count agtype, count_star agtype); min | max | count | count_star ---------+-------+-------+------------ "95060" | 96062 | 5 | 9 (1 row) CREATE TABLE min_max_tbl (oid oid); insert into min_max_tbl VALUES (16), (17188), (1000), (869); SELECT age_min(oid::int), age_max(oid::int) FROM min_max_tbl; age_min | age_max ---------+--------- 16 | 17188 (1 row) SELECT age_min(oid::int::float), age_max(oid::int::float) FROM min_max_tbl; age_min | age_max ---------+--------- 16.0 | 17188.0 (1 row) SELECT age_min(oid::int::float::numeric), age_max(oid::int::float::numeric) FROM min_max_tbl; age_min | age_max -------------+---------------- 16::numeric | 17188::numeric (1 row) SELECT age_min(oid::text), age_max(oid::text) FROM min_max_tbl; age_min | age_max ---------+--------- "1000" | "869" (1 row) DROP TABLE min_max_tbl; -- should return null SELECT * FROM cypher('UCSC', $$ RETURN min(NULL) $$) AS (min agtype); min ----- (1 row) SELECT * FROM cypher('UCSC', $$ RETURN max(NULL) $$) AS (max agtype); max ----- (1 row) SELECT age_min(NULL); age_min --------- (1 row) SELECT age_min(agtype_in('null')); age_min --------- (1 row) SELECT age_max(NULL); age_max --------- (1 row) SELECT age_max(agtype_in('null')); age_max --------- (1 row) -- should fail SELECT * FROM cypher('UCSC', $$ RETURN min() $$) AS (min agtype); ERROR: function ag_catalog.age_min() does not exist LINE 1: SELECT * FROM cypher('UCSC', $$ RETURN min() $$) AS (min agt... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('UCSC', $$ RETURN max() $$) AS (max agtype); ERROR: function ag_catalog.age_max() does not exist LINE 1: SELECT * FROM cypher('UCSC', $$ RETURN max() $$) AS (max agt... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT age_min(); ERROR: function age_min() does not exist LINE 1: SELECT age_min(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT age_min(); ERROR: function age_min() does not exist LINE 1: SELECT age_min(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- -- aggregate functions stDev() & stDevP() -- SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN stDev(u.gpa), stDevP(u.gpa) $$) AS (stDev agtype, stDevP agtype); stdev | stdevp -------------------+------------------- 0.549566929066705 | 0.508800109100231 (1 row) -- should return 0 SELECT * FROM cypher('UCSC', $$ RETURN stDev(NULL) $$) AS (stDev agtype); stdev ------- 0.0 (1 row) SELECT * FROM cypher('UCSC', $$ RETURN stDevP(NULL) $$) AS (stDevP agtype); stdevp -------- 0.0 (1 row) -- should fail SELECT * FROM cypher('UCSC', $$ RETURN stDev() $$) AS (stDev agtype); ERROR: function ag_catalog.age_stdev() does not exist LINE 1: SELECT * FROM cypher('UCSC', $$ RETURN stDev() $$) AS (stDev... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM cypher('UCSC', $$ RETURN stDevP() $$) AS (stDevP agtype); ERROR: function ag_catalog.age_stdevp() does not exist LINE 1: SELECT * FROM cypher('UCSC', $$ RETURN stDevP() $$) AS (stDe... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- -- aggregate functions percentileCont() & percentileDisc() -- SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN percentileCont(u.gpa, .55), percentileDisc(u.gpa, .55), percentileCont(u.gpa, .9), percentileDisc(u.gpa, .9) $$) AS (percentileCont1 agtype, percentileDisc1 agtype, percentileCont2 agtype, percentileDisc2 agtype); percentilecont1 | percentiledisc1 | percentilecont2 | percentiledisc2 -----------------+-----------------+-----------------+----------------- 3.765 | 3.75 | 3.94 | 4.0 (1 row) SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN percentileCont(u.gpa, .55) $$) AS (percentileCont agtype); percentilecont ---------------- 3.765 (1 row) SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN percentileDisc(u.gpa, .55) $$) AS (percentileDisc agtype); percentiledisc ---------------- 3.75 (1 row) -- should return null SELECT * FROM cypher('UCSC', $$ RETURN percentileCont(NULL, .5) $$) AS (percentileCont agtype); percentilecont ---------------- (1 row) SELECT * FROM cypher('UCSC', $$ RETURN percentileDisc(NULL, .5) $$) AS (percentileDisc agtype); percentiledisc ---------------- (1 row) -- should fail SELECT * FROM cypher('UCSC', $$ RETURN percentileCont(.5, NULL) $$) AS (percentileCont agtype); ERROR: percentile value NULL is not a valid numeric value SELECT * FROM cypher('UCSC', $$ RETURN percentileDisc(.5, NULL) $$) AS (percentileDisc agtype); ERROR: percentile value NULL is not a valid numeric value -- -- aggregate function collect() -- SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN collect(u.name), collect(u.age), collect(u.gpa), collect(u.zip) $$) AS (name agtype, age agtype, gqa agtype, zip agtype); name | age | gqa | zip ----------------------------------------------------------------------------+--------------------------------------+--------------------------------------------------------+--------------------------------------- ["Jack", "Jill", "Jim", "Rick", "Ann", "Derek", "Jessica", "Dave", "Mike"] | [21, 27, 32, 24, 23, 19, 20, 24, 18] | [3.0, 3.5, 3.75, 2.5, 3.8::numeric, 4.0, 3.9::numeric] | [94110, 95060, 96062, "95060", 90210] (1 row) SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN collect(u.gpa), collect(u.gpa) $$) AS (gpa1 agtype, gpa2 agtype); gpa1 | gpa2 --------------------------------------------------------+-------------------------------------------------------- [3.0, 3.5, 3.75, 2.5, 3.8::numeric, 4.0, 3.9::numeric] | [3.0, 3.5, 3.75, 2.5, 3.8::numeric, 4.0, 3.9::numeric] (1 row) SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN collect(u.zip), collect(u.zip) $$) AS (zip1 agtype, zip2 agtype); zip1 | zip2 ---------------------------------------+--------------------------------------- [94110, 95060, 96062, "95060", 90210] | [94110, 95060, 96062, "95060", 90210] (1 row) SELECT * FROM cypher('UCSC', $$ RETURN collect(5) $$) AS (result agtype); result -------- [5] (1 row) -- should return an empty aray SELECT * FROM cypher('UCSC', $$ RETURN collect(NULL) $$) AS (empty agtype); empty ------- [] (1 row) SELECT * FROM cypher('UCSC', $$ MATCH (u) WHERE u.name =~ "doesn't exist" RETURN collect(u.name) $$) AS (name agtype); name ------ [] (1 row) -- should fail SELECT * FROM cypher('UCSC', $$ RETURN collect() $$) AS (collect agtype); ERROR: function ag_catalog.age_collect() does not exist LINE 1: SELECT * FROM cypher('UCSC', $$ RETURN collect() $$) AS (col... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- test DISTINCT inside aggregate functions SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Sven", gpa: 3.2, age: 27, zip: 94110})$$) AS (a agtype); a --- (0 rows) SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN (u) $$) AS (vertex agtype); vertex ----------------------------------------------------------------------------------------------------------------------------- {"id": 844424930131969, "label": "students", "properties": {"age": 21, "gpa": 3.0, "zip": 94110, "name": "Jack"}}::vertex {"id": 844424930131970, "label": "students", "properties": {"age": 27, "gpa": 3.5, "zip": 95060, "name": "Jill"}}::vertex {"id": 844424930131971, "label": "students", "properties": {"age": 32, "gpa": 3.75, "zip": 96062, "name": "Jim"}}::vertex {"id": 844424930131972, "label": "students", "properties": {"age": 24, "gpa": 2.5, "zip": "95060", "name": "Rick"}}::vertex {"id": 844424930131973, "label": "students", "properties": {"age": 23, "gpa": 3.8::numeric, "name": "Ann"}}::vertex {"id": 844424930131974, "label": "students", "properties": {"age": 19, "gpa": 4.0, "zip": 90210, "name": "Derek"}}::vertex {"id": 844424930131975, "label": "students", "properties": {"age": 20, "gpa": 3.9::numeric, "name": "Jessica"}}::vertex {"id": 844424930131976, "label": "students", "properties": {"age": 24, "name": "Dave"}}::vertex {"id": 844424930131977, "label": "students", "properties": {"age": 18, "name": "Mike"}}::vertex {"id": 844424930131978, "label": "students", "properties": {"age": 27, "gpa": 3.2, "zip": 94110, "name": "Sven"}}::vertex (10 rows) SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN count(u.zip), count(DISTINCT u.zip) $$) AS (zip agtype, distinct_zip agtype); zip | distinct_zip -----+-------------- 6 | 5 (1 row) SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN count(u.age), count(DISTINCT u.age) $$) AS (age agtype, distinct_age agtype); age | distinct_age -----+-------------- 10 | 8 (1 row) -- test AUTO GROUP BY for aggregate functions SELECT create_graph('group_by'); NOTICE: graph "group_by" has been created create_graph -------------- (1 row) SELECT * FROM cypher('group_by', $$CREATE (:row {i: 1, j: 2, k:3})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('group_by', $$CREATE (:row {i: 1, j: 2, k:4})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('group_by', $$CREATE (:row {i: 1, j: 3, k:5})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('group_by', $$CREATE (:row {i: 2, j: 3, k:6})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('group_by', $$MATCH (u:row) RETURN u.i, u.j, u.k$$) AS (i agtype, j agtype, k agtype); i | j | k ---+---+--- 1 | 2 | 3 1 | 2 | 4 1 | 3 | 5 2 | 3 | 6 (4 rows) SELECT * FROM cypher('group_by', $$MATCH (u:row) RETURN u.i, u.j, sum(u.k)$$) AS (i agtype, j agtype, sumk agtype); i | j | sumk ---+---+------ 1 | 2 | 7 2 | 3 | 6 1 | 3 | 5 (3 rows) SELECT * FROM cypher('group_by', $$CREATE (:L {a: 1, b: 2, c:3})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('group_by', $$CREATE (:L {a: 2, b: 3, c:1})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('group_by', $$CREATE (:L {a: 3, b: 1, c:2})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('group_by', $$MATCH (x:L) RETURN x.a, x.b, x.c, x.a + count(*) + x.b + count(*) + x.c$$) AS (a agtype, b agtype, c agtype, result agtype); a | b | c | result ---+---+---+-------- 3 | 1 | 2 | 8 2 | 3 | 1 | 8 1 | 2 | 3 | 8 (3 rows) SELECT * FROM cypher('group_by', $$MATCH (x:L) RETURN x.a + x.b + x.c, x.a + x.b + x.c + count(*) + count(*) $$) AS (a_b_c agtype, result agtype); a_b_c | result -------+-------- 6 | 12 (1 row) -- with WITH clause SELECT * FROM cypher('group_by', $$MATCH(x:L) WITH x, count(x) AS c RETURN x.a + x.b + x.c + c$$) AS (result agtype); result -------- 7 7 7 (3 rows) SELECT * FROM cypher('group_by', $$MATCH(x:L) WITH x, count(x) AS c RETURN x.a + x.b + x.c + c + c$$) AS (result agtype); result -------- 8 8 8 (3 rows) SELECT * FROM cypher('group_by', $$MATCH(x:L) WITH x.a + x.b + x.c AS v, count(x) as c RETURN v + c + c $$) AS (result agtype); result -------- 12 (1 row) -- should fail SELECT * FROM cypher('group_by', $$MATCH (x:L) RETURN x.a, x.a + count(*) + x.b + count(*) + x.c$$) AS (a agtype, result agtype); ERROR: "x" must be either part of an explicitly listed key or used inside an aggregate function LINE 1: ...p_by', $$MATCH (x:L) RETURN x.a, x.a + count(*) + x.b + coun... ^ SELECT * FROM cypher('group_by', $$MATCH (x:L) RETURN x.a + count(*) + x.b + count(*) + x.c$$) AS (result agtype); ERROR: "x" must be either part of an explicitly listed key or used inside an aggregate function LINE 1: ...CT * FROM cypher('group_by', $$MATCH (x:L) RETURN x.a + coun... ^ --ORDER BY SELECT create_graph('order_by'); NOTICE: graph "order_by" has been created create_graph -------------- (1 row) SELECT * FROM cypher('order_by', $$CREATE ()$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('order_by', $$CREATE ({i: '1'})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('order_by', $$CREATE ({i: 1})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('order_by', $$CREATE ({i: 1.0})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('order_by', $$CREATE ({i: 1::numeric})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('order_by', $$CREATE ({i: true})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('order_by', $$CREATE ({i: false})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('order_by', $$CREATE ({i: {key: 'value'}})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('order_by', $$CREATE ({i: [1]})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('order_by', $$ MATCH (u) RETURN u.i ORDER BY u.i $$) AS (i agtype); i ------------------ {"key": "value"} [1] "1" false true 1::numeric 1 1.0 (9 rows) SELECT * FROM cypher('order_by', $$ MATCH (u) RETURN u.i ORDER BY u.i DESC $$) AS (i agtype); i ------------------ 1 1.0 1::numeric true false "1" [1] {"key": "value"} (9 rows) --CASE SELECT create_graph('case_statement'); NOTICE: graph "case_statement" has been created create_graph -------------- (1 row) SELECT * FROM cypher('case_statement', $$CREATE ({i: 1, j: null})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('case_statement', $$CREATE ({i: 'a', j: 'b'})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('case_statement', $$CREATE ({i: 0, j: 1})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('case_statement', $$CREATE ({i: true, j: false})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('case_statement', $$CREATE ({i: [], j: [0,1,2]})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('case_statement', $$CREATE ({i: {}, j: {i:1}})$$) AS (result agtype); result -------- (0 rows) --CASE WHEN condition THEN result END SELECT * FROM cypher('case_statement', $$ MATCH (n) RETURN n.i, n.j, CASE WHEN null THEN 'should not return me' WHEN n.i = 1 THEN 'i is 1' WHEN n.j = 'b' THEN 'j is b' WHEN n.i = 0 AND n.j = 1 THEN '0 AND 1' WHEN n.i = true OR n.j = true THEN 'i or j true' ELSE 'default' END $$ ) AS (i agtype, j agtype, case_statement agtype); i | j | case_statement ------+-----------+---------------- 1 | | "i is 1" "a" | "b" | "j is b" 0 | 1 | "0 AND 1" true | false | "i or j true" [] | [0, 1, 2] | "default" {} | {"i": 1} | "default" (6 rows) --CASE expression WHEN value THEN result END SELECT * FROM cypher('case_statement', $$ MATCH (n) RETURN n.j, CASE n.j WHEN null THEN 'should not return me' WHEN 'b' THEN 'b' WHEN 1 THEN 1 WHEN false THEN false WHEN [0,1,2] THEN [0,1,2] WHEN {i:1} THEN {i:1} ELSE 'not a or b' END $$ ) AS (j agtype, case_statement agtype); j | case_statement -----------+---------------- | "not a or b" "b" | "b" 1 | 1 false | false [0, 1, 2] | [0, 1, 2] {"i": 1} | {"i": 1} (6 rows) -- RETURN * and (u)--(v) optional forms SELECT create_graph('opt_forms'); NOTICE: graph "opt_forms" has been created create_graph -------------- (1 row) SELECT * FROM cypher('opt_forms', $$CREATE ({i:1})-[:KNOWS]->({i:2})<-[:KNOWS]-({i:3})$$)AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('opt_forms', $$MATCH (u) RETURN u$$) AS (result agtype); result ---------------------------------------------------------------------- {"id": 281474976710657, "label": "", "properties": {"i": 1}}::vertex {"id": 281474976710658, "label": "", "properties": {"i": 2}}::vertex {"id": 281474976710659, "label": "", "properties": {"i": 3}}::vertex (3 rows) SELECT * FROM cypher('opt_forms', $$MATCH (u) RETURN *$$) AS (result agtype); result ---------------------------------------------------------------------- {"id": 281474976710657, "label": "", "properties": {"i": 1}}::vertex {"id": 281474976710658, "label": "", "properties": {"i": 2}}::vertex {"id": 281474976710659, "label": "", "properties": {"i": 3}}::vertex (3 rows) SELECT * FROM cypher('opt_forms', $$MATCH (u)--(v) RETURN u.i, v.i$$) AS (u agtype, v agtype); u | v ---+--- 2 | 3 3 | 2 1 | 2 2 | 1 (4 rows) SELECT * FROM cypher('opt_forms', $$MATCH (u)-->(v) RETURN u.i, v.i$$) AS (u agtype, v agtype); u | v ---+--- 3 | 2 1 | 2 (2 rows) SELECT * FROM cypher('opt_forms', $$MATCH (u)<--(v) RETURN u.i, v.i$$) AS (u agtype, v agtype); u | v ---+--- 2 | 3 2 | 1 (2 rows) SELECT * FROM cypher('opt_forms', $$MATCH (u)-->()<--(v) RETURN u.i, v.i$$) AS (u agtype, v agtype); u | v ---+--- 3 | 1 1 | 3 (2 rows) SELECT * FROM cypher('opt_forms', $$MATCH (u) CREATE (u)-[:edge]->() RETURN *$$) AS (results agtype); results ---------------------------------------------------------------------- {"id": 281474976710657, "label": "", "properties": {"i": 1}}::vertex {"id": 281474976710658, "label": "", "properties": {"i": 2}}::vertex {"id": 281474976710659, "label": "", "properties": {"i": 3}}::vertex (3 rows) SELECT * FROM cypher('opt_forms', $$MATCH (u)-->()<--(v) RETURN *$$) AS (col1 agtype, col2 agtype); col1 | col2 ----------------------------------------------------------------------+---------------------------------------------------------------------- {"id": 281474976710659, "label": "", "properties": {"i": 3}}::vertex | {"id": 281474976710657, "label": "", "properties": {"i": 1}}::vertex {"id": 281474976710657, "label": "", "properties": {"i": 1}}::vertex | {"id": 281474976710659, "label": "", "properties": {"i": 3}}::vertex (2 rows) -- Added typecasts ::pg_bigint and ::pg_float8 SELECT * from cypher('expr', $$ RETURN pg_catalog.sqrt(pg_catalog.sqrt(pg_catalog.sqrt(256::pg_bigint))) $$) as (result agtype); result -------- 2.0 (1 row) SELECT * from cypher('expr', $$ RETURN pg_catalog.sqrt(pg_catalog.sqrt(pg_catalog.sqrt(256::pg_float8))) $$) as (result agtype); result -------- 2.0 (1 row) -- VLE SELECT create_graph('VLE'); NOTICE: graph "VLE" has been created create_graph -------------- (1 row) -- should return 0 rows SELECT * FROM cypher('VLE', $$MATCH (u)-[*]-(v) RETURN u, v$$) AS (u agtype, v agtype); u | v ---+--- (0 rows) SELECT * FROM cypher('VLE', $$MATCH (u)-[*0..1]-(v) RETURN u, v$$) AS (u agtype, v agtype); u | v ---+--- (0 rows) SELECT * FROM cypher('VLE', $$MATCH (u)-[*..1]-(v) RETURN u, v$$) AS (u agtype, v agtype); u | v ---+--- (0 rows) SELECT * FROM cypher('VLE', $$MATCH (u)-[*..5]-(v) RETURN u, v$$) AS (u agtype, v agtype); u | v ---+--- (0 rows) -- Create a graph to test SELECT * FROM cypher('VLE', $$CREATE (b:begin)-[:edge {name: 'main edge', number: 1, dangerous: {type: "all", level: "all"}}]->(u1:middle)-[:edge {name: 'main edge', number: 2, dangerous: {type: "all", level: "all"}, packages: [2,4,6]}]->(u2:middle)-[:edge {name: 'main edge', number: 3, dangerous: {type: "all", level: "all"}}]->(u3:middle)-[:edge {name: 'main edge', number: 4, dangerous: {type: "all", level: "all"}}]->(e:end), (u1)-[:self_loop {name: 'self loop', number: 1, dangerous: {type: "all", level: "all"}}]->(u1), (e)-[:self_loop {name: 'self loop', number: 2, dangerous: {type: "all", level: "all"}}]->(e), (b)-[:alternate_edge {name: 'alternate edge', number: 1, packages: [2,4,6], dangerous: {type: "poisons", level: "all"}}]->(u1), (u2)-[:alternate_edge {name: 'alternate edge', number: 2, packages: [2,4,6], dangerous: {type: "poisons", level: "all"}}]->(u3), (u3)-[:alternate_edge {name: 'alternate edge', number: 3, packages: [2,4,6], dangerous: {type: "poisons", level: "all"}}]->(e), (u2)-[:bypass_edge {name: 'bypass edge', number: 1, packages: [1,3,5,7]}]->(e), (e)-[:alternate_edge {name: 'backup edge', number: 1, packages: [1,3,5,7]}]->(u3), (u3)-[:alternate_edge {name: 'backup edge', number: 2, packages: [1,3,5,7]}]->(u2), (u2)-[:bypass_edge {name: 'bypass edge', number: 2, packages: [1,3,5,7], dangerous: {type: "poisons", level: "all"}}]->(b) RETURN b, e $$) AS (b agtype, e agtype); b | e ---------------------------------------------------------------------+-------------------------------------------------------------------- {"id": 844424930131969, "label": "begin", "properties": {}}::vertex | {"id": 1688849860263937, "label": "end", "properties": {}}::vertex (1 row) -- test vertex_stats command SELECT * FROM cypher('VLE', $$ MATCH (u) RETURN vertex_stats(u) $$) AS (result agtype); result ----------------------------------------------------------------------------------------------- {"id": 844424930131969, "label": "begin", "in_degree": 1, "out_degree": 2, "self_loops": 0} {"id": 1407374883553281, "label": "middle", "in_degree": 3, "out_degree": 2, "self_loops": 1} {"id": 1407374883553282, "label": "middle", "in_degree": 2, "out_degree": 4, "self_loops": 0} {"id": 1407374883553283, "label": "middle", "in_degree": 3, "out_degree": 3, "self_loops": 0} {"id": 1688849860263937, "label": "end", "in_degree": 4, "out_degree": 2, "self_loops": 1} (5 rows) -- test indirection operator for a function SELECT * FROM cypher('VLE', $$ MATCH (u) WHERE vertex_stats(u).self_loops <> 0 RETURN vertex_stats(u) $$) AS (result agtype); result ----------------------------------------------------------------------------------------------- {"id": 1407374883553281, "label": "middle", "in_degree": 3, "out_degree": 2, "self_loops": 1} {"id": 1688849860263937, "label": "end", "in_degree": 4, "out_degree": 2, "self_loops": 1} (2 rows) SELECT * FROM cypher('VLE', $$ MATCH (u) WHERE vertex_stats(u).in_degree < vertex_stats(u).out_degree RETURN vertex_stats(u) $$) AS (result agtype); result ----------------------------------------------------------------------------------------------- {"id": 844424930131969, "label": "begin", "in_degree": 1, "out_degree": 2, "self_loops": 0} {"id": 1407374883553282, "label": "middle", "in_degree": 2, "out_degree": 4, "self_loops": 0} (2 rows) SELECT * FROM cypher('VLE', $$ MATCH (u) WHERE vertex_stats(u).out_degree < vertex_stats(u).in_degree RETURN vertex_stats(u) $$) AS (result agtype); result ----------------------------------------------------------------------------------------------- {"id": 1407374883553281, "label": "middle", "in_degree": 3, "out_degree": 2, "self_loops": 1} {"id": 1688849860263937, "label": "end", "in_degree": 4, "out_degree": 2, "self_loops": 1} (2 rows) -- list functions relationships(), range(), keys() SELECT create_graph('keys'); NOTICE: graph "keys" has been created create_graph -------------- (1 row) -- keys() SELECT * FROM cypher('keys', $$CREATE ({name: 'hikaru utada', age: 38, job: 'singer'})-[:collaborated_with {song:"face my fears"}]->( {name: 'sonny moore', age: 33, stage_name: 'skrillex', job: 'producer'})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('keys', $$CREATE ({name: 'alexander guy cook', age: 31, stage_name:"a. g. cook", job: 'producer'})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('keys', $$CREATE ({name: 'keiko fuji', age: 62, job: 'singer'})$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('keys', $$MATCH (a),(b) WHERE a.name = 'hikaru utada' AND b.name = 'alexander guy cook' CREATE (a)-[:collaborated_with {song:"one last kiss"}]->(b)$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('keys', $$MATCH (a),(b) WHERE a.name = 'hikaru utada' AND b.name = 'keiko fuji' CREATE (a)-[:knows]->(b)$$) AS (result agtype); result -------- (0 rows) SELECT * FROM cypher('keys', $$MATCH (v) RETURN keys(v)$$) AS (vertex_keys agtype); vertex_keys -------------------------------------- ["age", "job", "name"] ["age", "job", "name", "stage_name"] ["age", "job", "name", "stage_name"] ["age", "job", "name"] (4 rows) SELECT * FROM cypher('keys', $$MATCH ()-[e]-() RETURN keys(e)$$) AS (edge_keys agtype); edge_keys ----------- [] [] ["song"] ["song"] ["song"] ["song"] (6 rows) SELECT * FROM cypher('keys', $$RETURN keys({a:1,b:'two',c:[1,2,3]})$$) AS (keys agtype); keys ----------------- ["a", "b", "c"] (1 row) --should return empty list SELECT * FROM cypher('keys', $$RETURN keys({})$$) AS (keys agtype); keys ------ [] (1 row) --should return sql null SELECT * FROM cypher('keys', $$RETURN keys(null)$$) AS (keys agtype); keys ------ (1 row) --should return error SELECT * from cypher('keys', $$RETURN keys([1,2,3])$$) as (keys agtype); ERROR: keys() argument must be a vertex, edge, object or null SELECT * from cypher('keys', $$RETURN keys("string")$$) as (keys agtype); ERROR: keys() argument must be a vertex, edge, object or null SELECT * from cypher('keys', $$MATCH u=()-[]-() RETURN keys(u)$$) as (keys agtype); ERROR: keys() argument must be a vertex, edge, object or null SELECT create_graph('list'); NOTICE: graph "list" has been created create_graph -------------- (1 row) SELECT * from cypher('list', $$CREATE p=({name:"rick"})-[:knows]->({name:"morty"}) RETURN p$$) as (path agtype); path ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ [{"id": 281474976710657, "label": "", "properties": {"name": "rick"}}::vertex, {"id": 844424930131969, "label": "knows", "end_id": 281474976710658, "start_id": 281474976710657, "properties": {}}::edge, {"id": 281474976710658, "label": "", "properties": {"name": "morty"}}::vertex]::path (1 row) SELECT * from cypher('list', $$CREATE p=({name:'rachael'})-[:knows]->({name:'monica'})-[:knows]->({name:'phoebe'}) RETURN p$$) as (path agtype); path --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [{"id": 281474976710659, "label": "", "properties": {"name": "rachael"}}::vertex, {"id": 844424930131971, "label": "knows", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {}}::edge, {"id": 281474976710660, "label": "", "properties": {"name": "monica"}}::vertex, {"id": 844424930131970, "label": "knows", "end_id": 281474976710661, "start_id": 281474976710660, "properties": {}}::edge, {"id": 281474976710661, "label": "", "properties": {"name": "phoebe"}}::vertex]::path (1 row) -- nodes() SELECT * from cypher('list', $$MATCH p=()-[]->() RETURN nodes(p)$$) as (nodes agtype); nodes ------------------------------------------------------------------------------------------------------------------------------------------------------------------- [{"id": 281474976710657, "label": "", "properties": {"name": "rick"}}::vertex, {"id": 281474976710658, "label": "", "properties": {"name": "morty"}}::vertex] [{"id": 281474976710660, "label": "", "properties": {"name": "monica"}}::vertex, {"id": 281474976710661, "label": "", "properties": {"name": "phoebe"}}::vertex] [{"id": 281474976710659, "label": "", "properties": {"name": "rachael"}}::vertex, {"id": 281474976710660, "label": "", "properties": {"name": "monica"}}::vertex] (3 rows) SELECT * from cypher('list', $$MATCH p=()-[]->()-[]->() RETURN nodes(p)$$) as (nodes agtype); nodes --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [{"id": 281474976710659, "label": "", "properties": {"name": "rachael"}}::vertex, {"id": 281474976710660, "label": "", "properties": {"name": "monica"}}::vertex, {"id": 281474976710661, "label": "", "properties": {"name": "phoebe"}}::vertex] (1 row) -- should return nothing SELECT * from cypher('list', $$MATCH p=()-[]->()-[]->()-[]->() RETURN nodes(p)$$) as (nodes agtype); nodes ------- (0 rows) -- should return SQL NULL SELECT * from cypher('list', $$RETURN nodes(NULL)$$) as (nodes agtype); nodes ------- (1 row) -- should return an error SELECT * from cypher('list', $$MATCH (u) RETURN nodes([1,2,3])$$) as (nodes agtype); ERROR: nodes() argument must resolve to a scalar value SELECT * from cypher('list', $$MATCH (u) RETURN nodes("string")$$) as (nodes agtype); ERROR: nodes() argument must be a path SELECT * from cypher('list', $$MATCH (u) RETURN nodes(u)$$) as (nodes agtype); ERROR: nodes() argument must be a path SELECT * from cypher('list', $$MATCH (u)-[]->() RETURN nodes(u)$$) as (nodes agtype); ERROR: nodes() argument must be a path -- relationships() SELECT * from cypher('list', $$MATCH p=()-[]->() RETURN relationships(p)$$) as (relationships agtype); relationships ----------------------------------------------------------------------------------------------------------------------------- [{"id": 844424930131969, "label": "knows", "end_id": 281474976710658, "start_id": 281474976710657, "properties": {}}::edge] [{"id": 844424930131970, "label": "knows", "end_id": 281474976710661, "start_id": 281474976710660, "properties": {}}::edge] [{"id": 844424930131971, "label": "knows", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {}}::edge] (3 rows) SELECT * from cypher('list', $$MATCH p=()-[]->()-[]->() RETURN relationships(p)$$) as (relationships agtype); relationships -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [{"id": 844424930131971, "label": "knows", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {}}::edge, {"id": 844424930131970, "label": "knows", "end_id": 281474976710661, "start_id": 281474976710660, "properties": {}}::edge] (1 row) -- should return nothing SELECT * from cypher('list', $$MATCH p=()-[]->()-[]->()-[]->() RETURN relationships(p)$$) as (relationships agtype); relationships --------------- (0 rows) -- should return SQL NULL SELECT * from cypher('list', $$RETURN relationships(NULL)$$) as (relationships agtype); relationships --------------- (1 row) -- should return an error SELECT * from cypher('list', $$MATCH (u) RETURN relationships([1,2,3])$$) as (relationships agtype); ERROR: relationships() argument must resolve to a scalar value SELECT * from cypher('list', $$MATCH (u) RETURN relationships("string")$$) as (relationships agtype); ERROR: relationships() argument must be a path SELECT * from cypher('list', $$MATCH (u) RETURN relationships(u)$$) as (relationships agtype); ERROR: relationships() argument must be a path SELECT * from cypher('list', $$MATCH ()-[e]->() RETURN relationships(e)$$) as (relationships agtype); ERROR: relationships() argument must be a path -- range() SELECT * from cypher('list', $$RETURN range(0, 10)$$) as (range agtype); range ------------------------------------ [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10] (1 row) SELECT * from cypher('list', $$RETURN range(0, 10, null)$$) as (range agtype); range ------------------------------------ [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10] (1 row) SELECT * from cypher('list', $$RETURN range(0, 10, 1)$$) as (range agtype); range ------------------------------------ [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10] (1 row) SELECT * from cypher('list', $$RETURN range(0, 10, 3)$$) as (range agtype); range -------------- [0, 3, 6, 9] (1 row) SELECT * from cypher('list', $$RETURN range(0, -10, -1)$$) as (range agtype); range ---------------------------------------------- [0, -1, -2, -3, -4, -5, -6, -7, -8, -9, -10] (1 row) SELECT * from cypher('list', $$RETURN range(0, -10, -3)$$) as (range agtype); range ----------------- [0, -3, -6, -9] (1 row) SELECT * from cypher('list', $$RETURN range(0, 10, 11)$$) as (range agtype); range ------- [0] (1 row) SELECT * from cypher('list', $$RETURN range(-20, 10, 5)$$) as (range agtype); range ------------------------------- [-20, -15, -10, -5, 0, 5, 10] (1 row) -- should return an empty list [] SELECT * from cypher('list', $$RETURN range(0, -10)$$) as (range agtype); range ------- [] (1 row) SELECT * from cypher('list', $$RETURN range(0, 10, -1)$$) as (range agtype); range ------- [] (1 row) SELECT * from cypher('list', $$RETURN range(-10, 10, -1)$$) as (range agtype); range ------- [] (1 row) -- should return an error SELECT * from cypher('list', $$RETURN range(null, -10, -3)$$) as (range agtype); ERROR: range(): neither start or end can be NULL SELECT * from cypher('list', $$RETURN range(0, null, -3)$$) as (range agtype); ERROR: range(): neither start or end can be NULL SELECT * from cypher('list', $$RETURN range(0, -10.0, -3.0)$$) as (range agtype); ERROR: range() unsupported argument type -- labels() SELECT * from cypher('list', $$CREATE (u:People {name: "John"}) RETURN u$$) as (Vertices agtype); vertices ------------------------------------------------------------------------------------- {"id": 1125899906842625, "label": "People", "properties": {"name": "John"}}::vertex (1 row) SELECT * from cypher('list', $$CREATE (u:People {name: "Larry"}) RETURN u$$) as (Vertices agtype); vertices -------------------------------------------------------------------------------------- {"id": 1125899906842626, "label": "People", "properties": {"name": "Larry"}}::vertex (1 row) SELECT * from cypher('list', $$CREATE (u:Cars {name: "G35"}) RETURN u$$) as (Vertices agtype); vertices ---------------------------------------------------------------------------------- {"id": 1407374883553281, "label": "Cars", "properties": {"name": "G35"}}::vertex (1 row) SELECT * from cypher('list', $$CREATE (u:Cars {name: "MR2"}) RETURN u$$) as (Vertices agtype); vertices ---------------------------------------------------------------------------------- {"id": 1407374883553282, "label": "Cars", "properties": {"name": "MR2"}}::vertex (1 row) SELECT * from cypher('list', $$MATCH (u) RETURN labels(u), u$$) as (Labels agtype, Vertices agtype); labels | vertices ------------+-------------------------------------------------------------------------------------- [""] | {"id": 281474976710657, "label": "", "properties": {"name": "rick"}}::vertex [""] | {"id": 281474976710658, "label": "", "properties": {"name": "morty"}}::vertex [""] | {"id": 281474976710659, "label": "", "properties": {"name": "rachael"}}::vertex [""] | {"id": 281474976710660, "label": "", "properties": {"name": "monica"}}::vertex [""] | {"id": 281474976710661, "label": "", "properties": {"name": "phoebe"}}::vertex ["People"] | {"id": 1125899906842625, "label": "People", "properties": {"name": "John"}}::vertex ["People"] | {"id": 1125899906842626, "label": "People", "properties": {"name": "Larry"}}::vertex ["Cars"] | {"id": 1407374883553281, "label": "Cars", "properties": {"name": "G35"}}::vertex ["Cars"] | {"id": 1407374883553282, "label": "Cars", "properties": {"name": "MR2"}}::vertex (9 rows) -- should return SQL NULL SELECT * from cypher('list', $$RETURN labels(NULL)$$) as (Labels agtype); labels -------- (1 row) -- should return an error SELECT * from cypher('list', $$RETURN labels("string")$$) as (Labels agtype); ERROR: labels() argument must be a vertex -- -- Cleanup -- SELECT * FROM drop_graph('chained', true); NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to table chained._ag_label_vertex drop cascades to table chained._ag_label_edge drop cascades to table chained.people NOTICE: graph "chained" has been dropped drop_graph ------------ (1 row) SELECT * FROM drop_graph('VLE', true); NOTICE: drop cascades to 9 other objects DETAIL: drop cascades to table "VLE"._ag_label_vertex drop cascades to table "VLE"._ag_label_edge drop cascades to table "VLE".begin drop cascades to table "VLE".edge drop cascades to table "VLE".middle drop cascades to table "VLE"."end" drop cascades to table "VLE".self_loop drop cascades to table "VLE".alternate_edge drop cascades to table "VLE".bypass_edge NOTICE: graph "VLE" has been dropped drop_graph ------------ (1 row) SELECT * FROM drop_graph('case_statement', true); NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table case_statement._ag_label_vertex drop cascades to table case_statement._ag_label_edge NOTICE: graph "case_statement" has been dropped drop_graph ------------ (1 row) SELECT * FROM drop_graph('opt_forms', true); NOTICE: drop cascades to 4 other objects DETAIL: drop cascades to table opt_forms._ag_label_vertex drop cascades to table opt_forms._ag_label_edge drop cascades to table opt_forms."KNOWS" drop cascades to table opt_forms.edge NOTICE: graph "opt_forms" has been dropped drop_graph ------------ (1 row) SELECT * FROM drop_graph('type_coercion', true); NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to table type_coercion._ag_label_vertex drop cascades to table type_coercion._ag_label_edge drop cascades to table type_coercion.edge NOTICE: graph "type_coercion" has been dropped drop_graph ------------ (1 row) SELECT * FROM drop_graph('order_by', true); NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table order_by._ag_label_vertex drop cascades to table order_by._ag_label_edge NOTICE: graph "order_by" has been dropped drop_graph ------------ (1 row) SELECT * FROM drop_graph('group_by', true); NOTICE: drop cascades to 4 other objects DETAIL: drop cascades to table group_by._ag_label_vertex drop cascades to table group_by._ag_label_edge drop cascades to table group_by."row" drop cascades to table group_by."L" NOTICE: graph "group_by" has been dropped drop_graph ------------ (1 row) SELECT * FROM drop_graph('UCSC', true); NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to table "UCSC"._ag_label_vertex drop cascades to table "UCSC"._ag_label_edge drop cascades to table "UCSC".students NOTICE: graph "UCSC" has been dropped drop_graph ------------ (1 row) SELECT * FROM drop_graph('expr', true); NOTICE: drop cascades to 5 other objects DETAIL: drop cascades to table expr._ag_label_vertex drop cascades to table expr._ag_label_edge drop cascades to table expr.v drop cascades to table expr.v1 drop cascades to table expr.e1 NOTICE: graph "expr" has been dropped drop_graph ------------ (1 row) SELECT * FROM drop_graph('regex', true); NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to table regex._ag_label_vertex drop cascades to table regex._ag_label_edge drop cascades to table regex."Person" NOTICE: graph "regex" has been dropped drop_graph ------------ (1 row) SELECT * FROM drop_graph('keys', true); NOTICE: drop cascades to 4 other objects DETAIL: drop cascades to table keys._ag_label_vertex drop cascades to table keys._ag_label_edge drop cascades to table keys.collaborated_with drop cascades to table keys.knows NOTICE: graph "keys" has been dropped drop_graph ------------ (1 row) SELECT * FROM drop_graph('list', true); NOTICE: drop cascades to 5 other objects DETAIL: drop cascades to table list._ag_label_vertex drop cascades to table list._ag_label_edge drop cascades to table list.knows drop cascades to table list."People" drop cascades to table list."Cars" NOTICE: graph "list" has been dropped drop_graph ------------ (1 row) -- -- End of tests --