pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGeneric Join on Arrays
3+ messages / 2 participants
[nested] [flat]
* Generic Join on Arrays
@ 2005-05-30 13:42 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 1 reply; 3+ messages in thread
From: KÖPFERL Robert @ 2005-05-30 13:42 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm currently on retrieving meta infromation about db-schemas.
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"
Is there a way to make some kind of indetermine number of JOINs on the
single array items?
I want to retrieve a list of argument type names, not plain numbers.
I want to avoid doing several resolutions oid->name
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Generic Join on Arrays
@ 2005-05-30 14:08 Michael Fuhr <mike@fuhr.org>
parent: KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 0 replies; 3+ messages in thread
From: Michael Fuhr @ 2005-05-30 14:08 UTC (permalink / raw)
To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Generic Join on Arrays
@ 2005-05-30 14:14 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 0 replies; 3+ messages in thread
From: KÖPFERL Robert @ 2005-05-30 14:14 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Thanks.
Since I was just interested in this special case (while the general wasn't
interesting, either) this helped me. Also psql -E is a nice new feature to
me.
|-----Original Message-----
|From: Michael Fuhr [mailto:mike@fuhr.org]
|Sent: Montag, 30. Mai 2005 16:09
|To: KÖPFERL Robert
|Cc: pgsql-sql@postgresql.org
|Subject: Re: [SQL] Generic Join on Arrays
|
|
|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/
|
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-05-30 14:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-30 13:42 Generic Join on Arrays KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-05-30 14:08 ` Michael Fuhr <mike@fuhr.org>
2005-05-30 14:14 Re: Generic Join on Arrays KÖPFERL Robert <robert.koepferl@sonorys.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox