Thread: Help working with arrays / vectors
Hello all, I am trying to write a function akin to M$'s (or Sybase's) sp_helptext, which basically returns the definition of a stored procedure (or function, in this case.) 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. Is there a way to fetch an oidvector type directly into an array and then manipulate the array? If not, is there a "best method" for fetching the oidvector into a text variable and parsing it to create an array?
Better yet, has anyone out there who knows what they're doing already written a similar function. I'm not too proud to borrow code.
Thanks in advance,
Brett
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