/*
* 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: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_vertex_pkey" for table "_ag_label_vertex"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_edge_pkey" for table "_ag_label_edge"
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 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 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
CONTEXT: referenced column: a
SELECT * from cypher('expr',
$$RETURN 0[[0]..[1]]$$) as r(a agtype);
ERROR: slice must access a list
CONTEXT: referenced column: a
-- should return nothing
SELECT * from cypher('expr',
$$RETURN [0][0..-2147483649]$$) as r(a agtype);
a
----
[]
(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 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: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_vertex_pkey" for table "_ag_label_vertex"
CONTEXT: referenced column: create_graph
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_edge_pkey" for table "_ag_label_edge"
CONTEXT: referenced column: create_graph
NOTICE: graph "regex" has been created
CONTEXT: referenced column: create_graph
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: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_vertex_pkey" for table "_ag_label_vertex"
CONTEXT: referenced column: create_graph
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_edge_pkey" for table "_ag_label_edge"
CONTEXT: referenced column: create_graph
NOTICE: graph "type_coercion" has been created
CONTEXT: referenced column: create_graph
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 type bigint: "1.0"
CONTEXT: referenced column: i
-- Casting to ints that will cause overflow
SELECT * FROM cypher('type_coercion', $$
RETURN 10000000000000000000
$$) AS (i smallint);
ERROR: smallint out of range
CONTEXT: referenced column: i
SELECT * FROM cypher('type_coercion', $$
RETURN 10000000000000000000
$$) AS (i int);
ERROR: integer out of range
CONTEXT: referenced column: i
--Invalid types
SELECT * FROM cypher('type_coercion', $$
RETURN true
$$) AS (i bigint);
ERROR: cannot cast agtype boolean to type int
CONTEXT: referenced column: i
SELECT * FROM cypher('type_coercion', $$
RETURN {key: 1}
$$) AS (i bigint);
ERROR: cannot cast agtype object to type int
CONTEXT: referenced column: i
SELECT * FROM cypher('type_coercion', $$
RETURN [1]
$$) AS (i bigint);
ERROR: cannot cast agtype array to type int
CONTEXT: referenced column: i
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
CONTEXT: referenced column: i
SELECT * FROM cypher('type_coercion', $$
MATCH ()-[e]-()
RETURN e
$$) AS (i bigint);
ERROR: cannot cast agtype edge to type int
CONTEXT: referenced column: i
SELECT * FROM cypher('type_coercion', $$
MATCH p=()-[]-()
RETURN p
$$) AS (i bigint);
ERROR: cannot cast agtype path to type int
CONTEXT: referenced column: i
--
-- 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 type bigint: "0.0"
CONTEXT: referenced column: result
SELECT * FROM cypher('expr', $$
RETURN '1.5'::int
$$) AS r(result agtype);
ERROR: invalid input syntax for type bigint: "1.5"
CONTEXT: referenced column: result
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
CONTEXT: referenced column: result
SELECT * FROM cypher('expr', $$
RETURN 'infinity'::float::int
$$) AS r(result agtype);
ERROR: bigint out of range
CONTEXT: referenced column: result
-- 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"
CONTEXT: referenced column: result
SELECT * FROM cypher('expr', $$
RETURN ('inf'::numeric)::numeric
$$) AS r(result agtype);
ERROR: invalid input syntax for type numeric: "inf"
CONTEXT: referenced column: result
SELECT * FROM cypher('expr', $$
RETURN ('infinity'::numeric)::numeric
$$) AS r(result agtype);
ERROR: invalid input syntax for type numeric: "infinity"
CONTEXT: referenced column: result
-- 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"
CONTEXT: referenced column: result
SELECT * FROM cypher('expr', $$
RETURN 'infi'::float
$$) AS r(result agtype);
ERROR: invalid input syntax for type double precision: "infi"
CONTEXT: referenced column: result
-- 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
CONTEXT: referenced column: result
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
CONTEXT: referenced column: result
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
CONTEXT: referenced column: result
SELECT * FROM cypher('expr', $$
RETURN {id:0, label:"vertex 0"}::vertex
$$) AS r(result agtype);
ERROR: typecast object is not a vertex
CONTEXT: referenced column: result
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
CONTEXT: referenced column: result
SELECT * FROM cypher('expr', $$
RETURN {}::vertex
$$) AS r(result agtype);
ERROR: typecast object is not a vertex
CONTEXT: referenced column: result
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
CONTEXT: referenced column: result
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
CONTEXT: referenced column: result
SELECT * FROM cypher('expr', $$
RETURN {}::edge
$$) AS r(result agtype);
ERROR: typecast object is not an edge
CONTEXT: referenced column: result
-- 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
CONTEXT: referenced column: agtype_in
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
CONTEXT: referenced column: agtype_in
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
CONTEXT: referenced column: result
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
CONTEXT: referenced column: result
SELECT * FROM cypher('expr', $$ RETURN []::path $$) AS r(result agtype);
ERROR: typecast argument is not a valid path
CONTEXT: referenced column: result
-- 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 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: start_id
SELECT * FROM cypher('expr', $$
RETURN start_id()
$$) AS (start_id agtype);
ERROR: function ag_catalog.age_start_id() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: end_id
SELECT * FROM cypher('expr', $$
RETURN end_id()
$$) AS (end_id agtype);
ERROR: function ag_catalog.age_end_id() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
------------------+------------------+----------------------------------------------------------------------------------
1407374883553282 | 1125899906842625 | {"id": 1125899906842625, "label": "v1", "properties": {"id": "initial"}}::vertex
1407374883553281 | 1125899906842626 | {"id": 1125899906842626, "label": "v1", "properties": {"id": "middle"}}::vertex
1407374883553282 | 1125899906842625 | {"id": 1125899906842625, "label": "v1", "properties": {"id": "initial"}}::vertex
1407374883553281 | 1125899906842626 | {"id": 1125899906842626, "label": "v1", "properties": {"id": "middle"}}::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
CONTEXT: referenced column: startnode
SELECT * FROM cypher('expr', $$
RETURN startNode()
$$) AS (startNode agtype);
ERROR: function ag_catalog.age_startnode() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
------------------+------------------+---------------------------------------------------------------------------------
1407374883553282 | 1125899906842626 | {"id": 1125899906842626, "label": "v1", "properties": {"id": "middle"}}::vertex
1407374883553281 | 1125899906842627 | {"id": 1125899906842627, "label": "v1", "properties": {"id": "end"}}::vertex
1407374883553282 | 1125899906842626 | {"id": 1125899906842626, "label": "v1", "properties": {"id": "middle"}}::vertex
1407374883553281 | 1125899906842627 | {"id": 1125899906842627, "label": "v1", "properties": {"id": "end"}}::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
CONTEXT: referenced column: endnode
SELECT * FROM cypher('expr', $$
RETURN endNode()
$$) AS (endNode agtype);
ERROR: function ag_catalog.age_endnode() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: type
SELECT * FROM cypher('expr', $$
RETURN type()
$$) AS (type agtype);
ERROR: function ag_catalog.age_type() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: label
SELECT * FROM cypher('expr', $$
RETURN label(1)
$$) AS (label agtype);
ERROR: label() argument must resolve to an edge or vertex
CONTEXT: referenced column: label
SELECT * FROM cypher('expr', $$
MATCH (n) RETURN label([n])
$$) AS (label agtype);
ERROR: label() argument must resolve to an edge or vertex
CONTEXT: referenced column: label
SELECT * FROM cypher('expr', $$
RETURN label({id: 0, label: 'failed', properties: {}})
$$) AS (label agtype);
ERROR: label() argument must resolve to an edge or vertex
CONTEXT: referenced column: label
-- 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
CONTEXT: referenced column: size
SELECT * FROM cypher('expr', $$
RETURN size()
$$) AS (size agtype);
ERROR: function ag_catalog.age_size() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: head
SELECT * FROM cypher('expr', $$
RETURN head()
$$) AS (head agtype);
ERROR: function ag_catalog.age_head() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: last
SELECT * FROM cypher('expr', $$
RETURN last()
$$) AS (last agtype);
ERROR: function ag_catalog.age_last() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: properties
SELECT * FROM cypher('expr', $$
RETURN properties()
$$) AS (properties agtype);
ERROR: function ag_catalog.age_properties() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: toboolean
SELECT * FROM cypher('expr', $$
RETURN toBoolean()
$$) AS (toBoolean agtype);
ERROR: function ag_catalog.age_toboolean() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: tofloat
SELECT * FROM cypher('expr', $$
RETURN toFloat()
$$) AS (toFloat agtype);
ERROR: function ag_catalog.age_tofloat() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: tointeger
SELECT * FROM cypher('expr', $$
RETURN toInteger()
$$) AS (toInteger agtype);
ERROR: function ag_catalog.age_tointeger() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: length
SELECT * FROM cypher('expr', $$
RETURN length()
$$) AS (length agtype);
ERROR: function ag_catalog.age_length() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: results
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
CONTEXT: referenced column: results
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 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: toupper
SELECT * FROM cypher('expr', $$
RETURN toUpper()
$$) AS (toUpper agtype);
ERROR: function ag_catalog.age_toupper() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: tolower
SELECT * FROM cypher('expr', $$
RETURN toLower()
$$) AS (toLower agtype);
ERROR: function ag_catalog.age_tolower() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN rTrim(true)
$$) AS (results agtype);
ERROR: rTrim() unsupported argument agtype 5
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN trim(true)
$$) AS (results agtype);
ERROR: trim() unsupported argument agtype 5
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN lTrim()
$$) AS (results agtype);
ERROR: function ag_catalog.age_ltrim() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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 1: SELECT * FROM cypher('expr', $$
^
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 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN left("123456789", -1)
$$) AS (results agtype);
ERROR: left() negative values are not supported for length
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN left()
$$) AS (results agtype);
ERROR: function ag_catalog.age_left() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN right("123456789", -1)
$$) AS (results agtype);
ERROR: right() negative values are not supported for length
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN right()
$$) AS (results agtype);
ERROR: function ag_catalog.age_right() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN substring("123456789", 0, -1)
$$) AS (results agtype);
ERROR: substring() negative values are not supported for offset or length
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN substring("123456789", -1)
$$) AS (results agtype);
ERROR: substring() negative values are not supported for offset or length
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN substring("123456789")
$$) AS (results agtype);
ERROR: substring() invalid number of arguments
CONTEXT: referenced column: results
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
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN split("a,b,c,d,e,f", -1)
$$) AS (results agtype);
ERROR: split() unsupported argument agtype 3
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN split("a,b,c,d,e,f")
$$) AS (results agtype);
ERROR: split() invalid number of arguments
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN split()
$$) AS (results agtype);
ERROR: function ag_catalog.age_split() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN replace("Hello", null)
$$) AS (results agtype);
ERROR: replace() invalid number of arguments
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN replace("Hello", "e", 1)
$$) AS (results agtype);
ERROR: replace() unsupported argument agtype 3
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN replace("Hello", 1, "e")
$$) AS (results agtype);
ERROR: replace() unsupported argument agtype 3
CONTEXT: referenced column: results
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 * FROM cypher('expr', $$
RETURN sin(3.1415)
$$) AS (results agtype), sin(3.1415);
results | sin
----------------------+----------------------
9.26535896604903e-05 | 9.26535896604903e-05
(1 row)
SELECT * FROM cypher('expr', $$
RETURN cos(3.1415)
$$) AS (results agtype), cos(3.1415);
results | cos
-------------------+-------------------
-.999999995707656 | -.999999995707656
(1 row)
SELECT * FROM cypher('expr', $$
RETURN tan(3.1415)
$$) AS (results agtype), tan(3.1415);
results | tan
-----------------------+-----------------------
-9.26535900581913e-05 | -9.26535900581913e-05
(1 row)
SELECT * FROM cypher('expr', $$
RETURN cot(3.1415)
$$) AS (results agtype), cot(3.1415);
results | cot
-------------------+-------------------
-10792.8899395258 | -10792.8899395258
(1 row)
SELECT * FROM sin(3.1415), age_sin(3.1415);
sin | age_sin
----------------------+----------------------
9.26535896604903e-05 | 9.26535896604903e-05
(1 row)
SELECT * FROM cos(3.1415), age_cos(3.1415);
cos | age_cos
-------------------+-------------------
-.999999995707656 | -.999999995707656
(1 row)
SELECT * FROM tan(3.1415), age_tan(3.1415);
tan | age_tan
-----------------------+-----------------------
-9.26535900581913e-05 | -9.26535900581913e-05
(1 row)
SELECT * FROM cot(3.1415), age_cot(3.1415);
cot | age_cot
-------------------+-------------------
-10792.8899395258 | -10792.8899395258
(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
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN cos("0")
$$) AS (results agtype);
ERROR: cos() unsupported argument agtype 1
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN tan("0")
$$) AS (results agtype);
ERROR: tan() unsupported argument agtype 1
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN cot("0")
$$) AS (results agtype);
ERROR: cot() unsupported argument agtype 1
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN sin()
$$) AS (results agtype);
ERROR: function ag_catalog.age_sin() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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 1: SELECT * FROM cypher('expr', $$
^
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 1: SELECT * FROM cypher('expr', $$
^
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 1: SELECT * FROM cypher('expr', $$
^
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
------------------+------------------
.785398163397448 | .785398163397448
(1 row)
SELECT * FROM atan2(1, 1), age_atan2(1, 1);
atan2 | age_atan2
------------------+------------------
.785398163397448 | .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
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN acos("0")
$$) AS (results agtype);
ERROR: acos() unsupported argument agtype 1
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN atan("0")
$$) AS (results agtype);
ERROR: atan() unsupported argument agtype 1
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN atan2("0", 1)
$$) AS (results agtype);
ERROR: atan2() unsupported argument agtype 1
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN atan2(0, "1")
$$) AS (results agtype);
ERROR: atan2() unsupported argument agtype 1
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN asin()
$$) AS (results agtype);
ERROR: function ag_catalog.age_asin() does not exist
LINE 1: SELECT * FROM cypher('expr', $$
^
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 1: SELECT * FROM cypher('expr', $$
^
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 1: SELECT * FROM cypher('expr', $$
^
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 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: results
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
------------------
.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 1: SELECT * FROM cypher('expr', $$
^
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 1: SELECT * FROM cypher('expr', $$
^
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 1: SELECT * FROM cypher('expr', $$
^
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 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN degrees("1")
$$) AS (results agtype);
ERROR: degrees() unsupported argument agtype 1
CONTEXT: referenced column: results
--
-- 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 1: SELECT * FROM cypher('expr', $$
^
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 1: SELECT * FROM cypher('expr', $$
^
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 1: SELECT * FROM cypher('expr', $$
^
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 1: SELECT * FROM cypher('expr', $$
^
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 1: SELECT * FROM cypher('expr', $$
^
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
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN ceil("1")
$$) AS (results agtype);
ERROR: ceil() unsupported argument agtype 1
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN floor("1")
$$) AS (results agtype);
ERROR: floor() unsupported argument agtype 1
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN round("1")
$$) AS (results agtype);
ERROR: round() unsupported argument agtype 1
CONTEXT: referenced column: results
SELECT * FROM cypher('expr', $$
RETURN sign("1")
$$) AS (results agtype);
ERROR: sign() unsupported argument agtype 1
CONTEXT: referenced column: results
--
-- 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 1: SELECT * from cypher('expr', $$
^
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 1: SELECT * from cypher('expr', $$
^
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 1: SELECT * from cypher('expr', $$
^
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
CONTEXT: referenced column: result
--
-- 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 1: SELECT * from cypher('expr', $$
^
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
CONTEXT: referenced column: result
--
-- 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 1: SELECT * from cypher('expr', $$
^
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
CONTEXT: referenced column: sqrt
SELECT * from cypher('expr', $$
RETURN pg_catalog.sqrt(-1::pg_float8)
$$) as (result agtype);
ERROR: cannot take square root of a negative number
CONTEXT: referenced column: sqrt
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 1: SELECT * from cypher('expr', $$
^
SELECT * from cypher('expr', $$
RETURN contains.age_sqrt(25)
$$) as (result agtype);
ERROR: schema "contains" does not exist
LINE 1: SELECT * from cypher('expr', $$
^
--
-- aggregate functions avg(), sum(), count(), & count(*)
--
SELECT create_graph('UCSC');
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_vertex_pkey" for table "_ag_label_vertex"
CONTEXT: referenced column: create_graph
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_edge_pkey" for table "_ag_label_edge"
CONTEXT: referenced column: create_graph
NOTICE: graph "UCSC" has been created
CONTEXT: referenced column: create_graph
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.
CONTEXT: referenced column: age_min
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.
CONTEXT: referenced column: age_min
--
-- aggregate functions stDev() & stDevP()
--
SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN stDev(u.gpa), stDevP(u.gpa) $$)
AS (stDev agtype, stDevP agtype);
stdev | stdevp
------------------+------------------
.549566929066705 | .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)
-- 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: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_vertex_pkey" for table "_ag_label_vertex"
CONTEXT: referenced column: create_graph
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_edge_pkey" for table "_ag_label_edge"
CONTEXT: referenced column: create_graph
NOTICE: graph "group_by" has been created
CONTEXT: referenced column: create_graph
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 | 3 | 5
2 | 3 | 6
1 | 2 | 7
(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
---+---+---+--------
2 | 3 | 1 | 8
3 | 1 | 2 | 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: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_vertex_pkey" for table "_ag_label_vertex"
CONTEXT: referenced column: create_graph
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_edge_pkey" for table "_ag_label_edge"
CONTEXT: referenced column: create_graph
NOTICE: graph "order_by" has been created
CONTEXT: referenced column: create_graph
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: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_vertex_pkey" for table "_ag_label_vertex"
CONTEXT: referenced column: create_graph
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_edge_pkey" for table "_ag_label_edge"
CONTEXT: referenced column: create_graph
NOTICE: graph "case_statement" has been created
CONTEXT: referenced column: create_graph
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: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_vertex_pkey" for table "_ag_label_vertex"
CONTEXT: referenced column: create_graph
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_edge_pkey" for table "_ag_label_edge"
CONTEXT: referenced column: create_graph
NOTICE: graph "opt_forms" has been created
CONTEXT: referenced column: create_graph
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
---+---
1 | 2
2 | 3
2 | 1
3 | 2
(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
---+---
1 | 3
3 | 1
(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": 281474976710657, "label": "", "properties": {"i": 1}}::vertex | {"id": 281474976710659, "label": "", "properties": {"i": 3}}::vertex
{"id": 281474976710659, "label": "", "properties": {"i": 3}}::vertex | {"id": 281474976710657, "label": "", "properties": {"i": 1}}::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: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_vertex_pkey" for table "_ag_label_vertex"
CONTEXT: referenced column: create_graph
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_edge_pkey" for table "_ag_label_edge"
CONTEXT: referenced column: create_graph
NOTICE: graph "VLE" has been created
CONTEXT: referenced column: create_graph
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)
-- list functions relationships(), range(), keys()
SELECT create_graph('keys');
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_vertex_pkey" for table "_ag_label_vertex"
CONTEXT: referenced column: create_graph
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_edge_pkey" for table "_ag_label_edge"
CONTEXT: referenced column: create_graph
NOTICE: graph "keys" has been created
CONTEXT: referenced column: create_graph
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
CONTEXT: referenced column: keys
SELECT * from cypher('keys', $$RETURN keys("string")$$) as (keys agtype);
ERROR: keys() argument must be a vertex, edge, object or null
CONTEXT: referenced column: keys
SELECT * from cypher('keys', $$MATCH u=()-[]-() RETURN keys(u)$$) as (keys agtype);
ERROR: keys() argument must be a vertex, edge, object or null
CONTEXT: referenced column: keys
SELECT create_graph('list');
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_vertex_pkey" for table "_ag_label_vertex"
CONTEXT: referenced column: create_graph
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "_ag_label_edge_pkey" for table "_ag_label_edge"
CONTEXT: referenced column: create_graph
NOTICE: graph "list" has been created
CONTEXT: referenced column: create_graph
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
CONTEXT: referenced column: nodes
SELECT * from cypher('list', $$MATCH (u) RETURN nodes("string")$$) as (nodes agtype);
ERROR: nodes() argument must be a path
CONTEXT: referenced column: nodes
SELECT * from cypher('list', $$MATCH (u) RETURN nodes(u)$$) as (nodes agtype);
ERROR: nodes() argument must be a path
CONTEXT: referenced column: nodes
SELECT * from cypher('list', $$MATCH (u)-[]->() RETURN nodes(u)$$) as (nodes agtype);
ERROR: nodes() argument must be a path
CONTEXT: referenced column: nodes
-- 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
CONTEXT: referenced column: relationships
SELECT * from cypher('list', $$MATCH (u) RETURN relationships("string")$$) as (relationships agtype);
ERROR: relationships() argument must be a path
CONTEXT: referenced column: relationships
SELECT * from cypher('list', $$MATCH (u) RETURN relationships(u)$$) as (relationships agtype);
ERROR: relationships() argument must be a path
CONTEXT: referenced column: relationships
SELECT * from cypher('list', $$MATCH ()-[e]->() RETURN relationships(e)$$) as (relationships agtype);
ERROR: relationships() argument must be a path
CONTEXT: referenced column: relationships
-- 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
CONTEXT: referenced column: range
SELECT * from cypher('list', $$RETURN range(0, null, -3)$$) as (range agtype);
ERROR: range(): neither start or end can be NULL
CONTEXT: referenced column: range
SELECT * from cypher('list', $$RETURN range(0, -10.0, -3.0)$$) as (range agtype);
ERROR: range() unsupported argument type
CONTEXT: referenced column: range
-- 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
CONTEXT: referenced column: labels
--
-- Cleanup
--
SELECT * FROM drop_graph('VLE', true);
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table "VLE"._ag_label_vertex
drop cascades to table "VLE"._ag_label_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
--
