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

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.

Thanks in advance.

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

> 
> 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
------------------recordcstringanyanyarrayvoidtriggerlanguage_handlerinternalopaqueanyelement
(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