CODEWITHSUNDEEP
functionpostgresqlplpgsql
Pavel V.
Pavel V.

Reputation: 2810

How to find if a function exists in PostgreSQL?

Unlike tables or sequences, user-defined functions cannot be found through pg_class. There are questions on how find a list of all functions to delete or grant them, but how to find an individual function (with known name and argument types) is not self-evident from them. So how to find whether a function exists or not?

EDIT: I want to use it in a function, in automated manner. Which solution is the best performance-wise? Trapping errors is quite expensive, so I guess the best solution for me would be something without the extra step of translating error to false, but I might be wrong in this assumption.

Upvotes: 25

Views: 48046

Answers (4)

Iman Rosstin
Iman Rosstin

Reputation: 2365

Late to the party, but it could be something like this (don't use select instead of perform if you are not using the result or you would get an error complaining about it :

ERROR: query has no destination for result data

So the following code will work :

DO $$
BEGIN
    BEGIN
        perform pg_get_functiondef('some_function()'::regprocedure);
        raise notice 'it exists!';
    EXCEPTION WHEN undefined_function THEN
        raise notice 'Does not exist';
    END;
END $$;

Upvotes: 0

Greg0ry
Greg0ry

Reputation: 1079

Based on @PavelStehule answer this is how I am checking this in my scripts (using postgres exceptions and available exception codes)

DO $_$
BEGIN
    BEGIN
        SELECT 'some_schema.some_function(text)'::regprocedure;
    EXCEPTION WHEN undefined_function THEN
        -- do something here, i.e. create function
    END;
END $_$;

Upvotes: 3

Pavel Stehule
Pavel Stehule

Reputation: 45950

Yes, you cannot to find functions in pg_class because functions are stored on system table pg_proc

postgres-# \df
                               List of functions
 Schema |        Name        | Result data type | Argument data types  |  Type  
--------+--------------------+------------------+----------------------+--------
 public | foo                | integer          | a integer, b integer | normal
 public | function_arguments | text             | oid                  | normal
(2 rows)

Query for list of custom functions based on pg_proc is simply

postgres=# select p.oid::regprocedure
              from pg_proc p 
                   join pg_namespace n 
                   on p.pronamespace = n.oid 
             where n.nspname not in ('pg_catalog', 'information_schema');
           oid           
-------------------------
 foo(integer,integer)
 function_arguments(oid)
(2 rows)

Most simply and fastest tests on functions existence are casting (without parameters) to regproc or regprocedure (with parameters):

postgres=# select 'foo'::regproc;
 regproc 
---------
 foo
(1 row)

postgres=# select 'foox'::regproc;
ERROR:  function "foox" does not exist
LINE 1: select 'foox'::regproc;
               ^
postgres=# select 'foo(int, int)'::regprocedure;
     regprocedure     
----------------------
 foo(integer,integer)
(1 row)

postgres=# select 'foo(int, text)'::regprocedure;
ERROR:  function "foo(int, text)" does not exist
LINE 1: select 'foo(int, text)'::regprocedure;
               ^

or you can do some similar with test against pg_proc

postgres=# select exists(select * from pg_proc where proname = 'foo');
 exists 
--------
 t
(1 row)

postgres=# select exists(select * 
                            from pg_proc 
                           where proname = 'foo' 
                             and function_arguments(oid) = 'integer, integer');
 exists 
--------
 t
(1 row)

where:

CREATE OR REPLACE FUNCTION public.function_arguments(oid)
RETURNS text LANGUAGE sql AS $function$
    select string_agg(par, ', ') 
       from (select format_type(unnest(proargtypes), null) par 
                from pg_proc where oid = $1) x
$function$

or you can use buildin functions:pg_get_function_arguments

p.s. trick for simply orientation in system catalog. Use a psql option -E:

[pavel@localhost ~]$ psql -E postgres
psql (9.2.8, server 9.5devel)
Type "help" for help.

postgres=# \df
********* 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 pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;
**************************

                               List of functions
 Schema |        Name        | Result data type | Argument data types  |  Type  
--------+--------------------+------------------+----------------------+--------
 public | foo                | integer          | a integer, b integer | normal
 public | function_arguments | text             | oid                  | normal
(2 rows)

Upvotes: 53

user330315
user330315

Reputation:

I think the easiest way would be to use pg_get_functiondef().

If it returns something, the function is there, otherwise the function does not exist:

select pg_get_functiondef('some_function()'::regprocedure);
select pg_get_functiondef('some_function(integer)'::regprocedure);

The drawback is that it will produce an error if the function isn't there instead of simply returning an empty result. But this could e.g. be overcome by writing a PL/pgSQL function that catches the exception and returns false instead.

Upvotes: 14

Related Questions