Jooq: Add support for PostgreSQL 11 procedures

[bhemar]

Use case:

I have simple stored procedure (new feature in Postgres 11):

CREATE OR REPLACE PROCEDURE test_procedure() LANGUAGE plpgsql AS $$ BEGIN SELECT now(); END $$;

When I run Java code generator, it will not generate Routine for that stored procedure.

Possible solution you'd like to see:

I would like to be able to call stored procedure from Java code:

Routines.testProcedure(dsl.configuration());

Possible workarounds:

No workarounds

Versions:

• jOOQ: 3.11.9
• Java: 8
• Database (include vendor): Postgres 11.2
• OS: Linux(CentOS 6)
• JDBC Driver (include name if inofficial driver): postgresql/postgresql 42.2.5

[lukaseder]

Thanks for the reminder. I had thought this was already covered by another issue but apparently it isn't. ~This should definitely go into jOOQ 3.12~ (I should never make such promises)

[lukaseder]

Looking into this now. Funny that procedures wouldn't be allowed to have OUT parameters...

[lukaseder]

With the various options available in PostgreSQL (including OUT parameters on functions!) standardised integration tests aren't too simple.

[mkurz]

OUT parameters in procedures make it in PostgreSQL 14: https://www.depesz.com/2020/10/05/waiting-for-postgresql-14-support-for-out-parameters-in-procedures/

So maybe just wait for PG14 before implementing procedures in jOOQ?

[lukaseder]

Thanks, @mkurz. That's an option, of course. I will still play with what's available. People might have used INOUT parameters for OUT parameters (similar to SQL Server)...