Re: Postgres subquery aliasing

[Ben Hood]

Hi Lukas,

Thanks for your help, it's very much appreciated.

On Thu, Nov 20, 2014 at 5:03 PM, Lukas Eder <lukas.e...@gmail.com> 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.


> 2014-11-20 12:20 GMT+01:00 Ben Hood <0x6e6...@gmail.com>:
> 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.
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?

Cheers,

Ben

-- 
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 jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.