Re: SQL to display user functions

Never mind... figured it out...

SELECT
  proname,
  pt.typname AS result,
  oidvectortypes(pc.proargtypes) AS arguments
FROM
  pg_proc pc, pg_user pu, pg_type pt
WHERE
  pc.proowner = pu.usesysid
  AND pc.prorettype = pt.oid
  AND pc.oid > '<max system oid>'::oid
UNION ALL
SELECT
  proname,
  pt.typname AS result,
  oidvectortypes(pc.proargtypes) AS arguments
FROM
  pg_proc pc, pg_user pu, pg_type pt
WHERE
  pc.proowner = pu.usesysid
  AND pc.prorettype = 0
  AND pc.oid > '<max system oid>'::oid

I just needed the UNION ALL and a second query looking for the prorettype =
0.

Who wants to patch psql?

-Dan

----- Original Message -----
From: "Dan Wilson" <phpPgAdmin@acucore.com>
To: "pgsql general" <pgsql-general@postgresql.org>
Sent: Wednesday, December 13, 2000 6:56 PM
Subject: [GENERAL] SQL to display user functions


> I'm the author of phpPgAdmin and just discovered a bug in my app.
>
> I use the following query to get the user defined functions:
>
> SELECT
>   proname,
>   pt.typname AS result,
>   oidvectortypes(pc.proargtypes) AS arguments
> FROM
>   pg_proc pc, pg_user pu, pg_type pt
> WHERE
>   pc.proowner = pu.usesysid
>   AND pc.prorettype = pt.oid
>   AND pc.oid > '<max system oid>'::oid
>
> I just realized that this does not retrieve functions with the return
> datatype of OPAQUE because it is not a registered datatype. The prorettype
=
> 0 when the function's return type is created as OPAQUE rather than the oid
> of a "real" datatype.  I know I could do an outer join, but they are not
yet
> fully implemented.
>
> What would be the most graceful way to handle this?
>
> Tom, you helped me with my last kludgy query to get the indicies... can
you
> help here?
>
> Thanks,
>
> -Dan
>
> PS. I'm pretty sure this is a bug in psql as well.  I tried a \df
> update_pg_pwd (which is a system function that has a rettype of OPAQUE [or
> at least 0]) and it return anything.