2014-11-20 17:58 GMT+01:00 Ben Hood <[email protected]>: > Hi Lukas, > > Thanks for your help, it's very much appreciated. > > On Thu, Nov 20, 2014 at 5:03 PM, Lukas Eder <[email protected]> wrote: > > What is (x).* and (x).id? I mean, there is no table called x in the outer > > query, only a table called y. Is x a user-defined type? > > x is not a user defined type, it is just an alias that I have used to > create a query. The query is quite large, so I just wanted to post you > a a fragment of the query so that you are not having to decipher weird > SQL. >
I forgot about this syntax - just tried it on my PostgreSQL instance. So, SELECT x FROM queues AS x or simply SELECT queues FROM queues Is PostgreSQL-specific, and it selects the whole row as a single row column. There's actually mention of this feature in the following issue, which aims to add support for nested records in jOOQ: https://github.com/jOOQ/jOOQ/issues/2360 OK, so that's clear now. However, you currently can't use that syntax in jOOQ. jOOQ cannot reason about DSL.field("x") being really a row with its nested columns. > 2014-11-20 12:20 GMT+01:00 Ben Hood <[email protected]>: > > You're renaming QUEUES to "x", but then you're still using the QUEUES > > reference to dereference columns from. Try doing this: > > > > Queues x = QUEUES.as("x") > > > > > > And then > > > > > > x.CONTEXT.eq("foo") > > x.ID, x.PRIORITY > > That works very well - I'd never actually used this form of "column > instantiation" (for want of a better term) in JOOQ before. > >> Exception in thread "main" java.lang.RuntimeException: > >> org.postgresql.util.PSQLException: ERROR: invalid reference to > >> FROM-clause entry for table "queues" > >> Hint: Perhaps you meant to reference the table alias "x". > >> Position: 46 > > > > > > In other words, precisely what I said ;-) > > Yes, you're probably looking at this and wondering what this guy is > trying to achieve. Maybe I should have given you the full context. > No worries. I was just confused by PostgreSQL syntax. > FWIW, this is the entire query (that works well when executed as a > regular SQL string): > > WITH RECURSIVE message AS ( > SELECT (x).*, pg_try_advisory_xact_lock((x).id) AS locked > FROM ( > SELECT x > FROM queues AS x > WHERE context = 'foo' > ORDER BY id, priority > LIMIT 1 > ) AS y > UNION ALL ( > SELECT (x).*, pg_try_advisory_xact_lock((x).id) AS locked > FROM ( > SELECT ( > SELECT x > FROM queues AS x > WHERE context = 'foo' > AND (id, priority) > (message.id, message.priority) > ORDER BY id, priority > LIMIT 1 > ) AS x > FROM message > WHERE NOT message.locked > LIMIT 1 > ) AS y > ) > ) > SELECT id, priority, enqueued_at, context, correlation_id > FROM message > WHERE locked > LIMIT 1; > > Now that this query has the behavior I need, I am trying to > re-construct it using JOOQ. > > Is this maybe something I shouldn't really be trying to do with JOOQ - > i.e. should I just use a plain SQL string or create a stored proc? While you certainly can implement this with jOOQ (without using that PostgreSQL-specific syntax), there's nothing keeping you from writing a view (if you don't have bind variables), or a table-valued function via CREATE FUNCTION .. RETURNS TABLE: http://www.postgresql.org/docs/9.3/static/sql-createfunction.html Those are supported by the jOOQ code generator since jOOQ 3.5: https://github.com/jOOQ/jOOQ/issues/3375 The documentation is still missing, but let's assume that your function will take that 'foo' value as a parameter, only: CREATE FUNCTION messages (p_context VARCHAR(100)) RETURNS TABLE ( id INT, priority INT, locked INT ) AS $$ BEGIN RETURN QUERY WITH RECURSIVE ... END $$ LANGUAGE plpgsql; You can then create a function call to be used in SQL like this: Messages messages = Tables.MESSAGES.call("foo"); select(messages.id, messages.priority, messages.locked) .from(messages) As you can see, the function call really returns an org.jooq.Table instance with column references that you can use. This is now supported for SQL Server (jOOQ 3.3), PostgreSQL (jOOQ 3.5), and Firebird (jOOQ 3.5). Let me know if you want to go further into writing this query with jOOQ Cheers Lukas -- 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.
