Hi Richard, Thank you very much for your enquiries and feedback. It is very interesting to see that Postgres Plus supports advanced ORDBMS syntax from Oracle, including PL/SQL OBJECT and TABLE types. That's something I had been missing in the past from the "ordinary" PostgreSQL distribution. Also, the PostgreSQL JDBC driver suffers from various issues when serialising composite types.
>From what you have mentioned, and from what I had assumed previously, this database product shouldn't be used with jOOQ's SQLDialect.POSTGRES, as many features, including these types, will work subtly differently. The best way forward would be for us to formally support and integration-test this database as an independent SQLDialect family. I have registered a feature request for this, which we can probably implement in jOOQ 3.8: https://github.com/jOOQ/jOOQ/issues/4944 At this point, I cannot comment on your concrete findings (e.g. the ClassCastException), except that I suspect that SQLDialect.POSTGRES is just not able to work with these Postgres Plus features. I'll keep you posted on our progress with #4944. I'm hoping to be able to present results any time soon. Best Regards, Lukas 2016-01-14 7:04 GMT+01:00 G. Richard Bellamy <[email protected]>: > I've been reading the GitHub issues (#1139 > <https://github.com/jOOQ/jOOQ/issues/1139> and #3375 > <https://github.com/jOOQ/jOOQ/issues/3375>) and the forum posts about > this (e.g. Strange behaviour of routines compared to regular SELECTs > <https://groups.google.com/forum/#!msg/jooq-user/rR8JDMJS-0E/WAVarRKYQxEJ>), > and I think I'm getting closer, but need more guidance. > > The closest I've got so far is the following: > > Record1<Object[]> results = > dslContext.select(BooksPkg.getGetBooksByAuthorId(authorId)).fetchAny(); > > ArrayList<BookObj> bookObjectRecords = new ArrayList<>(); > for (Object record : results.value1()) { > bookObjectRecords.add((BookObjectRecord)record); > } > return bookObjectRecords; > > > This produces the following SQL syntax: > > select "test"."author_pkg"."get_books_by_author"("authorId" := 1) > > And generates the following exception: > > java.lang.ClassCastException: com.edb.jdbc2.Struct cannot be cast to > com.test.udt.records.AuthorObjRecord > > DSL.selectFrom() doesn't work at all - the package routine signatures will > only allow returns of type Object[] or Field<Object[]>, and therefore are > not compatible with using the function in the FROM clause. Also, it appears > you deliberately decided against the "select <function name>" syntax for > returning TABLE valued functions - and here I am trying to use that legacy > syntax. > > -rb > > On Monday, January 11, 2016 at 11:47:02 PM UTC-8, G. Richard Bellamy wrote: >> >> Like PostgreSQL, Postgres Plus Advanced Server (PPAS) has OBJECT types. >> Unlike PostgreSQL, it also has packages, and TABLE object types. And of >> course you can nest these types. You can also return these table types from >> functions. This will look very familiar to those of you familiar with >> Oracle: >> >> CREATE TYPE author_obj AS ( >> id numeric, >> address address_obj, >> books book_tbl >> ); >> >> CREATE TYPE book_obj AS ( >> id numeric, >> title varchar(50) >> ); >> >> CREATE TYPE author_tbl IS TABLE OF author_obj; >> CREATE TYPE book_tbl IS TABLE OF book_obj; >> >> FUNCTION get_books_by_author(authorId numeric) RETURN book_tbl IS >> >> bookTbl book_tbl := book_tbl(); >> >> BEGIN >> >> select * BULK COLLECT INTO bookTbl from (select book_tbl( >> id, >> title) >> from books >> where author_id = authorId; >> >> return bookTbl; >> >> END; >> >> FUNCTION get_authors() RETURN author_tbl IS >> >> authorTbl author_tbl := author_tbl(); >> >> BEGIN >> >> select * BULK COLLECT INTO authorTbl from (select * from authors) >> return authorTbl; >> >> END; >> >> Traditional PostgreSQL syntax would lead us (and jOOQ) to believe that we >> should get our data out of this function using the following syntax: >> SELECT "get_books_by_author" FROM "get_books_by_author"("authorId" := 1); >> >> However, the above produces an exception: >> ERROR: function "get_books_by_author" in FROM has unsupported return type >> book_tbl >> SQL state: 42804 >> >> Now, I can get ROWS back from the above using the following syntax: >> SELECT * FROM TABLE("get_books_by_author" FROM >> "get_books_by_author"("authorId" := 1)); >> >> I haven't tried it, but presumably the jOOQ "table" function should do >> the trick. I'll be testing that, and looking particularly at whether it'll >> work with further nested table object types (say, I wanted to get the >> author_tbl, which includes the nested book_tbl). >> >> Now to the purpose of this message - jOOQ understands the object notation >> used by Postgres to represent objects where "(1, \"Wind and the >> Willows\")" would represent a single book_obj and {"(1, \"Wind and the >> Willows\")","(2, \"The Hobbit\")"} would represent the book_tbl. >> >> In PPAS, in order to get that table type returned from a function, >> without unnesting, you need the query to look like this: >> SELECT "get_books_by_author"("authorId" := 1); >> >> In other words, I need to be able to alter the Dialect, extending it to >> the point where the emitted SQL from the DSL is altered to accommodate this >> new(ish) syntax. >> >> What is the best way to accomplish this? I've found where jOOQ alters the >> emitted SQL just before sending it off to JDBC (e.g. >> "executeSelectFromPOSTGRES"), but I'm guessing there's a better way than to >> have an "execute" override in all my codegen'd routines which extend >> "AbstractRoutine", which would then heavily modify the behavior. >> Furthermore, the abstract class is clearly annotated as INTERNAL ONLY, so >> that's a clue (heh) that I should stay away from implementing anything to >> do with that class myself. >> > -- > 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 [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
