Thread: Create or Update Function

Create or Update Function

From
Steve Erickson
Date:
<div class="WordSection1"><p class="MsoNormal"><span style="color:black">I’ve got a script to update the schema on a
numberof servers.  These servers are at various versions of the schema, so I used the following syntax for each
function,not knowing if it needed to be created or merely updated:</span><p class="MsoNormal"><span
style="color:black"> </span><pclass="MsoNormal"><span style="color:black">CREATE OR REPLACE FUNCTION foo() …</span><p
class="MsoNormal"><spanstyle="color:black"> </span><p class="MsoNormal"><span style="color:black">But I get the error,
“functionfoo() does not exist” if it indeed does not exist.  Shouldn’t the “create or replace” just create it in this
instance? Why the error?</span><p class="MsoNormal"><span style="color:black"> </span><p class="MsoNormal"><span
style="color:black">Thanks,</span><pclass="MsoNormal"><span style="color:black"> </span><p class="MsoNormal">Steve
Erickson<pclass="MsoNormal">Senior Developer<p class="MsoNormal">266 East 7th Street, Floor 4<p class="MsoNormal">Saint
Paul,MN 55101<p class="MsoNormal">651.925.3237 office<p class="MsoNormal">612.242.1343 cell<p class="MsoNormal"> <p
class="MsoNormal">NOTICE: This email, including any attachments, is covered by the Electronic Communications Privacy
Act,is confidential and may be legally privileged.  If you are not the intended recipient, you are hereby notified that
anyretention, dissemination, distribution or copying of this communication is strictly prohibited.  If you have
receivedthis communication in error, please immediately notify our office by return email or at our telephone number
(651)925-3200.  Then delete and destroy all copies of this email and any attachments.<p class="MsoNormal"> <p
class="MsoNormal">Thankyou.<p class="MsoNormal"> </div> 

Re: Create or Update Function

From
Craig Ringer
Date:
On 07/10/2012 04:28 AM, Steve Erickson wrote:

I’ve got a script to update the schema on a number of servers.  These servers are at various versions of the schema, so I used the following syntax for each function, not knowing if it needed to be created or merely updated:

 

CREATE OR REPLACE FUNCTION foo() …

 

But I get the error, “function foo() does not exist” if it indeed does not exist.  Shouldn’t the “create or replace” just create it in this instance?  Why the error?


I think you need to show us the full conversation with the server. I suspect the CREATE OR REPLACE is failing because of bad function syntax or similar, so a following invocation of the function is causing your function foo() does not exist error.

--
Craig Ringer