Message-Id: <200312111949.hBBJn3X10554@findlaw.com.>
From: Jeff Rogers <jrogers@findlaw.com>
To: pgsql-general@postgresql.org
Subject: set returning functions
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Date: Thu, 11 Dec 2003 11:49:03 -0800


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