Обсуждение: pg_proc and pg_type

Hello,
  I have column proargtypes (oidvector) in pg_proc and oid in pg_type
Can I join tables pg_proc and pg_type and get row for every argument of
functions

regards
haris peco

snpe wrote:
> Hello,
>   I have column proargtypes (oidvector) in pg_proc and oid in pg_type
> Can I join tables pg_proc and pg_type and get row for every argument of
> functions

oidvector is a fixed-width array.  However, you can access individual
elements, at least in the 7.3 beta I use:

    select proargtypes[0] from pg_proc;
     proargtypes
    -------------
              23
              23

In <7.3 the length is fixed at 16, while in >=7.3 the length is 32.

You can then join that to pg_type:

    test=> select typname from pg_proc, pg_type
    test-> where proargtypes[0] = pg_type.oid;
       typname
    -------------
     int4
     int4
     int4
     int4

Hope that helps.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

I have join pg_type and pg_proc and get view arguments and return value
(btw for getProcedureColumns in JDBC)
This is not enough.I have join all rows from pg_type, which have any from
pg_proc.oidvector (I must know length from oidvector array)
That is fine operator which is true if oid in any oid from oidvector, else
false and that be simple pg_proc.oidvector=pg_proc.oid
I don't know how any function or operator add in core postgresql (procedure
for this), but I need this operator and function for size,scale and precision
for any type (special for varchar,char,numeric etc. - this is like
format_type function, but special function for size, scale and precision).
regards
Haris Peco
On Tuesday 17 September 2002 03:56 am, Bruce Momjian wrote:
> snpe wrote:
> > Hello,
> >   I have column proargtypes (oidvector) in pg_proc and oid in pg_type
> > Can I join tables pg_proc and pg_type and get row for every argument of
> > functions
>
> oidvector is a fixed-width array.  However, you can access individual
> elements, at least in the 7.3 beta I use:
>
>     select proargtypes[0] from pg_proc;
>      proargtypes
>     -------------
>               23
>               23
>
> In <7.3 the length is fixed at 16, while in >=7.3 the length is 32.
>
> You can then join that to pg_type:
>
>     test=> select typname from pg_proc, pg_type
>     test-> where proargtypes[0] = pg_type.oid;
>        typname
>     -------------
>      int4
>      int4
>      int4
>      int4
>
> Hope that helps.