CODEWITHSUNDEEP
postgresql
FeoJun
FeoJun

Reputation: 153

How to re-create function with same name in postgres?

I need to re-create a group of functions but I don't want to check their parameters to use drop if there are functions with the same names but different parameters. Is this possible to drop/recreate them only by name?

Or is this possible to catch exceptions, raise errors and continue to execute the transaction?

I'm trying to do it using

DO $$ 
    BEGIN
        BEGIN
            CREATE OR REPLACE FUNCTION public.test(id integer)
                 RETURNS text[]
                 LANGUAGE plpgsql
                AS $function$
                begin
                    
                end;
                $function$
                ; 
        EXCEPTION
            WHEN duplicate_function THEN RAISE NOTICE 'already exists';
        END;
    END;
$$;

But It completes scripts quietly and does not raise any errors.

Upvotes: 0

Views: 968

Answers (1)

Belayer
Belayer

Reputation: 14934

You are correct in that 42723 is raised when a named object already, However, it is not a named exception. Thus your exception handles does not recognized, so it takes no action. You can get what you want by directly referencing the SQLSTATE and your error code.

DO $$ 
    BEGIN
        BEGIN
            CREATE FUNCTION public.test(id integer)
                 RETURNS text[]
                 LANGUAGE plpgsql
                AS $function$
                begin
                    
                end;
                $function$
                ; 
        EXCEPTION
            WHEN SQLSTATE '42723' THEN RAISE NOTICE 'already exists';
        END;
    END;
$$;

Raise notice sends the text following to sysout, which is often not accessible in a production system. You might want to change to raise exception.

Upvotes: 2

Related Questions