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

Question

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.

Hmm, CREATE OR REPLACE will do what it says, replace it, when it's already there, so why would you expect an exception "duplicate_function" would be thrown? — sticky bit
– sticky bit, Commented Jan 11, 2022 at 18:33
If you are changing the parameters CREATE OR REPLACE will create the function with same name but a different signature, in other words it will overload the function. See CREATE FUNCTION for more info. An error will be thrown if you change the return type though. — Adrian Klaver
– Adrian Klaver, Commented Jan 11, 2022 at 22:50
But if I changer CREATE OR REPLACE to CREATE why it still not raising my error message? I'm getting [42723] error, as I understand this is a duplicate_function error — FeoJun
– FeoJun, Commented Jan 12, 2022 at 15:09

Belayer · Accepted Answer · 2022-01-12 20:55:19Z

2

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.

answered Jan 12, 2022 at 20:55

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related