wxString pgFunction::GetSql(ctlTree *browser)
{
if (sql.IsNull())
{
wxString qtName = GetQuotedFullIdentifier() + wxT("(") + GetArgListWithNames() + wxT(")");
wxString qtSig = GetQuotedFullIdentifier() + wxT("(") + GetArgSigList() + wxT(")");
sql = wxT("-- Function: ") + qtSig + wxT("\n\n")
+ wxT("-- DROP FUNCTION ") + qtSig + wxT(";")
+ wxT("\n\nCREATE OR REPLACE FUNCTION ") + qtName;
// Use Oracle style syntax for edb-spl functions
if (GetLanguage() == wxT("edbspl") && GetProcType() == 2)
{
sql += wxT("\nRETURN ");
sql += GetReturnType();
sql += wxT(" AS");
if (GetSource().StartsWith(wxT("\n")))
sql += GetSource();
else
sql += wxT("\n") + GetSource();
}
else
{
sql += wxT("\n RETURNS ");
if (GetReturnAsSet() && !GetReturnType().StartsWith(wxT("TABLE")))
sql += wxT("SETOF ");
sql += GetReturnType();
sql += wxT(" AS\n");
if (GetLanguage().IsSameAs(wxT("C"), false))
{
sql += qtDbString(GetBin()) + wxT(", ") + qtDbString(GetSource());
}
else
{
if (GetConnection()->BackendMinimumVersion(7, 5))
sql += qtDbStringDollar(GetSource());
else
sql += qtDbString(GetSource());
}
sql += wxT("\n LANGUAGE ") + GetLanguage() + wxT(" ");
if (GetConnection()->BackendMinimumVersion(8, 4) && GetIsWindow())
sql += wxT("WINDOW ");
sql += GetVolatility();
if (GetConnection()->BackendMinimumVersion(9, 2) && GetIsLeakProof())
sql += wxT(" LEAKPROOF");
if (GetIsStrict())
sql += wxT(" STRICT");
if (GetSecureDefiner())
sql += wxT(" SECURITY DEFINER");
// PostgreSQL 8.3+ cost/row estimations
if (GetConnection()->BackendMinimumVersion(8, 3))
{
sql += wxT("\n COST ") + NumToStr(GetCost());
if (GetReturnAsSet())
sql += wxT("\n ROWS ") + NumToStr(GetRows());
}
}
if (!sql.Strip(wxString::both).EndsWith(wxT(";")))
sql += wxT(";");
size_t i;
for (i = 0 ; i < configList.GetCount() ; i++)
{
if (configList.Item(i).BeforeFirst('=') != wxT("search_path") &&
configList.Item(i).BeforeFirst('=') != wxT("temp_tablespaces"))
sql += wxT("\nALTER FUNCTION ") + qtSig
+ wxT(" SET ") + configList.Item(i).BeforeFirst('=') + wxT("='") + configList.Item(i).AfterFirst('=') + wxT("';\n");
else
sql += wxT("\nALTER FUNCTION ") + qtSig
+ wxT(" SET ") + configList.Item(i).BeforeFirst('=') + wxT("=") + configList.Item(i).AfterFirst('=') + wxT(";\n");
}
sql += wxT("\n")
+ GetOwnerSql(8, 0, wxT("FUNCTION ") + qtSig)
+ GetGrant(wxT("X"), wxT("FUNCTION ") + qtSig);
if (!GetComment().IsNull())
{
sql += wxT("COMMENT ON FUNCTION ") + qtSig
+ wxT(" IS ") + qtDbString(GetComment()) + wxT(";\n");
}
if (GetConnection()->BackendMinimumVersion(9, 1))
sql += GetSeqLabelsSql();
}
return sql;
}
wxString dlgFunction::GetSql()
{
wxString sql;
wxString name;
wxString objType;
if (isProcedure)
objType = wxT("PROCEDURE ");
else
objType = wxT("FUNCTION ");
bool isC = cbLanguage->GetValue().IsSameAs(wxT("C"), false);
bool didChange = !function
|| cbLanguage->GetValue() != function->GetLanguage()
|| cbVolatility->GetValue() != function->GetVolatility()
|| chkSecureDefiner->GetValue() != function->GetSecureDefiner()
|| chkStrict->GetValue() != function->GetIsStrict()
|| GetArgs() != function->GetArgListWithNames()
|| chkLeakProof->GetValue() != function->GetIsLeakProof()
|| (isC && (txtObjectFile->GetValue() != function->GetBin() || txtLinkSymbol->GetValue() != function->GetSource()))
|| (!isC && txtSqlBox->GetText() != function->GetSource());
if (connection->BackendMinimumVersion(8, 3))
{
didChange = (didChange ||
txtCost->GetValue() != NumToStr(function->GetCost()) ||
(chkSetof->GetValue() && txtRows->GetValue() != NumToStr(function->GetRows())));
}
if (function)
{
name = GetName();
// edit mode
if (name != function->GetName())
{
if (!isProcedure)
AppendNameChange(sql, wxT("FUNCTION ") + function->GetQuotedFullIdentifier()
+ wxT("(") + function->GetArgSigList() + wxT(")"));
else
AppendNameChange(sql, wxT("FUNCTION ") + function->GetQuotedFullIdentifier());
}
if (didChange)
sql += wxT("CREATE OR REPLACE ") + objType;
}
else
{
name = qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName());
// create mode
sql = wxT("CREATE " ) + objType;
}
if (didChange)
{
if (isProcedure && GetArgs().IsEmpty())
{
sql += schema->GetQuotedPrefix() + qtIdent(GetName());
}
else
{
sql += schema->GetQuotedPrefix() + qtIdent(GetName())
+ wxT("(") + GetArgs() + wxT(")");
}
if (!isProcedure)
{
sql += wxT(" RETURNS ");
if (chkSetof->GetValue() && !cbReturntype->GetValue().StartsWith(wxT("TABLE")))
sql += wxT("SETOF ");
sql += cbReturntype->GetValue();
}
sql += wxT(" AS\n");
if (isProcedure)
{
sql += txtSqlBox->GetText();
sql = sql.Trim(true);
if (!sql.EndsWith(wxT(";")))
sql += wxT(";\n");
else
sql += wxT("\n");
}
else
{
if (cbLanguage->GetValue().IsSameAs(wxT("C"), false))
{
sql += qtDbString(txtObjectFile->GetValue());
if (!txtLinkSymbol->GetValue().IsEmpty())
sql += wxT(", ") + qtDbString(txtLinkSymbol->GetValue());
}
else
{
if (connection->BackendMinimumVersion(7, 5))
sql += qtDbStringDollar(txtSqlBox->GetText());
else
sql += qtDbString(txtSqlBox->GetText());
}
sql += wxT("\nLANGUAGE ") + cbLanguage->GetValue();
if (chkWindow->GetValue())
sql += wxT(" WINDOW ");
else
sql += wxT(" ");
sql += cbVolatility->GetValue();
if (connection->BackendMinimumVersion(9, 2))
{
if (!chkLeakProof->GetValue())
sql += wxT(" NOT");
sql += wxT(" LEAKPROOF");
}
if (chkStrict->GetValue())
sql += wxT(" STRICT");
if (chkSecureDefiner->GetValue())
sql += wxT(" SECURITY DEFINER");
// PostgreSQL 8.3+ cost/row estimations
if (connection->BackendMinimumVersion(8, 3))
{
if (txtCost->GetValue().Length() > 0)
sql += wxT("\nCOST ") + txtCost->GetValue();
if (chkSetof->GetValue() && txtRows->GetValue().Length() > 0)
sql += wxT("\nROWS ") + txtRows->GetValue();
}
sql += wxT(";\n");
}
}
name = schema->GetQuotedPrefix() + qtIdent(name)
+ wxT("(") + GetArgs(false, true) + wxT(")");
if (function)
{
AppendOwnerChange(sql, wxT("FUNCTION ") + name);
AppendSchemaChange(sql, wxT("FUNCTION ") + name);
}
else
{
if (cbOwner->GetCurrentSelection() > 0)
AppendOwnerNew(sql, wxT("FUNCTION ") + name);
}
if (isProcedure)
sql += GetGrant(wxT("X"), wxT("PROCEDURE ") + name);
else
{
wxArrayString vars;
size_t index;
if (function)
{
for (index = 0 ; index < function->GetConfigList().GetCount() ; index++)
vars.Add(function->GetConfigList().Item(index));
}
int cnt = lstVariables->GetItemCount();
int pos;
// check for changed or added vars
for (pos = 0 ; pos < cnt ; pos++)
{
wxString newVar = lstVariables->GetText(pos);
wxString newVal = lstVariables->GetText(pos, 1);
wxString oldVal;
for (index = 0 ; index < vars.GetCount() ; index++)
{
wxString var = vars.Item(index);
if (var.BeforeFirst('=').IsSameAs(newVar, false))
{
oldVal = var.Mid(newVar.Length() + 1);
vars.RemoveAt(index);
break;
}
}
// Reset the vars if they've changed, or the function definition has
// changed, which will remove them all :-(
if ((oldVal != newVal) || didChange)
{
if (newVar != wxT("search_path") && newVar != wxT("temp_tablespaces"))
sql += wxT("ALTER FUNCTION ") + name
+ wxT("\n SET ") + newVar
+ wxT("='") + newVal
+ wxT("';\n");
else
sql += wxT("ALTER FUNCTION ") + name
+ wxT("\n SET ") + newVar
+ wxT("=") + newVal
+ wxT(";\n");
}
}
// check for removed vars
for (pos = 0 ; pos < (int)vars.GetCount() ; pos++)
{
sql += wxT("ALTER FUNCTION ") + name
+ wxT("\n RESET ") + vars.Item(pos).BeforeFirst('=')
+ wxT(";\n");
}
sql += GetGrant(wxT("X"), wxT("FUNCTION ") + name);
}
if (isProcedure)
AppendComment(sql, wxT("PROCEDURE ") + name, function);
else
{
AppendComment(sql, wxT("FUNCTION ") + name, function);
if (seclabelPage && connection->BackendMinimumVersion(9, 1))
sql += seclabelPage->GetSqlForSecLabels(wxT("FUNCTION"), name);
}
return sql;
}
