unexpected unnest behaviour

From: James Harper <james(dot)harper(at)bendigoit(dot)com(dot)au>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: unexpected unnest behaviour
Date: 2014-04-01 10:46:59
Message-ID: F214C06498BC244DB3F9472E1C5368180F433363@BITCOM1.int.sbss.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 types of the input arguments and that I should use proallargtypes, and when I do I get the expected results, but the output when unnest is used on arrays of different sizes was something I didn't expect, and something I can't imagine anyone might want...

Mostly out of curiousity, why is this so?

Thanks

James

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stefan Keller 2014-04-01 10:59:49 Re: Postgres as In-Memory Database?
Previous Message shetty65 2014-04-01 07:13:22 Unattended Installation