I had also tried to put the routine in the from clause, because I had
noticed the whole row value mess PostgreSQL introduces.
The SQL workaround works great. At this point I think you're going to
implement the from(Routine) method, right? I think it makes sense, since
you can actually do SELECT * FROM myFunction(...) (and in most cases,
that's the only alternative).
I'm really enjoying jOOQ, I'm using it as parte of a university project and
after having tried Hibernate and Cayenne I have to say this is much much
better!
On Saturday, February 16, 2013 9:41:38 AM UTC+1, Lukas Eder wrote:
>
> 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] <javascript:>>:
> > 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] <javascript:>.
> > 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.
