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.
