From: Jeff Rogers <jrogers@findlaw.com>
To: pgsql-general@postgresql.org
Subject: set returning functions
Date: Thu, 11 Dec 2003 11:49:03 -0800
Message-ID: <200312111949.hBBJn3X10554@findlaw.com.> (raw)
I have a set-returning function that takes a text string and returns multiple
values from it. Its a small hack on the pgxml_xpath function from contrib/xml
that returns all matching nodes from an xml document rather than just a single
specified node.
I currently use it as follows:
create table xml_files (file text, doc text) ;
create view xnodes as select file, pgxml_xpath(doc, '/top/node') from
xml_files;
select * from xnodes ;
This gives me a table of all the matching nodes in all the documents.
However, the documentation says that using a SRF in the select list of a
query, but this capability is deprecated. I can't figure out how to call this
function in the from clause with it referring to a column in a table, I get an
error like
ERROR: FROM function expression may not refer to other relations of same
query level. Is there another way to accomplish this?
Another useful way to call my function would be
select file from xml_files where 'foo' in pgxml_xpath(doc,'/top/node')
but that gives be a parse error.
select file from xml_files where 'foo' in (select pgxml_xpath(doc,'/top/node'))
parses, but it doesn't seem to give correct results.
Thanks
-J
PS: Here's the function. This goes with pgxml.c, not pgxml_dom.c, bt could
probably be modified to work there as well.
PG_FUNCTION_INFO_V1(pgxml_xpath_all);
Datum
pgxml_xpath_all(PG_FUNCTION_ARGS) {
/* called as pgxml_xpath(document,pathstr) */
/* returns set of all matching results */
XPath_Results *xpresults;
text *restext;
MemoryContext oldContext;
FuncCallContext *funcctx;
text *t = PG_GETARG_TEXT_P(0); /* document buffer */
text *t2 = PG_GETARG_TEXT_P(1);
int32 ind;
if (SRF_IS_FIRSTCALL()) {
funcctx=SRF_FIRSTCALL_INIT();
oldContext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
xpresults = build_xpath_results(t, t2);
funcctx->user_fctx=xpresults;
MemoryContextSwitchTo(oldContext);
}
funcctx = SRF_PERCALL_SETUP();
xpresults=funcctx->user_fctx;
ind=funcctx->call_cntr;
if (xpresults == NULL || ind >= xpresults->rescount) {
if (xpresults != NULL) {
pfree(xpresults->resbuf);
pfree(xpresults);
}
SRF_RETURN_DONE(funcctx);
}
restext = (text *) palloc(xpresults->reslens[ind] + VARHDRSZ);
memcpy(VARDATA(restext), xpresults->results[ind], xpresults->reslens[ind]);
VARATT_SIZEP(restext) = xpresults->reslens[ind] + VARHDRSZ;
SRF_RETURN_NEXT(funcctx,restext);
}
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: jrogers@findlaw.com
Subject: Re: set returning functions
In-Reply-To: <200312111949.hBBJn3X10554@findlaw.com.>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox