set default schema inside postgres function

[kodi...@gmail.com]

Let's say I have following stored procedure:

create or replace function foo()
returns table(id integer, name text)
language plpgsql
as $func$
begin
    select id, name
    from white_house;
end
$func$

The foo function is located in my_schema schema (which is different that the default one - public). The same applies to the white_house table. By adding some info (inputSchema = 'my_schema') to gradle config I generated classes from this schema and I can call the schema with:

Routines.foo()

but I'm getting error that, the table does not exists.

org.postgresql.util.PSQLException: ERROR: relation "white_house" does not exist
  Where: PL/pgSQL function my_schema.foo() line 4 at OPEN

I tried to add:

configuration.set(new Settings().withRenderSchema(true));
but it also failed.

Is there any possibility to add the "my_schema" schema prefixes at runtime to the tables inside this foo() function without changing the function? I'm using jOOQ 3.8.5.

[Lukas Eder]

Hello,

This isn't strictly related to jOOQ - the same issue would happen when you ran the function call from JDBC or from any other API. With jOOQ, you could implement an ExecuteListener that runs an additional statement prior to the actual statement, putting your "my_schema" on PostgreSQL's search path.

Or, you just supply jOOQ with a JDBC connection that has been initialised this way.

Details here:

https://www.postgresql.org/docs/current/static/sql-set.html

SET search_path TO my_schema, public;

I hope this helps,

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

[kodi...@gmail.com]

Yes, previously I used 

set search_path = my_schema;

Creating class that implements DefaultExecuteListener with overridden method executeStart like

@Override
public void executeStart(ExecuteContext ctx) {
 try {
 ctx.connection().prepareStatement("set search path = my_schema;").executeUpdate();
 super.executeStart(ctx);
 } catch (SQLException e) {
 throw new RuntimeException(e);
 }
}

does the work perfectly.

Thank you Lucas :)

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Rob Sargent]

I have a suite of identical schemas, roles/logins specific to one schema(default search_path=<role's schema>, public).  Functions are public and name tables without schema, relying on caller's search_path.

--