How to get Postgres function name as well as function specific name from pg_catalog.pg_proc?

Question

Since Postgres supports function overloading, getting function name as well as function specific name(System generated without duplicates) is more meaning full.

Assume i have 2 functions in the name as Func1 which are overloaded as shown below,

  CREATE FUNCTION "Schema"."Func1"(IN param1 INTEGER,
  IN Param2 CHAR)
  RETURNS INTEGER
  AS $BODY$

  begin
  return param1+1;
  end  $BODY$
  LANGUAGE PLPGSQL;@


  CREATE FUNCTION "Schema"."Func1"(IN param1 INTEGER)
  RETURNS INTEGER
  AS $BODY$

  begin
  return param1+1;
  end  $BODY$
  LANGUAGE PLPGSQL;@

How do i load the functions as well as input parameters correctly from pg_catalog.pg_proc. With the help of information_schema.routines, there is a way to load function 1)specific_name 2) routine_name

But many other attributes are missing in information_schema.routines like 1) isWindow function 2) isStrict function 3) isProRetSet function

So is there some other means to get the function specific_name from pg_catalog.....

Answer 1

A general method is to use psql -E or set ECHO_HIDDEN inside psql and look at the queries it generates for backslash commands.

For instance, \df "Func1" produces this with PostgreSQL 9.1:

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 ~ '^(Func1)$'
  AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;

which gives you directions about how to get the different function signatures associated to the name "Func1"

The same with \df+ would lead to the other attributes, like volatility.