Thread: can external C-function get multiple rows?
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 nullprod | 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
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
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
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
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
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,
wemust fetch one tuple beyond the * COUNT limit before we can return NULL, else the subplan won't * be
properlypositioned 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
regionbefore 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
tuplefirst, 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