set returning functions

From: Jeff Rogers <jrogers(at)findlaw(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: set returning functions
Date: 2003-12-11 19:49:03
Message-ID: 200312111949.hBBJn3X10554@findlaw.com.
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Browse pgsql-general by date

  From Date Subject
Next Message Patrick Higgins 2003-12-11 19:58:09 PL/PgSQL composite parameter usage?
Previous Message Keith C. Perry 2003-12-11 19:14:41 Re: [NOVICE] PostgreSQL Training