Proposal: TABLE functions

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/