Date: Mon, 30 May 2005 08:08:57 -0600
From: Michael Fuhr <mike@fuhr.org>
To: =?iso-8859-1?Q?K=D6PFERL?= Robert <robert.koepferl@sonorys.at>
Cc: pgsql-sql@postgresql.org
Subject: Re: Generic Join on Arrays
Message-ID: <20050530140857.GA78280@winnie.fuhr.org>
References: <ED4E30DD9C43D5118DFB00508BBBA76EB16721@neptun.sonorys.at>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <ED4E30DD9C43D5118DFB00508BBBA76EB16721@neptun.sonorys.at>

On Mon, May 30, 2005 at 03:42:49PM +0200, KÖPFERL Robert wrote:
> 
> As I found out, pg_proc relation provides me with data about defined stored
> procedures. Togehter with other relations as pg_type I can retrieve readable
> information, like:
> select proname, pd.description FROM  pg_proc pp left outer join
> pg_description   pd on  pp.oid=pd.objoid
> 
> BUT, how about arrays of type "oidvector" (int4-array) containing foreign
> keys (to be joined) as pg_proc's column  "proargtypes" 

Have you used ECHO_HIDDEN (or psql -E) to look at the queries that \df
generates?  Would oidvectortypes() be useful?

SELECT proname, oidvectortypes(proargtypes)
FROM pg_proc
WHERE proname = 'substr';
 proname |     oidvectortypes      
---------+-------------------------
 substr  | bytea, integer
 substr  | text, integer
 substr  | bytea, integer, integer
 substr  | text, integer, integer
(4 rows)

Another possibility might be to cast the function's oid to regprocedure:

SELECT oid::regprocedure
FROM pg_proc
WHERE proname = 'substr';
              oid              
-------------------------------
 substr(bytea,integer)
 substr(text,integer)
 substr(bytea,integer,integer)
 substr(text,integer,integer)
(4 rows)

Is either of those what you're looking for?  They don't address the
problem in the general case, but they might serve in this particular
case.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/