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
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 |