Shouldn't this PostgreSQL function return zero rows?

create-function, postgresql, return-type, sql, user-defined-functions

Solution

That's because your function is broken by design. It should be:

CREATE FUNCTION all_users() RETURNS SETOF users AS
'SELECT * FROM users' LANGUAGE sql STABLE;

Or alternatively, the more flexible form `RETURNS TABLE (...)` like @Clodoaldo posted. But it's generally wiser to use `RETURNS SETOF users` for a query with `SELECT * FROM users`.

Your original function always returns a single value (a composite type), it has been declared that way. It will break in a more spectacular fashion if you insert some rows.

Consider this SQL Fiddle demo.

For better understanding, your function call does the same as this plain SELECT query:

SELECT (SELECT u from users u).*;

Returns:

id     | email
-------+------
<NULL> | <NULL>

The difference: Plain SQL will raise an exception if the subquery returns more than one row, while a function will just return the first row and discard the rest.

As always, details in the manual.

Problem

Given the schema ``` CREATE TABLE users ( id bigserial PRIMARY KEY, email varchar(254) NOT NULL ); CREATE UNIQUE INDEX on users (lower(email)); CREATE FUNCTION all_users() RETURNS users AS $$ SELECT * FROM users; $$ LANGUAGE SQL STABLE; ``` , shouldn't `SELECT * FROM all_users()` (assuming the `users` table is empty) return no rows, not a row with all `null` values? See the SQL Fiddle here: http://sqlfiddle.com/#!15/b5ba8/2

Original source