selecting from cursor/function

From: Alex Pilosov <alex(at)pilosoft(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: selecting from cursor/function
Date: 2001-07-10 18:27:20
Message-ID: Pine.BSO.4.10.10107061741030.27414-100000@spider.pilosoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Browse pgsql-hackers by date

  From Date Subject
Next Message Nathan Myers 2001-07-10 19:26:22 Re: AW: pg_index.indislossy
Previous Message Tom Lane 2001-07-10 17:51:15 SOMAXCONN (was Re: Solaris source code)