[DBIx-Class-Devel] [dbsrgits/sql-translator] Use 'CREATE OR REPLACE FUNCTION' syntax when body is changed (#88)

[Eugen Konkov]

This issues related to #82 

When body of function is changed but on this function depend other objects we got next error:

    Exception: DBD::Pg::db do failed: ERROR:  cannot drop function make_prow() because other objects depend on it

The produced `upgrade/downgrade` SQLs are like:

```
DROP FUNCTION make_prow ();
CREATE FUNCTION "make_prow" ();
```

According to the [DOC](https://www.postgresql.org/docs/9.6/static/sql-createfunction.html)
>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](https://github.com/SPodjasek/sql-translator/blob/topic/postgres-extensions/lib/SQL/Translator/Producer/PostgreSQL.pm#L718) 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 = ();
```

-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/dbsrgits/sql-translator/issues/88
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class-devel/attachments/20170406/75344a61/attachment.htm>