Proposal: TABLE functions

["Pavel Stehule" <pavel(dot)stehule(at)hotmail(dot)com>]

Hello,

Currently PostgreSQL support set returning functions.

ANSI SQL 2003 goes with new type of functions - table functions. With this 
syntax

CREATE FUNCTION foo() RETURNS TABLE (c1 t1, ... )

PostgreSQL equal statements are:

CREATE TYPE tmptype AS (c1 t1, ...)
CREATE FUNCTION ... RETURNS SETOF tmptype AS ...

All necessary infrastructure is done. Implementation needs propably only 
small changes in parser.

This feature doesn't need any changes in SQL functions. I expect so they 
will be more readable and consistent.

CREATE OR REPLACE FUNCTION foo(f integer)
RETURNS TABLE(a int, b int) AS
$$
 SELECT a, b FROM
   FROM footab
  WHERE a < f;
$$ LANGUAGE sql;

plpgpsql RETURN have to be enhanced for table expressions.

CREATE OR REPLACE FUNCTION foo(f integer)
RETURNS TABLE(a int, b int) AS -- they are not variables!
$$
 BEGIN
   RETURN TABLE(SELECT a, b          -- it's secure, a,b are not variables
                            FROM footab
                          WHERE a < f);
 END;
$$ LANGUAGE plpgsql;

RETURN NEXT can be used without changes. This feature doesn't allow 
combination of RETURN TABLE and RETURN NEXT statement.

Table functions can have only IN arguments.

Advances:
* conformance with ansi sql 2003
* less propability of colision varnames and colnames

Regards
Pavel Stehule

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. 
http://messenger.msn.cz/