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


Tue, 10 Jun 2003 16:45:20 +0400
Teodor Sigaev <teodor@sigaev.ru>
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

Tue, 10 Jun 2003 09:45:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Function returns composite type 
> 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


Tue, 10 Jun 2003 18:57:05 +0400
Teodor Sigaev <teodor@sigaev.ru>
Re: Function returns composite type
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



Tue, 10 Jun 2003 10:36:18 -0700
Joe Conway <mail@joeconway.com>
Re: Function returns composite type
> 
>> 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


Wed, 11 Jun 2003 12:23:33 +0400
Teodor Sigaev <teodor@sigaev.ru>
Re: Function returns composite type
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



Wed, 11 Jun 2003 08:39:07 -0700
Joe Conway <mail@joeconway.com>
Re: Function returns composite type
> 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


Wed, 11 Jun 2003 20:26:01 +0400
Teodor Sigaev <teodor@sigaev.ru>
Re: Function returns composite type
>> 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

Wed, 11 Jun 2003 12:37:46 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Function returns composite type 