Re: unexpected unnest behaviour

James Harper <james(dot)harper(at)bendigoit(dot)com(dot)au> writes:
> I have a query that blows the arguments in pg_proc out:
> SELECT pg_proc.oid, UNNEST(pg_proc.proargnames), UNNEST(pg_proc.proargtypes), UNNEST(pg_proc.proargmodes) FROM pg_proc

> And that works great if all arguments are input arguments, but if two are output arguments, then something unexpected happens.

One reason why that doesn't work is that proargtypes isn't necessarily of
the same length as the other two arrays, since it only counts input
arguments. Another is that multiple set-returning functions in the
targetlist don't work the way you want: you'll end up with a number of
rows equal to the least common multiple of their period lengths.

After some fooling about I was able to get sane-looking results using
multiple-argument UNNEST:

SELECT p.proname, pa, pt, pm
FROM pg_proc p left join
lateral unnest(p.proargnames, coalesce(p.proallargtypes, p.proargtypes), p.proargmodes) as u(pa,pt,pm) on true

but I'm not sure there's any convenient way to do this without that.

regards, tom lane