If you drop and then recreate a function, the new function is not the same entity as the old; you will have to drop existing rules, views, triggers, etc. that refer to the old function. Use CREATE OR REPLACE FUNCTION to change a function definition without breaking objects that refer to the function. Also, ALTER FUNCTION can be used to change most of the auxiliary properties of an existing function.
So here instead of DROP/CREATE we should use 'CREATE OR REPLACE FUNCTION' when {add_drop_procedure} option is supplied.
The patch:
--- a/lib/SQL/Translator/Producer/PostgreSQL.pm
+++ b/lib/SQL/Translator/Producer/PostgreSQL.pm
@@ -713,10 +713,7 @@ sub create_procedure {
my @statements;
- push @statements, drop_procedure( $procedure )
- if $options->{add_drop_procedure};
-
- my $sql = 'CREATE FUNCTION ';
+ my $sql = 'CREATE '. ($options->{add_drop_procedure} ? 'OR REPLACE ' : '') .'FUNCTION ';
$sql .= $generator->quote($procedure->name);
$sql .= ' (';
my @args = ();
This issues related to #82
When body of function is changed but on this function depend other objects we got next error:
The produced
upgrade/downgrade
SQLs are like:According to the DOC
So here instead of
DROP/CREATE
we should use 'CREATE OR REPLACE FUNCTION' when{add_drop_procedure}
option is supplied.The patch: