using temporary tables in postgresql

[Juri Krainjukov]

I'm trying to create a temporary table and use it with JOOQ.

Here's the query I use directly on DB and it works.

CREATE TEMPORARY TABLE temp_table_1

ON COMMIT DROP

AS

SELECT a1 FROM table1;

select * from func_uses_temp_table();

I tried to use    

createTemporaryTable("t1").as(query).onCommitDrop().execute();

and then

fetch("select * from t1"); 

but it fails with message "jOOQ; bad SQL grammar [select * from t1]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "t1" does not exist Position: 15"

So what would be the correct way to use temporary tables in queries?

[Lukas Eder]

I suspect you should turn off auto-commit in your JDBC driver...?

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

[Juri Krainjukov]

Is it possible to do it through DSLContext?

[Juri Krainjukov]

The problem is I don't want to turn it off for all queries.

[Lukas Eder]

Well, you could use jOOQ's transaction API, which turns auto-commit off for the scope of the transaction. E.g.:

ctx.transaction(c -> {

    // Within this scope, c contains a ConnectionProvider with auto-commit turned off

    DSL.using(c).createTemporaryTable(...);

    DSL.using(c).fetch(...);

});

Of course, you can also just write a more sophisticated ConnectionProvider as documented here:

http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/connection-vs-datasource

Or, you use Spring or Java EE or some other transaction API which manages the auto-commit flag for you.

As you can see, you have tons of options :)

Cheers,

Lukas