On Wed, Oct 29, 2008 at 09:40:00AM -0700, David Fetter wrote:
> Folks,
>
> Please find enclosed a WIP patch to add the ability for functions to
> see the qualifiers of the query in which they're called. It's not
> working just yet, and I'm not sure how best to get it working, but I'd
> like to see this as part of 8.4, as SQL/MED is just way too ambitious
> given the time frame.
>
> Any tips, hints, pointers, etc. would be much appreciated.
>
> Also, PL/Perl shouldn't be the only language to have this capability.
> How might we add similar capabilities to PL/PythonU and PL/Tcl? To
> the rest of the PLs? Would it make any sense to have it in SQL
> language functions?
Please find attached a patch which works in PL/Perl, the work having
been done by Andrew (RhodiumToad) Gierth. It's not clear to me how
this would be generally surface-able to SQL, though. Any ideas?
CREATE OR REPLACE FUNCTION show_qual()
RETURNS TEXT
LANGUAGE plperl
AS $$
return $_QUAL->{qual_string};
$$;
postgres=# SELECT * FROM show_qual() s(a) where a like '%';
a
------------------
(a ~~ '%'::text)
However, there are little lacunæ like this:
SELECT * FROM show_qual() s(a)
WHERE A NOT IN (
SELECT 'foo' UNION ALL SELECT 'bar'
);
a
------------------------
(NOT (hashed subplan))
Cheers,
David.
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/contrib/dblink/dblink.c b/contrib/dblink/dblink.c
index 7a9ddcd..c5b35be 100644
--- a/contrib/dblink/dblink.c
+++ b/contrib/dblink/dblink.c
@@ -728,6 +728,7 @@ dblink_record_internal(FunctionCallInfo fcinfo, bool
is_async, bool do_get)
char *conname = NULL;
remoteConn *rconn = NULL;
bool fail = true; /* default to backward
compatible */
+ ReturnSetInfo *rsi; /* set up for qual-pushing */
/* create a function context for cross-call persistence */
funcctx = SRF_FIRSTCALL_INIT();
@@ -802,6 +803,17 @@ dblink_record_internal(FunctionCallInfo fcinfo, bool
is_async, bool do_get)
elog(ERROR, "wrong number of arguments");
}
+ if (sql && rsi->qual) /* add qualifiers if available. */
+ {
+ char *quals = rsinfo_get_qual_str(rsi);
+ char *qualifiedQuery = palloc(strlen(sql) + strlen("
WHERE ") +
+
strlen(quals) + 1);
+
+ sprintf(qualifiedQuery, "%s WHERE %s", sql, quals);
+
+ sql = qualifiedQuery;
+ }
+
if (!conn)
DBLINK_CONN_NOT_AVAIL;
diff --git a/src/backend/executor/execQual.c b/src/backend/executor/execQual.c
index 4c4742d..571b2ae 100644
--- a/src/backend/executor/execQual.c
+++ b/src/backend/executor/execQual.c
@@ -45,6 +45,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "optimizer/clauses.h"
#include "optimizer/planmain.h"
#include "pgstat.h"
#include "utils/acl.h"
@@ -1693,6 +1694,27 @@ ExecMakeFunctionResultNoSets(FuncExprState *fcache,
return result;
}
+/*
+ *
+ * rsinfo_get_qual_str
+ *
+ * Get either an empty string or a batch of qualifiers.
+ *
+ */
+char *
+rsinfo_get_qual_str(ReturnSetInfo *rsinfo)
+{
+ Node *qual;
+ List *context;
+
+ if (!rsinfo->qual || !rsinfo->plan)
+ return pstrdup("");
+
+ qual = (Node *) make_ands_explicit(rsinfo->qual);
+ context = deparse_context_for_plan((Node *) rsinfo->plan, NULL,
rsinfo->rtable, NULL);
+
+ return deparse_expression(qual, context, false, false);
+}
/*
* ExecMakeTableFunctionResult
@@ -1703,6 +1725,7 @@ ExecMakeFunctionResultNoSets(FuncExprState *fcache,
Tuplestorestate *
ExecMakeTableFunctionResult(ExprState *funcexpr,
ExprContext *econtext,
+ Plan *plan, List
*rtable,
TupleDesc expectedDesc,
bool randomAccess)
{
@@ -1719,6 +1742,7 @@ ExecMakeTableFunctionResult(ExprState *funcexpr,
MemoryContext oldcontext;
bool direct_function_call;
bool first_time = true;
+ List *qual = plan->qual;
callerContext = CurrentMemoryContext;
@@ -1736,6 +1760,9 @@ ExecMakeTableFunctionResult(ExprState *funcexpr,
InitFunctionCallInfoData(fcinfo, NULL, 0, NULL, (Node *) &rsinfo);
rsinfo.type = T_ReturnSetInfo;
rsinfo.econtext = econtext;
+ rsinfo.qual = qual;
+ rsinfo.rtable = rtable;
+ rsinfo.plan = plan;
rsinfo.expectedDesc = expectedDesc;
rsinfo.allowedModes = (int) (SFRM_ValuePerCall | SFRM_Materialize);
if (randomAccess)
diff --git a/src/backend/executor/nodeFunctionscan.c
b/src/backend/executor/nodeFunctionscan.c
index 1e5086f..7945408 100644
--- a/src/backend/executor/nodeFunctionscan.c
+++ b/src/backend/executor/nodeFunctionscan.c
@@ -64,6 +64,8 @@ FunctionNext(FunctionScanState *node)
node->tuplestorestate = tuplestorestate =
ExecMakeTableFunctionResult(node->funcexpr,
node->ss.ps.ps_ExprContext,
+
node->ss.ps.plan,
+
estate->es_range_table,
node->tupdesc,
node->eflags & EXEC_FLAG_BACKWARD);
}
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index 1078a78..f14ad58 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -178,6 +178,7 @@ extern Datum GetAttributeByName(HeapTupleHeader tuple,
const char *attname,
bool *isNull);
extern Tuplestorestate *ExecMakeTableFunctionResult(ExprState *funcexpr,
ExprContext *econtext,
+ Plan *plan, List
*rtable,
TupleDesc expectedDesc,
bool randomAccess);
extern Datum ExecEvalExprSwitchContext(ExprState *expression, ExprContext
*econtext,
@@ -189,6 +190,7 @@ extern int ExecTargetListLength(List *targetlist);
extern int ExecCleanTargetListLength(List *targetlist);
extern TupleTableSlot *ExecProject(ProjectionInfo *projInfo,
ExprDoneCond *isDone);
+extern char *rsinfo_get_qual_str(ReturnSetInfo *rsinfo);
/*
* prototypes from functions in execScan.c
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index a4065d7..455056f 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -173,6 +173,9 @@ typedef struct ReturnSetInfo
ExprContext *econtext; /* context function is being called in
*/
TupleDesc expectedDesc; /* tuple descriptor expected by caller
*/
int allowedModes; /* bitmask: return modes caller
can handle */
+ List *qual; /* any quals to be applied to
the result */
+ List *rtable;
+ Plan *plan;
/* result status from function (but pre-initialized by caller): */
SetFunctionReturnMode returnMode; /* actual return mode */
ExprDoneCond isDone; /* status for ValuePerCall mode */
diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c
index 9dc184e..db2ee38 100644
--- a/src/pl/plperl/plperl.c
+++ b/src/pl/plperl/plperl.c
@@ -956,7 +956,7 @@ plperl_create_sub(char *proname, char *s, bool trusted)
ENTER;
SAVETMPS;
PUSHMARK(SP);
- XPUSHs(sv_2mortal(newSVstring("our $_TD; local $_TD=$_[0]; shift;")));
+ XPUSHs(sv_2mortal(newSVstring("our $_TD; local $_TD=$_[0]; shift; our
$_QUAL; local $_QUAL=$_[0]; shift;")));
XPUSHs(sv_2mortal(newSVstring(s)));
PUTBACK;
@@ -1064,6 +1064,19 @@ plperl_call_perl_func(plperl_proc_desc *desc,
FunctionCallInfo fcinfo)
XPUSHs(&PL_sv_undef); /* no trigger data */
+ /* set a qualifier string, if available */
+ if (fcinfo->resultinfo && ((ReturnSetInfo *)fcinfo->resultinfo)->qual)
+ {
+ ReturnSetInfo *rsi = (ReturnSetInfo *)fcinfo->resultinfo;
+ HV *hv = newHV();
+
+ hv_store_string(hv, "qual_string",
newSVstring(rsinfo_get_qual_str(rsi)));
+
+ XPUSHs(newRV_noinc((SV *)hv));
+ }
+ else
+ XPUSHs(&PL_sv_undef); /* no qualifier string */
+
for (i = 0; i < desc->nargs; i++)
{
if (fcinfo->argnull[i])
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
