Need to drop a function by oid value (I think)

Question

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?

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.