pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedcan external C-function get multiple rows?
6+ messages / 3 participants
[nested] [flat]
* can external C-function get multiple rows?
@ 2001-04-23 15:35 Alexey Nalbat <alexey@price.ru>
2001-04-26 14:41 ` Re: can external C-function get multiple rows? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Alexey Nalbat @ 2001-04-23 15:35 UTC (permalink / raw)
To: pgsql-interfaces
I want my external C-function 'triple(int4)' to return multiple rows as 'SETOF int4' in order to be used in the sql-statement
'select a.plno,a.prod from ( select * from plprice_00_1 ) a, ( select triple(17366760) as plno ) b where a.plno=b.plno'.
Table 'plprice_00_1' is
pl=# \d plprice_00_1
Table "plprice_00_1"
Attribute | Type | Modifier
-----------+---------------+-----------
plno | integer | not null
prod | varchar(512) |
Indices: i_prc_prod_00_1,
pk_prc_plno_00_1
with primary key
pl=# \d pk_prc_plno_00_1
Index "pk_prc_plno_00_1"
Attribute | Type
-----------+---------
plno | integer
unique btree (primary key)
I created C-function as
#include <stdlib.h>
int
triple(int i)
{
int *n = (int *)malloc(3*sizeof(int));
n[0] = i;
n[1] = 2*i;
n[2] = 3*i;
return *n;
}
and compiledit on Solaris using
# gcc -fpic -c triple.c
# gcc -G -o triple.so triple.o
Creating function in psql made no error
# CREATE FUNCTION triple(int4) RETURNS SETOF int4 AS '/var/local/lib/pgsql/lib/triple.so' LANGUAGE 'C';
CREATE
And now it just hung up (after pressing ^C it came back with "Cancel request sent ERROR: Query was cancelled.") when I call
# select triple(1);
So, can C-function return multiple rows? And if yes, what was my error?
Thanks in advance.
P.S.: Excuse me for bad english.
--
Alexey Nalbat
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can external C-function get multiple rows?
2001-04-23 15:35 can external C-function get multiple rows? Alexey Nalbat <alexey@price.ru>
@ 2001-04-26 14:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-26 15:25 ` Re: can external C-function get multiple rows? Alex Pilosov <alex@pilosoft.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2001-04-26 14:41 UTC (permalink / raw)
To: alexey@price.ru; +Cc: pgsql-interfaces
Alexey Nalbat <alexey@price.ru> writes:
> So, can C-function return multiple rows?
In 7.1 it is possible to do this by using the new function-call
interface and adhering to the "functions returning sets" protocol
described in src/backend/utils/fmgr/README.
Before 7.1 there was no way --- the set-return support was a kluge
that only worked for SQL-language functions, not any other language.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can external C-function get multiple rows?
2001-04-23 15:35 can external C-function get multiple rows? Alexey Nalbat <alexey@price.ru>
2001-04-26 14:41 ` Re: can external C-function get multiple rows? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-04-26 15:25 ` Alex Pilosov <alex@pilosoft.com>
2001-04-26 15:38 ` Re: can external C-function get multiple rows? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Alex Pilosov @ 2001-04-26 15:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
On Thu, 26 Apr 2001, Tom Lane wrote:
> Alexey Nalbat <alexey@price.ru> writes:
> > So, can C-function return multiple rows?
>
> In 7.1 it is possible to do this by using the new function-call
> interface and adhering to the "functions returning sets" protocol
> described in src/backend/utils/fmgr/README.
ooo. Does plpgsql or pltcl support that?
-alex
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can external C-function get multiple rows?
2001-04-23 15:35 can external C-function get multiple rows? Alexey Nalbat <alexey@price.ru>
2001-04-26 14:41 ` Re: can external C-function get multiple rows? Tom Lane <tgl@sss.pgh.pa.us>
2001-04-26 15:25 ` Re: can external C-function get multiple rows? Alex Pilosov <alex@pilosoft.com>
@ 2001-04-26 15:38 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2001-04-26 15:38 UTC (permalink / raw)
To: Alex Pilosov <alex@pilosoft.com>; +Cc: pgsql-interfaces
Alex Pilosov <alex@pilosoft.com> writes:
> On Thu, 26 Apr 2001, Tom Lane wrote:
>> Alexey Nalbat <alexey@price.ru> writes:
> So, can C-function return multiple rows?
>>
>> In 7.1 it is possible to do this by using the new function-call
>> interface and adhering to the "functions returning sets" protocol
>> described in src/backend/utils/fmgr/README.
> ooo. Does plpgsql or pltcl support that?
Not yet. Are you volunteering to make it happen ;-) ?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can external C-function get multiple rows?
@ 2001-04-27 12:50 Alexey Nalbat <alexey@price.ru>
2001-04-30 05:52 ` Re: Re: can external C-function get multiple rows? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Alexey Nalbat @ 2001-04-27 12:50 UTC (permalink / raw)
To: pgsql-interfaces
Tom, thank you very much.
I was succeeded in constructing such a test function. And now have another question.
I wrote function myarr(foo) which returns exactly 10 rows of random values in the range [0,foo).
But I also want this function to work correctly, when used in a query with limit clause, like
"select myarr(100) limit 6;". After a bit of experiments I supposed that while executing
this query postgres called myarr() seven times (not six!). And may be at the seven call
fcinfo has some_flag set to "stop_return", after checking which myarr() should do the same
as when returning 11's row with PG_RETURN_NULL and setting "isDone" to "ExprEndResult"
and resetting variables. Is it so? What are some_flag and "stop_return"?
Thanks in advance.
P.S.:
Now myarr() does not reset variables when "interrupted by limit", and because of this returns:
+++
+++
pl=# select myarr(100) limit 6;
?column?
----------
87
42
35
38
4
16
(6 rows)
pl=# select myarr(100) limit 6;
?column?
----------
69
9
40
(3 rows)
+++
+++
Here is myarr() code:
+++
+++
#include <stdlib.h>
#include "postgres.h"
#include "fmgr.h"
#include "nodes/execnodes.h"
#define N 10
int a_c[N];
int n_c=0;
int i_c=0;
PG_FUNCTION_INFO_V1(myarr);
Datum
myarr(PG_FUNCTION_ARGS)
{
int n=PG_GETARG_INT32(0);
if ( n_c!=n )
{
int j;
n_c=n;
i_c=0;
for ( j=0 ; j<N ; j++ )
{
a_c[j]=n_c*rand()/RAND_MAX;
}
}
if ( i_c<N )
{
i_c++;
((ReturnSetInfo*)fcinfo->resultinfo)->isDone=ExprMultipleResult;
PG_RETURN_INT32(a_c[i_c-1]);
}
else
{
n_c=0;
i_c=0;
((ReturnSetInfo*)fcinfo->resultinfo)->isDone=ExprEndResult;
PG_RETURN_NULL();
}
}
+++
+++
--
WBR, Alexey Nalbat
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Re: can external C-function get multiple rows?
2001-04-27 12:50 Re: can external C-function get multiple rows? Alexey Nalbat <alexey@price.ru>
@ 2001-04-30 05:52 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2001-04-30 05:52 UTC (permalink / raw)
To: alexey@price.ru; +Cc: pgsql-interfaces
Alexey Nalbat <alexey@price.ru> writes:
> But I also want this function to work correctly, when used in a query
> with limit clause, like "select myarr(100) limit 6;". After a bit of
> experiments I supposed that while executing this query postgres called
> myarr() seven times (not six!).
Indeed. Observe the comments in nodeLimit.c:
* If we have reached the subplan EOF or the limit, just quit.
*
* NOTE: when scanning forwards, we must fetch one tuple beyond the
* COUNT limit before we can return NULL, else the subplan won't
* be properly positioned to start going backwards. Hence test
* here is for position > netlimit not position >= netlimit.
*
* Similarly, when scanning backwards, we must re-fetch the last
* tuple in the offset region before we can return NULL.
* Otherwise we won't be correctly aligned to start going forward
* again. So, although you might think we can quit when position
* equals offset + 1, we have to fetch a subplan tuple first, and
* then exit when position = offset.
Relying on static state as you are having your function do is hopelessly
unreliable anyway --- what happens if the query is aborted partway
through by some error? You'll be messed up when a new query is issued,
that's what.
I would suggest storing the cross-call state you need in a memory
block that you allocate on first call and save a pointer to in
fcinfo->flinfo->fn_extra. Strictly speaking this is an abuse of the
fn_extra feature, since the caller is not required to preserve that
across successive calls in one query, but in practice it will work.
Don't forget to do the allocation in the proper context, viz
ptr = MemoryContextAlloc(fcinfo->flinfo->fn_mcxt, sizewanted);
In this way, the state automatically goes away at end of query,
and you'll always see a NULL fcinfo->flinfo->fn_extra at first
call in a new query.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-04-30 05:52 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-23 15:35 can external C-function get multiple rows? Alexey Nalbat <alexey@price.ru>
2001-04-26 14:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-26 15:25 ` Alex Pilosov <alex@pilosoft.com>
2001-04-26 15:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-27 12:50 Re: can external C-function get multiple rows? Alexey Nalbat <alexey@price.ru>
2001-04-30 05:52 ` Tom Lane <tgl@sss.pgh.pa.us>
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