0

I'm trying to fetch a tree from postgres DB using a native query, the next query is working fine using psql terminal:

SELECT col_1 FROM my_tree WHERE parent_id  ~ lquery('*.C.*')

but when I add the same query using entity manager:

private List<String> fetchTreeByParentId() {
        Query query = entityManager.createNativeQuery("SELECT col_1 FROM my_tree WHERE parent_id  ~ lquery('*.C.*')");
        return query.getResultList();
    }

I'm getting next error:

2021-04-28 08:21:51.105  WARN 107978 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42883
2021-04-28 08:21:51.105 ERROR 107978 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: function lquery(unknown) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 65

I there something I'm missing ... maybe update the postgresql jar?

The project is spring boot 2.4.2

vcg
  • 86
  • 5
  • Maybe your two sessions have different settings of search_path? What if you schema qualify the function? – jjanes Apr 28 '21 at 14:48
  • already tried with schema something like `SELECT col_1 FROM my_schema.my_tree WHERE parent_id ~ lquery('*.C.*')` and also with cast `SELECT col_1 FROM my_tree WHERE parent_id ~ cast('*.C.*' as lquery)` ... with the casting the error is that the data type `lquery` does not exists – vcg Apr 28 '21 at 22:20

1 Answers1

0

So thanks to @jjanes I noticed that data types like lquery & ltree are in my db schema so I had to change my code to:

1.- Get nodes using lquery to use patterns like '.C.'

@Query(value = "SELECT my_col FROM {h-schema}my_tree where parent_id OPERATOR({h-schema}~) {h-schema}lquery(:parentId)", nativeQuery = true)
List<String> treeByParentId(@Param("parentId") String parentId);

2.- Get nodes using ltree to use the label of node e.g. 'C'

@Query(value = "SELECT my_col from {h-schema}my_tree where parent_id OPERATOR({h-schema}<@) ( "
            + "    SELECT parent_id FROM {h-schema}my_tree WHERE node_label = :parentId "
            + "  );", nativeQuery = true)
List<String> treeByParentId(@Param("parentId") String parentId);

I you have the ltree extension in public schema there is no need to add {h-schema} and OPERATOR, I was having the issue because I'm not working with public schema

vcg
  • 86
  • 5