How can I copy a PostgreSQL function?

Question

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!

Well, the source code of that function should already be versioned, e.g. inside a Git repository. Then you can always go back to an old version. And it also doesn't matter if you just rename the existing function, as you still have the SQL script to re-create the function any time you want. The database itself is not a safe place to keep your source code. — user330315
– user330315, Commented May 18, 2022 at 12:51

Patrick · Accepted Answer · 2022-05-18 12:55:38Z

2

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.

answered May 18, 2022 at 12:55

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

2 Comments

user330315 Over a year ago

Or better, pg_get_functiondef()

Yot Yot5 Over a year ago

@a_horse_with_no_name This was what I was looking for, thanks! Unlike the other answers, it considers the parameters and configuration of the function along with the code.

Adrian Klaver · Accepted Answer · 2022-05-18 15:17:22Z

0

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.

answered May 18, 2022 at 15:17

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Collectives™ on Stack Overflow

How can I copy a PostgreSQL function?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related