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/

On Tue, 6 Feb 2007, 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, ... )
>
> PostgreSQL equal statements are:
>
> CREATE TYPE tmptype AS (c1 t1, ...)
> CREATE FUNCTION ... RETURNS SETOF tmptype AS ...

or you can do
CREATE FUNCTION foo(OUT c1 t1, OUT ...) RETURNS SETOF record AS ...

But I think this would be nice, I think the OUT parameters make less sense
than saying RETURNS TABLE(...). But what about functions not returning
SETOF?

--
The Schwine-Kitzenger Institute study of 47 men over the age of 100
showed that all had these things in common:

(1) They all had moderate appetites.
(2) They all came from middle class homes
(3) All but two of them were dead.

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/

> > 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 ...
>
>or you can do
>CREATE FUNCTION foo(OUT c1 t1, OUT ...) RETURNS SETOF record AS ...
>
>But I think this would be nice, I think the OUT parameters make less sense
>than saying RETURNS TABLE(...). But what about functions not returning
>SETOF?
>

This feature doesn't change current behaviour. And using TABLE function
means using SETOF.

Regards
Pavel Stehule

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/

On Tue, 2007-02-06 at 23:43 +0100, Pavel Stehule wrote:

> ANSI SQL 2003 goes with new type of functions - table functions. With this
> syntax
...
> All necessary infrastructure is done. Implementation needs propably only
> small changes in parser.
...
> * conformance with ansi sql 2003

Sounds good to me.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

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/

"Pavel Stehule" <pavel(dot)stehule(at)hotmail(dot)com> writes:
> 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.

I thought you said this was just syntactic sugar for capabilities we
already had?

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

I think stored procedures of that sort aren't functions at all, and
probably don't belong in pg_proc.

regards, tom lane