2

I want to write sqitch deploy script to create new version of trigger. To ensure that revert script will revert function to previous version, I'd like to make a backup of this function. Is there any way to copy (RENAME TO is not working!) trigger function with new name?

Jarek
  • 329
  • 2
  • 13
  • [pg-proc](https://www.postgresql.org/docs/current/static/catalog-pg-proc.html)? You are interesting in `proname` and `prosrc` columns I guess. – Abelisto Aug 05 '16 at 11:34
  • If you use [sqitch rework](https://github.com/sqitchers/sqitch/blob/develop/lib/sqitch-rework.pod), the revert script will be a copy of your original deploy script. – theory Apr 04 '19 at 20:11

1 Answers1

2

RENAME TO should work. Are you forgetting parentheses or including them on the other side?

Parentheses with alter table rename are a little tricky:

postgres=# create function test() returns bool language sql as $$ select true; $$;
CREATE FUNCTION
postgres=# alter function test() rename to old_test();
ERROR:  syntax error at or near "("
LINE 1: alter function test() rename to old_test();
                                                ^
postgres=# alter function test() rename to old_test;
ALTER FUNCTION

If that is not enough, then you could DO and EXECUTE the output of pg_get_functiondef after parsing and renaming it:

select * from pg_get_functiondef('old_test'::regproc);
Chris Travers
  • 25,424
  • 6
  • 65
  • 182
  • 1
    RENAME TO changes all references to renamed trigger function in CREATE TRIGGER statements, so I can't use it to make backup. – Jarek Aug 05 '16 at 11:32