On 22 January 2014 13:09, Tatsuo Ishii <is...@postgresql.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 >