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.
