Thread: function returning a row
Hello
I have made my own type alignres that holds a string and an integer. I have
created the type in postgres and tried to insert into it and it works. I also
have a function that returns a row (or composite type). I have used
tupdesc = RelationNameGetTupleDesc("alignres");
to set it to return my own type. When i try to use the function in postgres
it says
ERROR: relation "alignres" does not exist
If i try to let the function use a type that is only created in postgres (not
c code) then it works.
Is it possible to use my own type to be returned?
-Kjetil
On Thu, Nov 18, 2004 at 02:54:31PM +0100, Kjetil Haaland wrote:
>
> I have made my own type alignres that holds a string and an integer. I have
> created the type in postgres and tried to insert into it and it works. I also
> have a function that returns a row (or composite type). I have used
>
> tupdesc = RelationNameGetTupleDesc("alignres");
>
> to set it to return my own type. When i try to use the function in postgres
> it says
>
> ERROR: relation "alignres" does not exist
The type you created isn't a composite -- it's a base (scalar) type.
Do you want to return a row with a field that has your type, or do
you just want to return a single value of your type?
> If i try to let the function use a type that is only created in postgres (not
> c code) then it works.
I assume you mean it works for a composite type like this:
CREATE TYPE foo AS (val INTEGER, fstring TEXT);
As the CREATE TYPE documentation says, this is essentially the same
as the row type of a table. But your type is a scalar, not a composite,
so RelationNameGetTupleDesc() doesn't work.
> Is it possible to use my own type to be returned?
Yes, and you've already written a function that does: alignres_in().
Its CREATE FUNCTION statement says "RETURNS alignres", doesn't it?
If you want the new function to return a single value of your type,
then do what alignres_in() does.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Hi again I know that this is an old question but I have some more that i am wondering about. On Thursday 18 November 2004 17:19, Michael Fuhr wrote: > > If i try to let the function use a type that is only created in postgres > > (not c code) then it works. > > I assume you mean it works for a composite type like this: > > CREATE TYPE foo AS (val INTEGER, fstring TEXT); > yes thats correct. This works fine while i am following the documentation. > As the CREATE TYPE documentation says, this is essentially the same > as the row type of a table. But your type is a scalar, not a composite, > so RelationNameGetTupleDesc() doesn't work. > > > Is it possible to use my own type to be returned? > > Yes, and you've already written a function that does: alignres_in(). > Its CREATE FUNCTION statement says "RETURNS alignres", doesn't it? > If you want the new function to return a single value of your type, > then do what alignres_in() does. this i have done, i can return a single value of my own type from the function. So now i am wondering, is it possible to return a set of my type? If it is possible, how do i do that? thanks for all the help so far! -Kjetil
Kjetil Haaland wrote: > this i have done, i can return a single value of my own type from the > function. So now i am wondering, is it possible to return a set of my type? > If it is possible, how do i do that? > I haven't followed this thread from the beginning, but if I understand correctly, you have a custom scalar type, and you want to return a set of that type? You should be able to declare the function with "RETURNS setof alignres", and then implement it as an SRF. See for example normal_rand() in contrib/tablefunc. Joe
On Wed, Dec 15, 2004 at 07:16:20AM -0800, Joe Conway wrote: > Kjetil Haaland wrote: > > >this i have done, i can return a single value of my own type from the > >function. So now i am wondering, is it possible to return a set of my > >type? If it is possible, how do i do that? > > I haven't followed this thread from the beginning, but if I understand > correctly, you have a custom scalar type, and you want to return a set > of that type? You should be able to declare the function with > "RETURNS setof alignres", and then implement it as an SRF. See for > example normal_rand() in contrib/tablefunc. See also "Returning Sets from C-Language Functions" in the "C-Language Functions" section of the "Extending SQL" chapter in the documentation. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Wednesday 15 December 2004 18:05, Michael Fuhr wrote:
> On Wed, Dec 15, 2004 at 07:16:20AM -0800, Joe Conway wrote:
> See also "Returning Sets from C-Language Functions" in the "C-Language
> Functions" section of the "Extending SQL" chapter in the documentation.
Hi again.
I know it is kind of an old thread, but i have some problems with getting this
function to work. When i run it in the database, the database crashes. The
last thing it prints out before it stops is "return next". I have added my
code here, sorry that there is that much, but i didn't find anything that i
could leave out. I don't know if i need to have the tables in the structure,
since it is allocated with multi_call_memory, but i have tried with and
without it in the structure, and it didn't make any difference. Hope someone
can help.
thanks
- Kjetil
typedef struct {
bool finished;
int x_value;
int y_value;
int min_value;
int max_x;
int max_y;
int **dynamic;
int **backtracking;
bool **before;
}set_data;
Datum smith_waterman_set(PG_FUNCTION_ARGS) {
FuncCallContext *funcctx;
MemoryContext oldcontext;
int call_cntr;
set_data *set;
int x_value, y_value, max_x, max_y;
int min_value;
int **dynamic;
int **backtracking;
bool **before;
char *in;
char *sml;
char g[2]="-";
if(SRF_IS_FIRSTCALL()) {
funcctx = SRF_FIRSTCALL_INIT();
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
set = (set_data *) palloc(sizeof(*set));
text *intext = PG_GETARG_TEXT_P(0);
text *smltext = PG_GETARG_TEXT_P(1);
in = (char*)palloc(VARSIZE(intext)-VARHDRSZ+1);
sml = (char*)palloc(VARSIZE(smltext)-VARHDRSZ+1);
snprintf (in, VARSIZE(intext) - VARHDRSZ + 1, VARDATA(intext));
snprintf (sml, VARSIZE(smltext) - VARHDRSZ + 1, VARDATA(smltext));
dynamic = (int **) palloc((1+strlen(in))* sizeof(int));
backtracking = (int **) palloc((1+strlen(in)) *sizeof(int));
before = (bool **) palloc((1+strlen(in))*sizeof(bool));
if(dynamic == NULL) {
printf("\n failed to allocate memory for dynamic");
ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
errmsg("failed to allocate memory for dynamic")));
}
int i, j, diag, up, left, max, s, maxi, maxj;
int maxvalue = -1;
for(i=0; i<strlen(in)+1; i++) {
dynamic[i] = palloc((1+strlen(sml))* sizeof(int));
backtracking[i] = palloc((1+strlen(sml))*sizeof(int));
before[i] = palloc((1+strlen(sml))*sizeof(bool));
if(dynamic[i] == NULL) {
printf("\nfailed to allocate for dynamic[%d]", i);
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("failed to allocate memory for dynamic[%d]", i)));
}
if(backtracking[i] == NULL) {
printf("\nfailed to allocate for backtracking[%d]", i);
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("failed to allocate memory for backtracking[%d]", i)));
}
}
//sets up the dynamic programing table,
//all values are zero since it is local similarity
for(i=0; i<strlen(in)+1; i++) {
for(j=0; j<strlen(sml)+1; j++) {
dynamic[i][j] = 0;
backtracking[i][j] = 0;
}
}
for(i=0; i<strlen(in); i++) {
for(j=0; j<strlen(sml); j++) {
s = score(in+i, sml+j);
diag = dynamic[i][j] + s;
up = dynamic[i][j+1] + s - gap;
left = dynamic[i+1][j] + s - gap;
max = MAX3(diag, up, left);
if(max <= 0) {
dynamic[i+1][j+1] = 0;
backtracking[i+1][j+1] = 0;
}
else if(max == diag) {
dynamic[i+1][j+1] = diag;
backtracking[i+1][j+1] = 1;
}
else if(max == up) {
dynamic[i+1][j+1] = up;
backtracking[i+1][j+1] = 2;
}
else if(max == left) {
dynamic[i+1][j+1] = left;
backtracking[i+1][j+1] = 3;
}
if(dynamic[i+1][j+1] > maxvalue) {
maxvalue = dynamic[i+1][j+1];
maxi = i+1;
maxj = j+1;
}
}
}
set->finished = false;
set->x_value = strlen(in);
set->y_value = strlen(sml);
set->min_value = PG_GETARG_INT32(2);
set->max_x = strlen(in);
set->max_y = strlen(sml);
set->dynamic = dynamic;
set->backtracking = backtracking;
set->before = before;
funcctx->user_fctx = set;
MemoryContextSwitchTo(oldcontext);
}
//stuff done on every call of the function
funcctx = SRF_PERCALL_SETUP();
call_cntr = funcctx->call_cntr;
set = funcctx->user_fctx;
x_value = set->x_value;
y_value = set->y_value;
min_value = set->min_value;
max_x = set->max_x;
max_y = set->max_y;
dynamic = set->dynamic;
backtracking = set->backtracking;
before = set->before;
if(!(set->finished)) {
alignres *result;
char *res1 = (char *) palloc((strlen(in)+strlen(sml))*sizeof(char));
char *res2 = (char *) palloc((strlen(in)+strlen(sml))*sizeof(char));
//finds the alignment
int k=0, i=0, j=0, temp_i=0, temp_j=0;
int lengde=0;
bool more = false;
bool first = true;
bool found = false;
for(i=x_value; i>0; i--) {
elog(NOTICE, "found = %d", found);
if(!found) {
for(j=max_y; j>0; j--) {
if(first) {
j=y_value;
first = false;
}
elog(NOTICE, "dynamic[%d][%d]=%d", i, j, dynamic[i][j]);
if(dynamic[i][j] >= min_value && before[i][j] == false) {
before[i][j] = true;
elog(NOTICE, "more");
more = true;
if(j>0) {
set->x_value = i;
set->y_value = j-1;
}
else if(i>0){
set->x_value = i-1;
set->y_value = max_y;
}
else
set->finished = true;
found = true;
temp_i = i;
temp_j = j;
break;
}
}
}
else
break;
}
i = temp_i;
j = temp_j;
while(more)
lengde++;
if(backtracking[i][j]==1) {
res1[k]=in[i-1];
res2[k]=sml[j-1];
i=i-1;
j=j-1;
before[i][j] = true;
}
else if(backtracking[i][j]==2) {
res1[k]=in[i-1];
res2[k]=g[0];
i=i-1;
before[i][j] = true;
}
else if(backtracking[i][j]==3) {
res1[k]=g[0];
res2[k]=sml[j-1];
j=j-1;
before[i][j] = true;
}
else {
more = false;
lengde--;
}
k++;
}
if(backtracking[i][j] > 0) {
if(i != 0)
res1[k]=in[i-1];
else
res1[k]=g[0];
if(j != 0)
res2[k]=sml[j-1];
else
res2[k]=g[0];
lengde++;
}
smst_in = (char*) palloc(1+lengde*sizeof(char));
smst_sml = (char*) palloc(1+lengde*sizeof(char));
i = lengde-1;
j = 0;
while (*(smst_in+j) = *(res1+i)) {
j++;
i--;
}
smst_in[lengde] = '\0';
i = lengde-1;
j = 0;
while (*(smst_sml+j) = *(res2+i)) {
j++;
i--;
}
smst_sml[lengde] = '\0';
elog(NOTICE, "smst_in=%s, smst_sml=%s", smst_in, smst_sml);
result = (alignres *)
palloc(sizeof(*result)+strlen(smst_in)+strlen(smst_sml));
bool first2 = true;
set->finished = true;
for(i=x_value; i>0; i--) {
for(j=max_y; j>0; j--) {
if(first2) {
j=y_value;
first2 = false;
}
if(dynamic[i][j] >= min_value && before[i][j] == false) {
elog(NOTICE, "verdi = %d, setter finished til false", dynamic[i][j]);
set->x_value = i;
set->y_value = j;
set->finished = false;
break;
}
}
}
funcctx->user_fctx = set;
elog(NOTICE, "return next");
SRF_RETURN_NEXT(funcctx, PointerGetDatum(result));
}
else {
elog(NOTICE, "return done");
SRF_RETURN_DONE(funcctx);
}
}
Kjetil Haaland <kjetil.haaland@student.uib.no> writes:
> text *intext = PG_GETARG_TEXT_P(0);
> text *smltext = PG_GETARG_TEXT_P(1);
> in = (char*)palloc(VARSIZE(intext)-VARHDRSZ+1);
> sml = (char*)palloc(VARSIZE(smltext)-VARHDRSZ+1);
> snprintf (in, VARSIZE(intext) - VARHDRSZ + 1, VARDATA(intext));
> snprintf (sml, VARSIZE(smltext) - VARHDRSZ + 1, VARDATA(smltext));
I'm not sure what you think the above snprintf's are going to do, but
it seems certain to be bad. The "format string" you are passing is
neither null-terminated nor guaranteed to be free of %'s.
memcpy would be safer; and of course you need to explicitly append a
null byte afterwards.
regards, tom lane
On Monday 31 January 2005 18:23, Tom Lane wrote:
> memcpy would be safer; and of course you need to explicitly append a
> null byte afterwards.
Hi again
I have now used memcopy instead since that is safer and it looks like this
text *intext = PG_GETARG_TEXT_P(0);
text *smltext = PG_GETARG_TEXT_P(1);
in = (char*)palloc(VARSIZE(intext)-VARHDRSZ+1);
sml = (char*)palloc(VARSIZE(smltext)-VARHDRSZ+1);
memcpy(in, VARDATA(intext), VARSIZE(intext)-VARHDRSZ);
in[VARSIZE(intext)-VARHDRSZ] = '\0';
memcpy(sml, VARDATA(smltext), VARSIZE(smltext)-VARHDRSZ);
sml[VARSIZE(smltext)-VARHDRSZ] = '\0';
set = (set_data *) palloc(sizeof(*set)+strlen(in)+strlen(sml)+1);
I have also added a char table to the structure, so I can keep the input
strings here. That is why the palloc is changed. But I still have the same
problems with it as before. Is there anything wrong in the way I have used
the API for set returning functions? I think i have saved all the values that
I need for the next round now, so I can't find whats wrong with it.
-Kjetil
hello I have tested my function some more and found out that it is while i am trying to return it crashes. If i use a standard type like float it works, but with my own type alignres it doesn't. I have used this type as return value in other functions before, but not in set returning functions. When returning a float value, SRF_RETURN_NEXT(funcctx, Float8GetDatum(result)) is used. What am i supposed to use for my own type to get the datum value? I have tried using PointerGetDatum(result) but then the function crashes. regards Kjetil
On Thu, Feb 10, 2005 at 01:16:37PM +0100, Kjetil Haaland wrote: > > I have tested my function some more and found out that it is while i am trying > to return it crashes. If i use a standard type like float it works, but with > my own type alignres it doesn't. I have used this type as return value in > other functions before, but not in set returning functions. I'd suggest getting a simple set-returning function working before adding the logic in the code you posted a couple of weeks ago. For example, start with a function that returns a set of integers, not the custom type. When you get that working, modify the function to return a set of the custom type, but don't do anything fancy -- just create an instance of the type in the simplest way possible and stuff some test values into it. When that works, start adding the extra logic that you need, but do so incrementally if possible. The idea is to start with something that you know works, then add a little bit at a time until it's finished or it breaks. If it breaks, then something about the last thing you added is likely to be the cause, so you know where to start looking. > When returning a float value, SRF_RETURN_NEXT(funcctx, Float8GetDatum(result)) > is used. What am i supposed to use for my own type to get the datum value? I > have tried using PointerGetDatum(result) but then the function crashes. PointerGetDatum() works for me, at least in the tests I've done with custom types. Do you have a core dump? Have you used a debugger to get a stack trace? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Thu, Feb 10, 2005 at 01:16:37PM +0100, Kjetil Haaland wrote: > I have tested my function some more and found out that it is while i am trying > to return it crashes. If i use a standard type like float it works, but with > my own type alignres it doesn't. I have used this type as return value in > other functions before, but not in set returning functions. I was just looking at the code you posted here: http://archives.postgresql.org/pgsql-novice/2005-01/msg00371.php About 20 lines above the call to SRF_RETURN_NEXT(), you allocate memory and assign it to "result", but then you never do anything with that variable before trying to return it. It probably contains garbage, which could be a problem if it's a variable-length type since the first 4 bytes are supposed to contain the length of the data. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Hi again Thanks a lot! That was the hole problem. Was using an old function that i was extending to return a set, and had forgoten that part. Now it works! -Kjetil On Friday 11 February 2005 11:09, Michael Fuhr wrote: > On Thu, Feb 10, 2005 at 01:16:37PM +0100, Kjetil Haaland wrote: > > I have tested my function some more and found out that it is while i am > > trying to return it crashes. If i use a standard type like float it > > works, but with my own type alignres it doesn't. I have used this type as > > return value in other functions before, but not in set returning > > functions. > > I was just looking at the code you posted here: > > http://archives.postgresql.org/pgsql-novice/2005-01/msg00371.php > > About 20 lines above the call to SRF_RETURN_NEXT(), you allocate > memory and assign it to "result", but then you never do anything > with that variable before trying to return it. It probably contains > garbage, which could be a problem if it's a variable-length type > since the first 4 bytes are supposed to contain the length of > the data.