DSL.sql() and question mark which are not placeholders?

[victo...@gmail.com]

Hi,

I'm writing this in Java:

DSL.sql("ltree2text(subpath({0} ?~ {1} || '.*', {2}, {2}))", Tables.LINE.PATHS, id, level + 1);

The objective is to integrate with the PostgreSQL ltree functions, and from what I understood, they are not programmatically available.

There is an operator "?~" (see https://www.postgresql.org/docs/current/static/ltree.html) to work on an array of ltree.

The problem is that jooq renders this as:

ltree2text(subpath("public"."Line"."paths" "public"."InvoiceLine"."paths" ~ 'someId' || '.*', 1, 1)

instead of:

ltree2text(subpath("public"."Line"."paths" ?~ 'someId' || '.*', 1, 1)

Is there anyway to escape ? putting a "\\" in front of it doesn't seem to work…

Thanks!

Victor

[Lukas Eder]

Hi Victor,

Thanks for pointing out those operators. I've created and fixed #7035 for jOOQ 3.11, and it will ship the fix also with 3.10.4 next week.

https://github.com/jOOQ/jOOQ/issues/7035

Note that we can only work around the operators that are composed of ? and at least an additional character, not single ? characters, such as in:

ltree ? lquery[]	boolean	does ltree match any lquery in array?
lquery[] ? ltree	boolean	does ltree match any lquery in array?

And

ltree[] ? lquery[]	boolean	does ltree array contain any path matching any lquery?
lquery[] ? ltree[]	boolean	does ltree array contain any path matching any lquery?

Also, the PostgreSQL JDBC driver is unable to handle ? characters in operators anyway, so you might want to apply the workaround that I've documented here:

https://stackoverflow.com/a/38370973/521799

I.e. use the more readable plain english function name that implements the operator. The following query:

SELECT 

  oprname, 

  oprcode || '(' || format_type(oprleft,  NULL::integer) || ', ' 

                 || format_type(oprright, NULL::integer) || ')' AS function

FROM pg_operator 

WHERE oprname LIKE '?%';

Yields, on my PostgreSQL installation:

oprname |function                        |

--------|--------------------------------|

?#      |path_inter(path, path)          |

?#      |box_overlap(box, box)           |

?-      |point_horiz(point, point)       |

?|      |point_vert(point, point)        |

?#      |lseg_intersect(lseg, lseg)      |

?||     |lseg_parallel(lseg, lseg)       |

?-|     |lseg_perp(lseg, lseg)           |

?-      |lseg_horizontal(-, lseg)        |

?|      |lseg_vertical(-, lseg)          |

?#      |inter_sl(lseg, line)            |

?#      |inter_sb(lseg, box)             |

?#      |inter_lb(line, box)             |

?#      |line_intersect(line, line)      |

?||     |line_parallel(line, line)       |

?-|     |line_perp(line, line)           |

?-      |line_horizontal(-, line)        |

?|      |line_vertical(-, line)          |

?       |jsonb_exists(jsonb, text)       |

?|      |jsonb_exists_any(jsonb, text[]) |

?&      |jsonb_exists_all(jsonb, text[]) |

?       |exist(hstore, text)             |

?|      |exists_any(hstore, text[])      |

?&      |exists_all(hstore, text[])      |

I hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

I'll fix this also for geometry operators:

https://github.com/jOOQ/jOOQ/issues/7037

[victo...@gmail.com]

As always, excellent and precise answer, thank you very much!

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.