You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@age.apache.org by jo...@apache.org on 2021/02/26 22:32:00 UTC
[incubator-age] branch master updated: Allow Agtype to be casted to
a Postgres integer
This is an automated email from the ASF dual-hosted git repository.
joshinnis pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-age.git
The following commit(s) were added to refs/heads/master by this push:
new 1af3303 Allow Agtype to be casted to a Postgres integer
1af3303 is described below
commit 1af3303ecdfde6ec83d402345ea986455d5ed3c7
Author: Josh Innis <Jo...@gmail.com>
AuthorDate: Fri Feb 26 13:36:39 2021 -0800
Allow Agtype to be casted to a Postgres integer
Allow Agtype values be explicitly cast into ints.
Also added test cases for ORDER BY
---
age--0.3.0.sql | 36 +++++++
regress/expected/expr.out | 223 +++++++++++++++++++++++++++++++++++++++++
regress/sql/cypher_match.sql | 1 -
regress/sql/expr.sql | 100 ++++++++++++++++++
src/backend/utils/adt/agtype.c | 133 ++++++++++++++++++++++++
5 files changed, 492 insertions(+), 1 deletion(-)
diff --git a/age--0.3.0.sql b/age--0.3.0.sql
index b594d84..21b4211 100644
--- a/age--0.3.0.sql
+++ b/age--0.3.0.sql
@@ -780,6 +780,42 @@ AS 'MODULE_PATHNAME';
CREATE CAST (int8 AS agtype)
WITH FUNCTION ag_catalog.int8_to_agtype(int8);
+-- agtype -> int8
+CREATE FUNCTION ag_catalog.agtype_to_int8(variadic "any")
+RETURNS bigint
+LANGUAGE c
+STABLE
+RETURNS NULL ON NULL INPUT
+PARALLEL SAFE
+AS 'MODULE_PATHNAME';
+
+CREATE CAST (agtype AS bigint)
+WITH FUNCTION ag_catalog.agtype_to_int8(variadic "any");
+
+-- agtype -> int4
+CREATE FUNCTION ag_catalog.agtype_to_int4(variadic "any")
+RETURNS int
+LANGUAGE c
+STABLE
+RETURNS NULL ON NULL INPUT
+PARALLEL SAFE
+AS 'MODULE_PATHNAME';
+
+-- agtype -> int2
+CREATE CAST (agtype AS int)
+WITH FUNCTION ag_catalog.agtype_to_int4(variadic "any");
+
+CREATE FUNCTION ag_catalog.agtype_to_int2(variadic "any")
+RETURNS smallint
+LANGUAGE c
+STABLE
+RETURNS NULL ON NULL INPUT
+PARALLEL SAFE
+AS 'MODULE_PATHNAME';
+
+CREATE CAST (agtype AS smallint)
+WITH FUNCTION ag_catalog.agtype_to_int2(variadic "any");
+
--
-- agtype - access operators
--
diff --git a/regress/expected/expr.out b/regress/expected/expr.out
index 822d513..a0e22a1 100644
--- a/regress/expected/expr.out
+++ b/regress/expected/expr.out
@@ -665,6 +665,119 @@ $$) AS r(result agtype);
(1 row)
--
+--Coearce to Postgres 3 int types (smallint, int, bigint)
+--
+SELECT create_graph('type_coercion');
+NOTICE: graph "type_coercion" has been created
+ create_graph
+--------------
+
+(1 row)
+
+SELECT * FROM cypher('type_coercion', $$
+ RETURN NULL
+$$) AS (i bigint);
+ i
+---
+
+(1 row)
+
+SELECT * FROM cypher('type_coercion', $$
+ RETURN 1
+$$) AS (i smallint);
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM cypher('type_coercion', $$
+ RETURN 1
+$$) AS (i int);
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM cypher('type_coercion', $$
+ RETURN 1
+$$) AS (i bigint);
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM cypher('type_coercion', $$
+ RETURN 1.0
+$$) AS (i bigint);
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM cypher('type_coercion', $$
+ RETURN 1.0::numeric
+$$) AS (i bigint);
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM cypher('type_coercion', $$
+ RETURN '1'
+$$) AS (i bigint);
+ i
+---
+ 1
+(1 row)
+
+--Invalid String Format
+SELECT * FROM cypher('type_coercion', $$
+ RETURN '1.0'
+$$) AS (i bigint);
+ERROR: invalid input syntax for integer: "1.0"
+-- Casting to ints that will cause overflow
+SELECT * FROM cypher('type_coercion', $$
+ RETURN 10000000000000000000
+$$) AS (i smallint);
+ERROR: smallint out of range
+SELECT * FROM cypher('type_coercion', $$
+ RETURN 10000000000000000000
+$$) AS (i int);
+ERROR: integer out of range
+--Invalid types
+SELECT * FROM cypher('type_coercion', $$
+ RETURN true
+$$) AS (i bigint);
+ERROR: cannot cast agtype boolean to type int
+SELECT * FROM cypher('type_coercion', $$
+ RETURN {key: 1}
+$$) AS (i bigint);
+ERROR: cannot cast agtype object to type int
+SELECT * FROM cypher('type_coercion', $$
+ RETURN [1]
+$$) AS (i bigint);
+ERROR: cannot cast agtype array to type int
+SELECT * FROM cypher('type_coercion', $$CREATE ()-[:edge]->()$$) AS (result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('type_coercion', $$
+ MATCH (v)
+ RETURN v
+$$) AS (i bigint);
+ERROR: cannot cast agtype vertex to type int
+SELECT * FROM cypher('type_coercion', $$
+ MATCH ()-[e]-()
+ RETURN e
+$$) AS (i bigint);
+ERROR: cannot cast agtype edge to type int
+SELECT * FROM cypher('type_coercion', $$
+ MATCH p=()-[]-()
+ RETURN p
+$$) AS (i bigint);
+ERROR: cannot cast agtype path to type int
+--
-- Test typecasting '::' transform and execution logic
--
--
@@ -4795,9 +4908,119 @@ AS (result agtype);
ERROR: "x" must be either part of an explicitly listed key or used inside an aggregate function
LINE 1: ...CT * FROM cypher('group_by', $$MATCH (x:L) RETURN x.a + coun...
^
+--ORDER BY
+SELECT create_graph('order_by');
+NOTICE: graph "order_by" has been created
+ create_graph
+--------------
+
+(1 row)
+
+SELECT * FROM cypher('order_by', $$CREATE ()$$) AS (result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('order_by', $$CREATE ({i: '1'})$$) AS (result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('order_by', $$CREATE ({i: 1})$$) AS (result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('order_by', $$CREATE ({i: 1.0})$$) AS (result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('order_by', $$CREATE ({i: 1::numeric})$$) AS (result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('order_by', $$CREATE ({i: true})$$) AS (result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('order_by', $$CREATE ({i: false})$$) AS (result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('order_by', $$CREATE ({i: {key: 'value'}})$$) AS (result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('order_by', $$CREATE ({i: [1]})$$) AS (result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('order_by', $$
+ MATCH (u)
+ RETURN u.i
+ ORDER BY u.i
+$$) AS (i agtype);
+ i
+------------------
+ {"key": "value"}
+ [1]
+ "1"
+ false
+ true
+ 1::numeric
+ 1
+ 1.0
+
+(9 rows)
+
+SELECT * FROM cypher('order_by', $$
+ MATCH (u)
+ RETURN u.i
+ ORDER BY u.i DESC
+$$) AS (i agtype);
+ i
+------------------
+
+ 1
+ 1.0
+ 1::numeric
+ true
+ false
+ "1"
+ [1]
+ {"key": "value"}
+(9 rows)
+
--
-- Cleanup
--
+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
diff --git a/regress/sql/cypher_match.sql b/regress/sql/cypher_match.sql
index 2ee2afe..3211f70 100644
--- a/regress/sql/cypher_match.sql
+++ b/regress/sql/cypher_match.sql
@@ -434,7 +434,6 @@ $$) AS (i agtype);
--
-- Clean up
--
-
SELECT drop_graph('cypher_match', true);
--
diff --git a/regress/sql/expr.sql b/regress/sql/expr.sql
index 8585e38..5688664 100644
--- a/regress/sql/expr.sql
+++ b/regress/sql/expr.sql
@@ -356,6 +356,80 @@ RETURN "abcdefghijklmnopqrstuvwxyz" CONTAINS "klmo"
$$) AS r(result agtype);
--
+--Coearce to Postgres 3 int types (smallint, int, bigint)
+--
+SELECT create_graph('type_coercion');
+SELECT * FROM cypher('type_coercion', $$
+ RETURN NULL
+$$) AS (i bigint);
+
+SELECT * FROM cypher('type_coercion', $$
+ RETURN 1
+$$) AS (i smallint);
+
+SELECT * FROM cypher('type_coercion', $$
+ RETURN 1
+$$) AS (i int);
+
+SELECT * FROM cypher('type_coercion', $$
+ RETURN 1
+$$) AS (i bigint);
+
+SELECT * FROM cypher('type_coercion', $$
+ RETURN 1.0
+$$) AS (i bigint);
+
+SELECT * FROM cypher('type_coercion', $$
+ RETURN 1.0::numeric
+$$) AS (i bigint);
+
+SELECT * FROM cypher('type_coercion', $$
+ RETURN '1'
+$$) AS (i bigint);
+
+--Invalid String Format
+SELECT * FROM cypher('type_coercion', $$
+ RETURN '1.0'
+$$) AS (i bigint);
+
+-- Casting to ints that will cause overflow
+SELECT * FROM cypher('type_coercion', $$
+ RETURN 10000000000000000000
+$$) AS (i smallint);
+
+
+SELECT * FROM cypher('type_coercion', $$
+ RETURN 10000000000000000000
+$$) AS (i int);
+
+--Invalid types
+SELECT * FROM cypher('type_coercion', $$
+ RETURN true
+$$) AS (i bigint);
+
+SELECT * FROM cypher('type_coercion', $$
+ RETURN {key: 1}
+$$) AS (i bigint);
+
+SELECT * FROM cypher('type_coercion', $$
+ RETURN [1]
+$$) AS (i bigint);
+
+SELECT * FROM cypher('type_coercion', $$CREATE ()-[:edge]->()$$) AS (result agtype);
+SELECT * FROM cypher('type_coercion', $$
+ MATCH (v)
+ RETURN v
+$$) AS (i bigint);
+SELECT * FROM cypher('type_coercion', $$
+ MATCH ()-[e]-()
+ RETURN e
+$$) AS (i bigint);
+SELECT * FROM cypher('type_coercion', $$
+ MATCH p=()-[]-()
+ RETURN p
+$$) AS (i bigint);
+
+--
-- Test typecasting '::' transform and execution logic
--
@@ -1980,9 +2054,35 @@ AS (a agtype, result agtype);
SELECT * FROM cypher('group_by', $$MATCH (x:L) RETURN x.a + count(*) + x.b + count(*) + x.c$$)
AS (result agtype);
+--ORDER BY
+SELECT create_graph('order_by');
+SELECT * FROM cypher('order_by', $$CREATE ()$$) AS (result agtype);
+SELECT * FROM cypher('order_by', $$CREATE ({i: '1'})$$) AS (result agtype);
+SELECT * FROM cypher('order_by', $$CREATE ({i: 1})$$) AS (result agtype);
+SELECT * FROM cypher('order_by', $$CREATE ({i: 1.0})$$) AS (result agtype);
+SELECT * FROM cypher('order_by', $$CREATE ({i: 1::numeric})$$) AS (result agtype);
+SELECT * FROM cypher('order_by', $$CREATE ({i: true})$$) AS (result agtype);
+SELECT * FROM cypher('order_by', $$CREATE ({i: false})$$) AS (result agtype);
+SELECT * FROM cypher('order_by', $$CREATE ({i: {key: 'value'}})$$) AS (result agtype);
+SELECT * FROM cypher('order_by', $$CREATE ({i: [1]})$$) AS (result agtype);
+
+SELECT * FROM cypher('order_by', $$
+ MATCH (u)
+ RETURN u.i
+ ORDER BY u.i
+$$) AS (i agtype);
+
+SELECT * FROM cypher('order_by', $$
+ MATCH (u)
+ RETURN u.i
+ ORDER BY u.i DESC
+$$) AS (i agtype);
+
--
-- Cleanup
--
+SELECT * FROM drop_graph('type_coercion', true);
+SELECT * FROM drop_graph('order_by', true);
SELECT * FROM drop_graph('group_by', true);
SELECT * FROM drop_graph('UCSC', true);
SELECT * FROM drop_graph('expr', true);
diff --git a/src/backend/utils/adt/agtype.c b/src/backend/utils/adt/agtype.c
index 4f2bb7a..b0889ce 100644
--- a/src/backend/utils/adt/agtype.c
+++ b/src/backend/utils/adt/agtype.c
@@ -2145,6 +2145,9 @@ static void cannot_cast_agtype_value(enum agtype_value_type type,
{AGTV_BOOL, gettext_noop("cannot cast agtype boolean to type %s")},
{AGTV_ARRAY, gettext_noop("cannot cast agtype array to type %s")},
{AGTV_OBJECT, gettext_noop("cannot cast agtype object to type %s")},
+ {AGTV_VERTEX, gettext_noop("cannot cast agtype vertex to type %s")},
+ {AGTV_EDGE, gettext_noop("cannot cast agtype edge to type %s")},
+ {AGTV_PATH, gettext_noop("cannot cast agtype path to type %s")},
{AGTV_BINARY,
gettext_noop("cannot cast agtype array or object to type %s")}};
int i;
@@ -2181,6 +2184,136 @@ Datum agtype_to_bool(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(agtv.val.boolean);
}
+PG_FUNCTION_INFO_V1(agtype_to_int8);
+/*
+ * Cast agtype to int8.
+ */
+Datum agtype_to_int8(PG_FUNCTION_ARGS)
+{
+ agtype *agtype_in = AG_GET_ARG_AGTYPE_P(0);
+ agtype_value agtv;
+ int8 result = 0x0;
+ agtype *arg_agt;
+
+ /* get the agtype equivalence of any convertable input type */
+ arg_agt = get_one_agtype_from_variadic_args(fcinfo, 0, 1);
+
+ /* Return null if arg_agt is null. This covers SQL and Agtype NULLS */
+ if (arg_agt == NULL)
+ PG_RETURN_NULL();
+
+ if (!agtype_extract_scalar(&arg_agt->root, &agtv) ||
+ (agtv.type != AGTV_FLOAT &&
+ agtv.type != AGTV_INTEGER &&
+ agtv.type != AGTV_NUMERIC &&
+ agtv.type != AGTV_STRING))
+ cannot_cast_agtype_value(agtv.type, "int");
+
+ PG_FREE_IF_COPY(agtype_in, 0);
+
+ if (agtv.type == AGTV_INTEGER)
+ result = agtv.val.int_value;
+ else if (agtv.type == AGTV_FLOAT)
+ result = DatumGetInt64(DirectFunctionCall1(dtoi8,
+ Float8GetDatum(agtv.val.float_value)));
+ else if (agtv.type == AGTV_NUMERIC)
+ result = DatumGetInt64(DirectFunctionCall1(numeric_int8,
+ NumericGetDatum(agtv.val.numeric)));
+ else if (agtv.type == AGTV_STRING)
+ result = DatumGetInt64(DirectFunctionCall1(int8in,
+ CStringGetDatum(agtv.val.string.val)));
+
+ PG_RETURN_INT64(result);
+}
+
+PG_FUNCTION_INFO_V1(agtype_to_int4);
+
+/*
+ * Cast agtype to int4.
+ */
+Datum agtype_to_int4(PG_FUNCTION_ARGS)
+{
+ agtype *agtype_in = AG_GET_ARG_AGTYPE_P(0);
+ agtype_value agtv;
+ int32 result = 0x0;
+ agtype *arg_agt;
+
+ /* get the agtype equivalence of any convertable input type */
+ arg_agt = get_one_agtype_from_variadic_args(fcinfo, 0, 1);
+
+ /* Return null if arg_agt is null. This covers SQL and Agtype NULLS */
+ if (arg_agt == NULL)
+ PG_RETURN_NULL();
+
+ if (!agtype_extract_scalar(&arg_agt->root, &agtv) ||
+ (agtv.type != AGTV_FLOAT &&
+ agtv.type != AGTV_INTEGER &&
+ agtv.type != AGTV_NUMERIC &&
+ agtv.type != AGTV_STRING))
+ cannot_cast_agtype_value(agtv.type, "int");
+
+ PG_FREE_IF_COPY(agtype_in, 0);
+
+ if (agtv.type == AGTV_INTEGER)
+ result = DatumGetInt32(DirectFunctionCall1(int84,
+ Int64GetDatum(agtv.val.int_value)));
+ else if (agtv.type == AGTV_FLOAT)
+ result = DatumGetInt32(DirectFunctionCall1(dtoi4,
+ Float8GetDatum(agtv.val.float_value)));
+ else if (agtv.type == AGTV_NUMERIC)
+ result = DatumGetInt32(DirectFunctionCall1(numeric_int4,
+ NumericGetDatum(agtv.val.numeric)));
+ else if (agtv.type == AGTV_STRING)
+ result = DatumGetInt32(DirectFunctionCall1(int4in,
+ CStringGetDatum(agtv.val.string.val)));
+
+ PG_RETURN_INT32(result);
+}
+
+PG_FUNCTION_INFO_V1(agtype_to_int2);
+
+/*
+ * Cast agtype to int2.
+ */
+Datum agtype_to_int2(PG_FUNCTION_ARGS)
+{
+ agtype *agtype_in = AG_GET_ARG_AGTYPE_P(0);
+ agtype_value agtv;
+ int16 result = 0x0;
+ agtype *arg_agt;
+
+ /* get the agtype equivalence of any convertable input type */
+ arg_agt = get_one_agtype_from_variadic_args(fcinfo, 0, 1);
+
+ /* Return null if arg_agt is null. This covers SQL and Agtype NULLS */
+ if (arg_agt == NULL)
+ PG_RETURN_NULL();
+
+ if (!agtype_extract_scalar(&arg_agt->root, &agtv) ||
+ (agtv.type != AGTV_FLOAT &&
+ agtv.type != AGTV_INTEGER &&
+ agtv.type != AGTV_NUMERIC &&
+ agtv.type != AGTV_STRING))
+ cannot_cast_agtype_value(agtv.type, "int");
+
+ PG_FREE_IF_COPY(agtype_in, 0);
+
+ if (agtv.type == AGTV_INTEGER)
+ result = DatumGetInt16(DirectFunctionCall1(int82,
+ Int64GetDatum(agtv.val.int_value)));
+ else if (agtv.type == AGTV_FLOAT)
+ result = DatumGetInt32(DirectFunctionCall1(dtoi2,
+ Float8GetDatum(agtv.val.float_value)));
+ else if (agtv.type == AGTV_NUMERIC)
+ result = DatumGetInt16(DirectFunctionCall1(numeric_int2,
+ NumericGetDatum(agtv.val.numeric)));
+ else if (agtv.type == AGTV_STRING)
+ result = DatumGetInt16(DirectFunctionCall1(int2in,
+ CStringGetDatum(agtv.val.string.val)));
+
+ PG_RETURN_INT16(result);
+}
+
PG_FUNCTION_INFO_V1(agtype_to_float8);
/*