Re: Proposal: TABLE functions

Hello,

it can by more simple than I though. I need only one flag, and if its true
then I don't create language variables for OUT params. But I need one next
column in pg_proc.

Currently a lot of columns in pg_proc is bool. What about one binary columns
for other options? I hope so next versions can support autonomous
transaction, which need flag too.

Regards
Pavel Stehule

>
>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/
>

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