How can I copy a PostgreSQL function?-CodePudding

I have a simple problem and I'm hoping there's a simple solution. I've got a function written on a PostgreSQL server, let's call it function_A(), and I want to modify it. However, I also want to save a backup of the function so that I can revert the changes if I screw up.

Is this possible? I want something along the lines of CREATE FUNCTION function_B() AS function_A(). I saw another thread where the syntax ALTER FUNCTION function_A() RENAME TO function_B() was presented, but this doesn't create a second function; it only renames the original, which means it isn't a backup at all.

I could create a new function from scratch, but this would require carefully copying the code and reconfiguring all twenty parameters, which I'd like to avoid if possible.

Thanks in advance!

CodePudding user response：

If your function is written in PL/PgSQL then:

SELECT prosrc FROM pg_proc WHERE proname = 'function_A'

You can then save this in a safe spot for when disaster strikes.

CodePudding user response：

In psql:

\ef animal
-- Above opens editor with complete function
-- Option 1 copy and paste below into another text editor
-- Option 2 save to file. In my case with Vim w:fnc_animal.sql
CREATE OR REPLACE FUNCTION public.animal(a_type character varying)
 RETURNS record
 LANGUAGE plpgsql
AS $function$
BEGIN
    SELECT id, cond, animal FROM animals where animal = a_type;
END;
$function$

FYI, this will work for all user functions not written in C.