Re: Why select * from function doesn't work when function

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