How to CREATE FUNCTION IF NOT EXISTS?

Question

Is there are an easy way to do CREATE FUNCTION IF NOT EXISTS? I have multiple schemas and I am preparing a script that will create missing objects in the target schema. The plan is to run a script to check if the object exists, do nothing if it doesn't it will create it. 'CREATE SOMETHING IF NOT EXISTS' perfectly working with tables sequences and others, however cannot find the solution for functions. I am from Tsql world and it has this checking. However, looks like Postgres 9.6 doesn't have it. Is there any easy way around this limitation?

Answer 1

You may wrap function definitions with anonymous block and handle duplicate name exception:

create function f(int)
returns int
language sql
as 'select $1';

✓

do $$
begin
  create function f (int)
  returns int
  language sql
  as 'select $1';
end; $$

ERROR:  function "f" already exists with same argument types
CONTEXT:  SQL statement "create function f (int)
 returns int
 language sql
 as 'select $1'"
PL/pgSQL function inline_code_block line 3 at SQL statement

do $$
begin
    create function f (int)
    returns int
    language sql
    as 'select $1';
    
  exception
    when duplicate_function then
    null;
end; $$

✓

db<>fiddle here

Answer 2

You can use create or replace - but that doesn't work if the function's signature changes.

The other alternative is to use drop function if exists followed by a create function. Just make sure you set needed privileges after that again.

Answer 3

This is what I have to do

IF EXISTS (SELECT FROM information_schema.routines 
                 WHERE routine_schema = 'schema_name'
                   AND routine_name = 'vw_cmp') 
            THEN
                raise notice 'Routine vw_cmp EXISTS';
            ELSE
              --  create proc SQL
END IF;

Answer 4

I find this pretty intuitive:

If you dont care about function overloading, then use this.

DO $$
begin  
  PERFORM proname "name" FROM pg_proc WHERE proname LIKE 'summary_stats';
  IF NOT FOUND THEN
    CREATE OR REPLACE FUNCTION summary_stats(col TEXT, tbl TEXT) 
        RETURNS TABLE (
            "name" TEXT,
            "min" NUMERIC,
            "max" NUMERIC,
            "mean" NUMERIC,
            "sd" NUMERIC
        ) 
    AS $func$
    BEGIN
        RETURN QUERY EXECUTE FORMAT('SELECT
            ''%1$I''::text AS "name",
            ROUND(MIN(%1$I),5) "min",
            ROUND(MAX(%1$I),5) "max",
            ROUND(AVG(%1$I), 5) "mean",
            ROUND(stddev(%1$I), 5) "sd"
        FROM %2$I', col, tbl);
    END
    $func$
    LANGUAGE 'plpgsql';
  END IF;
END $$;

SELECT * FROM summary_stats('dividend', 'company_ratings')

But if you care about overloading, it gets more complicated:

DO $$
DECLARE
    this_func_name TEXT := 'summary_stats';
    this_func_nargs SMALLINT := 2;
    this_func_arg_names text[] := ARRAY['col', 'tbl'];
    
    same_arg_count BOOLEAN;
    same_arg_names BOOLEAN;
    found_funcs INTEGER;
BEGIN
    CREATE TEMP TABLE procs AS with tbl AS (
        SELECT
            oid id,
            proname "name",
            pronargs "nargs",
            unnest(proargnames) "argnames"
        FROM 
            pg_proc
        WHERE proname LIKE 'summary_stats'
        ORDER BY "id", "nargs"
    )
    SELECT 
        "name",
        (array_agg(DISTINCT "nargs"))[1] "nargs",
        (array_agg("argnames"))[1:"nargs"] "argnames"
    FROM tbl
    GROUP BY "id", "name", "nargs"
    ORDER BY "id", "nargs";
    
    found_funcs := (SELECT COUNT(*)::INTEGER FROM procs);
    same_arg_count := EXISTS(SELECT name FROM procs WHERE "nargs" = this_func_nargs);
    same_arg_names := EXISTS(SELECT name FROM procs WHERE "argnames" = this_func_arg_names);
  

  
  IF found_funcs = 0 OR same_arg_count = false OR same_arg_names = false THEN
    RAISE NOTICE 'CREATED FUNCTION ''%''', this_func_name;
    CREATE OR REPLACE FUNCTION summary_stats(col TEXT, tbl TEXT) 
        RETURNS TABLE (
            "name" TEXT,
            "min" NUMERIC,
            "max" NUMERIC,
            "mean" NUMERIC,
            "sd" NUMERIC
        ) 
    AS $func$
    BEGIN
        RETURN QUERY EXECUTE FORMAT('SELECT
            ''%1$I''::text AS "name",
            ROUND(MIN(%1$I),5) "min",
            ROUND(MAX(%1$I),5) "max",
            ROUND(AVG(%1$I), 5) "mean",
            ROUND(stddev(%1$I), 5) "sd"
        FROM %2$I', col, tbl);
    END
    $func$
    LANGUAGE 'plpgsql';
  END IF;
  DROP TABLE procs;
END $$;

SELECT * FROM summary_stats('dividend', 'company_ratings')

This function calculates the min, max, mean and sd of a column: col TEXT of a given table tbl TEXT.

If the function is not found in the PERFORM proname... line, it creates.

If the function is found, it skips the creation and simply runs it.

The output will always be:

+-----------+----------+---------+---------+---------+
|   name    |   min    |   max   |  mean   |   sd    |
+-----------+----------+---------+---------+---------+
| "dividend | -0.59737 | 0.72279 | 0.00374 | 0.12645 |
+-----------+----------+---------+---------+---------+

Answer 5

I had to do the same just for procedures (I think the same can be applied for functions). I have not found a proper solution for CREATE PROCEDURE IF NOT EXISTS, so I had to figure it out. The tricky part was the embedding of $$ signs and that can be done as follows:

DO
$main$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_proc p
      JOIN pg_namespace n ON p.pronamespace = n.oid
      WHERE n.nspname = 'someschema'
      AND p.proname = 'someproc'
   ) THEN
    EXECUTE 'CREATE OR REPLACE PROCEDURE someschema.someproc() LANGUAGE plpgsql AS $proc$
    BEGIN
        RAISE NOTICE ''dummy message'';
    END;
    $proc$';
    RAISE NOTICE 'Procedure someschema.someproc() someproc';
  ELSE 
    RAISE NOTICE 'Procedure someschema.someproc() someproc exists';
  END IF;
END;
$main$;

To provide a correct answer, here you are the one for CREATE FUNCTION IF NOT EXISTS:

DO
$main$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_proc p
    JOIN pg_namespace n ON p.pronamespace = n.oid
    WHERE n.nspname = 'someschema'
    AND p.proname = 'is_odd'
  ) THEN
      EXECUTE 'CREATE OR REPLACE FUNCTION someschema.is_odd(num integer) RETURNS boolean AS $func$
      BEGIN
        RETURN num % 2 <> 0;
      END;
      $func$ LANGUAGE plpgsql;';
      RAISE NOTICE 'Function someschema.is_odd(num integer) created';
  ELSE 
      RAISE NOTICE 'Function someschema.is_odd(num integer) exists';
  END IF;
END;
$main$;