A postgreql function that drops itself

belden-redef.md

Playing around on the train this morning:

drop function if exists belden_redef();
create function belden_redef()
returns bool
as
$$
begin
  raise notice 'now you see me...';
  drop function if exists belden_redef();
  raise notice '...now you don''t';
  return true;
end;
$$
language plpgsql volatile;
select belden_redef();
select belden_redef();

In other words:

1. Try to drop a function
2. Create a function that drops itself halfway through execution
3. Call the function
4. Call the function again

Output:

NOTICE:  function belden_redef() does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
NOTICE:  now you see me...
NOTICE:  ...now you don't
 belden_redef
--------------
 t
(1 row)

ERROR:  function belden_redef() does not exist
LINE 1: select belden_redef();
               ^
HINT:   No function matches the given name and argument types.
        You might need to add explicit type casts.