Thread: SRF related and other questions
Hi,
First, I have the following question on Set Returning Functions:
Regarding the return type, there are two ways of defining it:
Either set it to: "setof _some_predifined_type"
or
set it to: "setof records" and then define the expected results with "as(attr1 type, ..., attr_n type)".
Right?
If these are indeed the only ways, is it possible to write an SRF whose return type is defined inside the code (something like the second way mentioned above but altering TupleDesc or something like that?) based on the executed query?
What I want to do is to write an SRF, which will execute a query maybe different than (but derived from) the original one passed to this function. Obviously the first way is not suitable since I cannot know a priori (before entering my SRF) what the result type will exactly be.
In general, (having the Query/Plan etc. available) are there any functions (or anything) that could help defining the type of the expected results?
A solution I suppose would be to parse the Query but in this case I would like to know if there are any functions that would help create the TupleDesc and anything else requiered.
Second, could you please tell me where in the code an incoming request, from a remote dblink_record() call, is handled? I'm a little lost here :-)
Regards,
Ntinos Katsaros
Katsaros Kwn/nos wrote:
> Either set it to: "setof _some_predifined_type" or set it to:
> "setof records" and then define the expected results with "as(attr1
> type, ..., attr_n type)".
>
> Right?
Correct. Except it should be "setof record", not "setof records".
> If these are indeed the only ways, is it possible to write an SRF
> whose return type is defined inside the code (something like the
> second way mentioned above but altering TupleDesc or something like
> that?) based on the executed query? What I want to do is to write an
> SRF, which will execute a query maybe different than (but derived
> from) the original one passed to this function. Obviously the first
> way is not suitable since I cannot know a priori (before entering my
> SRF) what the result type will exactly be.
If I understand correctly what you are asking, the answer is yes ;-)
When returning "setof record", the column definition must exist in the
query, and must match what ultimately is returned. This means that
whatever logic you use in your application to write the sql statement
must be able to derive the appropriate column types. That said, inside
your function you have two choices (at least):
-- you can directly determine the column definition used in the sql statement, as in dblink_record()
/* get the requested return tuple description */ tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
-- you can use the same logic that your application did to derive the column desc and build it yourself, similar to
make_crosstab_tupledesc() in contrib/tablefunc
(see line 1636 in cvs HEAD sources)
> Second, could you please tell me where in the code an incoming
> request, from a remote dblink_record() call, is handled? I'm a little
> lost here :-)
I don't understand what you're asking here. Can you elaborate?
Joe
On Sat, 2004-11-20 at 19:56, Joe Conway wrote: > When returning "setof record", the column definition must exist in the > query, and must match what ultimately is returned. This means that > whatever logic you use in your application to write the sql statement > must be able to derive the appropriate column types. That said, inside > your function you have two choices (at least): > > -- you can directly determine the column definition used in the sql > statement, as in dblink_record() > > /* get the requested return tuple description */ > tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc); > Actually I use the code of dblink_record() to execute queries on remote sites! The thing is that I want either to avoid giving the column types in startup (I don't think this is possible) OR (preferred and possible, I suppose) to BYPASS this mechanism by providing another tupdesc "object" referring to the executed query (as I said this NOT always the query provided on function call). This would be something like: tupdesc = CreateTupleDescCopy(my_tupdesc); > -- you can use the same logic that your application did to derive > the column desc and build it yourself, similar to > make_crosstab_tupledesc() in contrib/tablefunc > If this query is executed using SPI calls then this function shows exactly what I need.But what happends if the query is to be executed using PQexec() like functions, that is in my case a call to a remote db? In this case, is there any mechanism that could help retrieve the tupdesc for this query? I may end up providing different results than those expected from the original query. An idea was to make a pg_attribute query to fill the Form_pg_attribute array of the my_tupdesc but I don't think this is enough (what about TupleConstr *constr, and aggregate results). Another idea was to parse the Query node (available for the executed query) and fill this array (again what about TupleConstr *constr?). > > Second, could you please tell me where in the code an incoming > > request, from a remote dblink_record() call, is handled? I'm a little > > lost here :-) > I don't understand what you're asking here. Can you elaborate? This is topic I just started to study so excuse me if I I'm not so clear:-). What I'm asking is: what is the mechanism that handles incoming requests (from remote nodes). Which module listens on port 5432 (I think this is it...) for calls from other computers? Which modules take the incoming query (execute it and) put the results on wire? I just need a pointer. Thanks, Ntinos Katsaros