Skip site navigation (1) Skip section navigation (2)

first cut at PL/PgSQL table functions

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 (view raw or flat)
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: plpgsql-srf-13.patch
Description: text/x-patch (23.5 KB)

Responses

pgsql-patches by date

Next:From: Neil ConwayDate: 2002-08-19 20:16:38
Subject: Re: first cut at PL/PgSQL table functions
Previous:From: Joe ConwayDate: 2002-08-19 18:29:56
Subject: Re: bytea operator bugs (was Re: [GENERAL] BYTEA, indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group