Emulating the SQL standard derived column list

Derived column lists are a fine feature, if your database supports them. The SQL:2008 standard specifies

   7.6 <table reference> 

    <table reference> ::= 
        <table factor> 
      | <joined table> 

    <table factor> ::= 
        <table primary> [ <sample clause> ] 

    <table primary> ::= 
        <table or query name> [ [ AS ] <correlation name> 
      [ <left paren> <derived column list> <right paren> ] ] 
      | <derived table> [ AS ] <correlation name> 
      [ <left paren> <derived column list> <right paren> ] 
      | [...] 

When you put this in action in an actual query, the above becomes (in Postgres SQL):

SELECT t.a, t.b
FROM (
  SELECT 1, 2
) t(a, b)

In words: You can rename a derived table AND its columns in a single step, by supplying a <derived column list> to your <correlation name> (also known as table alias). The main advantage of this syntax is the fact that you only need to know the degree of your derived table, not the concrete (possibly auto-generated) names of its columns. This is particularly useful when renaming columns from an unnested table or from a table or array function:

SELECT t.a, t.b
FROM unnest(my_table_function()) t(a, b)

Emulating derived column lists

Not all databases support <derived column lists> along with a <correlation name>. But you can always emulate them. The simplest way is to use common table expressions. In Oracle, for instance, you could rewrite the above Postgres SQL statement to this:

-- Postgres
SELECT t.a, t.b
FROM (
  SELECT 1, 2
) t(a, b)

-- Equivalent Oracle query
WITH t(a, b) AS (
  SELECT 1, 2 FROM DUAL
)
SELECT * FROM t

-- Or a bit more verbose, if you really want to hide the
-- common table expression within the derived table
SELECT t.a, t.b
FROM (
  WITH t(a, b) AS (
    SELECT 1, 2 FROM DUAL
  )
  SELECT * FROM t
) t

Note that the CTE solution was given on this Stack Overflow question:
http://stackoverflow.com/q/14127707/521799

If your database supports neither derived column lists, nor common table expressions, you will have to push down the derived column list into the derived table and transform your nested SQL. In MySQL or H2, the above Postgres query would look like this:

-- MySQL, H2, and others
SELECT t.a, t.b
FROM (
  SELECT 1 a, 2 b
) t

It looks simple, but of course you’ll have to be able to actually transform your nested select and other sorts of table references

The solution to rule them all

If the latter isn’t an option for you, here’s one that will always work:

-- All databases
SELECT t.a, t.b
FROM (
  SELECT null a, null b FROM DUAL WHERE 1 = 0
  UNION ALL
  SELECT 1, 2 FROM DUAL
) t

Just concatenate an empty record in front of your actual query, which imposes column names upon your UNION ALL query. Thanks go to Bill’s great answer on Stack Overflow

jOOQ-meta. A “hard-core SQL” proof of concept

jOOQ-meta is more than just meta data navigation for your database schema. It is also a proof of concept for the more complex jOOQ queries. It is easy for you users to believe that the simple vanilla queries of this form will work:

create.selectFrom(AUTHOR).where(LAST_NAME.equal("Cohen"));

But jOOQ claims to be a “hard-core SQL library”.

A “hard-core SQL” example

So let’s have a little look at some of jOOQ-meta’s hard-core SQL. Here’s a nice Postgres query that maps Postgres stored functions to jOOQ’s common concept of routines. There are two very curious features in Postgres, which are modelled by the example query

  1. Postgres only knows functions. If functions have one OUT parameter, then that parameter can be treated as the function return value. If functions have more than one OUT parameter, then those parameters can be treated as a function return cursor. In other words, all functions are tables. Quite interesting indeed. But for now, jOOQ doesn’t support that, so several OUT parameters need to be treated as a void result
  2. Postgres allows for overloading standalone functions (which isn’t allowed in Oracle, for instance). So in order to generate an overload index for every function directly in a SQL statement, I’m running a SELECT COUNT(*) subselect within a CASE expression, defaulting to null

Beware. SQL ahead! No dummy query!

Let’s have a look at the SQL:

Routines r1 = ROUTINES.as("r1");
Routines r2 = ROUTINES.as("r2");

for (Record record : create().select(
        r1.ROUTINE_NAME,
        r1.SPECIFIC_NAME,

        // 1. Ignore the data type when there is at least one out parameter
        decode()
            .when(exists(create()
                .selectOne()
                .from(PARAMETERS)
                .where(PARAMETERS.SPECIFIC_SCHEMA.equal(r1.SPECIFIC_SCHEMA))
                .and(PARAMETERS.SPECIFIC_NAME.equal(r1.SPECIFIC_NAME))
                .and(upper(PARAMETERS.PARAMETER_MODE).notEqual("IN"))),
                    val("void"))
            .otherwise(r1.DATA_TYPE).as("data_type"),
        r1.NUMERIC_PRECISION,
        r1.NUMERIC_SCALE,
        r1.TYPE_UDT_NAME,

        // 2. Calculate overload index if applicable
        decode().when(
        exists(
            create().selectOne()
                .from(r2)
                .where(r2.ROUTINE_SCHEMA.equal(getSchemaName()))
                .and(r2.ROUTINE_NAME.equal(r1.ROUTINE_NAME))
                .and(r2.SPECIFIC_NAME.notEqual(r1.SPECIFIC_NAME))),
            create().select(count())
                .from(r2)
                .where(r2.ROUTINE_SCHEMA.equal(getSchemaName()))
                .and(r2.ROUTINE_NAME.equal(r1.ROUTINE_NAME))
                .and(r2.SPECIFIC_NAME.lessOrEqual(r1.SPECIFIC_NAME)).asField())
        .as("overload"))
    .from(r1)
    .where(r1.ROUTINE_SCHEMA.equal(getSchemaName()))
    .orderBy(r1.ROUTINE_NAME.asc())
    .fetch()) {

    // [...] do the loop

The above SQL statement is executed when you generate source code for Postgres and works like a charm. With jOOQ 2.0, the DSL will become even less verbose and more powerful. You couldn’t write much less SQL when using JDBC directly. And you can forget it immediately, with other products, such as JPA, JPQL, HQL, etc :-).

For a comparison, this is what jOOQ renders (For better readability, I removed the escaping of table/field names):

select
  r1.routine_name,
  r1.specific_name,
  case when exists (
            select 1 from information_schema.parameters
            where (information_schema.parameters.specific_schema
              = r1.specific_schema
            and information_schema.parameters.specific_name
              = r1.specific_name
            and upper(information_schema.parameters.parameter_mode)
              <> 'IN'))
       then 'void'
       else r1.data_type
       end as data_type,
  r1.numeric_precision,
  r1.numeric_scale,
  r1.type_udt_name,
  case when exists (
            select 1 from information_schema.routines as r2
            where (r2.routine_schema = 'public'
            and r2.routine_name = r1.routine_name
            and r2.specific_name <> r1.specific_name))
       then (select count(*)
             from information_schema.routines as r2
             where (r2.routine_schema = 'public'
             and r2.routine_name = r1.routine_name
             and r2.specific_name <= r1.specific_name))
       end as overload
from information_schema.routines as r1
where r1.routine_schema = 'public'
order by r1.routine_name asc