Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 275
Pavel V. Avatar asked Jul 16 '14 06:07

Pavel V.


People also ask

Is exists in Postgres?

In PostgreSQL, the EXISTS operator is used to test for the existence of rose in a subquery.It is generally used with correlated subqueries. If the subquery returns at least one row, the result of EXISTS is true. In case the subquery returns no row, the result is of EXISTS is false.

How do you call a function within a Postgres function?

The normal syntax to call another PL/pgSQL function from within PL/pgSQL is to either reference the function in a SQL SELECT statement, or during the assignment of a variable. For example: SELECT function_identifier ( arguments ); variable_identifier := function_identifier ( arguments );

Is there if statement in PostgreSQL?

PostgreSQL has an IF statement executes `statements` if a condition is true. If the condition evaluates to false, the control is passed to the next statement after the END IF part.


3 Answers

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)
like image 170
Pavel Stehule Avatar answered Oct 12 '22 11:10

Pavel Stehule


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.

like image 41
a_horse_with_no_name Avatar answered Oct 12 '22 10:10

a_horse_with_no_name


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 $_$;

like image 2
Greg0ry Avatar answered Oct 12 '22 11:10

Greg0ry