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/