Thread: unexpected unnest behaviour

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.

So for a proc declared as:

CREATE FUNCTION "master.dbo".xp_instance_regread(OUT tsql_int, "@root" tsql_sysname, "@key" tsql_sysname, "@name"
tsql_sysname,OUT "@value" tsql_sysname) 

My query above returns 15 rows instead of the expected 5. When I investigate I find that proargtypes only contains the
typesof the input arguments and that I should use proallargtypes, and when I do I get the expected results, but the
outputwhen unnest is used on arrays of different sizes was something I didn't expect, and something I can't imagine
anyonemight want... 

Mostly out of curiousity, why is this so?

Thanks

James

James Harper <james.harper@bendigoit.com.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