Re: Proposal: variant of regclass

On 22 January 2014 13:09, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:

> > 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;
> **************************
>

I thought the general use case is to be able to use such a functionality
using SQL queries (as against \df), so that the DBA can automate things,
without having to worry about the query returning error. And hence, I
thought to_regprocedure() can be used in a query just like how
::regprocedure is used.

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