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(at)acucore(dot)com>
To: "pgsql general" <pgsql-general(at)postgresql(dot)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.