You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@age.apache.org by John Gemignani <jo...@bitnine.net> on 2021/04/02 20:03:02 UTC
Re: [GitHub] [incubator-age] pdpotter opened a new issue #48: Using
geographic objects as properties with PostGIS
When a function isn't prefaced with the schema in the cypher function call,
the transform logic will see it as a cypher function and prepend age_ to
the name and then add the schema ag_catalog. This avoids naming clashes and
helps with understanding where the system is looking for something. So, if
you want to use a specific PG or other user function, you need to specify
the specific schema that it is in. When a schema is provided, the transform
logic no longer looks for it in ag_catalog with a prepended age_. The
function call transforms, inside the cypher function call, recurse through
the arguments. So, nested calls will be processed by AGE in the above
manner.
This logic may, or may not change in the future. The issue here is that the
PG routines that search for the function will exit if one is not found. So
our code - unless we add in more of PG's code and modify it - will never
get a second chance to process it for additional matches. So, we had to
find a happy middle ground. Unfortunately, some of PG's errors are vague.
Was it not found at all? or just not one that matches the arguments? Only
the debugger can tell you.
For this particular issue, this means that any non-AGE function needs its
schema name added if it appears in the cypher function command. As an
example, for PG's sqrt(4), it would be pg_catalog.sqrt(4).
Additionally, any function argument inside the cypher function command will
be transformed through AGE. These are not the same typecasts as PG's
typecasts. So, typecasts like 4::float, will be translated as some number
into agtype float, not PG's float. We plan on adding ones like ::pg_float
shortly. There is currently an exception to this, and that is due to an
implicit cast that is going to be removed and replaced by an explicit cast.
There is an implicit cast to float, that will be removed shortly, so if
everything is correctly named, what you have above should work - for items
that can be cast to a float.
As an example -
psql-11.5-5432-pgsql=# SELECT * from cypher('test', $$
RETURN pg_catalog.sqrt(pg_catalog.sqrt(pg_catalog.sqrt(256)))
$$) as (result agtype);
result
--------
2.0
(1 row)
Hopefully this helps. Unfortunately, I am unable to get Postgis installed
and therefore I can't debug it further at this time.
John
On Mon, Mar 29, 2021 at 6:18 AM GitBox <gi...@apache.org> wrote:
>
> pdpotter opened a new issue #48:
> URL: https://github.com/apache/incubator-age/issues/48
>
>
> Is there a way to add PostGis data as property to a node (e.g., a
> [point geometry](https://postgis.net/docs/ST_MakePoint.html))?
>
> I've tried:
> ```
> SELECT * FROM cypher('testgraph', $$
> CREATE (n:Test {id: 1, location: ST_SetSRID(ST_MakePoint(-71.10,
> 42.32),4326)}) return n
> $$) as (a agtype);
> ERROR: function ag_catalog.age_st_makepoint(agtype, agtype) does not
> exist
> LINE 2: ... (n:Test {id: 1, location: ST_SetSRID(ST_MakePoint(-71.10,
> 4...
> ^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
>
> SELECT * FROM cypher('testgraph', $$
> CREATE (n:Test {id: 1, location:
> public.ST_SetSRID(public.ST_MakePoint(-71.10, 42.32),4326)}) return n
> $$) as (a agtype);
> ERROR: function public.ST_MakePoint(agtype, agtype) does not exist
> LINE 2: ...1, location: public.ST_SetSRID(public.ST_MakePoint(-71.10,
> 4...
> ^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
>
> SELECT * FROM cypher('testgraph', $$
> CREATE (n:Test {id: 1, location:
> public.ST_SetSRID(public.ST_MakePoint(-71.10::float, 42.32::float),4326)})
> return n
> $$) as (a agtype);
> ERROR: ag function does not exist
> LINE 1: SELECT * FROM cypher('testgraph', $$
> ^
> DETAIL: agtype_typecast_float(1)
>
> SELECT * FROM cypher('testgraph', $$
> CREATE (n:Test {id: 1, location:
> public.ST_GeomFromText('POINT(-71.10, 42.32)', 4326)}) return n
> $$) as (a agtype);
> ERROR: function public.ST_GeomFromText(agtype, agtype) does not exist
> LINE 2: ...E (n:Test {id: 1, location:
> public.ST_GeomFromText('POINT(-7...
> ^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
>
> SELECT * FROM cypher('testgraph', $$
> CREATE (n:Test {id: 1, location:
> public.ST_GeomFromText('POINT(-71.10, 42.32)'::text, 4326)}) return n
> $$) as (a agtype);
> ERROR: typecast 'text' not supported
> LINE 1: SELECT * FROM cypher('testgraph', $$
> ^
> ```
>
> Additional information: the indivial extensions (age, postgis) do work:
> ```
> SELECT * FROM cypher('testgraph', $$
> CREATE (n:Test {id: 1}) return n
> $$) as (a agtype);
> a
>
>
> ----------------------------------------------------------------------------
> {"id": 1688849860263978, "label": "Test", "properties": {"id":
> 1}}::vertex
> (1 row)
>
> SELECT ST_SetSRID(ST_MakePoint(-71.10, 42.31),4326) as location;
> location
> ----------------------------------------------------
> 0101000020E61000006666666666C651C048E17A14AE274540
> (1 row)
>
> SELECT PostGIS_Version();
> postgis_version
> ---------------------------------------
> 2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
> (1 row)
> ```
>
>
> --
> This is an automated message from the Apache Git Service.
> To respond to the message, please log on to GitHub and use the
> URL above to go to the specific comment.
>
> For queries about this service, please contact Infrastructure at:
> users@infra.apache.org
>
>
>