Function returns composite type

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