first cut at PL/PgSQL table functions
От | Neil Conway |
---|---|
Тема | first cut at PL/PgSQL table functions |
Дата | |
Msg-id | 877kim1uqi.fsf@mailbox.samurai.com обсуждение исходный текст |
Ответы |
Re: first cut at PL/PgSQL table functions
Re: first cut at PL/PgSQL table functions |
Список | pgsql-patches |
The attached patch is my first attempt at implementing table functions for PL/PgSQL. The approach I used is (apparently) the method suggested by Tom: - nodeFunctionscan.c special-cases set-returning PL/PgSQL functions. Rather than calling the SRF repeatedly until all the tuples have been produced, the SRF is called once and is expected to return a pointer to a tuple store containing the result set. - inside PL/PgSQL, the application developer can add another tuple to the result set by using RETURN NEXT. PL/PgSQL takes care of creating a tuple store, etc etc. Still remaining to be done: - memory allocation: the tuple store needs to be accessible after the PL/PgSQL function returns, so it must be allocated in a sufficiently long-lived memory context (e.g. SPI_palloc won't work). I chose to just piggy back on TopTransactionContext, but there's likely a better way to do this... - syntax: the current patch uses 'NEXT', not 'RETURN NEXT'. That's because the latter syntax causes the parsing a regular RETURN statement to go haywire, for some reason I haven't yet determined. Any ideas on how to solve this (and/or use alternative syntax) would be welcome. - returning data: currently, the patch only allows you to call NEXT to return a tuple stored in a 'record' variable. This should be expanded to allow for set-returning scalar functions, as well as tuples stored in row-type variables, and perhaps even 'NEXT' followed by an SQL query. I'll be improving this shortly. - regression tests & documentation: this will be coming as soon as I've whipped the patch into better shape. Here's an example set-returning function in PL/PgSQL: create or replace function test_func() returns setof pg_class AS ' DECLARE r RECORD; BEGIN FOR r in SELECT * FROM pg_class LOOP NEXT r; END LOOP; RETURN; END;' language 'plpgsql'; Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Вложения
В списке pgsql-patches по дате отправления: