pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedset returning functions
9+ messages / 5 participants
[nested] [flat]
* set returning functions
@ 2003-12-11 19:49 Jeff Rogers <jrogers@findlaw.com>
0 siblings, 0 replies; 9+ messages in thread
From: Jeff Rogers @ 2003-12-11 19:49 UTC (permalink / raw)
To: 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);
}
^ permalink raw reply [nested|flat] 9+ messages in thread
* set returning functions.
@ 2007-09-21 18:29 Rhys Stewart <rhys.stewart@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Rhys Stewart @ 2007-09-21 18:29 UTC (permalink / raw)
To: pgsql-general
Hi all,
How can one make a set-returning function behave like
generate_series(). If I cobble up an srf with plpgsql i have to do
select * from mysrf().
But i can do select generate_series(1,10) or select this, that,the
other,generate_series(1,5) from sometable. Essentially I would like to
be able to have my srf behave like that.
Rhys
Live Long & Prosper | Peace & Love
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: set returning functions.
@ 2007-09-24 22:09 Pavel Stehule <pavel.stehule@gmail.com>
parent: Rhys Stewart <rhys.stewart@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Pavel Stehule @ 2007-09-24 22:09 UTC (permalink / raw)
To: Rhys Stewart <rhys.stewart@gmail.com>; +Cc: pgsql-general
Hello Rhys
its not mystery
create or replace function mysrf(a integer)
returns integer
as $$
begin
for i in 1..a loop
return next i; -- <<<< important
end loop;
return;
end;
$$ language plpgsql;
you can call select * from mysrf(10);
Regards
Pavel Stehule
2007/9/21, Rhys Stewart <rhys.stewart@gmail.com>:
> Hi all,
>
> How can one make a set-returning function behave like
> generate_series(). If I cobble up an srf with plpgsql i have to do
> select * from mysrf().
> But i can do select generate_series(1,10) or select this, that,the
> other,generate_series(1,5) from sometable. Essentially I would like to
> be able to have my srf behave like that.
>
>
>
>
> Rhys
> Live Long & Prosper | Peace & Love
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: set returning functions.
@ 2007-09-24 23:01 Scott Marlowe <scott.marlowe@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Marlowe @ 2007-09-24 23:01 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Rhys Stewart <rhys.stewart@gmail.com>; pgsql-general
On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hello Rhys
>
> its not mystery
Yeah, but I think he wanted to be able to make his own function he
could call like:
select myfunc(10);
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: set returning functions.
@ 2007-09-24 23:18 Rhys Stewart <rhys.stewart@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Rhys Stewart @ 2007-09-24 23:18 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-general
yes indeed. thats exactly it scott!!!
On 9/24/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> > Hello Rhys
> >
> > its not mystery
>
> Yeah, but I think he wanted to be able to make his own function he
> could call like:
>
> select myfunc(10);
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: set returning functions.
@ 2007-09-25 04:46 Pavel Stehule <pavel.stehule@gmail.com>
parent: Rhys Stewart <rhys.stewart@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Pavel Stehule @ 2007-09-25 04:46 UTC (permalink / raw)
To: Rhys Stewart <rhys.stewart@gmail.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-general
2007/9/25, Rhys Stewart <rhys.stewart@gmail.com>:
> yes indeed. thats exactly it scott!!!
>
> On 9/24/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> > On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> > > Hello Rhys
> > >
> > > its not mystery
> >
> > Yeah, but I think he wanted to be able to make his own function he
> > could call like:
> >
> > select myfunc(10);
> >
Then you have to use sql language
create or replace function mysrf(int)
returns setof integer as $$
select i from generate_series(1, $1) g(i);
$$ language sql;
Regards
Pavel Stehule
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: set returning functions.
@ 2007-09-25 14:12 Scott Marlowe <scott.marlowe@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Marlowe @ 2007-09-25 14:12 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Rhys Stewart <rhys.stewart@gmail.com>; pgsql-general
On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2007/9/25, Rhys Stewart <rhys.stewart@gmail.com>:
> > yes indeed. thats exactly it scott!!!
> >
> > On 9/24/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> > > On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> > > > Hello Rhys
> > > >
> > > > its not mystery
> > >
> > > Yeah, but I think he wanted to be able to make his own function he
> > > could call like:
> > >
> > > select myfunc(10);
> > >
>
> Then you have to use sql language
>
> create or replace function mysrf(int)
> returns setof integer as $$
> select i from generate_series(1, $1) g(i);
> $$ language sql;
Do you HAVE to use sql plsql to get this to work? I thought that all
pl languages worked like this with pgsql.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: set returning functions.
@ 2007-09-25 14:29 Pavel Stehule <pavel.stehule@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Pavel Stehule @ 2007-09-25 14:29 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Rhys Stewart <rhys.stewart@gmail.com>; pgsql-general
2007/9/25, Scott Marlowe <scott.marlowe@gmail.com>:
> On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> > 2007/9/25, Rhys Stewart <rhys.stewart@gmail.com>:
> > > yes indeed. thats exactly it scott!!!
> > >
> > > On 9/24/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> > > > On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> > > > > Hello Rhys
> > > > >
> > > > > its not mystery
> > > >
> > > > Yeah, but I think he wanted to be able to make his own function he
> > > > could call like:
> > > >
> > > > select myfunc(10);
> > > >
> >
> > Then you have to use sql language
> >
> > create or replace function mysrf(int)
> > returns setof integer as $$
> > select i from generate_series(1, $1) g(i);
> > $$ language sql;
>
> Do you HAVE to use sql plsql to get this to work? I thought that all
> pl languages worked like this with pgsql.
>
if you can call SRF function in normal context (like SELECT srf()),
you have to use SQL language
Pavel
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: set returning functions.
@ 2007-09-25 15:53 Merlin Moncure <mmoncure@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Merlin Moncure @ 2007-09-25 15:53 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Rhys Stewart <rhys.stewart@gmail.com>; pgsql-general
On 9/25/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2007/9/25, Scott Marlowe <scott.marlowe@gmail.com>:
> > On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> > > 2007/9/25, Rhys Stewart <rhys.stewart@gmail.com>:
> > > > yes indeed. thats exactly it scott!!!
> > > >
> > > > On 9/24/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> > > > > On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> > > > > > Hello Rhys
> > > > > >
> > > > > > its not mystery
> > > > >
> > > > > Yeah, but I think he wanted to be able to make his own function he
> > > > > could call like:
> > > > >
> > > > > select myfunc(10);
> > > > >
> > >
> > > Then you have to use sql language
> > >
> > > create or replace function mysrf(int)
> > > returns setof integer as $$
> > > select i from generate_series(1, $1) g(i);
> > > $$ language sql;
> >
> > Do you HAVE to use sql plsql to get this to work? I thought that all
> > pl languages worked like this with pgsql.
> >
>
> if you can call SRF function in normal context (like SELECT srf()),
> you have to use SQL language
You can also do this in a C function also. you can always wrap your
plpgsql function in an sql function and sneak around the problem.
That said, it would be extremely nice if pl/pgsql functions could work
this way.
merlin
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-09-25 15:53 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-11 19:49 set returning functions Jeff Rogers <jrogers@findlaw.com>
2007-09-21 18:29 set returning functions. Rhys Stewart <rhys.stewart@gmail.com>
2007-09-24 22:09 ` Re: set returning functions. Pavel Stehule <pavel.stehule@gmail.com>
2007-09-24 23:01 ` Re: set returning functions. Scott Marlowe <scott.marlowe@gmail.com>
2007-09-24 23:18 ` Re: set returning functions. Rhys Stewart <rhys.stewart@gmail.com>
2007-09-25 04:46 ` Re: set returning functions. Pavel Stehule <pavel.stehule@gmail.com>
2007-09-25 14:12 ` Re: set returning functions. Scott Marlowe <scott.marlowe@gmail.com>
2007-09-25 14:29 ` Re: set returning functions. Pavel Stehule <pavel.stehule@gmail.com>
2007-09-25 15:53 ` Re: set returning functions. Merlin Moncure <mmoncure@gmail.com>
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