Hi Ben, 2015-05-04 17:02 GMT+02:00 Ben Hood <[email protected]>:
> On Mon, May 4, 2015 at 3:44 PM, Lukas Eder <[email protected]> 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. -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
