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?

Lactose

In Postgres you can overload functions so do you want to check all the variations? For starters take a look at the system catalog pg_proc. You can check for a functions existence there. For the pronamespace(schema name) field use something like pronamespace = 'public'::regnamespace. – Boatel

Typically I would use CREATE OR REPLACE which is approaching the problem from the other side. Also 9.6 isn't going to be supported for that much longer, make sure you have your upgrade plan in place. – Omura

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

Olenolin

good idea but I have more than 90 procs/functions and it. will overcomplicate my script – Lactose

Do you know if there is a code to catch duplicate triggers? I tried duplicate_trigger but that didn't work. – Mannos

@Mannos Seems that duplicate_object should handle this. db<>fiddle – Olenolin

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.

Stunsail

yeah I know about this, but I cannot drop and recreate functions, I need just to do a simple check if exist do nothing if not create – Lactose

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;

Lactose

But... You still have 90+ objects, and you'll have to write this for each of them. How does it differ from duplicate object exception handling? – Olenolin

@astentx, not a big difference just don't want to have exceptions :) prefer do check however your approach will also work :) – Lactose

there is a typo: rouitine_name – Compassionate

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 |
+-----------+----------+---------+---------+---------+

Stephanus

The question is about creation not about running functions. – Barbecue

This is doing both. If you dont want to run it. Erase the line SELECT * FROM summary_stats('dividend', 'company_ratings'). I included it just to make a point. – Stephanus

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

Vernacularize