Re: Help working with arrays / vectors

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Brett Krebs <bkrebs(at)RINECO(dot)COM>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Help working with arrays / vectors
Date: 2002-06-25 13:47:25
Message-ID: 7121.1025012845@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Brett Krebs <bkrebs(at)RINECO(dot)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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Sol 2002-06-25 15:01:10 [OT] OOo postgres connection
Previous Message Sharda Srinivasan 2002-06-25 11:54:23 SQL operators