From: | Neil Conway <neilc(at)samurai(dot)com> |
---|---|
To: | PostgreSQL Patches <pgsql-patches(at)postgresql(dot)org> |
Subject: | first cut at PL/PgSQL table functions |
Date: | 2002-08-19 20:03:49 |
Message-ID: | 877kim1uqi.fsf@mailbox.samurai.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)samurai(dot)com> || PGP Key ID: DB3C29FC
Attachment | Content-Type | Size |
---|---|---|
plpgsql-srf-13.patch | text/x-patch | 23.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Neil Conway | 2002-08-19 20:16:38 | Re: first cut at PL/PgSQL table functions |
Previous Message | Joe Conway | 2002-08-19 18:29:56 | Re: bytea operator bugs (was Re: [GENERAL] BYTEA, indexes |