Thread: 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.

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.

"Dan Wilson" <phpPgAdmin@acucore.com> writes:
> What would be the most graceful way to handle this?

If you don't want to use an outer join (reasonable, if you don't want
to break compatibility with 7.0.* yet) then see the FAQ for the standard
UNION-based workaround for outer joins.

Eventually I'd like to get rid of this hack of using OID 0 to mean
OPAQUE (or ANY, or sundry other things).  It's a version or two away
though...

            regards, tom lane