Thread: Inconsistent behavior on select * from void_function()?

Folks,

This seems wrong to me:

postgres=# create table test1 ( testy int );
CREATE TABLE
postgres=# insert into test1 values ( 5 );
INSERT 0 1

postgres=# create function void_func ( IN theval int ) returns void as $f$
postgres$# update test1 set testy = $1;
postgres$# $f$ language sql;
CREATE FUNCTION                     ^
postgres=# select * from void_func( 9 );void_func
-----------

(1 row)

postgres=# select void_func( 10 ) is null;?column?
----------t
(1 row)

postgres=# create function void_func2( IN theval int )
postgres-# returns void as $f$
postgres$# begin
postgres$# update test1 set testy = theval;
postgres$# return;
postgres$# end;$f$ language plpgsql;
CREATE FUNCTION
postgres=# select * from void_func2(19);void_func2
------------

(1 row)


postgres=# select void_func2(19) is null;?column?
----------f
(1 row)


Why is a function which returns void returning a row?   Why is that row 
NULL if it's a SQL function and empty if it's a PLPGSQL function?

(version 8.2.3)

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

"Josh Berkus" <josh@agliodbs.com> writes:

> postgres=# select * from void_func2(19);
>  void_func2
> ------------
>
> (1 row)
>
>
> postgres=# select void_func2(19) is null;
>  ?column?
> ----------
>  f
> (1 row)
>
>
> Why is a function which returns void returning a row?   Why is that row 
> NULL if it's a SQL function and empty if it's a PLPGSQL function?

Generally you can treat functions that return a data type as if they returned
a set of rows of that data type. I get the impression this is a considered a
quirk of the implementation and not an advertised feature though: 


postgres=# create function foo() returns integer as 'select 1' language sql;
CREATE FUNCTION
postgres=# select foo();foo 
-----  1
(1 row)

postgres=# select * from foo();foo 
-----  1
(1 row)



I can't speak to the handling of IS NULL though. It is a bit curious.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com

Josh Berkus <josh@agliodbs.com> writes:
> Why is a function which returns void returning a row?

Returning a scalar result that happens to be of type VOID is an entirely
different thing from returning a set result that contains no rows.

> Why is that row 
> NULL if it's a SQL function and empty if it's a PLPGSQL function?

I'd say that the SQL function is probably doing the right thing.  It
appears that plpgsql has hacked this specially "for backward
compatibility":
   /*    * Special hack for function returning VOID: instead of NULL, return a    * non-null VOID value.  This is of
dubiousimportance but is kept for    * backwards compatibility.  Note that the only other way to get here is    * to
havewritten "RETURN NULL" in a function returning tuple.    */   if (estate->fn_rettype == VOIDOID)   {
estate->retval= (Datum) 0;       estate->retisnull = false;       estate->rettype = VOIDOID;   }
 

I haven't tested, but I think that diking out this section would make
the result be a null (still of type void).
        regards, tom lane