Add support for reading arbitrary enum values as String
#429
Comments
|
On a related note: Given that PostgreSQL has a very easily extendable type system, I think it should be possible to get a |
|
Have you seen https://github.com/pgjdbc/r2dbc-postgresql#postgres-enum-types to consume enums as Java enums? I actually like the idea of having a generic representation of a value. With R2DBC 0.9, we introduced |
I have not. That works for end users that use R2DBC directly, but it's not too practical for a client library like jOOQ:
However, assuming the library knows the user-type, it would be useful to think about some type registry API that can be used in an ad-hoc fashion, rather than globally. For example, JDBC has |
That's another thing I'd like to avoid. The OID of a user-defined type is unstable and can change depending on the system, so it would have to be looked up all the time. Caching is possible, but in principle, between two usages, the OID could change if the user drops / re-creates the type. The name is the only reliable way to identify a type.
That's another limitation that I wouldn't want to support :) Again, the identifier of an enum type should be the fully qualified type name, not the OID or any other handle, such as a client representation. |
|
If you accept a provided |
Sure. I'd expect to be able to do the same as with JDBC: where enum_column = cast(? as schema.enum_type)And then bind a string value. In my case, I always know the enum type's qualified identifier. If users work with enums and R2DBC directly, they'll also do that, they're used to doing it with JDBC, too. It works for many types this way, in PostgreSQL |
|
As a workaround, you could cast the enum to varchar when selecting data. |
Users could easily, but for me, it's a bit more tricky:
So, an ideal solution wouldn't require tampering with the SQL for the read case. The write case is different, because it's only a bind variable, nothing complex. |
|
... I mean, it's definitely doable for jOOQ, but if there's going to be an out-of-the-box solution in the next versions of r2dbc-postgresql, then I prefer not to work around this. |
|
I'm trying to estimate where this is going. The driver doesn't maintain a type cache nor does it look up types during result processing as that would require another query while consuming a result and that doesn't work with the streaming approach of result consumption. Let me come up with a draft for something. |
|
Any idea yet on how to move this forward? I'm working on a project trying to use jOOQ with r2dbc-postgresql and it's stuck on this. |
|
As a first step we could generally apply a In any case, pull requests are welcome. |
If there is no immediately suitable codec can be found and the request is to decode as a String, then fallback to using the VARCHAR codec. [pgjdbc#429]
Automatically register an EnumStringCodec and an EnumStringArrayCodec for enums. [pgjdbc#429]
Automatically register an EnumStringCodec and an EnumStringArrayCodec for enums. [pgjdbc#429]
mp911de
changed the title
String
|
@lukaseder care to check whether this change works for you? Snapshots are built and deployed for both, 0.8.x and 0.9.x development lines. |
Refactor enum to string codecs into StringDecoder and StringArrayDecoder as fallback codecs if no other codec could be found. Allow reuse of ArrayCodec. [#429][resolves #454] Signed-off-by: Mark Paluch <[email protected]>
Refactor enum to string codecs into StringDecoder and StringArrayDecoder as fallback codecs if no other codec could be found. Allow reuse of ArrayCodec. [#429][resolves #454] Signed-off-by: Mark Paluch <[email protected]>
|
I'll check right away, thanks for the ping: jOOQ/jOOQ#12193 |
|
That seems to work: System.out.println(
Flux.from(cf.create())
.flatMap(c -> c.createStatement("select $1::public.u_book_status").bind(0, "SOLD OUT").execute())
.flatMap(it -> it.map((r, m) -> r.get(0, String.class)))
.collectList()
.block()
);
;Will try updating the jOOQ implementation |
|
Yeah, seems to work fine! Thanks a lot! |
|
Hi, it seems if we have option to read pg_enum as string in model, it's logical to have option also to write such string as enum. But in such case StringCodec is used that tries to update field as PostgresqlObjectId.VARCHAR and error is ocurred. Is this good solution, or it may cause any performance issues? |
Feature Request
Is your feature request related to a problem? Please describe
It's currently not possible (I think?) to read enum types with r2dbc-postgresql. Create a type like this:
And try reading it like this:
There's an exception:
Describe the solution you'd like
There should be codecs to read enum types and map them to strings, which is a reasonable default serialisation for enum types
The text was updated successfully, but these errors were encountered: