Re: Proposal: TABLE functions

Pavel Stehule wrote:
> 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, ... )
> 

Yeah this should be pretty easy because a table is just a composite
type. You can already do this:

CREATE TABLE foo (id bigint, first_name text);

CREATE FUNCTION foo() RETURNS SET OF foo...

> 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/
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>               http://www.postgresql.org/docs/faq
> 


-- 
     === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997            http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/