<p>This issues related to <a href="https://github.com/dbsrgits/sql-translator/issues/82" class="issue-link js-issue-link" data-url="https://github.com/dbsrgits/sql-translator/issues/82" data-id="158200708" data-error-text="Failed to load issue title" data-permission-text="Issue title is private">#82</a></p>
<p>When body of function is changed but on this function depend other objects we got next error:</p>
<pre><code>Exception: DBD::Pg::db do failed: ERROR:  cannot drop function make_prow() because other objects depend on it
</code></pre>
<p>The produced <code>upgrade/downgrade</code> SQLs are like:</p>
<pre><code>DROP FUNCTION make_prow ();
CREATE FUNCTION "make_prow" ();
</code></pre>
<p>According to the <a href="https://www.postgresql.org/docs/9.6/static/sql-createfunction.html">DOC</a></p>
<blockquote>
<p>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.</p>
</blockquote>
<p>So <a href="https://github.com/SPodjasek/sql-translator/blob/topic/postgres-extensions/lib/SQL/Translator/Producer/PostgreSQL.pm#L718">here</a> instead of <code>DROP/CREATE</code> we should use 'CREATE OR REPLACE FUNCTION' when <code>{add_drop_procedure}</code> option is supplied.</p>
<p>The patch:</p>
<pre><code>--- 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-&gt;{add_drop_procedure};
-
-  my $sql = 'CREATE FUNCTION ';
+  my $sql = 'CREATE '. ($options-&gt;{add_drop_procedure} ? 'OR REPLACE ' : '') .'FUNCTION ';
   $sql .= $generator-&gt;quote($procedure-&gt;name);
   $sql .= ' (';
   my @args = ();
</code></pre>

<p style="font-size:small;-webkit-text-size-adjust:none;color:#666;">&mdash;<br />You are receiving this because you are subscribed to this thread.<br />Reply to this email directly, <a href="https://github.com/dbsrgits/sql-translator/issues/88">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/AASeAq153tKnBIuBygAeeJ6K1si0s5OPks5rtTm2gaJpZM4M2DCm">mute the thread</a>.<img alt="" height="1" src="https://github.com/notifications/beacon/AASeAkMydwU5TIX8gbHHM2E8tHH-HppQks5rtTm2gaJpZM4M2DCm.gif" width="1" /></p>
<div itemscope itemtype="http://schema.org/EmailMessage">
<div itemprop="action" itemscope itemtype="http://schema.org/ViewAction">
  <link itemprop="url" href="https://github.com/dbsrgits/sql-translator/issues/88"></link>
  <meta itemprop="name" content="View Issue"></meta>
</div>
<meta itemprop="description" content="View this Issue on GitHub"></meta>
</div>

<script type="application/json" data-scope="inboxmarkup">{"api_version":"1.0","publisher":{"api_key":"05dde50f1d1a384dd78767c55493e4bb","name":"GitHub"},"entity":{"external_key":"github/dbsrgits/sql-translator","title":"dbsrgits/sql-translator","subtitle":"GitHub repository","main_image_url":"https://cloud.githubusercontent.com/assets/143418/17495839/a5054eac-5d88-11e6-95fc-7290892c7bb5.png","avatar_image_url":"https://cloud.githubusercontent.com/assets/143418/15842166/7c72db34-2c0b-11e6-9aed-b52498112777.png","action":{"name":"Open in GitHub","url":"https://github.com/dbsrgits/sql-translator"}},"updates":{"snippets":[{"icon":"DESCRIPTION","message":"Use 'CREATE OR REPLACE FUNCTION' syntax when body is changed (#88)"}],"action":{"name":"View Issue","url":"https://github.com/dbsrgits/sql-translator/issues/88"}}}</script>