Обсуждение: Function returns composite type
Hi! For some reason I wish to write C-function for SQL which returns set of composite type, but this type is defined function itself and composite type can be changed from call to call. So I can't define create type by 'CREATE TYPE' command. It's documented (34.7.8. Returning Rows (Composite Types) from C-Language Functions) there are only two ways to get TupleDesc: for a named relation and based on type OID. That ways are useless in my case. Is it possibly? -- Teodor Sigaev E-mail: teodor@sigaev.ru
Teodor Sigaev <teodor@sigaev.ru> writes:
> For some reason I wish to write C-function for SQL which returns set of
> composite type, but this type is defined function itself and composite type can
> be changed from call to call. So I can't define create type by 'CREATE TYPE'
> command.
Perhaps the RECORD stuff would help you? It's poorly documented, but
you could look at the code for SRFs to see how to use it.
regards, tom lane
> Perhaps the RECORD stuff would help you? It's poorly documented, but
> you could look at the code for SRFs to see how to use it.
>
> regards, tom lane
Ok, RECORD was a keyword, thank you. But I have questions:
1 What do you mean SRF? Set Return Function?
2 Some example I found in contrib/tablefunc, and so I create test function:
Datum
qqn(PG_FUNCTION_ARGS) { int attnum = PG_NARGS(); TupleDesc tupdesc; char
attname[NAMEDATALEN]; int i; TupleTableSlot *slot; AttInMetadata *attinmeta; Datum
*dvalues,result; char *nulls; HeapTuple tuple;
/* set tupledesc */ tupdesc = CreateTemplateTupleDesc(attnum, false); for (i = 0; i < attnum;
i++){ sprintf(attname, "z%d", i+1); TupleDescInitEntry(tupdesc, i+1, attname, INT4OID,
-1,0, false); }
slot = TupleDescGetSlot(tupdesc);
attinmeta = TupleDescGetAttInMetadata(tupdesc);
dvalues = (Datum *) palloc(attnum * sizeof(Datum)); nulls = (char *) palloc(attnum * sizeof(char));
for (i = 0; i < attnum; i++) { nulls[i] = ' '; dvalues[i] = PG_GETARG_DATUM(i);
}
/* tupdesc = attinmeta->tupdesc */ tuple = heap_formtuple(tupdesc, dvalues, nulls);
pfree(dvalues); pfree(nulls); result = TupleGetDatum(slot, tuple);
PG_RETURN_DATUM(result);
}
create function qqN(int4)
returns record
AS 'MODULE_PATHNAME'
LANGUAGE 'C';
create function qqN(int4,int4)
returns record
AS 'MODULE_PATHNAME'
LANGUAGE 'C';
# select * from qqn(1) as c(qq int4); qq
---- 1
(1 row)
# select * from qqn(1,2) as c(qq int4, qq1 int4); qq | qq1
----+----- 1 | 2
(1 row)
It works fine. But is there way not to point 'as c(qq int4, qq1 int4)'? Notice:
qqn isn't real, it's test only.
Thank you.
--
Teodor Sigaev E-mail: teodor@sigaev.ru
Teodor Sigaev wrote: > Ok, RECORD was a keyword, thank you. But I have questions: > 1 What do you mean SRF? Set Return Function? It refers to functions returning one or more tuples. From a practical standpoint, it is a function that is (and must be) used in the FROM clause like a table (hence also known as a "table function"). > # select * from qqn(1,2) as c(qq int4, qq1 int4);> qq | qq1> ----+-----> 1 | 2> (1 row)> > It works fine. But is there way not to point 'as c(qq int4, qq1 int4)'? If you mean, is there a way to leave out the 'as c(qq int4, qq1 int4)', the answer is no. You need to either declare the function to return a determinate data type, or you have to specify the data type at runtime in the query string. Joe
>
>> It works fine. But is there way not to point 'as c(qq int4, qq1 int4)'?
>
>
> If you mean, is there a way to leave out the 'as c(qq int4, qq1 int4)',
> the answer is no. You need to either declare the function to return a
> determinate data type, or you have to specify the data type at runtime
> in the query string.
it's a great pity :(.
But in function I already make TupleDesc: tupdesc = CreateTemplateTupleDesc(attnum, false); for (i = 0; i
<attnum; i++) { sprintf(attname, "z%d", i+1); TupleDescInitEntry(tupdesc, i+1, attname,
INT4OID,-1, 0, false); }
As I understand, this code makes full description of returning value, including
types and column's names.
Is this info used anywhere?
--
Teodor Sigaev E-mail: teodor@sigaev.ru
Teodor Sigaev wrote:
> it's a great pity :(.
>
> But in function I already make TupleDesc:
> tupdesc = CreateTemplateTupleDesc(attnum, false);
> for (i = 0; i < attnum; i++) {
> sprintf(attname, "z%d", i+1);
> TupleDescInitEntry(tupdesc, i+1, attname, INT4OID, -1,
> 0, false);
> }
> As I understand, this code makes full description of returning value,
> including types and column's names.
> Is this info used anywhere?
You could actually get the tupdesc from the caller if you wanted. See,
for example crosstab_hash() in contrib/tablefunc:
<snip> /* check to see if caller supports us returning a tuplestore */ if (!rsinfo || !(rsinfo->allowedModes &
SFRM_Materialize)) elog(ERROR, "crosstab: materialize mode required, but it is not " "allowed in this
context");
per_query_ctx = rsinfo->econtext->ecxt_per_query_memory; oldcontext = MemoryContextSwitchTo(per_query_ctx);
/* get the requested return tuple description */ tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
</snip>
The problem is that the parser needs the column data types long before
your function gets called by the executor. Therefore you either need the
predetermined return type, or the query string definition.
We've discussed a couple of times allowing the parser to "interrogate"
the function at parse time to let it determine what the runtime tupdesc
will be, but I haven't been able to come up with a good way to do that.
Joe
> You could actually get the tupdesc from the caller if you wanted. See, > for example crosstab_hash() in contrib/tablefunc: > > <snip> > /* check to see if caller supports us returning a tuplestore */ > if (!rsinfo || !(rsinfo->allowedModes & SFRM_Materialize)) > elog(ERROR, "crosstab: materialize mode required, but it is not " > "allowed in this context"); > > per_query_ctx = rsinfo->econtext->ecxt_per_query_memory; > oldcontext = MemoryContextSwitchTo(per_query_ctx); > > /* get the requested return tuple description */ > tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc); > </snip> Thank you for point. > > The problem is that the parser needs the column data types long before > your function gets called by the executor. Therefore you either need the > predetermined return type, or the query string definition. Ok, I see > > We've discussed a couple of times allowing the parser to "interrogate" > the function at parse time to let it determine what the runtime tupdesc > will be, but I haven't been able to come up with a good way to do that. Can we make follow: Functions, returning record and called in sql without description, ??lled with specific arguments (as is done for SRF by SRF_IS_FIRSTCALL()). With this arguments it should return TupleDesc. As I see, the place to such call is addRangeTableEntryForFunction at src/parser/parse_relation.c near lines N921-964. In this place we have all that we need. Am I wrong? -- Teodor Sigaev E-mail: teodor@sigaev.ru
>> We've discussed a couple of times allowing the parser to "interrogate"
>> the function at parse time to let it determine what the runtime tupdesc
>> will be, but I haven't been able to come up with a good way to do that.
This seems fairly unworkable to me, as in interesting cases the parser
could not find out what parameter values to pass to the function, so the
function wouldn't have enough information to know what it will return
either.
regards, tom lane