FAQ
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

Search Discussions

  • Nigel J. Andrews at Jul 22, 2003 at 7:28 pm

    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
  • Francisco Figueiredo Jr. at Jul 22, 2003 at 7:54 pm

    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
  • Nigel J. Andrews at Jul 22, 2003 at 8:24 pm

    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
  • Francisco Figueiredo Jr. at Jul 22, 2003 at 8:47 pm

    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
  • Nigel J. Andrews at Jul 22, 2003 at 8:56 pm

    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
  • Francisco Figueiredo Jr. at Jul 24, 2003 at 1:51 pm

    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
  • Tom Lane at Jul 24, 2003 at 2:45 pm

    "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
  • Francisco Figueiredo Jr. at Jul 26, 2003 at 12:18 am

    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 at Jul 24, 2003 at 11:19 pm

    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
  • Francisco Figueiredo Jr. at Jul 26, 2003 at 12:27 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJul 22, '03 at 7:15p
activeJul 26, '03 at 12:27a
posts11
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase