Hi All,
I have been working on support for WINDOW function in PostgreSQL 8.4.
Please find the patch for it.
Problem definition:
- Add an entry to the property list to indicate whether or not a
function is a window function.
- Modify the reverse engineered SQL to include the WINDOW keyword
where appropriate.
- Modify the function dialogue to allow the user to create window
functions (adding a checkbox seems the sensible way, which becomes
read-only in edit mode).
- For window functions, supported language will be 'c' only.
New function syntax can be found at:
http://developer.postgresql.org/pgdocs/postgres/sql-createfunction.html
As I asked a suggestion on utilizing the space while using boolean
parameters, Dave Page has suggested to use wxCheckListBox, but it has
limitation of "Not able to disable particular checkbox in that list."
I come up with a simple solution:
Put all the boolean parameters together and instead of putting them in
each row, kept them in 2x2 table. It does not look that so decent. :(
But, we can use this approach, until we come up with any good solution
for that.
--
Regards,
Ashesh Vashi
EnterpriseDB INDIA: http://www.enterprisedb.com
Index: include/schema/pgFunction.h
===================================================================
--- include/schema/pgFunction.h (revision 7543)
+++ include/schema/pgFunction.h (working copy)
@@ -79,6 +79,8 @@
void iSetSecureDefiner(bool b) { secureDefiner = b; }
bool GetIsStrict() const { return isStrict; }
void iSetIsStrict(bool b) { isStrict = b; }
+ bool GetIsWindow() const { return isWindow; }
+ void iSetIsWindow(bool b) { isWindow = b; }
wxArrayString& GetConfigList() { return configList; }
bool CanRestore() { return true; }
@@ -102,7 +104,7 @@
private:
wxString returnType, language, volatility, source, bin;
wxArrayString argNamesArray, argTypesArray, argModesArray, argDefsArray;
- bool returnAsSet, secureDefiner, isStrict;
+ bool returnAsSet, secureDefiner, isStrict, isWindow;
long argCount, cost, rows, argDefValCount;
wxArrayString configList;
};
Index: include/dlg/dlgFunction.h
===================================================================
--- include/dlg/dlgFunction.h (revision 7543)
+++ include/dlg/dlgFunction.h (working copy)
@@ -56,6 +56,7 @@
void OnVarRemove(wxCommandEvent &ev);
void OnVarSelChange(wxListEvent &ev);
void OnVarnameSelChange(wxCommandEvent &ev);
+ void OnChangeWindow(wxCommandEvent &ev);
void SetupVarEditor(int var);
wxString GetSelectedDirection();
Index: schema/pgFunction.cpp
===================================================================
--- schema/pgFunction.cpp (revision 7543)
+++ schema/pgFunction.cpp (working copy)
@@ -116,7 +116,10 @@
else
sql += qtDbString(GetSource());
}
- sql += wxT("\n LANGUAGE '") + GetLanguage() + wxT("' ") + GetVolatility();
+ sql += wxT("\n LANGUAGE '") + GetLanguage() + wxT("' ");
+ if (GetIsWindow())
+ sql += wxT("WINDOW ");
+ sql += GetVolatility();
if (GetIsStrict())
sql += wxT(" STRICT");
@@ -190,6 +193,7 @@
properties->AppendItem(_("Volatility"), GetVolatility());
properties->AppendItem(_("Security of definer?"), GetSecureDefiner());
properties->AppendItem(_("Strict?"), GetIsStrict());
+ properties->AppendItem(_("Window?"), GetIsWindow());
size_t i;
for (i=0 ; i < configList.GetCount() ; i++)
@@ -450,6 +454,9 @@
getArrayFromCommaSeparatedList(tmp, argDefValArray);
function->iSetArgDefValCount(functions->GetLong(wxT("pronargdefaults")));
+
+ // Check if it is a window function
+ function->iSetIsWindow(functions->GetBool(wxT("proiswindow")));
}
// Now iterate the arguments and build the arrays
Index: dlg/dlgFunction.cpp
===================================================================
--- dlg/dlgFunction.cpp (revision 7543)
+++ dlg/dlgFunction.cpp (working copy)
@@ -32,6 +32,7 @@
#define chkSetof CTRL_CHECKBOX("chkSetof")
#define cbVolatility CTRL_COMBOBOX("cbVolatility")
#define chkStrict CTRL_CHECKBOX("chkStrict")
+#define chkWindow CTRL_CHECKBOX("chkWindow")
#define chkSecureDefiner CTRL_CHECKBOX("chkSecureDefiner")
#define txtCost CTRL_TEXT("txtCost")
#define txtRows CTRL_TEXT("txtRows")
@@ -98,6 +99,7 @@
EVT_RADIOBUTTON(XRCID("rdbOut"), dlgFunction::OnChangeArgMode)
EVT_RADIOBUTTON(XRCID("rdbInOut"), dlgFunction::OnChangeArgMode)
EVT_RADIOBUTTON(XRCID("rdbVariadic"), dlgFunction::OnChangeArgMode)
+ EVT_CHECKBOX(XRCID("chkWindow"), dlgFunction::OnChangeWindow)
#ifdef __WXMAC__
EVT_SIZE( dlgFunction::OnChangeSize)
#endif
@@ -194,6 +196,11 @@
if (!connection->BackendMinimumVersion(8, 0))
txtArgName->Disable();
+ // Window function can not be modified
+ // Disable it for editing
+ if (function || !isBackendMinVer84)
+ chkWindow->Disable();
+
if (isProcedure)
{
if (function)
@@ -201,6 +208,7 @@
cbOwner->Disable();
cbLanguage->Disable();
chkStrict->Disable();
+ chkWindow->Disable();
chkSecureDefiner->Disable();
chkSetof->Disable();
cbVolatility->Disable();
@@ -294,6 +302,10 @@
chkSetof->SetValue(function->GetReturnAsSet());
chkStrict->SetValue(function->GetIsStrict());
+ chkWindow->SetValue(function->GetIsWindow());
+ // Do not allow to change language in case of WINDOW function
+ if (function->GetIsWindow())
+ cbLanguage->Disable();
chkSecureDefiner->SetValue(function->GetSecureDefiner());
if (function->GetLanguage().IsSameAs(wxT("C"), false))
@@ -516,6 +528,11 @@
if (!(isProcedure && connection->GetIsEdb()))
CheckValid(enable, cbLanguage->GetGuessedSelection() >= 0, _("Please select language."));
+ // In edit mode, existing window function allow 'internal' as language too
+ // But, when creating new function, we will allow only 'C' language
+ if (isBackendMinVer84)
+ CheckValid(enable, (!function && chkWindow->GetValue()) ? isC : true, _("Please select 'C' as language"));
+
if (isC)
{
wxString objfile=txtObjectFile->GetValue();
@@ -865,8 +882,12 @@
sql += qtDbString(txtSqlBox->GetText());
}
- sql += wxT("\nLANGUAGE ") + qtDbString(cbLanguage->GetValue())
- + wxT(" ") + cbVolatility->GetValue();
+ sql += wxT("\nLANGUAGE ") + qtDbString(cbLanguage->GetValue());
+ if (chkWindow->GetValue())
+ sql += wxT(" WINDOW ");
+ else
+ sql += wxT(" ");
+ sql += cbVolatility->GetValue();
if (chkStrict->GetValue())
sql += wxT(" STRICT");
if (chkSecureDefiner->GetValue())
@@ -965,4 +986,8 @@
}
+void dlgFunction::OnChangeWindow(wxCommandEvent &ev)
+{
+ CheckChange();
+}
Index: ui/dlgFunction.xrc
===================================================================
--- ui/dlgFunction.xrc (revision 7544)
+++ ui/dlgFunction.xrc (working copy)
@@ -17,10 +17,10 @@
<object class="wxPanel" name="pnlProperties">
<object class="wxFlexGridSizer">
<cols>2</cols>
- <rows>14</rows>
+ <rows>13</rows>
<vgap>5</vgap>
<hgap>5</hgap>
- <growablerows>12</growablerows>
+ <growablerows>11</growablerows>
<growablecols>1</growablecols>
<object class="sizeritem">
<object class="wxStaticText" name="stName">
@@ -126,43 +126,33 @@
<border>4</border>
</object>
<object class="sizeritem">
- <object class="wxStaticText" name="stSetof">
+ <object class="wxCheckBox" name="chkSetof">
<label>Returns set</label>
+ <flag>wxALIGN_RIGHT</flag>
</object>
- <flag>wxALIGN_CENTRE_VERTICAL|wxTOP|wxLEFT|wxRIGHT</flag>
- <border>4</border>
- </object>
- <object class="sizeritem">
- <object class="wxCheckBox" name="chkSetof">
- <label></label>
- </object>
<flag>wxEXPAND|wxALIGN_CENTER_VERTICAL|wxTOP|wxLEFT|wxRIGHT</flag>
<border>4</border>
</object>
<object class="sizeritem">
- <object class="wxStaticText" name="stStrict">
+ <object class="wxCheckBox" name="chkStrict">
<label>Strict</label>
+ <flag>wxALIGN_RIGHT</flag>
</object>
- <flag>wxALIGN_CENTRE_VERTICAL|wxTOP|wxLEFT|wxRIGHT</flag>
- <border>4</border>
- </object>
- <object class="sizeritem">
- <object class="wxCheckBox" name="chkStrict">
- <label></label>
- </object>
<flag>wxEXPAND|wxALIGN_CENTER_VERTICAL|wxTOP|wxLEFT|wxRIGHT</flag>
<border>4</border>
</object>
<object class="sizeritem">
- <object class="wxStaticText" name="stSecureDefiner">
+ <object class="wxCheckBox" name="chkSecureDefiner">
<label>Security of definer</label>
+ <flag>wxALIGN_RIGHT</flag>
</object>
- <flag>wxALIGN_CENTRE_VERTICAL|wxTOP|wxLEFT|wxRIGHT</flag>
+ <flag>wxEXPAND|wxALIGN_CENTER_VERTICAL|wxTOP|wxLEFT|wxRIGHT</flag>
<border>4</border>
</object>
<object class="sizeritem">
- <object class="wxCheckBox" name="chkSecureDefiner">
- <label></label>
+ <object class="wxCheckBox" name="chkWindow">
+ <label>Window</label>
+ <flag>wxALIGN_RIGHT</flag>
</object>
<flag>wxEXPAND|wxALIGN_CENTER_VERTICAL|wxTOP|wxLEFT|wxRIGHT</flag>
<border>4</border>
--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
