How to select a table dynamically with HSQLDB and Hibernate?

I have a table with references to other tables. Stored is the table name and the entity id.

Like this:

         ref_table

   id  | table_name | refId
-------+------------+-------
    1  |   test     |   6
    2  |   test     |   9
    3  |   other    |   5

Now I try to formulate an SQL/FUNCTION that retu开发者_运维问答rns the correct entities from the correct tables. Something like:

SELECT * FROM resolveId(3)

I would expect to get the entity with the id "5" from the table "other". Is this possible? I would guess I can do it with a stored procedure (CREATE FUNCTION). The function would have to inspect the "ref_table" and return the name of the table to use in the SQL statement ... but how exactly?

If you want to use the resuling entities in select statements or joins, you should use CREATE FUNCTION with RETURNS TABLE ( .. )

There is a limitation in HSQLDB routines which disallows dynamically creating SQL. Therefore the body of the CREATE FUNCTION may include a CASE or IF ELSE block that switches to a pre-defined SELECT statement based on the input value (1, 2, 3, ..).

The details of CREATE FUNCTION are documented here: http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html#N12CC4 There is one example for an SQL function with RETURNS TABLE.