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. > 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. -- 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.
