I've added a similar function as yours to my integration tests. Using the
syntax you've used:
create().select(fSearchBook("Animal", 1L, 0L)).fetch();
This yields
+-----------------------------------------+
|f_search_book |
+-----------------------------------------+
|(2,1,,,"Animal Farm",1945,1,,,"ON STOCK")|
+-----------------------------------------+
Postgres turns the non-scala table type into a row value expression. The
correct way to do this would be to put the function in the FROM clause:
create().select().from(table(fSearchBook(...))).fetch();
But unfortunately, that's what's not yet supported by jOOQ. I'm afraid, I
currently don't see a workaround to this, short of using plain SQL:
String sql = fSearchBook("Animal", 1L, 0L).toString();
create().select().from(sql).fetch();
2013/2/16 Francesco Pontillo <[email protected]>:
> Actually, I don't think so. This one on github is my jOOQ-generated
> EmployeeRecord (I have a "DB" suffix added by a custom generator which
does
> nothing else), so the names should be mapped right from the database.
>
>
> On Saturday, February 16, 2013 9:27:06 AM UTC+1, Lukas Eder wrote:
>>
>> This would probably be due to the fact that the field names might not
>> be the same (e.g. upper / lower case). Can you confirm that?
>>
>> 2013/2/16 Francesco Pontillo <[email protected]>:
>> > Hello, I have just thought about this, and tried too. It does not work,
>> > though: jOOQ converts the list (meaning the number of EmployerRecord is
>> > correct) but all of its values are null.
>> >
>> >
>> > On Saturday, February 16, 2013 9:19:17 AM UTC+1, Lukas Eder wrote:
>> >>
>> >> Hello,
>> >>
>> >> Unfortunately, Postgres TABLE types returned from stored procedures
>> >> aren't fully supported (yet). There is a pending feature request for
>> >> this:
>> >> https://github.com/jOOQ/jOOQ/issues/1139
>> >>
>> >> The trickiness here lies in the fact that for postgres, functions are
>> >> tables as well, regardless if they return a table type, or several
>> >> scalar OUT parameters. I have not yet given this enough thought,
>> >> though.
>> >>
>> >> Since you know that your actual records are of type EmployerRecord,
>> >> you can work around this for now, using Result.into(EMPLOYER)
>> >>
>> >>
>> >>
http://www.jooq.org/javadoc/latest/org/jooq/Result.html#into(org.jooq.Table)
>> >>
>> >> This will convert your RecordImpl into an EmployerRecord
>> >>
>> >> Cheers
>> >> Lukas
>> >>
>> >> 2013/2/16 Francesco Pontillo <[email protected]>:
>> >> > Hello (again),
>> >> > I have the following function defined on my PostgreSQL database;
>> >> >
>> >> > CREATE OR REPLACE FUNCTION search_employer(firstlast character
>> >> > varying,
>> >> > lim
>> >> > bigint, offs bigint)
>> >> > RETURNS SETOF employer AS
>> >> > $BODY$
>> >> > SELECT * FROM employer
>> >> > WHERE (LOWER(name || ' ' || lastname) LIKE LOWER('%' || firstlast ||
>> >> > '%'))
>> >> > OR (LOWER(lastname || ' ' || name) LIKE LOWER('%' || firstlast ||
>> >> > '%'))
>> >> > LIMIT lim OFFSET offs;
>> >> > $BODY$
>> >> > LANGUAGE sql VOLATILE
>> >> > COST 100
>> >> > ROWS 1000;
>> >> >
>> >> > Basically, it looks for some employers based on first and last name.
>> >> > My
>> >> > Java
>> >> > code is the following:
>> >> >
>> >> > Result<Record> r = f.select(searchEmployer(firstlast, pageSize,
>> >> > offset)).fetch();
>> >> >
>> >> > The problem is I expect to get, in return, a Result<EmployerRecord>,
>> >> > because
>> >> > that's what I get when I do a regular select() with jOOQ. Is there
>> >> > something
>> >> > I am doing wrong? Or is this the expected behavior? If so, how can I
>> >> > transform a RecordImpl (which I've noticed is deprecated too) into a
>> >> > EmployerRecord? Casting the Result doesn't throw anything, but if I
>> >> > cast
>> >> > the
>> >> > inner object I get a
>> >> >
>> >> > java.lang.ClassCastException: org.jooq.impl.RecordImpl cannot be
cast
>> >> > to
>> >> > my.package.jooq.gen.tables.records.EmployerRecord. Thanks!
>> >> >
>> >> > --
>> >> > 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/groups/opt_out.
>> >> >
>> >> >
>> >
>> > --
>> > 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/groups/opt_out.
>> >
>> >
>
> --
> 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/groups/opt_out.
>
>
--
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/groups/opt_out.
