Re: Proposal: variant of regclass

> I, as a user would be happier if we also have to_regprocedure() and
> to_regoperator(). The following query looks a valid use-case where one
> needs to find if a particular function exists. Using to_regproc('sum') does
> not make sense here because it will return InvalidOid, which will not tell
> us whether that is because there is no such function or whether there are
> duplicate function names.
> select * from pg_proc where oid = to_regprocedure('sum(int)');

I doubt the value of the use case above. Hasn't psql already done an
excellent job?

test=# \df sum
                         List of functions
   Schema   | Name | Result data type | Argument data types | Type
------------+------+------------------+---------------------+------
 pg_catalog | sum  | numeric          | bigint              | agg
 pg_catalog | sum  | double precision | double precision    | agg
 pg_catalog | sum  | bigint           | integer             | agg
 pg_catalog | sum  | interval         | interval            | agg
 pg_catalog | sum  | money            | money               | agg
 pg_catalog | sum  | numeric          | numeric             | agg
 pg_catalog | sum  | real             | real                | agg
 pg_catalog | sum  | bigint           | smallint            | agg
(8 rows)

If you need simliar functionality in the backend, you could always
define a view using the query generated by psql.

********* QUERY **********
SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
  WHEN p.proisagg THEN 'agg'
  WHEN p.proiswindow THEN 'window'
  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
  ELSE 'normal'
 END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname ~ '^(sum)$'
  AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;
**************************

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp