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; }