Re: Help working with arrays / vectors

Brett Krebs <bkrebs@RINECO.COM> writes:
> Using pl/pgSQL I have written a function that
> returns the body of a given function by returning the corresponding prosrc
> attribute from pg_proc.  The difficulty comes in figuring out a way to also
> return the parameters required by the function.  I am aware that the
> pronargs attribute contains the number of arguments, and the proargtypes
> attribute contains an array (vector) of the oids of the argument types.
> That's where I get stuck, I haven't found any documentation detailing a
> method for manipulating vectors in pl/pgSQL.

It's pretty weak --- there's no way to assign to an individual array
element.  However, you surely could do it, with something along the line
of
    for i := 0 to pronargs-1 do
        arglist := arglist || format_type(proargtypes[i]);
(this is just pseudocode, I'm too lazy to look up how plpgsql spells
its for-loops).

But I'd recommend not bothering.  The oidvectortypes() function
will do exactly what you want.  Look at the SQL generated by psql for
its \df command:

$ psql -E regression
regression=# \df oidvector
********* QUERY **********
SELECT format_type(p.prorettype, NULL) as "Result data type",
  p.proname as "Name",
  oidvectortypes(p.proargtypes) as "Argument data types"
FROM pg_proc p
WHERE p.prorettype <> 0 AND (pronargs = 0 OR oidvectortypes(p.proargtypes) <> '') AND NOT p.proisagg
  AND p.proname ~ '^oidvector'
ORDER BY 2, 1, 3;
**************************

                    List of functions
 Result data type |      Name      | Argument data types
------------------+----------------+----------------------
 boolean          | oidvectoreq    | oidvector, oidvector
 boolean          | oidvectorge    | oidvector, oidvector
 boolean          | oidvectorgt    | oidvector, oidvector
 boolean          | oidvectorle    | oidvector, oidvector
 boolean          | oidvectorlt    | oidvector, oidvector
 boolean          | oidvectorne    | oidvector, oidvector
 text             | oidvectortypes | oidvector
(7 rows)


            regards, tom lane