Postgres SETOF routines

[Ben Hood]

Hi Lukas,

Using 3.6.1 I'm having an issue generating a routine object for a
RETURNS SETOF VOID function:

CREATE OR REPLACE FUNCTION f (arg BIGINT) RETURNS SETOF VOID AS $$

It looks like the code generator doesn't seem to find the relevant
object in the schema metadata.

However, if I change the header to return VOID instead of SETOF VOID,
the routine object will get generated.

Looking back over some tickets it seems that SETOF has been support since 3.6.0:

https://github.com/jOOQ/jOOQ/issues/3376

Possibly I'm misunderstanding how the process works - is this style of
return type supported by JOOQ?

Cheers,

Ben

[Lukas Eder]

Hmm, what is the idea of a SETOF VOID type? Can you show an example?

It's quite possible that this generates a signature that is currently not captured by jOOQ-meta's queries against the dictionary views. Currently, we're running the following query in org.jooq.util.postgres.PostgresDatabase:

                .select()

                .from(

                     select(

                        TABLES.TABLE_SCHEMA,

                        TABLES.TABLE_NAME,

                        TABLES.TABLE_NAME.as("specific_name"),

                        inline(false).as("table_valued_function"),

                        PG_DESCRIPTION.DESCRIPTION)

                    .from(TABLES)

                    .join(PG_NAMESPACE)

                        .on(TABLES.TABLE_SCHEMA.eq(PG_NAMESPACE.NSPNAME))

                    .join(PG_CLASS)

                        .on(PG_CLASS.RELNAME.eq(TABLES.TABLE_NAME))

                        .and(PG_CLASS.RELNAMESPACE.eq(oid(PG_NAMESPACE)))

                    .leftOuterJoin(PG_DESCRIPTION)

                        .on(PG_DESCRIPTION.OBJOID.eq(oid(PG_CLASS)))

                        .and(PG_DESCRIPTION.OBJSUBID.eq(0))

                    .where(TABLES.TABLE_SCHEMA.in(getInputSchemata()))

                // [#3375] [#3376] Include table-valued functions in the set of tables

                .unionAll(

                    select(

                        ROUTINES.ROUTINE_SCHEMA,

                        ROUTINES.ROUTINE_NAME,

                        ROUTINES.SPECIFIC_NAME,

                        inline(true).as("table_valued_function"),

                        inline(""))

                    .from(ROUTINES)

                    .join(PG_NAMESPACE).on(ROUTINES.SPECIFIC_SCHEMA.eq(PG_NAMESPACE.NSPNAME))

                    .join(PG_PROC).on(PG_PROC.PRONAMESPACE.eq(oid(PG_NAMESPACE)))

                                  .and(PG_PROC.PRONAME.concat("_").concat(oid(PG_PROC)).eq(ROUTINES.SPECIFIC_NAME))

                    .where(ROUTINES.ROUTINE_SCHEMA.in(getInputSchemata()))

                    .and(PG_PROC.PRORETSET))

                .asTable("tables"))

                .orderBy(1, 2)

                .fetch()) {

Perhaps, this specific case doesn't set the PG_PROC.PRORETSET flag...?

Best,

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

[Ben Hood]

Actually, on further inspection, I've created a dummy function
returning SETOF BIGINT to make sure that it is not just SETOF VOID
that is not working in my current setup. The SETOF BIGINT routine
object does not appear to get generated either. So I am assuming that
there is something with my environment - can you turn up the log
verbosity for the code generator somehow?

On Mon, May 4, 2015 at 2:45 PM, Lukas Eder <lukas...@gmail.com> wrote:
> Hmm, what is the idea of a SETOF VOID type? Can you show an example?

SETOF VOID is just a Postgres-ism to avoid having a function returning
a result row for a void return type. If the function returns VOID, the
invocation will return a row to contain the void result.

> Perhaps, this specific case doesn't set the PG_PROC.PRORETSET flag...?

What is the PG_PROC.PRORETSET flag used for?

[Ben Hood]

On Mon, May 4, 2015 at 3:12 PM, Ben Hood <0x6e...@gmail.com> wrote:
> What is the PG_PROC.PRORETSET flag used for?

In my schema, the following metadata exists:

SELECT p.proname, oidvectortypes(p.proargtypes), p.proretset, p.prorettype
FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)
WHERE ns.nspname = 'public';

proname oidvectortypes proretset prorettype
------------- ------------------------- ---------
----------
set_caller_id bigint, character varying true 20

Not sure how much this helps in this case.

[Lukas Eder]

Actually, on further inspection, I've created a dummy function
returning SETOF BIGINT to make sure that it is not just SETOF VOID
that is not working in my current setup. The SETOF BIGINT routine
object does not appear to get generated either. So I am assuming that
there is something with my environment - can you turn up the log
verbosity for the code generator somehow?

I see, our integration tests currently only cover SETOF [ table type ]. I'll have to investigate to see whether SETOF BIGINT is really supported already. How is that different from returning a BIGINT[] type?

(PostgreSQL never stops surprising me...)

 

On Mon, May 4, 2015 at 2:45 PM, Lukas Eder <lukas...@gmail.com> wrote:
> Hmm, what is the idea of a SETOF VOID type? Can you show an example?
SETOF VOID is just a Postgres-ism to avoid having a function returning
a result row for a void return type. If the function returns VOID, the
invocation will return a row to contain the void result.

Aha, so SETOF VOID will return an empty set, whereas VOID will return a single record? I see, that's clever, actually.

 

> Perhaps, this specific case doesn't set the PG_PROC.PRORETSET flag...?

What is the PG_PROC.PRORETSET flag used for?

It's the only way I've found to identify (explicit) table-valued functions in the dictionary views. The information_schema doesn't seem to reveal that. I suspect the name is short for something like PROcedure RETurning a SET.

I'm aware that in PostgreSQL, everything is really a table-valued function. But some are more table-valued than others, at least as far as their pgplsql signature is concerned.

I'm curious about the whole function, in fact. I'd like to understand the use-case of a SETOF BIGINT return type. 

[Ben Hood]

I've just executed this query directly against the DB without JOOQ,
and it returns the SETOF BIGINT function, albeit classified as a table
valued function (for which it generated a TableImpl class, as opposed
to an AbstractRoutine class):

SELECT
"tables"."table_schema",
"tables"."table_name",
"tables"."specific_name",
"tables"."table_valued_function",
"tables"."description"
FROM
(
(
SELECT
"information_schema"."tables"."table_schema",
"information_schema"."tables"."table_name",
"information_schema"."tables"."table_name" AS "specific_name",
false AS
"table_valued_function",
"pg_catalog"."pg_description"."description"
FROM
"information_schema"."tables"
JOIN
"pg_catalog"."pg_namespace"
ON
"information_schema"."tables"."table_schema" =
"pg_catalog"."pg_namespace"."nspname"
JOIN
"pg_catalog"."pg_class"
ON
(
"pg_catalog"."pg_class"."relname" =
"information_schema"."tables"."table_name"
AND "pg_catalog"."pg_class"."relnamespace" =
"pg_catalog"."pg_namespace".oid)
LEFT OUTER JOIN
"pg_catalog"."pg_description"
ON
(
"pg_catalog"."pg_description"."objoid" =
"pg_catalog"."pg_class".oid
AND "pg_catalog"."pg_description"."objsubid" = 0)
WHERE
"information_schema"."tables"."table_schema" IN ('public'))
UNION ALL
(
SELECT
"information_schema"."routines"."routine_schema",
"information_schema"."routines"."routine_name",
"information_schema"."routines"."specific_name",
true AS "table_valued_function",
''
FROM
"information_schema"."routines"
JOIN
"pg_catalog"."pg_namespace"
ON
"information_schema"."routines"."specific_schema" =
"pg_catalog"."pg_namespace"."nspname"
JOIN
"pg_catalog"."pg_proc"
ON
(
"pg_catalog"."pg_proc"."pronamespace" =
"pg_catalog"."pg_namespace".oid
AND ((
"pg_catalog"."pg_proc"."proname" || '_') ||
CAST("pg_catalog"."pg_proc".oid
AS VARCHAR)) =
"information_schema"."routines"."specific_name")
WHERE
(
"information_schema"."routines"."routine_schema" IN ('public')
AND "pg_catalog"."pg_proc"."proretset"))) AS "tables"
ORDER BY
1 ASC,
2 ASC;

[Ben Hood]

On Mon, May 4, 2015 at 3:44 PM, Lukas Eder <lukas...@gmail.com> wrote:
> I see, our integration tests currently only cover SETOF [ table type ]. I'll
> have to investigate to see whether SETOF BIGINT is really supported already.

Ah OK, good to know.

> How is that different from returning a BIGINT[] type?

Um ..... that's an interview question - I guess I didn't get the job?

I *think* an array in Postgres will bound as a type to a single row,
from which it can be casted/desugared/etc. The SETOF splits the
results into different rows.

But there's probably somebody out the who actually knows what they are
talking about.

> Aha, so SETOF VOID will return an empty set, whereas VOID will return a
> single record? I see, that's clever, actually.

Yes, it makes consumer code a little bit more compact.

> It's the only way I've found to identify (explicit) table-valued functions
> in the dictionary views. The information_schema doesn't seem to reveal that.
> I suspect the name is short for something like PROcedure RETurning a SET.
>
> I'm aware that in PostgreSQL, everything is really a table-valued function.
> But some are more table-valued than others, at least as far as their pgplsql
> signature is concerned.

OK, good to know - I guess that ties in with this metadata query:

SELECT p.proname, oidvectortypes(p.proargtypes), p.proretset, p.prorettype
FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)
WHERE ns.nspname = 'public';

> I'm curious about the whole function, in fact. I'd like to understand the
> use-case of a SETOF BIGINT return type.

I have some procs that allocate resources to the caller. Each resource
is identified by a BIGINT id. In practice, n resources will be
allocated during the invocation of this proc, where n can be 0. Having
SETOF BIGINT allows the caller to handle the case when n = 0 a little
bit neater that when a row is returned. But it is a not a game
changing feature by any stretch of the imagination.

BTW and FYI I'm using procs mostly when I can't get the JOOQ to
support the syntax I need, for example CTEs with DML.

[Lukas Eder]

Hi Ben,

2015-05-04 17:02 GMT+02:00 Ben Hood <0x6e...@gmail.com>:

On Mon, May 4, 2015 at 3:44 PM, Lukas Eder <lukas...@gmail.com> wrote:
> I see, our integration tests currently only cover SETOF [ table type ]. I'll
> have to investigate to see whether SETOF BIGINT is really supported already.

Ah OK, good to know.

It isn't supported. The code generator doesn't find any columns for the table-valued function. That's a bug which should be fixed immediately:

https://github.com/jOOQ/jOOQ/issues/4269

 

> How is that different from returning a BIGINT[] type?

Um ..... that's an interview question - I guess I didn't get the job?

I *think* an array in Postgres will bound as a type to a single row,
from which it can be casted/desugared/etc. The SETOF splits the
results into different rows.

But there's probably somebody out the who actually knows what they are
talking about.

Fair enough :-)

Yeah, I guess the SETOF BIGINT vs BIGINT[] types really help distinguish the intent of how a resulting collection should be used by the consumer. OTOH, I may just have never really understood why the SQL standard (and PostgreSQL) included arrays in the first place. Nested tables seem much more idiomatic for (not-so-) every day SQL.

> I'm curious about the whole function, in fact. I'd like to understand the
> use-case of a SETOF BIGINT return type.

I have some procs that allocate resources to the caller. Each resource
is identified by a BIGINT id. In practice, n resources will be
allocated during the invocation of this proc, where n can be 0. Having
SETOF BIGINT allows the caller to handle the case when n = 0 a little
bit neater that when a row is returned. But it is a not a game
changing feature by any stretch of the imagination.

BTW and FYI I'm using procs mostly when I can't get the JOOQ to
support the syntax I need, for example CTEs with DML.

Yeah, that makes sense. Well, some things are also better handled by stored procedures, e.g. when several server round-trips add too much latency in a batch job, for instance. 

[Lukas Eder]

Ben,

2015-05-05 7:59 GMT+02:00 Lukas Eder <lukas...@gmail.com>:

> I see, our integration tests currently only cover SETOF [ table type ]. I'll
> have to investigate to see whether SETOF BIGINT is really supported already.

Ah OK, good to know.

It isn't supported. The code generator doesn't find any columns for the table-valued function. That's a bug which should be fixed immediately:

https://github.com/jOOQ/jOOQ/issues/4269

This change seems to fix things in our integration tests:

https://github.com/jOOQ/jOOQ/commit/4ef2b92d3c51a341c22c2b024287b363a196e4fc

I've added the following two test functions:

CREATE OR REPLACE FUNCTION f_search_book_ids(p_title character varying, p_limit bigint, p_offset bigint)

  RETURNS SETOF INT AS

$BODY$

SELECT id FROM t_book

WHERE (LOWER(title) LIKE LOWER('%' || $1 || '%'))

LIMIT $2 OFFSET $3;

$BODY$

  LANGUAGE sql VOLATILE

  COST 100

  ROWS 1000;

/

CREATE OR REPLACE FUNCTION f_search_book_titles(p_title character varying, p_limit bigint, p_offset bigint)

  RETURNS SETOF VARCHAR(50) AS

$BODY$

SELECT title FROM t_book

WHERE (LOWER(title) LIKE LOWER('%' || $1 || '%'))

LIMIT $2 OFFSET $3;

$BODY$

  LANGUAGE sql VOLATILE

  COST 100

  ROWS 1000;

/

 

The fix will be merged to jOOQ 3.6.2.

If you have time to verify the fix already before the release, that would be greatly appreciated of course. The change didn't incur any regressions in our integration tests, but there may still be edge-cases or function return types that we haven't foreseen yet...

Cheers,

Lukas

[Ben Hood]

On Tue, May 5, 2015 at 6:59 AM, Lukas Eder <lukas...@gmail.com> wrote:
> Yeah, I guess the SETOF BIGINT vs BIGINT[] types really help distinguish the
> intent of how a resulting collection should be used by the consumer. OTOH, I
> may just have never really understood why the SQL standard (and PostgreSQL)
> included arrays in the first place. Nested tables seem much more idiomatic
> for (not-so-) every day SQL.

Using sets with a technology that borrows from set theory kind of
makes sense, doesn't it :-)

That said, I'm experienced enough not to pretend that I actually
understand the type system.

For example, I have a function that takes

VARCHAR(20)[]

as an input parameter.

I wonder if I could use SETOF VARCHAR(20) instead?

My naive guess would be that this would break a bunch of proc calling
conventions from various different platforms and languages, but this
is just pure speculation.

[Ben Hood]

On Tue, May 5, 2015 at 7:40 AM, Lukas Eder <lukas...@gmail.com> wrote:
> If you have time to verify the fix already before the release, that would be
> greatly appreciated of course. The change didn't incur any regressions in
> our integration tests, but there may still be edge-cases or function return
> types that we haven't foreseen yet...

What's the easiest way to grab this version and reference it from my
project? Do I clone the JOOQ upstream, check out that revision and
install it into my local .m2? Or are there some Maven nightly's
kicking around somewhere?

[Lukas Eder]

Using sets with a technology that borrows from set theory kind of
makes sense, doesn't it :-)

One might think so ;-)

Then again, they're bags, not really sets...

 

That said, I'm experienced enough not to pretend that I actually
understand the type system.

Heh. Trust me. More experience won't necessary lead to more understanding in the case of SQL and its implementations. But it's eventually easier to figure out workarounds ;-)

For example, I have a function that takes
VARCHAR(20)[]
as an input parameter.
I wonder if I could use SETOF VARCHAR(20) instead?

You can't have SETOF arguments. In fact, Oracle seems to be the only database that supports table arguments to functions, although if you really want to nest them, the SQL gets really hairy.

It would be extremely powerful for functions to act as set pipelines, but I guess we're not quite there yet.

This should be sufficient:

$ git clone https://github.com/jOOQ/jOOQ.git

$ mvn install

Possibly with -Dmaven.test.skip=true in case the Open Source Edition's tests are currently failing...

[Ben Hood]

On Tue, May 5, 2015 at 4:59 PM, Lukas Eder <lukas...@gmail.com> wrote:
>
> This should be sufficient:
>
> $ git clone https://github.com/jOOQ/jOOQ.git
> $ mvn install
>
> Possibly with -Dmaven.test.skip=true in case the Open Source Edition's tests
> are currently failing...

The mvn install from 337cd45c installed fine for me with the tests.

However, I created this two cut down functions:

CREATE OR REPLACE FUNCTION do_nothing (arg BIGINT) RETURNS SETOF VOID AS $$
BEGIN
RETURN;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION do_something (arg BIGINT) RETURNS SETOF BIGINT AS $$
BEGIN
RETURN QUERY SELECT COUNT(*) FROM information_schema.tables;
END;
$$
LANGUAGE plpgsql;

And it doesn't look like the code generator is picking this up:

[INFO] Generating record : DoNothingRecord.java
[INFO] Generating record : DoSomethingRecord.java

--snip--

[INFO] Routines fetched : 0 (0 included, 0 excluded)

Moreover - I also have my proc called set_caller_id in this schema,
which works fine with 3.6.1:

/**
* This class is generated by jOOQ
*/
package se.qall.middleton.jooq;


import javax.annotation.Generated;

import se.qall.middleton.jooq.tables.DoNothing;
import se.qall.middleton.jooq.tables.DoSomething;
import se.qall.middleton.jooq.tables.SetCallerId;


/**
* Convenience access to all stored procedures and functions in public
*/
@Generated(
value = {
"http://www.jooq.org",
"jOOQ version:3.7.0"
},
comments = "This class is generated by jOOQ"
)
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Routines {

/**
* Get <code>public.do_nothing</code> as a field
*/
public static DoNothing doNothing() {
return DoNothing.DO_NOTHING.call();
}

/**
* Get <code>public.do_something</code> as a field
*/
public static DoSomething doSomething() {
return DoSomething.DO_SOMETHING.call();
}

/**
* Get <code>public.set_caller_id</code> as a field
*/
public static SetCallerId setCallerId() {
return SetCallerId.SET_CALLER_ID.call();
}
}

Here is the error:


[WARNING] SQL exception : Exception while executing meta
query: ERROR: column parameters.parameter_default does not exist
Position: 427

Please report this bug here: https://github.com/jOOQ/jOOQ/issues/new

select
"information_schema"."parameters"."parameter_name",
"information_schema"."parameters"."data_type",
"information_schema"."parameters"."character_maximum_length",
"information_schema"."parameters"."numeric_precision",
"information_schema"."parameters"."numeric_scale",
"information_schema"."parameters"."udt_name",
"information_schema"."parameters"."ordinal_position",
"information_schema"."parameters"."parameter_mode",
"information_schema"."parameters"."parameter_default"
from "information_schema"."parameters"
where (
"information_schema"."parameters"."specific_schema" = 'public'
and "information_schema"."parameters"."specific_name" = 'do_nothing_1321414'
)
order by "information_schema"."parameters"."ordinal_position" asc
[ERROR] Error while generating table public.do_nothing
org.jooq.exception.DataAccessException: SQL [select
"information_schema"."parameters"."parameter_name",
"information_schema"."parameters"."data_type",
"information_schema"."parameters"."character_maximum_length",
"information_schema"."parameters"."numeric_precision",
"information_schema"."parameters"."numeric_scale",
"information_schema"."parameters"."udt_name",
"information_schema"."parameters"."ordinal_position",
"information_schema"."parameters"."parameter_mode",
"information_schema"."parameters"."parameter_default" from
"information_schema"."parameters" where
("information_schema"."parameters"."specific_schema" = ? and
"information_schema"."parameters"."specific_name" = ?) order by
"information_schema"."parameters"."ordinal_position" asc]; ERROR:
column parameters.parameter_default does not exist
Position: 427
at org.jooq.impl.Utils.translate(Utils.java:1645)
at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:661)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:356)
at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:290)
at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2316)
at org.jooq.util.postgres.PostgresRoutineDefinition.init0(PostgresRoutineDefinition.java:119)
at org.jooq.util.AbstractRoutineDefinition.init(AbstractRoutineDefinition.java:105)
at org.jooq.util.AbstractRoutineDefinition.getInParameters(AbstractRoutineDefinition.java:122)
at org.jooq.util.postgres.PostgresTableValuedFunction.getParameters0(PostgresTableValuedFunction.java:196)
at org.jooq.util.AbstractTableDefinition.getParameters(AbstractTableDefinition.java:159)
at org.jooq.util.JavaGenerator.generateTable(JavaGenerator.java:2827)
at org.jooq.util.JavaGenerator.generateTable(JavaGenerator.java:2697)
at org.jooq.util.JavaGenerator.generateTables(JavaGenerator.java:2684)
at org.jooq.util.JavaGenerator.generate(JavaGenerator.java:305)
at org.jooq.util.JavaGenerator.generate(JavaGenerator.java:265)
at org.jooq.util.GenerationTool.run(GenerationTool.java:429)
at org.jooq.util.GenerationTool.generate(GenerationTool.java:176)
at org.jooq.util.maven.Plugin.execute(Plugin.java:120)
at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:133)
at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:208)
at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153)
at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145)
at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:108)
at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:76)
at org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder.build(SingleThreadedBuilder.java:51)
at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:116)
at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:361)
at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:155)
at org.apache.maven.cli.MavenCli.execute(MavenCli.java:584)
at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:213)
at org.apache.maven.cli.MavenCli.main(MavenCli.java:157)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:289)
at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:229)
at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415)
at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356)
Caused by: org.postgresql.util.PSQLException: ERROR: column
parameters.parameter_default does not exist
Position: 427
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:419)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:412)
at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:194)
at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:247)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:342)
... 36 more
[INFO] Generating table : DoSomething.java
[input=do_something, output=do_something, pk=N/A]
[WARNING] SQL exception : Exception while executing meta
query: ERROR: column parameters.parameter_default does not exist
Position: 427

Please report this bug here: https://github.com/jOOQ/jOOQ/issues/new

select
"information_schema"."parameters"."parameter_name",
"information_schema"."parameters"."data_type",
"information_schema"."parameters"."character_maximum_length",
"information_schema"."parameters"."numeric_precision",
"information_schema"."parameters"."numeric_scale",
"information_schema"."parameters"."udt_name",
"information_schema"."parameters"."ordinal_position",
"information_schema"."parameters"."parameter_mode",
"information_schema"."parameters"."parameter_default"
from "information_schema"."parameters"
where (
"information_schema"."parameters"."specific_schema" = 'public'
and "information_schema"."parameters"."specific_name" = 'do_something_1321415'
)
order by "information_schema"."parameters"."ordinal_position" asc
[ERROR] Error while generating table public.do_something
org.jooq.exception.DataAccessException: SQL [select
"information_schema"."parameters"."parameter_name",
"information_schema"."parameters"."data_type",
"information_schema"."parameters"."character_maximum_length",
"information_schema"."parameters"."numeric_precision",
"information_schema"."parameters"."numeric_scale",
"information_schema"."parameters"."udt_name",
"information_schema"."parameters"."ordinal_position",
"information_schema"."parameters"."parameter_mode",
"information_schema"."parameters"."parameter_default" from
"information_schema"."parameters" where
("information_schema"."parameters"."specific_schema" = ? and
"information_schema"."parameters"."specific_name" = ?) order by
"information_schema"."parameters"."ordinal_position" asc]; ERROR:
column parameters.parameter_default does not exist
Position: 427
at org.jooq.impl.Utils.translate(Utils.java:1645)
at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:661)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:356)
at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:290)
at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2316)
at org.jooq.util.postgres.PostgresRoutineDefinition.init0(PostgresRoutineDefinition.java:119)
at org.jooq.util.AbstractRoutineDefinition.init(AbstractRoutineDefinition.java:105)
at org.jooq.util.AbstractRoutineDefinition.getInParameters(AbstractRoutineDefinition.java:122)
at org.jooq.util.postgres.PostgresTableValuedFunction.getParameters0(PostgresTableValuedFunction.java:196)
at org.jooq.util.AbstractTableDefinition.getParameters(AbstractTableDefinition.java:159)
at org.jooq.util.JavaGenerator.generateTable(JavaGenerator.java:2827)
at org.jooq.util.JavaGenerator.generateTable(JavaGenerator.java:2697)
at org.jooq.util.JavaGenerator.generateTables(JavaGenerator.java:2684)
at org.jooq.util.JavaGenerator.generate(JavaGenerator.java:305)
at org.jooq.util.JavaGenerator.generate(JavaGenerator.java:265)
at org.jooq.util.GenerationTool.run(GenerationTool.java:429)
at org.jooq.util.GenerationTool.generate(GenerationTool.java:176)
at org.jooq.util.maven.Plugin.execute(Plugin.java:120)
at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:133)
at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:208)
at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153)
at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145)
at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:108)
at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:76)
at org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder.build(SingleThreadedBuilder.java:51)
at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:116)
at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:361)
at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:155)
at org.apache.maven.cli.MavenCli.execute(MavenCli.java:584)
at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:213)
at org.apache.maven.cli.MavenCli.main(MavenCli.java:157)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:289)
at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:229)
at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415)
at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356)
Caused by: org.postgresql.util.PSQLException: ERROR: column
parameters.parameter_default does not exist
Position: 427
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:419)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:412)
at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:194)
at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:247)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:342)
... 36 more

[Lukas Eder]

Hi Ben,

Yes, these functions are being treated as tables / table-valued functions, not as routines. There might be a bit of confusion between the different notions here. Historically, jOOQ knows:

- Routines, which return a single value that can be used as a column reference in SQL, wherever columns can be used (i.e. an org.jooq.Field)

- Table-valued functions, which return a set of values or a set of records that can be used as a table reference in SQL, wherever tables can be used (i.e. an org.jooq.Field)

In PostgreSQL, tables and columns are more or less the same, but the jOOQ API still distinguishes these things. Perhaps that's the reason for the confusion here...? Or am I missing something?

 

Moreover - I also have my proc called set_caller_id in this schema,
which works fine with 3.6.1:

[...]
Here is the error

Thanks for reporting this regression. It is because of:

https://github.com/jOOQ/jOOQ/issues/4254

I have fixed this now:

https://github.com/jOOQ/jOOQ/commit/98530ee8714ea447881c4440968b32c9d4177bd8 

[Ben Hood]

On Wed, May 6, 2015 at 1:44 PM, Lukas Eder <lukas...@gmail.com> wrote:
> Thanks for reporting this regression. It is because of:
> https://github.com/jOOQ/jOOQ/issues/4254
>
> I have fixed this now:
> https://github.com/jOOQ/jOOQ/commit/98530ee8714ea447881c4440968b32c9d4177bd8

I've just installed 98530ee and the error I reported with 337cd45 is
now gone, but unfortunately it is not picking up these routines:

[INFO] Generating record : DoNothingRecord.java
[INFO] Generating record : DoSomethingRecord.java

....

[Lukas Eder]

Hi Ben,

What exactly do you mean by "not picking them up"? As I've mentioned before, table-valued functions are considered to be tables (with parameters), not routines, by the code generator. This is why they're listed as:

[INFO] Generating routines and table-valued functions

Is there any reason why you'd need them to be "picked up" as routines, explicitly?

[Ben Hood]

On Wed, May 6, 2015 at 2:28 PM, Lukas Eder <lukas...@gmail.com> wrote:
> What exactly do you mean by "not picking them up"? As I've mentioned before,
> table-valued functions are considered to be tables (with parameters), not
> routines, by the code generator. This is why they're listed as:

Sorry, that was my misunderstanding - I had assumed that the changes
you made meant that these {SETOF VOID, SETOF BIGINT} returning
functions would result in a generated class that inherits from
AbstractRoutine.

They are however being correctly detected as table valued functions.

So just put that comment down to my ignorance of the process.

> Is there any reason why you'd need them to be "picked up" as routines,
> explicitly?

The reason for code to be generated for these functions at all is to
be able to call the function and the compile time check of the input
and output parameters. Whether this is achieved as a subclass of
AbstractRoutine or a subclass of TableImpl makes no difference.

As I said before, my question stemmed from my lack of understanding,
not from wanting to do something funky.

So I guess you can use the generated TableImpl artefacts to invoke the
function. That said, I can't seem to find the part of the manual that
relates to the calling convention for this. For example, this example
works and returns the correct result back:

Record result = ctx.select().
from(DO_SOMETHING.call(1L)).
fetchOne();

But I was also assuming I that the fetchOne() would be typed something like:

Long result = ctx.select(DO_SOMETHING.DO_SOMETHING_).
from(DO_SOMETHING).
fetchOne().
value1();

But the second variant returns an error:

Wrapped by: org.jooq.exception.DataAccessException: SQL [select
"do_something"."do_something" from "public"."do_something"()]; ERROR:
function public.do_something() does not exist
Hint: No function matches the given name and argument types. You
might need to add explicit type casts.
Position: 43

Am I doing it wrong?

[Lukas Eder]

2015-05-06 16:07 GMT+02:00 Ben Hood <0x6e...@gmail.com>:

On Wed, May 6, 2015 at 2:28 PM, Lukas Eder <lukas...@gmail.com> wrote:
> What exactly do you mean by "not picking them up"? As I've mentioned before,
> table-valued functions are considered to be tables (with parameters), not
> routines, by the code generator. This is why they're listed as:

Sorry, that was my misunderstanding - I had assumed that the changes
you made meant that these {SETOF VOID, SETOF BIGINT} returning
functions would result in a generated class that inherits from
AbstractRoutine.

They are however being correctly detected as table valued functions.

So just put that comment down to my ignorance of the process.

Well, the log is indeed a bit confusing. I have registered an issue to improve this:

https://github.com/jOOQ/jOOQ/issues/4276

 

> Is there any reason why you'd need them to be "picked up" as routines,
> explicitly?

The reason for code to be generated for these functions at all is to
be able to call the function and the compile time check of the input
and output parameters. Whether this is achieved as a subclass of
AbstractRoutine or a subclass of TableImpl makes no difference.

As I said before, my question stemmed from my lack of understanding,
not from wanting to do something funky.

So I guess you can use the generated TableImpl artefacts to invoke the
function. That said, I can't seem to find the part of the manual that
relates to the calling convention for this.

There's this page here:

http://www.jooq.org/doc/latest/manual/sql-building/table-expressions/table-valued-functions/

 

For example, this example
works and returns the correct result back:

Record result = ctx.select().
                               from(DO_SOMETHING.call(1L)).
                               fetchOne();

But I was also assuming I that the fetchOne() would be typed something like:

Long result = ctx.select(DO_SOMETHING.DO_SOMETHING_).
                           from(DO_SOMETHING).
                           fetchOne().
                           value1();

But the second variant returns an error:

Hmm, yes, I haven't thought of that yet. That's tricky. The static table reference DO_SOMETHING is needed to dereference result columns. At the same time, it unfortunately is of type Table<?>, which makes it a compatible argument to the from() clause, even if that doesn't make any sense.

Note also, there is a generated method Tables.DO_SOMETHING(Long), so you might also be able to call your function like this:

[Lukas Eder]

... sent too early

2015-05-06 16:15 GMT+02:00 Lukas Eder <lukas...@gmail.com>:

Hmm, yes, I haven't thought of that yet. That's tricky. The static table reference DO_SOMETHING is needed to dereference result columns. At the same time, it unfortunately is of type Table<?>, which makes it a compatible argument to the from() clause, even if that doesn't make any sense.

Note also, there is a generated method Tables.DO_SOMETHING(Long), so you might also be able to call your function like this:

Long result = ctx.select(DO_SOMETHING.DO_SOMETHING_).
                  from(DO_SOMETHING(1L)).
                  fetchOne().

                  value1(); 

We've had little feedback about usability of table-valued functions on this list so far. So any feedback for improvements is very welcome!