Why select * from function doesn't work when function has return type void??

On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote:

Hi all,

I would like to know why does calling a function with select * from
function doesn't work when its return type is set to void.

I'm asking this because I have a code which uses this syntax to add
support for returning resultsets from functions. This way, regardless
the function returns a resultset or a single value, I could do select *
from function and it works very well.

The problem appears when the function has its returns type to void.
I get the following error message:

npgsql_tests=> select * from funcF();
ERROR: function funcf() in FROM has unsupported return type
ERROR: function funcf() in FROM has unsupported return type

where funcF is defined as:

npgsql_tests=> create function funcF() returns void as 'delete from
tablea where field_serial > 5' language 'sql';

CREATE FUNCTION

But it does work if I call it as:

select funcF();

I'd like to know if would be possible to change this behaviour to return
an empty result set with a null value. This way, there would be
consistency in calling all functions regardless of its return type with
select * from function.

Try returning an integer but returning a null for that integer...on the other
hand I see you're using sql as the language and I don't know how that would
work.

Have you looked at plpgsql? Perhaps that is acceptable for you, in which case:

create function funcF ( ) returns integer as '
begin
delete from blah;
return null;
end;
' as language 'plpgsql';

select * from funcF();

I believe that would work but don't quote me :)

--
Nigel J. Andrews

Nigel J. Andrews wrote:

Try returning an integer but returning a null for that integer...on the other
hand I see you're using sql as the language and I don't know how that would
work.

I tried that and it works. I changed the function body to do a query
which returns null. The problem only appears if the return type is void.

Have you looked at plpgsql? Perhaps that is acceptable for you, in which case:

create function funcF ( ) returns integer as '
begin
delete from blah;
return null;
end;
' as language 'plpgsql';

select * from funcF();

Yeap, it works, but you specified integer as the return type :)

I'd like to have the return type as void and be possible to call it with
select * from funcF();

Thanks Nigel.

--
Regards,

Francisco Figueiredo Jr.

------
"My grandfather once told me that there are two
kinds of people: those
who work and those who take the credit. He told me
to try to be in the
first group; there was less competition there."
- Indira Gandhi

On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote:

select * from funcF();

Yeap, it works, but you specified integer as the return type :)

Yes, that's because I knew the void wouldn't work. :]

I'd like to have the return type as void and be possible to call it with
select * from funcF();

I don't believe it is possible. Makes sense since void doesn't really make
sense in that position in the statment.

How's this for an alternative if you really don't want any rows returned:

create function fincF ( ) returns setof integer as '
begin
delete from blah;
return;
end;
' language 'plpgsql';

--
Nigel J. Andrews

Nigel J. Andrews wrote:

On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote:

select * from funcF();

Yeap, it works, but you specified integer as the return type :)

Yes, that's because I knew the void wouldn't work. :]

:)

How's this for an alternative if you really don't want any rows returned:

create function fincF ( ) returns setof integer as '
begin
delete from blah;
return;
end;
' language 'plpgsql';

This works, but what I really want is not to return any rows. I mean,
the problem is not return null, but the error I get if I select * from
voidfunction.

I just wanted void functions behave like others when called as select *
from voidfunction So I dont have to do select voidfunction. :)

--
Regards,

Francisco Figueiredo Jr.

------
"My grandfather once told me that there are two
kinds of people: those
who work and those who take the credit. He told me
to try to be in the
first group; there was less competition there."
- Indira Gandhi

On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote:

How's this for an alternative if you really don't want any rows returned:

create function fincF ( ) returns setof integer as '
begin
delete from blah;
return;
end;
' language 'plpgsql';

This works, but what I really want is not to return any rows. I mean,
the problem is not return null, but the error I get if I select * from
voidfunction.

I just wanted void functions behave like others when called as select *
from voidfunction So I dont have to do select voidfunction. :)

But that last does exactly that. Doesn't even return a null. Give it a quick
go, skip the delete statement obviously, and see. You'll get something like:

?
-------

(0 rows)

--
Nigel J. Andrews

Nigel J. Andrews wrote:

On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote:

How's this for an alternative if you really don't want any rows returned:

create function fincF ( ) returns setof integer as '
begin
delete from blah;
return;
end;
' language 'plpgsql';

This works, but what I really want is not to return any rows. I mean,
the problem is not return null, but the error I get if I select * from
voidfunction.

I just wanted void functions behave like others when called as select *
from voidfunction So I dont have to do select voidfunction. :)

But that last does exactly that. Doesn't even return a null. Give it a quick
go, skip the delete statement obviously, and see. You'll get something like:

?
-------

(0 rows)

Uhmmm, I think I didn't make myself clear. What I mean by void function
wasn't a function which just doesn't return anything. What I meant is a
function created like this:

create function voidfunction returns *void* as [...]

The problem to me is the void in the returns ;)

If you create a function with the returns void above you'll see that if
you do select * from voidfunction it gives you the error I said. But it
works with select voidfunction.

I just wanted it to work with select * from voidfunction too. :)

Thanks Nigel.

--
Regards,

Francisco Figueiredo Jr.

------
"My grandfather once told me that there are two
kinds of people: those
who work and those who take the credit. He told me
to try to be in the
first group; there was less competition there."
- Indira Gandhi

"Francisco Figueiredo Jr." <fxjrlists@yahoo.com.br> writes:

I just wanted void functions behave like others when called as select *
from voidfunction So I dont have to do select voidfunction. :)

It's not only void functions that fail --- I believe the code will
reject any pseudo-type, which includes several things:

regression=# select typname from pg_type where typtype = 'p';
typname
------------------
record
cstring
any
anyarray
void
trigger
language_handler
internal
opaque
anyelement
(10 rows)

regression=#

Some of these might be safe to allow, but some clearly are not.

regards, tom lane

On Thu, 24 Jul 2003, Francisco Figueiredo Jr. wrote:

Nigel J. Andrews wrote:

On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote:

How's this for an alternative if you really don't want any rows returned:

create function fincF ( ) returns setof integer as '
begin
delete from blah;
return;
end;
' language 'plpgsql';

This works, but what I really want is not to return any rows. I mean,
the problem is not return null, but the error I get if I select * from
voidfunction.

I just wanted void functions behave like others when called as select *
from voidfunction So I dont have to do select voidfunction. :)

But that last does exactly that. Doesn't even return a null. Give it a quick
go, skip the delete statement obviously, and see. You'll get something like:

?
-------

(0 rows)

Uhmmm, I think I didn't make myself clear. What I mean by void function
wasn't a function which just doesn't return anything. What I meant is a
function created like this:

create function voidfunction returns *void* as [...]

I knew what you meant but why the insistence on the void return type? All it's
saying is that there isn't any interpretation that can be applied to anything
that may (or may not) be returned from it so what are you trying to gain by
forcing the void type when you're forced into ignoring the result anyway?

The problem to me is the void in the returns ;)

If you create a function with the returns void above you'll see that if
you do select * from voidfunction it gives you the error I said. But it
works with select voidfunction.

Well don't create the function as returning void :)

I just wanted it to work with select * from voidfunction too. :)

I think I did most of mine as returning integer type and the value 1 (just for
something to return).

Nigel Andrews

Tom Lane wrote:

"Francisco Figueiredo Jr." <fxjrlists@yahoo.com.br> writes:

I just wanted void functions behave like others when called as select *
from voidfunction So I dont have to do select voidfunction. :)

It's not only void functions that fail --- I believe the code will
reject any pseudo-type, which includes several things:

regression=# select typname from pg_type where typtype = 'p';
typname
------------------
record
cstring
any
anyarray
void
trigger
language_handler
internal
opaque
anyelement
(10 rows)

regression=#

Some of these might be safe to allow, but some clearly are not.

regards, tom lane

Ohhh, I thought there was only the void type which had this behaviour. :)

So, I will have to use the select voidfunction();

Thanks Tom Lane.

--
Regards,

Francisco Figueiredo Jr.

------
"My grandfather once told me that there are two
kinds of people: those
who work and those who take the credit. He told me
to try to be in the
first group; there was less competition there."
- Indira Gandhi

Nigel J. Andrews wrote:

On Thu, 24 Jul 2003, Francisco Figueiredo Jr. wrote:

Nigel J. Andrews wrote:

On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote:

How's this for an alternative if you really don't want any rows returned:

create function fincF ( ) returns setof integer as '
begin
delete from blah;
return;
end;
' language 'plpgsql';

This works, but what I really want is not to return any rows. I mean,
the problem is not return null, but the error I get if I select * from
voidfunction.

I just wanted void functions behave like others when called as select *

from voidfunction So I dont have to do select voidfunction. :)

But that last does exactly that. Doesn't even return a null. Give it a quick
go, skip the delete statement obviously, and see. You'll get something like:

?
-------

(0 rows)

Uhmmm, I think I didn't make myself clear. What I mean by void function
wasn't a function which just doesn't return anything. What I meant is a
function created like this:

create function voidfunction returns *void* as [...]

I knew what you meant but why the insistence on the void return type? All it's
saying is that there isn't any interpretation that can be applied to anything
that may (or may not) be returned from it so what are you trying to gain by
forcing the void type when you're forced into ignoring the result anyway?

Oh, good. I thought I was being a little confused and could be leading
to some misunderstand. :)

I was faced by this when I received a bug report on Npgsql
(http://gborg.postgresql.org/project/npgsql/bugs/bugupdate.php?554)
about functions with void result wasn't being able to execute.

The problem is that internally, to get support of returning resultsets
from function calls, I was calling all functions with select * from
function. As I could call it this way regardless the function returned
just a single value or a resultset. I wasn't aware of the void type and
as Tom Lane also said now, other pseudotypes. So, I thought it was just
the void type which had this problem, and I was asking about why it
would behave differently from other types. I thougth it could be some
missing type, and would be a easy fix. But now I see it isn't so simple :)

The problem to me is the void in the returns ;)

If you create a function with the returns void above you'll see that if
you do select * from voidfunction it gives you the error I said. But it
works with select voidfunction.

Well don't create the function as returning void :)

:)

I just wanted it to work with select * from voidfunction too. :)

I think I did most of mine as returning integer type and the value 1 (just for
something to return).

Yeah, this also would work.

Thanks Nigel for all your feedback.

--
Regards,

Francisco Figueiredo Jr.

------
"My grandfather once told me that there are two
kinds of people: those
who work and those who take the credit. He told me
to try to be in the
first group; there was less competition there."
- Indira Gandhi