Proposal: TABLE functions

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.stehule@hotmail.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

"Pavel Stehule" <pavel.stehule@hotmail.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?

My mistake. I am sorry. I have to store somewhere flag. One bit, which
signalise "don't use OUT arguments as function's parameters". Other is only
game in parser.

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.

Why not? Some people use "ugly" implementation of it in plperlu and DBI.
pg_proc and related infrastructure works well. It miss only little bit
bigger adaptability. I thing so can be interesting one general option byte,
and one byte reservated for language handlers.

Regards
Pavel Stehule

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

"Pavel Stehule" <pavel.stehule@hotmail.com> writes:

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

My mistake. I am sorry. I have to store somewhere flag. One bit, which
signalise "don't use OUT arguments as function's parameters".

Huh? What exactly is the meaning of the arguments then?

It sounds to me like this might be better thought of as a new
proargmode value, but I'm quite unsure what you're talking about ...

regards, tom lane

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

My mistake. I am sorry. I have to store somewhere flag. One bit, which
signalise "don't use OUT arguments as function's parameters".

Huh? What exactly is the meaning of the arguments then?

It sounds to me like this might be better thought of as a new
proargmode value, but I'm quite unsure what you're talking about ...

My basic idea was:

CREATE FUNCTION aaa(IN a1, OUT a, OUT b)
RETURNS SETOF RECORD AS $$
..

is similar
CREATE FUNCTION aaa(IN a1)
RETURNS SETOF RECORD AS $$

from executor perspective there isn't any difference. But PL languages have
to create only IN variables. It's protection before identifier's name
colision. With special flag I don't need any changes in executor. And small
change in PL compile rutines. Special proargmode can be solution too. I
don't need new column in pg_proc, but have to modify executor and need more
changes in output rutines in PL.

I'll go on the way to spec. proargmode. It's good idea.

Thank You
Pavel Stehule

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