PostgreSQL Bugs - set returning function

Author

set returning function

Dennis Bjorklund

2005-05-09, 7:23 am

An issue came up on irc. How come that this work:

SELECT generate_series(0,1)
;

but

SELECT foo(0,1);

does not, where foo is my own set returning function, like this example:

CREATE FUNCTION foo(a int, b int)
RETURNS setof int
AS 'BEGIN RETURN NEXT a;
RETURN NEXT b;
RETURN;
END'
LANGUAGE plpgsql;

As far as I can tell the generate_series() example is not supposed to
work either.

Kris Jurka showed me another example that do work in the same way as
generate_series:

CREATE FUNCTION bar(int, int)
RETURNS setof int
AS 'SELECT $1 UNION ALL SELECT $2;'
LANGUAGE sql;

So it seems that it's not just the type that decide how the function can
be used, it's also the language the function is defined in.

Bug?

--
/Dennis Björklund

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Kris Jurka

2005-05-09, 7:24 am

On Mon, 9 May 2005, Dennis Bjorklund wrote:

> [ I can call sql or C SRFs without FROM, but not plpgsql.]

Trying this in pltcl (while knowing nothing about tcl and the docs not
mentioning any srf support) shows:

CREATE FUNCTION tclset() RETURNS SETOF int AS 'return 0' LANGUAGE pltcl;

SELECT * FROM tclset(); -- works

SELECT tclset(); -- goes into an infinite loop

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org

Tom Lane

2005-05-09, 9:24 am

Dennis Bjorklund <db@zigo.dhs.org> writes:
> So it seems that it's not just the type that decide how the function can
> be used, it's also the language the function is defined in.

Yup.

> Bug?

Unimplemented feature.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)

Dennis Bjorklund

2005-05-09, 1:23 pm

On Mon, 9 May 2005, Tom Lane wrote:

>
> Unimplemented feature.

Is

SELECT 42, srf();

the same as

SELECT 42, * FROM srf();

?

In my view the first version is an error. It's not like you can put a
normal table in the select list, so why can we put a set returning
function there? Ie, is it really a feature?

--
/Dennis Björklund

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Tom Lane

2005-05-09, 1:23 pm

Dennis Bjorklund <db@zigo.dhs.org> writes:
> Is
> SELECT 42, srf();
> the same as
> SELECT 42, * FROM srf();
> ?

No.

> In my view the first version is an error. It's not like you can put a
> normal table in the select list, so why can we put a set returning
> function there? Ie, is it really a feature?

To some extent it's a hangover from PostQUEL ... but there are things
you can do with it that you can't currently do with SRFs in FROM.
See for instance the "listchildren" example in the manual:
http://www.postgresql.org/docs/8.0/...l.html#AEN29503

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Sponsored Links