I'm on Postgres 13 (RDS) and need to drop a function by its oid value. Here's what I'm looking at:

postgres> select oid,proowner::regrole,proname||'('||pg_catalog.pg_get_function_identity_arguments(oid)||')' as func from pg_proc where proname='unnest' order by oid;
   oid   | proowner |                                           func
    2331 | jclough  | unnest(anyarray)
    3322 | rdsadmin | unnest(tsvector tsvector, OUT lexeme text, OUT positions smallint[], OUT weights text[])
 1030822 | jclough  | unnest(anyarray)
(3 rows)

I'd like to drop the function with oid 1030822 and then

alter function unnest(anyarray) owner to rdsadmin;

That's going to be tricky because

postgres> set role rdsadmin;
ERROR:  permission denied to set role "rdsadmin"

but that's a future battle.

A little history might be in order. We were on Postgres 8 and 9 for a long time, and I implemented my own unnest(anyarray) function because it only existed in versions >=10. When we made the jump from 9 to 13 (years later), my cobbled-together unnest function came along for the ride. So here I am with two versions of a function with identical signatures.

I should also mention I'm an application programmer masquerading as a DBA, at which I suck. I guess I'm going to have to modify pg_proc (or whatever) directly. Is there a least dangerous way to do this?

  • So, what happens when you drop function unnest(anyarray)?
    – mustaccio
    Mar 16 at 22:17

1 Answer 1


Don't cause sorrow for all involved by messing with the catalogs.

Instead, figure out in which schema the functions are. Unless you are dealing with actual data corruption, tow functions can only have the same name and signature if they are in the same schema.

Try the following:

SET search_path = '';

SELECT oid::regprocedure
FROM pg_proc
WHERE proname = 'unnest';

The output will contain the schema of the respective functions. Output of the schema is suppressed if the schema is on your search_path, so set that to an empty value to see the schemas.

  • This worked great! I'll have to remember this about search_path. So drop function public.unnest(anyarray); did the job. Mar 20 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.