Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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
	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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group