edbPackageFunction *edbPackageFunctionFactory::AppendFunctions(pgObject *obj, edbPackage *package, ctlTree *browser, const wxString &restriction)
{
edbPackageFunction *packageFunction = 0;
pgSet *packageFunctions;
// Caches
cacheMap typeCache, exprCache;
wxString sql, argDefsCol;
if (obj->GetConnection()->HasFeature(FEATURE_FUNCTION_DEFAULTS))
{
if (obj->GetConnection()->EdbMinimumVersion(8, 4))
{
argDefsCol = wxT("pg_get_expr(proargdefaults, 'pg_catalog.pg_class'::regclass) AS argdefaults, pronargdefaults, ");
}
else
{
argDefsCol = wxT("proargdefvals AS argdefaults, COALESCE(substring(array_dims(proargdefvals), E'1:(.*)\\]')::integer, 0) AS pronargdefaults, ");
}
}
if (obj->GetConnection()->EdbMinimumVersion(8, 2))
{
sql = wxT("SELECT pg_proc.oid, proname AS eltname, prorettype AS eltdatatype, pronargs AS nargs, proaccess AS visibility,\n")
wxT(" proallargtypes AS allargtypes, proargtypes AS argtypes, proargnames AS argnames, proargmodes AS argmodes,") + argDefsCol + wxT("\n")
wxT(" CASE WHEN format_type(prorettype, NULL) = 'void' THEN 'P' ELSE 'F' END AS eltclass\n")
wxT(" FROM pg_proc, pg_namespace\n")
+ restriction + wxT("\n")
wxT(" AND pg_proc.pronamespace = pg_namespace.oid\n")
wxT(" ORDER BY eltname");
}
else
{
sql = wxT("SELECT oid, eltname, eltdatatype, eltclass, nargs, visibility,\n")
wxT(" allargtypes, argtypes, argnames, argmodes\n")
wxT(" FROM edb_pkgelements\n")
+ restriction + wxT("\n")
wxT(" ORDER BY eltname");
}
packageFunctions = obj->GetDatabase()->ExecuteSet(sql);
pgSet *types = obj->GetDatabase()->ExecuteSet(wxT(
"SELECT oid, format_type(oid, NULL) AS typname FROM pg_type"));
while(!types->Eof())
{
typeCache[types->GetVal(wxT("oid"))] = types->GetVal(wxT("typname"));
types->MoveNext();
}
if (packageFunctions)
{
while (!packageFunctions->Eof())
{
size_t inModeCnt = 0;
size_t defaultArgsCnt = 0;
if (packageFunctions->GetVal(wxT("eltclass")) == wxT("F"))
packageFunction = new edbPackageFunction(package, packageFunctions->GetVal(wxT("eltname")));
else
packageFunction = new edbPackageProcedure(package, packageFunctions->GetVal(wxT("eltname")));
// Tokenize the arguments
wxStringTokenizer argTypesTkz(wxEmptyString), argModesTkz(wxEmptyString);
queryTokenizer argNamesTkz(wxEmptyString, (wxChar)',');
wxArrayString argDefValArray;
wxString tmp;
// Types
tmp = packageFunctions->GetVal(wxT("allargtypes"));
if (!tmp.IsEmpty())
argTypesTkz.SetString(tmp.Mid(1, tmp.Length() - 2), wxT(","));
else
{
tmp = packageFunctions->GetVal(wxT("argtypes"));
if (!tmp.IsEmpty())
argTypesTkz.SetString(tmp);
}
// Names
tmp = packageFunctions->GetVal(wxT("argnames"));
if (!tmp.IsEmpty())
argNamesTkz.SetString(tmp.Mid(1, tmp.Length() - 2), wxT(","));
// Modes
tmp = packageFunctions->GetVal(wxT("argmodes"));
if (!tmp.IsEmpty())
argModesTkz.SetString(tmp.Mid(1, tmp.Length() - 2), wxT(","));
// Function defaults
if (obj->GetConnection()->HasFeature(FEATURE_FUNCTION_DEFAULTS))
{
defaultArgsCnt = packageFunctions->GetLong(wxT("pronargdefaults"));
if (defaultArgsCnt > 0)
{
tmp = packageFunctions->GetVal(wxT("argdefaults"));
if (!tmp.IsEmpty())
{
getArrayFromCommaSeparatedList(tmp.Mid(1, tmp.Length() - 2), argDefValArray);
}
}
}
// Now iterate the arguments and build the arrays
wxString type, name, mode;
while (argTypesTkz.HasMoreTokens())
{
// Add the arg type. This is a type oid, so
// look it up in the hashmap
type = argTypesTkz.GetNextToken();
packageFunction->iAddArgType(typeCache[type]);
// Now add the name, stripping the quotes if
// necessary.
name = argNamesTkz.GetNextToken();
if (!name.IsEmpty())
{
if (name[0] == '"')
name = name.Mid(1, name.Length() - 2);
packageFunction->iAddArgName(name);
}
else
packageFunction->iAddArgName(wxEmptyString);
// Now the mode
mode = argModesTkz.GetNextToken();
if (!mode.IsEmpty())
{
if (mode == wxT('o') || mode == wxT("2"))
mode = wxT("OUT");
else if (mode == wxT("b"))
if (packageFunctions->GetVal(wxT("eltclass")) == wxT("F"))
mode = wxT("IN OUT");
else
mode = wxT("INOUT");
else if (mode == wxT("3"))
mode = wxT("IN OUT");
else if (mode == wxT("v"))
{
inModeCnt++;
mode = wxT("VARIADIC");
}
else
{
inModeCnt++;
mode = wxT("IN");
}
packageFunction->iAddArgMode(mode);
}
else
packageFunction->iAddArgMode(wxEmptyString);
}
// Finally the defaults, as we got them.
if (packageFunction->GetConnection()->HasFeature(FEATURE_FUNCTION_DEFAULTS))
{
size_t currINindex = 0;
while (inModeCnt)
{
for (size_t index = 0; index < packageFunction->GetArgTypesArray().Count(); index++)
{
wxString def = wxEmptyString;
if(packageFunction->GetArgModesArray()[index].IsEmpty() ||
packageFunction->GetArgModesArray()[index] == wxT("IN") ||
packageFunction->GetArgModesArray()[index] == wxT("VARIADIC"))
{
if (!argDefValArray.IsEmpty() && inModeCnt <= argDefValArray.GetCount())
{
def = argDefValArray[currINindex++];
if (!def.IsEmpty() && def != wxT("-"))
{
// Only EDB 8.3 does not support get the
// default value using pg_get_expr directly
if (!packageFunction->GetConnection()->BackendMinimumVersion(8, 4))
{
// Check the cache first - if we don't
// have a value, get it and cache for
// next time
wxString val = exprCache[def];
if (val == wxEmptyString)
{
val = obj->GetDatabase()->ExecuteScalar(
wxT("SELECT pg_get_expr('") + def.Mid(1, def.Length() - 2) + wxT("', 'pg_catalog.pg_class'::regclass)"));
exprCache[def] = val;
}
def = val;
}
}
else
{
def = wxEmptyString;
}
}
inModeCnt--;
}
packageFunction->iAddArgDef(def);
}
}
}
packageFunction->iSetOid(packageFunctions->GetOid(wxT("oid")));
packageFunction->iSetArgCount(packageFunctions->GetOid(wxT("nargs")));
packageFunction->iSetReturnType(typeCache[packageFunctions->GetVal(wxT("eltdatatype"))]);
if (packageFunctions->GetVal(wxT("visibility")) == wxT("+"))
packageFunction->iSetVisibility(_("Public"));
else if (packageFunctions->GetVal(wxT("visibility")) == wxT("-"))
packageFunction->iSetVisibility(_("Private"));
else
packageFunction->iSetVisibility(_("Unknown"));
packageFunction->iSetSource(package->GetBodyInner());
if (browser)
{
browser->AppendObject(obj, packageFunction);
packageFunctions->MoveNext();
}
else
break;
}
delete packageFunctions;
delete types;
}
return packageFunction;
}
pgFunction *pgFunctionFactory::AppendFunctions(pgObject *obj, pgSchema *schema, ctlTree *browser, const wxString &restriction)
{
// Caches
cacheMap typeCache, exprCache;
pgFunction *function = 0;
wxString argNamesCol, argDefsCol, proConfigCol, proType, seclab;
if (obj->GetConnection()->BackendMinimumVersion(8, 0))
argNamesCol = wxT("proargnames, ");
if (obj->GetConnection()->HasFeature(FEATURE_FUNCTION_DEFAULTS) && !obj->GetConnection()->BackendMinimumVersion(8, 4))
argDefsCol = wxT("proargdefvals, COALESCE(substring(array_dims(proargdefvals), E'1:(.*)\\]')::integer, 0) AS pronargdefaults, ");
if (obj->GetConnection()->BackendMinimumVersion(8, 4))
argDefsCol = wxT("pg_get_expr(proargdefaults, 'pg_catalog.pg_class'::regclass) AS proargdefaultvals, pronargdefaults, ");
if (obj->GetConnection()->BackendMinimumVersion(8, 3))
proConfigCol = wxT("proconfig, ");
if (obj->GetConnection()->EdbMinimumVersion(8, 1))
proType = wxT("protype, ");
if (obj->GetConnection()->BackendMinimumVersion(9, 1))
{
seclab = wxT(",\n")
wxT("(SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=pr.oid) AS labels,\n")
wxT("(SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=pr.oid) AS providers");
}
pgSet *functions = obj->GetDatabase()->ExecuteSet(
wxT("SELECT pr.oid, pr.xmin, pr.*, format_type(TYP.oid, NULL) AS typname, typns.nspname AS typnsp, lanname, ") +
argNamesCol + argDefsCol + proConfigCol + proType +
wxT(" pg_get_userbyid(proowner) as funcowner, description") + seclab + wxT("\n")
wxT(" FROM pg_proc pr\n")
wxT(" JOIN pg_type typ ON typ.oid=prorettype\n")
wxT(" JOIN pg_namespace typns ON typns.oid=typ.typnamespace\n")
wxT(" JOIN pg_language lng ON lng.oid=prolang\n")
wxT(" LEFT OUTER JOIN pg_description des ON des.objoid=pr.oid\n")
+ restriction +
wxT(" ORDER BY proname"));
pgSet *types = obj->GetDatabase()->ExecuteSet(wxT(
"SELECT oid, format_type(oid, NULL) AS typname FROM pg_type"));
if (types)
{
while(!types->Eof())
{
typeCache[types->GetVal(wxT("oid"))] = types->GetVal(wxT("typname"));
types->MoveNext();
}
}
if (functions)
{
while (!functions->Eof())
{
bool isProcedure = false;
wxString lanname = functions->GetVal(wxT("lanname"));
wxString typname = functions->GetVal(wxT("typname"));
// Is this an EDB Stored Procedure?
if (obj->GetConnection()->EdbMinimumVersion(8, 1))
{
wxString protype = functions->GetVal(wxT("protype"));
if (protype == wxT("1"))
isProcedure = true;
}
else if (obj->GetConnection()->EdbMinimumVersion(8, 0) &&
lanname == wxT("edbspl") && typname == wxT("void"))
isProcedure = true;
// Create the new object
if (isProcedure)
function = new pgProcedure(schema, functions->GetVal(wxT("proname")));
else if (typname == wxT("\"trigger\"") || typname == wxT("trigger"))
function = new pgTriggerFunction(schema, functions->GetVal(wxT("proname")));
else
function = new pgFunction(schema, functions->GetVal(wxT("proname")));
// Tokenize the arguments
wxStringTokenizer argTypesTkz(wxEmptyString), argModesTkz(wxEmptyString);
wxString tmp;
wxArrayString argNamesArray;
wxArrayString argDefValArray;
// We always have types
argTypesTkz.SetString(functions->GetVal(wxT("proargtypes")));
// We only have names in 8.0+
if (obj->GetConnection()->BackendMinimumVersion(8, 0))
{
tmp = functions->GetVal(wxT("proargnames"));
if (!tmp.IsEmpty())
getArrayFromCommaSeparatedList(tmp.Mid(1, tmp.Length() - 2), argNamesArray);
}
// EDB 8.0 had modes in pg_proc.proargdirs
if (!obj->GetConnection()->EdbMinimumVersion(8, 1) && isProcedure)
argModesTkz.SetString(functions->GetVal(wxT("proargdirs")));
if (obj->GetConnection()->EdbMinimumVersion(8, 1))
function->iSetProcType(functions->GetLong(wxT("protype")));
// EDB 8.1 and PostgreSQL 8.1 have modes in pg_proc.proargmodes
if (obj->GetConnection()->BackendMinimumVersion(8, 1))
{
tmp = functions->GetVal(wxT("proallargtypes"));
if (!tmp.IsEmpty())
argTypesTkz.SetString(tmp.Mid(1, tmp.Length() - 2), wxT(","));
tmp = functions->GetVal(wxT("proargmodes"));
if (!tmp.IsEmpty())
argModesTkz.SetString(tmp.Mid(1, tmp.Length() - 2), wxT(","));
}
// EDB 8.3: Function defaults
if (obj->GetConnection()->HasFeature(FEATURE_FUNCTION_DEFAULTS) &&
!obj->GetConnection()->BackendMinimumVersion(8, 4))
{
tmp = functions->GetVal(wxT("proargdefvals"));
if (!tmp.IsEmpty())
getArrayFromCommaSeparatedList(tmp.Mid(1, tmp.Length() - 2), argDefValArray);
function->iSetArgDefValCount(functions->GetLong(wxT("pronargdefaults")));
}
if (obj->GetConnection()->BackendMinimumVersion(8, 4))
{
tmp = functions->GetVal(wxT("proargdefaultvals"));
getArrayFromCommaSeparatedList(tmp, argDefValArray);
function->iSetArgDefValCount(functions->GetLong(wxT("pronargdefaults")));
// Check if it is a window function
function->iSetIsWindow(functions->GetBool(wxT("proiswindow")));
}
else
function->iSetIsWindow(false);
// Now iterate the arguments and build the arrays
wxString type, name, mode;
size_t nArgsIN = 0;
size_t nArgNames = 0;
while (argTypesTkz.HasMoreTokens())
{
if (nArgNames < argNamesArray.GetCount())
{
name = argNamesArray[nArgNames++];
}
else
name = wxEmptyString;
if (!name.IsEmpty())
{
// Now add the name, stripping the quotes and \" if
// necessary.
if (name[0] == '"')
name = name.Mid(1, name.Length() - 2);
name.Replace(wxT("\\\""), wxT("\""));
// In EDBAS 90, if an SPL-function has both an OUT-parameter
// and a return value (which is not possible on PostgreSQL otherwise),
// the return value is transformed into an extra OUT-parameter
// named "_retval_"
if (obj->GetConnection()->EdbMinimumVersion(9, 0))
{
if (name == wxT("_retval_"))
{
type = argTypesTkz.GetNextToken();
// this will be the return type for this object
function->iSetReturnType(typeCache[type]);
// consume uniformly, mode will definitely be "OUT"
mode = argModesTkz.GetNextToken();
continue;
}
}
function->iAddArgName(name);
}
else
function->iAddArgName(wxEmptyString);
// Add the arg type. This is a type oid, so
// look it up in the hashmap
type = argTypesTkz.GetNextToken();
function->iAddArgType(typeCache[type]);
// Now the mode
mode = argModesTkz.GetNextToken();
if (!mode.IsEmpty())
{
if (mode == wxT('o') || mode == wxT("2"))
mode = wxT("OUT");
else if (mode == wxT("b") || mode == wxT("3"))
{
if (isProcedure)
mode = wxT("IN OUT");
else
{
mode = wxT("INOUT");
// 'edbspl' does not support default values for the
// INOUT parameters.
if (lanname != wxT("edbspl"))
{
nArgsIN++;
}
}
}
else if (mode == wxT("v"))
{
mode = wxT("VARIADIC");
nArgsIN++;
}
else if (mode == wxT("t"))
mode = wxT("TABLE");
else
{
mode = wxT("IN");
nArgsIN++;
}
function->iAddArgMode(mode);
}
else
{
function->iAddArgMode(wxEmptyString);
nArgsIN++;
}
}
function->iSetArgCount(functions->GetLong(wxT("pronargs")));
wxString strReturnTableArgs;
// Process default values
size_t currINindex = 0;
for (size_t index = 0; index < function->GetArgModesArray().Count(); index++)
{
wxString def = wxEmptyString;
if(function->GetArgModesArray()[index].IsEmpty() ||
function->GetArgModesArray()[index] == wxT("IN") ||
(function->GetArgModesArray()[index] == wxT("INOUT") &&
lanname != wxT("edbspl")) ||
function->GetArgModesArray()[index] == wxT("VARIADIC"))
{
if (!argDefValArray.IsEmpty() && nArgsIN <= argDefValArray.GetCount())
{
def = argDefValArray[currINindex++];
if (!def.IsEmpty() && def != wxT("-"))
{
// Only EDB 8.3 does not support get the default value
// using pg_get_expr directly
if (function->GetConnection()->HasFeature(FEATURE_FUNCTION_DEFAULTS) &&
!function->GetConnection()->BackendMinimumVersion(8, 4))
{
// Check the cache first - if we don't have a value, get it and cache for next time
wxString val = exprCache[def];
if (val == wxEmptyString)
{
val = obj->GetDatabase()->ExecuteScalar(
wxT("SELECT pg_get_expr('") + def.Mid(1, def.Length() - 2) + wxT("', 'pg_catalog.pg_class'::regclass)"));
exprCache[def] = val;
}
def = val;
}
}
else
{
def = wxEmptyString;
}
}
nArgsIN--;
}
else if(function->GetConnection()->BackendMinimumVersion(8, 4) &&
function->GetArgModesArray()[index] == wxT("TABLE"))
{
if (strReturnTableArgs.Length() > 0)
strReturnTableArgs += wxT(", ");
wxString strName = function->GetArgNamesArray()[index];
if (!strName.IsEmpty())
strReturnTableArgs += qtIdent(strName) + wxT(" ");
strReturnTableArgs += function->GetArgTypesArray()[index];
}
function->iAddArgDef(def);
}
function->iSetOid(functions->GetOid(wxT("oid")));
function->iSetXid(functions->GetOid(wxT("xmin")));
if (browser)
function->UpdateSchema(browser, functions->GetOid(wxT("pronamespace")));
function->iSetOwner(functions->GetVal(wxT("funcowner")));
function->iSetAcl(functions->GetVal(wxT("proacl")));
// set the return type only if not already set..
if (function->GetReturnType().IsEmpty())
{
wxString strType = functions->GetVal(wxT("typname"));
if (strType.Lower() == wxT("record") && !strReturnTableArgs.IsEmpty())
{
strType = wxT("TABLE(") + strReturnTableArgs + wxT(")");
}
function->iSetReturnType(strType);
}
function->iSetComment(functions->GetVal(wxT("description")));
function->iSetLanguage(lanname);
function->iSetSecureDefiner(functions->GetBool(wxT("prosecdef")));
function->iSetReturnAsSet(functions->GetBool(wxT("proretset")));
function->iSetIsStrict(functions->GetBool(wxT("proisstrict")));
function->iSetSource(functions->GetVal(wxT("prosrc")));
function->iSetBin(functions->GetVal(wxT("probin")));
wxString vol = functions->GetVal(wxT("provolatile"));
function->iSetVolatility(
vol.IsSameAs(wxT("i")) ? wxT("IMMUTABLE") :
vol.IsSameAs(wxT("s")) ? wxT("STABLE") :
vol.IsSameAs(wxT("v")) ? wxT("VOLATILE") : wxT("unknown"));
// PostgreSQL 8.3 cost/row estimations
if (obj->GetConnection()->BackendMinimumVersion(8, 3))
{
function->iSetCost(functions->GetLong(wxT("procost")));
function->iSetRows(functions->GetLong(wxT("prorows")));
wxString cfg = functions->GetVal(wxT("proconfig"));
if (!cfg.IsEmpty())
FillArray(function->GetConfigList(), cfg.Mid(1, cfg.Length() - 2));
}
if (obj->GetConnection()->BackendMinimumVersion(9, 1))
{
function->iSetProviders(functions->GetVal(wxT("providers")));
function->iSetLabels(functions->GetVal(wxT("labels")));
}
if (obj->GetConnection()->BackendMinimumVersion(9, 2))
{
function->iSetIsLeakProof(functions->GetBool(wxT("proleakproof")));
}
if (browser)
{
browser->AppendObject(obj, function);
functions->MoveNext();
}
else
break;
}
delete functions;
delete types;
}
return function;
}