I have 'select ... from cursor foo, tables ...' working, and halfway done
with functions-as-result sets.
A few questions to gurus:
a) Currently, I have a shift-reduce conflict, because cursor is a valid
TokenId. It can be resolved by removing it from list of TokenId, but if
someone was naming one of their columns or tables "CURSOR", this will bite
them.
SQL92 says that 'CURSOR' is a reserved word. Is it OK to remove it from
TokenId list?
On other hand, its fairly simple to allow syntax 'select * from foo' and
treat foo as a cursor IF there is such a cursor, but I'm not sure if its
the right thing, it may be counterintuitive when your cursor disappears?
b) Next issue is ReScan of a cursor. To have consistent results, cursor
must be set to the beginning of underlying query every time. On other
hand, it may be surprising to do
declare foo cursor for select * from test;
fetch 5 from foo;
select * from cursor foo;
and have cursor rewind back to the beginning of 'test' table.
Advice?
c) Here's how I'm implementing a function-as-table-source, i.e.:
select * from func(args)
Query plan is transformed into multiple queries:
first, 'open portal' query for "SELECT func(args)" (however, with
tupledesc having all the fields function returns (if it returns a tuple),
not one field).
second, original query itself, but referring to portal instead of
function,
third, 'close portal' query.
Now, small questions:
1) Currently, there's a calling convention for functions-that-return-sets:
* fn_extra in fcinfo is set to null on first call, and can be set by function to distinguish a next call from first
call
* ResultSetInfo must be set to 'done' by function when its done returning values.
I think it all makes sense so far.
However, what should be the calling convention for returning a record from
a function? Currently, (correct me if I'm wrong), a function returning a
record (well, only a SQL function is capable of doing that meaningfully),
will return a TupleTableSlot *.
Previous suggestion from Jan Wieck was to make functions that return
records put them into a portal, and return refcursor of that portal, but I
don't see utility of that.
Is it a bad style for functions to create slots and return TupleTableSlot
*?
Thanks for all advice
-alex