Why do PostgreSQL functions return null columns instead of no rows when the return type is a table name?

Question

If I create a function f that queries a function I think it becomes easier to read if the return type is the name of a table instead of RETURNS TABLE(id integer, name text).

CREATE TABLE users ( id integer, name text );

CREATE OR REPLACE FUNCTION f() 
 RETURNS users 
 AS $$ 
   SELECT * FROM users
   WHERE FALSE 
 $$ 
 LANGUAGE SQL;

But I'm getting weird results when the query in the function is returning zero rows.

SELECT * FROM f();

Expected result

+------+--------+
| id   | name   |
|------+--------|
+------+--------+

Actual result

+--------+--------+
| id     | name   |
|--------+--------|
| <null> | <null> |
+--------+--------+

If there are more columns they will still all be null. If the query in the function returns any rows then it works as expected. I'm not getting this behaviour if I'm using the RETURNS TABLE(...) syntax.

Is there a way to get around this?

I'm using PostgreSQL 9.6

Whopping

Answer 1

That has nothing to do with the return type, but with the fact that you declared the function as returning a single row.

Replace

RETURNS users

with

RETURNS SETOF users

and your function will work as expected.

As your function is, it will return NULLs if the query has no results, and it will return the first row if the query has more than one result rows.

Tripper