[SOLVED] ltree postgres type using spring data jpa -- geting syntax error after defining a function and cast within postgres

ltree postgres type using spring data jpa -- geting syntax error after defining a function and cast within postgres

When attempting to write an entity containing a value of ltree type, I was getting the following error:

column "path" is of type ltree but expression is of type character varying

Googling that error got me to Macaddr/Inet type of postgres in slick

The answer there by Craig Ringer got me going, though creating a cast text -> ltree didn't work, so I created a cast varchar -> ltree as follows

CREATE OR REPLACE FUNCTION ltree_invarchar(varchar) RETURNS ltree AS $$
SELECT ltree_in($1::cstring);
$$ LANGUAGE SQL IMMUTABLE;

CREATE CAST (varchar AS ltree) WITH FUNCTION ltree_invarchar(varchar) AS IMPLICIT;

That got me past the initial error, but now when I attempt to insert an ltree value from Java using Spring Boot Starter Data JPA, I get a long stack trace that ends like so:

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at position 0
  Where: SQL function "ltree_invarchar" statement 1
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:135)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
    at com.sun.proxy.$Proxy120.executeUpdate(Unknown Source)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
    ... 71 more

I'm new to postgres, so I created the function above by modifying those given in the answers I found, but now I'm stuck. I need help identifying what the syntax error is and how to fix it.

Solution

I have discovered that the syntax error message is not telling me that there's a problem with my SQL or the function; it is telling me that there's a problem with the data being sent to the function. In my case, there was an error in my Java code that was sending a path beginning with ".", which is not allowed. Fixing that bug in the Java code fixed my problem.